Shadowgraph
TrendingBuzzResearch
Chrome extension
Sign in

NikolayS/postgres_dba

The missing set of useful tools for Postgres DBAs and all engineers

PLpgSQL

1,293

440 commits

updated Aug 18, 2026

See the code

README

🐘 postgres_dba

CI PostgreSQL 13–18 License: BSD-3

34 diagnostic reports for PostgreSQL, right inside psql. No agents, no daemons, no external dependencies — just SQL.

Bloat estimation, index health, lock trees, vacuum monitoring, query analysis, corruption checks, buffer cache inspection, and more. Type :dba and go.

Demo

Quick Start

git clone https://github.com/NikolayS/postgres_dba.git
cd postgres_dba
bash -c "echo \"\\set dba '\\\\\\\\i $(pwd)/start.psql'\" >> ~/.psqlrc"

Connect to any Postgres server via psql and type :dba.

Requires psql 10+. The server can be any version. For best results, use the latest psql client.

Reports

General (0–3)

IDReport
0Node info: primary/replica, replication lag, database size, temp files, WAL, replication slots
1Database sizes and stats
2Table and index sizes, row counts
3Load profile

Activity & Locks

IDReport
a1Current connections grouped by database, user, state
l1Lock trees (lightweight)
l2Lock trees with wait times (PG14+ pg_locks.waitstart)

Bloat

IDReport
b1Table bloat estimation
b2B-tree index bloat estimation
b3Table bloat via pgstattuple (expensive)
b4B-tree index bloat via pgstattuple (expensive)
b5Tables without stats (bloat can't be estimated)

Corruption Checks (amcheck)

IDLockReport
c1AccessShareLockQuick index check: btree + GIN (PG18+). Safe for production.
c2AccessShareLockIndexes + heap/TOAST (PG14+). Safe but reads all data.
c3⚠️ ShareLockB-tree parent check — detects glibc/collation corruption. Use on clones.
c4⚠️⚠️ ShareLockFull: heapallindexed + parent + heap. Proves every tuple is indexed.

Memory

IDReport
m1Buffer cache contents (pg_buffercache, expensive)

Indexes

IDReport
i1Unused and rarely used indexes
i2Redundant indexes
i3Foreign keys with missing indexes
i4Invalid indexes
i5Index cleanup DDL generator (DO & UNDO)

Vacuum

IDReport
v1Vacuum: current activity
v2Autovacuum progress and queue

Progress

IDReport
p1CREATE INDEX / REINDEX progress

Statements (pg_stat_statements)

IDReport
s1Slowest queries by total time
s2Full query performance report
s3Workload profile by query type

Tuning & Config

IDReport
t1Postgres parameters tuning
t2Objects with custom storage parameters
e1Installed extensions
x1Alignment padding analysis (experimental)
r1Create user with random password
r2Alter user with random password

Optional Extensions

Some reports benefit from additional extensions:

ExtensionReportsInstall
pg_stat_statementss1, s2, s3shared_preload_libraries = 'pg_stat_statements'
amcheckc1, c2, c3, c4CREATE EXTENSION amcheck;
pgstattupleb3, b4CREATE EXTENSION pgstattuple;
pg_buffercachem1CREATE EXTENSION pg_buffercache;

Compatibility

Tested on PostgreSQL 13 through 18 via CI on every commit. Older versions (9.6–12) may work but are not actively tested.

Works with the pg_monitor role — superuser is not required for most reports (corruption checks need superuser or explicit GRANT EXECUTE).

Adding Custom Reports

Drop a .sql file in sql/. The filename format is <id>_<name>.sql. The first line must be a -- comment with the description — it becomes the menu entry automatically.

# Regenerate the menu after adding/removing reports
bash ./init/generate.sh

pspg makes tabular output much easier to read:

\setenv PAGER pspg
\pset border 2
\pset linestyle unicode

Credits

Built on diagnostic queries contributed by many people over the years:

  • Gilles Darold (ioguix) — bloat estimation queries
  • Alexey Lesovsky, Maxim Boguk, Ilya Kosmodemiansky, Andrey Ermakov — pg-utils diagnostic suite
  • Josh Berkus, Greg Smith, Christophe Pettus, Quinn Weaver — pgx_scripts collection

License

BSD 3-Clause

Contact

Nikolay Samokhvalov — nik@postgres.ai

Open an issue for questions, ideas, or bug reports.

dba
postgres
postgres-dba
postgresql
postgres-server
psql

Significant stargazers

Yurii Rashkovskii

822 followers · starred Sep 2023

Oleg Kovalov

656 followers · starred Jun 2022

Matthew Bauer

331 followers · starred Apr 2022

Milton Mazzarri

201 followers · starred Dec 2023

Similar repos

josevalim/ecto_psql_extras

Ecto PostgreSQL database performance insights. Locks, index usage, buffer cache hit ratios, vacuum…

395

lesovsky/pgcenter

Command-line admin tool for observing and troubleshooting Postgres.

1,631

heroku/heroku-pg-extras

A heroku plugin for awesome pg:* commands that are also great and fun and super.

1,352

beh74/pgassistant-community

A PostgreSQL assistant for developers Understand, optimize, and improve your PostgreSQL database…

56

pawurb/rails-pg-extras

Rails PostgreSQL database performance insights. Locks, index usage, buffer cache hit ratios, vacuum…

1,248

abhiraj-ku/iCli

Suggest the indexing strategy for your database

2

pgrundev/pgbot

Postgres intelligence for ai agents

1,381

pgexperts/pgx_scripts

A collection of useful little scripts for database analysis and administration, created by our team…

1,469

NikolayS/postgres_dba

The missing set of useful tools for Postgres DBAs and all engineers

PLpgSQL

1,293

440 commits

updated Aug 18, 2026

See the code

README

🐘 postgres_dba

CI PostgreSQL 13–18 License: BSD-3

34 diagnostic reports for PostgreSQL, right inside psql. No agents, no daemons, no external dependencies — just SQL.

Bloat estimation, index health, lock trees, vacuum monitoring, query analysis, corruption checks, buffer cache inspection, and more. Type :dba and go.

Demo

Quick Start

git clone https://github.com/NikolayS/postgres_dba.git
cd postgres_dba
bash -c "echo \"\\set dba '\\\\\\\\i $(pwd)/start.psql'\" >> ~/.psqlrc"

Connect to any Postgres server via psql and type :dba.

Requires psql 10+. The server can be any version. For best results, use the latest psql client.

Reports

General (0–3)

IDReport
0Node info: primary/replica, replication lag, database size, temp files, WAL, replication slots
1Database sizes and stats
2Table and index sizes, row counts
3Load profile

Activity & Locks

IDReport
a1Current connections grouped by database, user, state
l1Lock trees (lightweight)
l2Lock trees with wait times (PG14+ pg_locks.waitstart)

Bloat

IDReport
b1Table bloat estimation
b2B-tree index bloat estimation
b3Table bloat via pgstattuple (expensive)
b4B-tree index bloat via pgstattuple (expensive)
b5Tables without stats (bloat can't be estimated)

Corruption Checks (amcheck)

IDLockReport
c1AccessShareLockQuick index check: btree + GIN (PG18+). Safe for production.
c2AccessShareLockIndexes + heap/TOAST (PG14+). Safe but reads all data.
c3⚠️ ShareLockB-tree parent check — detects glibc/collation corruption. Use on clones.
c4⚠️⚠️ ShareLockFull: heapallindexed + parent + heap. Proves every tuple is indexed.

Memory

IDReport
m1Buffer cache contents (pg_buffercache, expensive)

Indexes

IDReport
i1Unused and rarely used indexes
i2Redundant indexes
i3Foreign keys with missing indexes
i4Invalid indexes
i5Index cleanup DDL generator (DO & UNDO)

Vacuum

IDReport
v1Vacuum: current activity
v2Autovacuum progress and queue

Progress

IDReport
p1CREATE INDEX / REINDEX progress

Statements (pg_stat_statements)

IDReport
s1Slowest queries by total time
s2Full query performance report
s3Workload profile by query type

Tuning & Config

IDReport
t1Postgres parameters tuning
t2Objects with custom storage parameters
e1Installed extensions
x1Alignment padding analysis (experimental)
r1Create user with random password
r2Alter user with random password

Optional Extensions

Some reports benefit from additional extensions:

ExtensionReportsInstall
pg_stat_statementss1, s2, s3shared_preload_libraries = 'pg_stat_statements'
amcheckc1, c2, c3, c4CREATE EXTENSION amcheck;
pgstattupleb3, b4CREATE EXTENSION pgstattuple;
pg_buffercachem1CREATE EXTENSION pg_buffercache;

Compatibility

Tested on PostgreSQL 13 through 18 via CI on every commit. Older versions (9.6–12) may work but are not actively tested.

Works with the pg_monitor role — superuser is not required for most reports (corruption checks need superuser or explicit GRANT EXECUTE).

Adding Custom Reports

Drop a .sql file in sql/. The filename format is <id>_<name>.sql. The first line must be a -- comment with the description — it becomes the menu entry automatically.

# Regenerate the menu after adding/removing reports
bash ./init/generate.sh

pspg makes tabular output much easier to read:

\setenv PAGER pspg
\pset border 2
\pset linestyle unicode

Credits

Built on diagnostic queries contributed by many people over the years:

  • Gilles Darold (ioguix) — bloat estimation queries
  • Alexey Lesovsky, Maxim Boguk, Ilya Kosmodemiansky, Andrey Ermakov — pg-utils diagnostic suite
  • Josh Berkus, Greg Smith, Christophe Pettus, Quinn Weaver — pgx_scripts collection

License

BSD 3-Clause

Contact

Nikolay Samokhvalov — nik@postgres.ai

Open an issue for questions, ideas, or bug reports.

dba
postgres
postgres-dba
postgresql
postgres-server
psql

Significant stargazers

Yurii Rashkovskii

822 followers · starred Sep 2023

Oleg Kovalov

656 followers · starred Jun 2022

Matthew Bauer

331 followers · starred Apr 2022

Milton Mazzarri

201 followers · starred Dec 2023

Similar repos

josevalim/ecto_psql_extras

Ecto PostgreSQL database performance insights. Locks, index usage, buffer cache hit ratios, vacuum…

395

lesovsky/pgcenter

Command-line admin tool for observing and troubleshooting Postgres.

1,631

heroku/heroku-pg-extras

A heroku plugin for awesome pg:* commands that are also great and fun and super.

1,352

beh74/pgassistant-community

A PostgreSQL assistant for developers Understand, optimize, and improve your PostgreSQL database…

56

pawurb/rails-pg-extras

Rails PostgreSQL database performance insights. Locks, index usage, buffer cache hit ratios, vacuum…

1,248

abhiraj-ku/iCli

Suggest the indexing strategy for your database

2

pgrundev/pgbot

Postgres intelligence for ai agents

1,381

pgexperts/pgx_scripts

A collection of useful little scripts for database analysis and administration, created by our team…

1,469