PostgreSQL Performance Boost: Understanding Vacuum, Analyze, and Bloat

Maintaining peak PostgreSQL database performance means understanding the crucial processes of vacuuming and analysis. Left unchecked, dead tuples (deleted or updated rows) can lead to table bloat, slowing queries and wasting space. This article explains a PostgreSQL script that reveals bloat, shows how VACUUM and ANALYZE optimize your database, and offers essential insights.

Sample Code from Command Line

 1SELECT
 2 schemaname,
 3 relname,
 4 n_live_tup,
 5 n_dead_tup,
 6 n_dead_tup / GREATEST(n_live_tup + n_dead_tup, 1)::float * 100 AS dead_percentage,
 7 n_mod_since_analyze,
 8 last_vacuum,
 9 last_autovacuum,
10 last_analyze,
11 last_autoanalyze,
12 vacuum_count,
13 autovacuum_count,
14 analyze_count,
15 autoanalyze_count
16FROM
17 pg_stat_user_tables
18ORDER BY
19 n_dead_tup DESC,
20 n_mod_since_analyze DESC,
21 last_vacuum DESC,
22 last_analyze DESC,
23 last_autovacuum DESC,
24 last_autoanalyze DESC;

Postgres Pre 9.4 SQL

 1SELECT
 2  schemaname,
 3  relname,
 4  n_live_tup,
 5  n_dead_tup,
 6  n_dead_tup / GREATEST(n_live_tup + n_dead_tup, 1)::float * 100 AS dead_percentage,
 7  -- not available on PostgreSQL <= 9.3
 8  --n_mod_since_analyze,
 9  last_vacuum,
10  last_autovacuum,
11  last_analyze,
12  last_autoanalyze
13  -- not available on PostgreSQL <= 9.0
14  --vacuum_count,
15  --autovacuum_count,
16  -- not available on PostgreSQL <= 9.0
17  --analyze_count,
18  --autoanalyze_count
19FROM
20  pg_stat_user_tables
21ORDER BY
22  n_dead_tup DESC,
23  last_vacuum DESC,
24  last_analyze DESC,
25  last_autovacuum DESC,
26  last_autoanalyze DESC;

Postgres Pre 9.1 SQL

 1SELECT
 2  schemaname,
 3  relname,
 4  n_live_tup,
 5  n_dead_tup,
 6  n_dead_tup / GREATEST(n_live_tup + n_dead_tup, 1)::float * 100 AS dead_percentage,
 7  last_vacuum,
 8  last_autovacuum
 9  -- not available on PostgreSQL <= 9.0
10  --vacuum_count,
11  --autovacuum_count
12FROM pg_stat_user_tables
13WHERE
14  n_dead_tup > 0
15ORDER BY
16  n_dead_tup DESC,
17  last_vacuum DESC,
18  last_autovacuum DESC;

Notes PostgreSQL Analyze info for tables with rows modified since last analyze. Tested on PostgreSQL 9.4+, 10.x, 11.x, 12.x, 13.0.

Code Breakdown:

• SELECT: Chooses specific columns from the pg_stat_user_tables view. Key columns include:
  • schemaname: The schema containing the table.
  • relname: The table name.
  • n_mod_since_analyze: Approximate rows modified since the last ANALYZE.
  • last_analyze: Timestamp of the last manual ANALYZE.
  • last_autoanalyze: Timestamp of the last autoanalyze run.
  • analyze_count: Total manual ANALYZE executions.
  • autoanalyze_count: Total autoanalyze executions.
• FROM pg_stat_user_tables: Specifies the view providing statistics on user tables.
• ORDER BY: Sorts results primarily by rows modified since analysis, and secondarily by analysis timestamps.

Purpose

This script's primary purpose is to help DB administrators:

1. Pinpoint Bloat: Tables with high n_dead_tup and dead_percentage are prime candidates for VACUUM.
2. Target ANALYZE: Large n_mod_since_analyze values indicate outdated statistics needing refresh.
3. Monitor Maintenance: Shows when VACUUM and ANALYZE processes ran last, both manual and automated.

Insights and Explanations

• MVCC and Bloat: PostgreSQL's MVCC (Multiversion Concurrency Control) creates dead tuples, leading to bloat if not vacuumed.
• VACUUM to the Rescue: VACUUM reclaims space from dead tuples and updates visibility information.
• ANALYZE for Query Planning: Accurate statistics from ANALYZE help the query planner make smarter decisions.

References:

PostgreSQL VACUUM docs: https://www.postgresql.org/docs/current/sql-vacuum.html

PostgreSQL ANALYZE docs: https://www.postgresql.org/docs/current/sql-analyze.html

Source:

https://github.com/HariSekhon/SQL-scripts/blob/master/postgres_last_vacuum_analyze.sql

https://github.com/HariSekhon/SQL-scripts/blob/master/postgres_last_vacuum_analyze_pre94.sql

https://github.com/HariSekhon/SQL-scripts/blob/master/postgres_last_vacuum_pre91.sql