Let's break autovacuum in Postgres: reproducing failures to make it observable | Coroot Blog

Skip to main content<br>Customer PortalToggle menu

← All postsEngineering<br>Let's break autovacuum in Postgres: reproducing failures to make it observable<br>Nikolay Sivko<br>July 21, 202621 min read

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 do the thing we always do at Coroot: 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 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...