PostgreSQL Autovacuum and Table Bloat: What to Check

Find large tables with dead-row estimates

select
  schemaname, relname,
  n_live_tup, n_dead_tup,
  last_autovacuum, autovacuum_count,
  pg_size_pretty(pg_total_relation_size(relid)) as total_size
from pg_stat_user_tables
order by n_dead_tup desc
limit 25;

n_live_tup and n_dead_tup are estimates. Use them to identify candidates, then investigate the workload and physical size.

Know what regular VACUUM changes

Regular VACUUM makes dead-row space available for reuse and updates visibility information. It normally does not shrink the table file back to the operating system. That is why a table can remain physically large after cleanup while future updates reuse its space.

VACUUM FULL rewrites the table into a new file and takes an ACCESS EXCLUSIVE lock. It can reclaim filesystem space, but it needs temporary disk capacity and blocks normal access. Use it only when reclaiming that space justifies the operational cost.

Understand the trigger calculation

Autovacuum uses a base threshold plus a fraction of the table size. On busy large tables, a percentage-based threshold can allow many dead rows to accumulate before vacuum starts. Current PostgreSQL also supports a maximum threshold that caps the calculated trigger.

Changing global defaults can create unnecessary work across every table. Set per-table storage parameters for a table whose update pattern requires a different threshold, and record why the override exists.

Inspect and tune one busy table

select relname, reloptions
from pg_class
where oid = 'app.events'::regclass;

alter table app.events set (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.02
);

The sample values are not universal defaults. Estimate the table's row-change rate and observe the effect before applying them elsewhere.

Look for work autovacuum cannot finish

A long-running transaction can keep old row versions visible and prevent their removal. An idle in transaction session can cause the same problem even when it is not running a query. Replication slots and prepared transactions can also retain old data.

Before increasing workers or making vacuum more aggressive, inspect old transaction start times and the blocker behind the oldest xmin. Ending a valid long transaction without coordination can be worse than waiting, so identify its owner first.

List old open transactions

select
  pid, usename, application_name, state,
  xact_start, now() - xact_start as transaction_age,
  wait_event_type, wait_event, query
from pg_stat_activity
where xact_start is not null
order by xact_start;

Watch vacuum progress

pg_stat_progress_vacuum shows the phase and block progress for running VACUUM operations, including autovacuum workers. VACUUM FULL appears in pg_stat_progress_cluster because it rewrites the table.

Inspect active VACUUM work

select
  pid, datname, relid::regclass as table_name, phase,
  heap_blks_scanned, heap_blks_total,
  index_vacuum_count
from pg_stat_progress_vacuum
order by pid;

Measure bloat with the right level of confidence

Statistics views provide useful estimates but do not calculate exact reclaimable bytes. Extension-based checks such as pgstattuple can inspect a relation more closely, at additional cost and with required privileges. Sampling variants reduce the scan cost but remain estimates.

Track physical size, dead rows, vacuum frequency, write volume, and query latency together. Use the PostgreSQL monitoring guide to place maintenance signals beside locks, sessions, and slow queries instead of treating table size as an isolated alarm.