Release 7.0

postgres_dba 7.0

34 reports | Tested on PostgreSQL 13–18 | Works with pg_monitor role

New Reports

Corruption checks (c1–c4) — powered by amcheck

Four levels of integrity checking, from quick production-safe to full paranoia:

Report	Lock	What it checks	When to use
c1	AccessShareLock	B-tree pages, GIN indexes (PG18+)	Production — fast, safe, non-blocking
c2	AccessShareLock	c1 + heap/TOAST integrity (PG14+)	Production — safe but reads all data
c3	ShareLock	B-tree parent-child ordering, sibling pointers, rootdescend, checkunique (PG14+)	Clones — detects glibc/collation corruption
c4	ShareLock	Everything in c3 + heapallindexed + verify_heapam with full TOAST	Clones only — proves every heap tuple is indexed, slow

All four check system catalog indexes (pg_catalog, pg_toast).

Requires CREATE EXTENSION amcheck. Graceful handling when extension is missing or user lacks privileges. Version-conditional function signatures for PG11–18. GIN support via gin_index_check() on PG18+.

m1 — Buffer cache contents

What's in shared_buffers: cached size vs total, % of cache per object, dirty buffer counts. Includes system catalogs. Requires pg_buffercache.

s3 — Workload profile by query type

Groups pg_stat_statements by first SQL keyword (SELECT, INSERT, UPDATE, DELETE, etc.). Handles leading block comments (/* ... */) and line comments (-- ...).

t2 — Objects with custom storage parameters

Tables, indexes, and materialized views with non-default reloptions. Flags: disabled autovacuum on large tables, low fillfactor, aggressive vacuum scale factors.

Report 0 — WAL and replication slot info

Node information now includes WAL position, file count, total WAL size, and replication slot status.

Report Renames

Old	New	Reason
b6	m1	Buffer cache → m (memory) category
c1	p1	Index creation progress → p (progress) category
p1	x1	Alignment padding → x (experimental) category

v1/v2 descriptions clarified: v1 is "running operations (detailed progress)", v2 is "autovacuum queue and pending tables".

Bug Fixes

• s1, s2: Fixed blk_read_time does not exist on PG17+ (renamed to shared_blk_read_time in pg_stat_statements 1.11)
• s3: Fixed function round(double precision, integer) does not exist — added ::numeric casts
• i3: Fixed operator is not unique error when intarray extension is installed
• m1: Include system catalogs in buffer cache report (was showing empty on small databases)
• i2: Removed dead code (redundant_indexes_grouped CTE)
• s1: Removed duplicate sum(calls) in pre-PG13 code path

Terminology

Master → Primary across all reports and CI.

Other Improvements

• Modernized README with badges, individual credits, optional extensions table
• Fixed Quick Start psqlrc escaping
• Fixed menu spacing for new reports
• alt_shits → alt_shifts (p1)
• Various typo fixes across b1, b2, b3, b4, l1, s2, v2

CI

• All 34 reports tested on PG 13, 14, 15, 16, 17, 18
• Added amcheck, intarray, pg_buffercache extensions to test matrix
• Added i3 regression test with intarray installed
• Added PAGER=cat to prevent pager hangs

Release 6.0

Two new reports have been added to help with analysis of locking issues and autovacuum monitoring and tuning

1. l1 – Locks: analysis of "locking trees". The report shows "the forest of trees" of blocked and blocking sessions with the corresponding queries. It helps to understand, which session is being blocked by which one, and what is "the depth" of the lock chain.
   • Use it to find the "roots" of trees, then use pg_cancel_backend(pid) or pg_terminate_backend(pid) to get rid of blockers and release the locks.
   • More info and examples: https://gitlab.com/snippets/1890428
2. v2 – Vacuum: VACUUM progress and autovacuum queue. In this report, you can not only see the progress of vacuuming processes, but also see the "queue" of tables that are already "waiting" to be auto-vacuumed
   • The report can be useful in monitoring. Higher, sustained numbers of "waiting" tables are a strong indicator of lack of autovacuum workers, their aggressiveness, and therefore, necessity in proper autovacuum tuning.
   • For the "queue" part, the report analyzes "global" and individual, table-level settings (autovacuum_***) and finds which tables modified significantly enough to be already auto-vacuumed.
   • The "queue" part of the report is applied only to the current database. The "progress" part considers all vacuum processes, including objects from other databases – for them, the table names are extracted from pg_stat_activity.
   • vt means vacuum threshold (autovacuum_vacuum_threshold), vsf means vacuum scale factor (autovacuum_vacuum_scale_factor). Effective values are provided for each table (values that are derived from the analysis of both global and individual settings). Read more in the official documentation: https://www.postgresql.org/docs/current/runtime-config-autovacuum.html.
   • More info and examples: https://gitlab.com/snippets/1889668.

Support of PostgreSQL 12 added (@dmius)

• b1: support Postgres 12. Mirroring the changes made by the original author, @ioguix.
  See: ioguix/pgsql-bloat-estimation@f1f7d13#diff-b405e00f7d75c50f16e920c46fecccbe
• p1: Fix collation error in Postgres 12. The following error fixed:

psql:/root/postgres_dba/sql/p1_alignment_padding.sql:197: ERROR:  recursive query "analyze_alignment" column 13 has collation "default" in non-recursive term but collation "C" overall
LINE 68:     null::text as prev_column_name,
             ^
HINT:  Use the COLLATE clause to set the collation of the non-recursive term.

Discussion in pgsql-bugs: https://www.postgresql.org/message-id/flat/28491.1560519572%40sss.pgh.pa.us#79d963145876231f3ec7327074cbf0c3

Internal: Improve version handling in SQL scripts (@alexeyklyukin)

• Avoid parsing version() output with regexps, as it breaks for
  non-released versions (i.e. devel or beta). Get the value from the
  server_version_num instead. Remove the version fetch from t1_tuning.sql,
  as it seems to serve no purpose there.

5.0: Merge pull request #40 from NikolayS/dmius-i2-improve

tbd

Release 5.0-pre

i* reports (index analysis) reworked.

New reports:

• invalid indexes (i4)
• redundant indexes (i2)
• "do/undo migration for index cleanup" renamed from i2 to i5

Release 4.0

• Changed release numbering schema.
• Added new report: "e1 – List of extensions installed in the current DB".
• Added new report: "t1 – Postgres parameters tuning".
• Added new report: "v1 – Vacuum: Current Activity".
• Removed "Wide" option. Now it's easy to copy/paste reports from ./sql directory to use them manually in Postgres versions 9.6 and older.
• Report "b2 – B-tree Indexes Bloat, rough estimation" now works in DBs with custom schemas (bug reported by @sgrinko #21).
• "i2 – Unused/Redundant Indexes Do & Undo Migration DDL" show all found indexes, limiting was removed
• In "i2 - Unused/Redundant Indexes Do & Undo Migration DDL" print CONCURRENTLY for CREATE INDEX and DROP INDEX statements.
• Improved README with installation instructions.
• Added more information to "0 – Node & Current DB Information: master/replica, lag, DB size, tmp files, etc".
• Various bugfixes and improvements.

More reports!

New reports:

• (report 2) Load Profile, show how many tuples where selected/inserted/updated/deleted in each table, and also aggregated for each tablespace and in total. Additionally, shows sequential scan ratio and HOT-updates ratio;
• (report 3) Activity: shows counters for each database-username pair, and also aggregated values for each DB user and in total;
• (report p1) Alignment Padding (experimental) shows opportunities to optimize table – detects "gaps" between fields in each record and suggests column reordering;

Modifications:

• report 2 "Table Sizes" now shows also tablespace-related information;
• menu was slightly redesigned.

Fixes:

• report 1 "Node Information" now works with Postgres 10.

UX improvements

This is the second "alpha" release. It includes UX improvements:

• reports 2, b1, and b2 were reworked, becoming more human-readable;
• "wide" mode (OFF by default) allows seeing more details. Trade-off: more columns, might not feet your psql window;
• no more requirement "to sit in the PostgresDBA directory" when launching it;
• :dba shortcut instructions in README: faster way to launch PostgresDBA from anywhere.

Very basic version

In this version you can see:

• general DB instance info (master/replica, lag for replica, DB size, cache effectiveness, tmp files count/size, deadlocks, etc)
• table and index (b-tree only) bloat estimation
• find rarely used indexes
• find unused/redundant indexes and generate migration script to DROP them
• list of slowest queries, ordered by Total Time.