Bloat & vacuum health

Chapter 4 showed the mechanism with a microscope — dead tuples on the page, VACUUM reclaiming them, one old snapshot starving it all. This lesson is the dashboard version: the same facts from pg_stat_user_tables, the view your monitoring should already be scraping.

A> INSERT INTO inventory SELECT g, 10 FROM generate_series(1, 5) g;
INSERT 0 5

A> UPDATE inventory SET qty = qty + 1 WHERE id <= 3;
UPDATE 3

A> SELECT pg_stat_force_next_flush(); -- stats reach the views lazily; force it for the demo
 pg_stat_force_next_flush 
--------------------------
                          
(1 row)

Chapter 4 proved updates leave dead tuples behind; pg_stat_user_tables is where you SEE them:

M> SELECT relname, n_live_tup::int, n_dead_tup::int, last_vacuum IS NULL AS never_vacuumed
   FROM pg_stat_user_tables WHERE relname = 'inventory';
  relname  | n_live_tup | n_dead_tup | never_vacuumed 
-----------+------------+------------+----------------
 inventory |          5 |          3 | t              
(1 row)

VACUUM cleans up — and the same view proves it happened:

A> VACUUM inventory;
VACUUM

M> SELECT relname, n_live_tup::int, n_dead_tup::int, last_vacuum IS NOT NULL AS vacuumed
   FROM pg_stat_user_tables WHERE relname = 'inventory';
  relname  | n_live_tup | n_dead_tup | vacuumed 
-----------+------------+------------+----------
 inventory |          5 |          0 | t        
(1 row)

The one number that must never reach its limit: the database's xid age vs the emergency threshold.

M> SELECT age(datfrozenxid) < current_setting('autovacuum_freeze_max_age')::int AS wraparound_ok
   FROM pg_database WHERE datname = current_database();
 wraparound_ok 
---------------
 t             
(1 row)

Verified against PostgreSQL 18.4 · Run it yourself · Scenario source

Reading the dashboard

Start with n_dead_tup, the "Estimated number of dead rows". It's estimated because it comes from the statistics system rather than a table scan, which is what keeps it cheap enough to poll every minute. Watch its ratio to n_live_tup rather than the raw count: a queue table that's 99% dead tuples explains its own slow scans. When you need exact numbers, the pgstattuple extension scans for real.

last_vacuum and last_autovacuum tell you when cleanup last ran, manually or via the daemon. A hot table whose last_autovacuum is days old is either configured wrong or, more often, blocked by something holding the horizon — cross-check with detector 3.

age(datfrozenxid) against autovacuum_freeze_max_age is the wraparound margin, and the scenario renders it as a boolean on purpose because that's what your alert should be. Once the age reaches the threshold (200 million transactions by default), PostgreSQL forces an anti-wraparound autovacuum on the table, and it does so even if you've turned autovacuum off. Alert at half that margin and you'll never meet the forced pass.

Poll pg_stat_user_tables for the dead-to-live ratio and the age of last_autovacuum on your busiest tables, and bloat announces itself long before disk-full does. When vacuum looks like it "stopped working," the fault is almost never vacuum's — it's the oldest open transaction or an orphaned prepared transaction pinning the horizon. Wraparound stays a boolean rather than a graph to admire: age(datfrozenxid) < autovacuum_freeze_max_age / 2, or a human gets paged.

Further reading

• PostgreSQL docs: pg_stat_all_tables
• PostgreSQL docs: routine vacuuming — including the wraparound section chapter 4 walked through
• The same lesson on MySQL