Autovacuum and Bloat: Keeping Postgres From Grinding to a Halt
What MVCC actually costs you, why dead rows pile up, and the settings that keep autovacuum ahead of them

Contents
A Postgres instance I’d been ignoring for the better part of a year finally told on itself with a query that used to take twenty milliseconds taking four seconds instead. Nothing about the query had changed. The row count hadn’t grown unreasonably. What had happened, I discovered after an hour of increasingly confused EXPLAIN ANALYZE output, was that the table had quietly grown to four times its logical size, almost all of it dead rows that update and delete had left behind and that autovacuum had fallen behind on clearing. The fix took ten minutes. Understanding why it happened took rather longer, and it’s worth the detour, because bloat is one of those Postgres failure modes that’s invisible until it isn’t.
Why Postgres makes dead rows in the first place
Postgres uses MVCC — multi-version concurrency control — to let readers and writers work on a table simultaneously without blocking each other. The mechanism is elegant: when you UPDATE a row, Postgres doesn’t modify it in place. It writes an entirely new row version and marks the old one as expired, because some other transaction that started before your update might still need to see the row as it was. A DELETE behaves the same way — the row isn’t actually removed, just marked as no longer visible to new transactions. This is why Postgres can give every transaction a consistent snapshot of the database without a locking nightmare, and it’s a genuinely good design. The cost is that every update and delete leaves a corpse behind: a dead tuple, still physically occupying space on disk, that no longer means anything to anyone once no transaction can still see it.

Left alone, dead tuples accumulate. A table with a high churn rate — rows updated frequently, a queue table where rows are inserted and deleted constantly, a sessions table that’s rewritten on every request — generates dead tuples fast enough that without something actively reclaiming that space, the table’s on-disk size grows without bound relative to its actual row count. That gap between logical size (how much data you’d expect) and physical size (how much disk it’s actually consuming) is what “bloat” means.
What autovacuum is supposed to do about it
Autovacuum is Postgres’s background process for reclaiming dead tuples. It doesn’t shrink the file on disk in the general case — that’s a separate, more disruptive operation — but it marks the space dead tuples occupied as free for reuse by future inserts and updates, which is usually enough, because a table in steady state churning through the same rows over and over doesn’t need to shrink, it needs its dead space recycled rather than endlessly growing.
The trigger for autovacuum to run on a given table is a formula, not a fixed number of dead rows:
| |
The defaults are autovacuum_vacuum_threshold = 50 and autovacuum_vacuum_scale_factor = 0.2, meaning autovacuum waits until 20% of a table’s rows are dead before it bothers vacuuming. For a small table that’s fine — 20% of a few thousand rows is nothing. For a table with fifty million rows, autovacuum waits for ten million dead tuples to accumulate before it acts, and by the time it starts, the vacuum itself has an enormous amount of work queued up, competing for I/O with your actual workload the whole time it runs. This scale-factor design is the single most common reason autovacuum “isn’t working” on a large, hot table — it’s working exactly as configured, and the configuration was written for tables far smaller than the ones a real workload accumulates.
Tuning it before it becomes an incident
The fix is lowering the scale factor, and doing it per-table for your hottest tables rather than globally, since a blanket change affects every table including the small ones that were fine already:
| |
Dropping the scale factor to 2% means autovacuum kicks in on a fifty-million-row table after a million dead tuples instead of ten million — smaller, more frequent vacuums instead of one enormous one, which is both easier on I/O at any given moment and keeps the table’s actual bloat ratio far lower on an ongoing basis. The autovacuum_vacuum_cost_limit increase alongside it matters just as much: autovacuum throttles itself by design, so it doesn’t starve your real queries of I/O, but the default throttle is conservative enough that on a dedicated homelab box with spare I/O capacity, it’s leaving performance on the table. Raising the cost limit lets autovacuum work faster once it decides to run, at the cost of a slightly bigger momentary I/O hit — a trade worth making on hardware that isn’t shared with anything else.
For visibility into whether a table actually needs this treatment, pg_stat_user_tables tells you directly:
| |
A dead_pct sitting comfortably under 10% on your busiest tables is healthy. A table climbing past 30–40%, or a last_autovacuum that’s suspiciously old given how much write traffic the table sees, is the early warning I wish I’d been watching for on the table that eventually cost me an afternoon.
Autovacuum falling behind, and why it happens
The formula tuning above assumes autovacuum runs when it’s supposed to. Several things stop it from keeping up even with reasonable settings:
- Long-running transactions. A transaction that’s been open for hours — a forgotten
BEGINin a psql session, a batch job that opens a transaction and does slow work inside it — prevents Postgres from reclaiming any dead tuple that’s newer than that transaction’s start, because the open transaction might still need to see it. One stuck transaction can single-handedly stall vacuum progress on every table in the database, not just the one it touched. - Not enough autovacuum workers.
autovacuum_max_workersdefaults to 3, which is genuinely too few once you have more than a handful of large, actively-churning tables, since each worker can only vacuum one table at a time and a big table can occupy a worker for a long stretch. maintenance_work_memtoo small. Vacuum uses this memory to track dead tuple locations before it goes back and cleans up indexes; too little of it forces multiple passes over the table’s indexes instead of one, multiplying the I/O cost of the same vacuum run.
| |
Anything in that output running for hours with no good reason is worth killing with pg_terminate_backend() before you touch a single autovacuum setting, because no amount of tuning outruns a transaction that’s holding the door open.
The transaction ID wraparound problem hiding behind all of this
There’s a second, scarier reason vacuum matters that has nothing to do with disk space: Postgres identifies each transaction with a 32-bit transaction ID, and that number space, while enormous, is finite. Vacuum’s other job — separate from reclaiming dead tuples — is “freezing” old rows so Postgres stops needing to compare their original transaction ID against the current one at all, which is what keeps the ID counter from ever actually running out. If autovacuum is disabled, or so starved by the problems above that it never completes a freeze pass across a database, Postgres eventually forces the issue: it stops accepting new transactions entirely rather than risk transaction ID wraparound corrupting data. That’s a full outage.
| |
Postgres starts warning in the logs once age(datfrozenxid) passes 200 million, and forces single-user mode around 2 billion. Any homelab instance old enough to have accumulated real history is worth checking here at least once — it’s the one autovacuum failure mode that doesn’t announce itself with a slow query first, and the settings that fix the bloat problems above (lower scale factors, more aggressive workers) are the same settings that keep freeze passes running often enough that wraparound never becomes a live concern.
When bloat has already won: VACUUM FULL and pg_repack
If a table is already badly bloated — the dead_pct query above showing something absurd, or disk usage that’s grown well past what the row count justifies — autovacuum’s normal mode won’t shrink the file, only stop it growing further. VACUUM FULL does reclaim the space, but it works by rewriting the entire table into a new file and takes an ACCESS EXCLUSIVE lock for the duration, which on any table your application still queries means a full outage until it finishes. On a genuinely large table that’s an unacceptable trade for a homelab service anyone’s actually using.
pg_repack solves the same problem without the outage: it builds a new copy of the table in the background using triggers to capture concurrent changes, then swaps it in with only a brief lock at the very end.
| |
Worth reaching for pg_repack specifically when a table has grown 2–3x its logical size and you need the disk back or the physical bloat is itself now the performance problem — index scans on a bloated table walk far more pages than the row count justifies, even once vacuum has stopped new bloat from accumulating.
Troubleshooting
autovacuum: table "x": removed N row versions never appears in logs for a table you know is churning. Check log_autovacuum_min_duration — if it’s set high or disabled, autovacuum is running but not logging unless a run exceeds the threshold. Set it low (a few hundred milliseconds) temporarily to confirm autovacuum is actually touching the table at all.
Vacuum runs constantly but bloat keeps climbing anyway. This usually means the insert/update/delete rate has simply outgrown even an aggressively-tuned autovacuum’s throughput on the hardware it’s running on. Check autovacuum_vacuum_cost_limit and autovacuum_vacuum_cost_delay together — if the delay is non-zero and the limit is still conservative, autovacuum is throttling itself below what your write rate needs, and the fix is raising the limit further or lowering the delay, not adding more workers to a single table that can only ever have one worker on it at a time.
Index bloat persists even after table vacuum looks healthy. Regular vacuum reclaims space within an index but, like table vacuum, doesn’t shrink the index file itself. REINDEX CONCURRENTLY (available since Postgres 12) rebuilds an index without the exclusive lock older versions required, and is the right tool once pg_stat_user_indexes shows an index that’s grown disproportionately to the table it serves.
A table was just bulk-loaded or bulk-deleted and queries are slow immediately after. Autovacuum’s threshold-based trigger means it may not react to a single enormous bulk operation as quickly as a manual VACUUM ANALYZE would. After any bulk load, truncate-and-reload, or mass delete, running VACUUM ANALYZE on that table by hand immediately is worth the few seconds it costs, rather than waiting for autovacuum’s scale factor to notice.
Is it worth tuning, or is the default fine?
For a small homelab database — a handful of low-traffic services, a table that gets a few hundred writes a day — the defaults are genuinely fine, and there’s no reason to go hunting for a problem that doesn’t exist yet. The tuning in this post earns its keep specifically on tables with real churn: queue tables, session stores, anything an application updates or deletes from constantly rather than mostly appending to. If you’re running Postgres behind Supabase or any other stack with a busy sessions or events table, check pg_stat_user_tables now rather than after a query that used to be instant starts taking seconds — bloat is one of the few Postgres problems that’s completely painless to prevent and mildly painful to fix after the fact, and the gap between those two states is a five-minute ALTER TABLE. Pair it with the rest of the settings in Postgres tuning for homelabbers and a database sized to its actual hardware rarely surprises you again.




