postgres_dba Files

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.