Postgres Dba Versions Save

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: https://github.com/ioguix/pgsql-bloat-estimation/commit/f1f7d13bb2277bf1b42a8ad730d5bc65948b2f30#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.

tbd

i* reports (index analysis) reworked.

New reports:

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

• 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.

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.

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.

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.