Home / Databases / PostgreSQL

DatabasesTroubleshootingAdvanced

PostgreSQL: finding table bloat and fixing autovacuum lag

Find tables with dead tuples, the long transactions that block vacuum, and transaction ID wraparound risk, then tune autovacuum for each table.

Deepak Bhardwaj2 min read

Applies toPostgreSQL 13–17, Azure Database for PostgreSQL, Amazon RDS/Aurora, OCI Database with PostgreSQL

Symptoms

  • Queries slow down over days or weeks, then speed up again after a manual VACUUM.
  • The table's size on disk keeps growing even though its row count stays flat.
  • Log lines like WARNING: database "x" must be vacuumed within N transactions.

Which tables have the most dead rows?

SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum,
       last_autoanalyze,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 15;

If dead_pct is above about 20% and last_autovacuum is old or NULL, autovacuum isn't keeping up on that table.

What's blocking vacuum?

Vacuum can't remove rows that an open transaction might still see. Look for long-running and idle-in-transaction sessions:

SELECT pid, usename, application_name, state,
       now() - xact_start AS xact_age,
       left(query, 80) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 10;

Other things that hold back the xmin horizon:

SELECT slot_name, active, xmin, catalog_xmin FROM pg_replication_slots;    -- abandoned slots
SELECT gid, prepared FROM pg_prepared_xacts;                               -- forgotten 2PC

Also check hot_standby_feedback = on on replicas, because long queries on a replica hold back vacuum on the primary.

Caution

An inactive replication slot prevents vacuum from cleaning up and also retains WAL until the disk fills. Drop slots you no longer use: SELECT pg_drop_replication_slot('name');

Transaction ID wraparound risk

SELECT datname, age(datfrozenxid) AS xid_age,
       round(100.0 * age(datfrozenxid) / 2000000000, 1) AS pct_to_wraparound
FROM pg_database ORDER BY xid_age DESC;

Above 50%, investigate. Above 75%, run VACUUM (FREEZE, VERBOSE) on the oldest tables during a quiet period.

Tune autovacuum for each table

The default autovacuum_vacuum_scale_factor of 0.2 means a 100 M-row table waits for 20 M dead rows before autovacuum runs. Lower it on large, busy tables:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_vacuum_cost_limit = 2000
);

Server-level settings to review:

Parameter Typical change
autovacuum_max_workers 3 → 5–8 on servers with many busy tables
autovacuum_vacuum_cost_limit Raise it, so vacuum isn't throttled on fast storage
maintenance_work_mem / autovacuum_work_mem 1–2 GB, for fewer index passes
idle_in_transaction_session_timeout Set it, for example 10min, to kill forgotten sessions

Reclaiming space

VACUUM makes space reusable but rarely shrinks the files on disk. To give space back to the OS:

  • VACUUM FULL: rewrites the table under an ACCESS EXCLUSIVE lock, so it needs downtime.
  • pg_repack: rewrites the table online with only brief locks. It's available on most managed services as an extension.
pg_repack -h $HOST -U $USER -d appdb -t public.orders --no-superuser-check

Related fixes