PostgreSQL Does Not Delete Rows. It Hides Them.
When you DELETE a row in PostgreSQL, it is not removed. It is marked as dead — invisible to future transactions but still occupying space on disk. The same happens with every UPDATE: the old version of the row becomes dead, the new version is written alongside it.
VACUUM is the process that cleans up dead tuples and makes the space reusable. Autovacuum does this in the background automatically — but it can fall behind on tables with heavy write activity, and it can be blocked by long-running transactions.
When autovacuum falls behind, tables bloat. Scans slow down because PostgreSQL reads pages full of dead rows to find live ones. And at the extreme end — after 2 billion transactions — PostgreSQL must perform an emergency freeze to avoid transaction ID wraparound, which can take a database offline.
This script shows you where vacuum is healthy, where it is struggling, and which tables are at risk.
Loading…
Reading the Results
Vacuum and Bloat Stats (pg_stat_user_tables)
| Column | What It Tells You |
|---|---|
schemaname / relname | Table location |
n_live_tup | Estimated live (visible) rows |
n_dead_tup | Estimated dead rows not yet cleaned |
dead_ratio | Dead tuples as a percentage of total — above 10-20% needs attention |
last_vacuum | When a manual VACUUM last ran on this table |
last_autovacuum | When autovacuum last ran |
last_analyze | When statistics were last collected |
last_autoanalyze | When autovacuum last collected statistics |
vacuum_count | Total manual vacuum runs since stats reset |
autovacuum_count | Total autovacuum runs since stats reset |
Transaction ID Wraparound Risk
| Column | What It Tells You |
|---|---|
relname | Table name |
age | Age in transactions since the table was last frozen |
max_age | The autovacuum_freeze_max_age threshold (default 200 million) |
pct_to_emergency | How close this table is to forcing an emergency vacuum (%) |
What to Watch For
| Signal | What It Means |
|---|---|
dead_ratio > 20% on a large table | Autovacuum is not keeping up — the table is bloating |
last_autovacuum is null or weeks ago | Autovacuum has never run or is being prevented |
pct_to_emergency > 50% | Transaction ID wraparound risk — escalate immediately |
Tables with no last_analyze | Query planner is working with no statistics — plans will be bad |
High n_dead_tup after a bulk delete | A large delete was not followed by vacuum — run VACUUM manually |
| autovacuum never running on a busy table | A long-running transaction may be blocking vacuum — check pg_stat_activity |
Tuning Autovacuum for Busy Tables
The default autovacuum thresholds are designed for average tables. High-traffic tables need tighter settings:
Loading…
For tables at wraparound risk, run an immediate freeze:
Loading…
This is safe to run on a live table and does not block normal reads or writes.
Gareth Winterman