Autovacuum is one of those Postgres background jobs that quietly keeps your database healthy. It cleans up the dead row versions that every UPDATE and DELETE leaves behind, and it keeps the database away from a hard transaction-ID limit that would take it offline. Most of the time you don't think about it, because it just works.
Until it doesn't. When autovacuum falls behind, nothing pages you. Tables bloat, queries slow down, the disk fills up, and transaction IDs get closer to wraparound. It's slow and quiet, which is what makes it easy to miss and hard to monitor well. The usual setup is a graph of dead rows piling up and a hope that someone notices when it looks wrong, but that number swings up and down by design, so it can't tell you whether autovacuum is keeping up or falling behind. Simple threshold alerts don't help either. Something like "not vacuumed in 2 hours" or "more than a million dead rows" mostly fires on big, busy tables that are perfectly healthy.
So: let's break it on purpose. We'll reproduce the common ways autovacuum fails, one at a time, and watch each one turn into a clear finding in Coroot (Open Source and Apache 2.0) that names the table, the cause, and the fix. For every signal I'll show which Postgres system view it came from, because that's the whole point. It's all already sitting in data Postgres hands you, you just have to collect the right bits.
A quick refresher: MVCC, dead tuples, and 32-bit transaction IDs
Postgres uses MVCC (Multi-Version Concurrency Control) so that readers never block writers. When you UPDATE a row, Postgres doesn't overwrite it in place. It writes a new version of the row and marks the old one as no longer visible to future transactions. DELETE does the same thing: the row is just marked dead, not physically removed. This is what lets a long-running SELECT keep seeing a consistent snapshot while other transactions modify the same rows.
The catch is that those dead row versions (dead tuples) pile up, and something has to come along later and reclaim the space. That something is VACUUM. It walks a table, finds tuples that are no longer visible to any running transaction, and frees them for reuse. Autovacuum is just Postgres running VACUUM for you automatically, in the background, once a table has accumulated enough dead tuples.
VACUUM has a second, less obvious job. Every transaction gets a 32-bit transaction ID (XID), which wraps around after ~4 billion transactions. Postgres uses the age of an XID to tell the past from the future, so to stop old XIDs from suddenly looking like they're in the future, VACUUM "freezes" old rows, stamping them as permanently visible. If freezing doesn't keep up, the XID age climbs toward the wraparound limit, and Postgres will start emitting warnings and eventually refuse new writes to protect your data. This is the dreaded transaction ID wraparound, and it has caused real outages.
So autovacuum quietly does two critical things: it keeps bloat in check, and it keeps you away from the wraparound cliff. Both fail silently, which is exactly the kind of thing observability is for. (The same daemon also runs ANALYZE to keep the query planner's stats fresh, but that's a latency problem rather than a bloat one, so we'll save it for another post.)
Why "dead tuples" alone is a bad signal
So how do you tell whether autovacuum is keeping up? The obvious answer is to watch the number of dead rows, and that's exactly where most monitoring goes wrong. Almost every Postgres dashboard just graphs n_dead_tup (the dead-row count) and stops there.
The problem is that on a healthy table this number is supposed to go up and down. It climbs as you write, autovacuum cleans up, it drops, and round it goes again. A big number isn't bad by itself. 5 million dead rows in a 500-million-row table is nothing. The same 5 million in a 6-million-row table means half the table is dead weight. The raw count can't tell those two apart, so any alert you set on it is either too noisy or too loose.
Postgres itself doesn't use the raw count either. It decides to autovacuum a table when:
n_dead_tup >= autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * n_live_tup
With the defaults (threshold = 50, scale_factor = 0.2), that's "50 rows plus 20% of the table." The right side of that formula is the number that actually matters. It's the point where autovacuum should kick in. So instead of graphing the raw count, we divide one by the other:
autovacuum pressure = n_dead_tup / (threshold + scale_factor * n_live_tup)
Now the table size cancels out. A healthy table hovers around 1.0. A table stuck at 5 has five times more dead rows than the level where autovacuum should have cleaned it up, so it's falling behind, whether the table is tiny or huge.
Pressure is a great signal, but it has one weak spot. A tiny table can shoot up to a huge ratio and still not matter. So Coroot uses two signals at once. It only raises a finding when a table is both over a pressure threshold (default 2x) and holding a real amount of dead data (at least 512 MiB). Pressure says "this table is genuinely behind." The size check says "and it's big enough to care about." Together they cover each other's weak spot.
Step 1: detect that a table is behind
Everything starts from the pressure signal, and pressure needs three numbers per table: dead tuples, live tuples, and the on-disk size for the materiality gate. All of it comes from pg_stat_user_tables, joined to pg_class:
SELECT s.schemaname, s.relname, s.n_dead_tup, s.n_live_tup,
(s.n_dead_tup::float8 / NULLIF(s.n_live_tup + s.n_dead_tup, 0) * pg_relation_size(s.relid))::bigint,
EXTRACT(EPOCH FROM now() - s.last_autovacuum),
array_to_string(c.reloptions, ',')
FROM pg_stat_user_tables s
JOIN pg_class c ON c.oid = s.relid
WHERE s.n_dead_tup > 0
ORDER BY 5 DESC NULLS LAST
LIMIT 20
That ORDER BY ... LIMIT 20 matters. We don't collect per-table metrics for every table, only the top 20 by dead-tuple bytes, which keeps cardinality sane on databases with thousands of tables. And because we sort by exactly the quantity the check cares about, any table with enough dead data to be a problem is always in that set. A table that isn't in the top 20 by dead bytes isn't the one that's going to page you.
The first three columns are all detection needs. They become:
pg_table_dead_tuples/pg_table_live_tuples: the raw counts, so pressure can be computed on the backendpg_table_dead_tuple_bytes: dead rows in bytes, for the materiality gate and the "how much is reclaimable" number
When pressure stays above the threshold (default 2x) on a table that's also holding a material amount of dead data (≥ 512 MiB), we know something is wrong and raise the check. That's the what. The last two columns of that query (last_autovacuum and reloptions) we'll use in the next step.
Step 2: figure out why
Detecting the problem was the easy half. The half that actually helps is answering why a table is stuck, because the fix is totally different depending on the reason. Good news: there are only a few of them, and each one leaves a fingerprint.
The first cause we already get from Step 1, for free. The reloptions we read there tell us whether autovacuum has been switched off on a table. We expose them as pg_table_autovacuum_setting, a small per-table metric that carries any overrides as labels: autovacuum_enabled, autovacuum_vacuum_scale_factor/threshold, and autovacuum_vacuum_cost_delay/limit.
Step 1 gives us one more thing that every answer below leans on: pg_table_seconds_since_last_autovacuum, how long ago the table was last vacuumed. If a table is behind but was vacuumed just seconds ago, autovacuum obviously is running. It isn't asleep, so something must be stopping it from finishing the job.
The "vacuum horizon" case needs one extra piece: not just that the horizon is stuck, but who is holding it there. Postgres won't delete a dead row while it's still visible to the oldest snapshot anywhere in the system, and that snapshot can come from four different places. We measure how old the oldest one from each source is and emit them all as pg_oldest_xmin_age{holder=...} (the same numbers feed the wraparound check):
SELECT
(SELECT COALESCE(max(age(backend_xmin)), 0) FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL AND backend_type = 'client backend'), -- running_transaction
(SELECT COALESCE(max(age(backend_xmin)), 0) FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL AND backend_type = 'walsender'), -- standby_feedback
(SELECT COALESCE(max(GREATEST(age(xmin), age(catalog_xmin))), 0)
FROM pg_replication_slots), -- replication_slot
(SELECT COALESCE(max(age(transaction)), 0) FROM pg_prepared_xacts) -- prepared_transaction
Four holders, four totally different fixes: an app transaction someone left open, a standby feeding its xmin back through hot_standby_feedback, a replication slot, or a two-phase (prepared) transaction nobody ever committed. So when a table is behind, was vacuumed recently, and one of these is old, we've found our culprit, and the finding says which one.
That leaves two causes, and each needs a query of its own.
Is a vacuum running, and is it being throttled? For that we look at pg_stat_progress_vacuum (the vacuum that's running right now) and join it to pg_stat_activity to check whether the worker is asleep on the cost limiter:
SELECT n.nspname, c.relname, COALESCE(a.wait_event = 'VacuumDelay', false)
FROM pg_stat_progress_vacuum p
JOIN pg_class c ON c.oid = p.relid
JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_stat_activity a ON a.pid = p.pid
One gotcha here that's easy to lose an afternoon to: pg_stat_progress_vacuum.relid only resolves through pg_class inside the same database. Run it on the wrong connection and you get zero rows and no error at all. So the agent runs it per-database. This gives us pg_table_vacuum_in_progress and pg_table_vacuum_throttled.
And how many workers are busy? We just count the backends in pg_stat_activity whose backend_type is 'autovacuum worker' and expose it as pg_autovacuum_workers. Line that up against autovacuum_max_workers and you can see the moment autovacuum runs out of hands.
That's the whole toolkit: one query to spot trouble, a few more signals to explain it. Time to start breaking things.
Setting up the environment
The setup is a Postgres cluster monitored by Coroot, with the cluster-agent collecting the metrics above. To make dead tuples on demand I use a few test tables, wsat_1 through wsat_4, each around 2 million rows (~2 GB) with a throwaway filler column. Rewriting every row turns the whole table into dead tuples in one shot:
UPDATE wsat_1 SET filler = filler; -- 2M rows rewritten, 2M dead tuples
Under normal conditions autovacuum eats those dead tuples within a minute or two and the table's pressure drops back toward 1. For each failure below, we'll sabotage a different part of that loop and watch Coroot call it out.
That’s all for the character limit. To start diving into the five failure incidents and how to solve them, mosey over here for the full blog.
Top comments (0)