Why architecture matters here
Architecture matters here because vacuum's failure mode is not slowness — it is a cliff. Bloat grows quietly and mostly harmlessly for a long time. Queries get a little slower as sequential scans read more dead pages. Indexes get a little larger. Nobody notices, because each day is only slightly worse than the last. Then a threshold is crossed — the disk fills, or the wraparound protection engages — and the system goes from fine to stopped with no useful warning in between. Systems with cliffs need monitoring designed around the cliff, not around the gradient.
The second reason is that the thing blocking vacuum is usually nowhere near the database. The visibility horizon is cluster-wide: a single transaction left idle in transaction by an application that forgot to commit pins the horizon for every table in the cluster. So does a replication slot for a replica that has been down for a week. So does a prepared transaction from a distributed commit that never resolved. Vacuum runs, works hard, and removes nothing — and the diagnosis requires looking at a connection in an application nobody suspects. That indirection is why bloat incidents take so long to solve.
Third, the defaults are calibrated for tables far smaller than the ones that cause trouble. Autovacuum triggers when dead tuples exceed autovacuum_vacuum_threshold plus scale_factor times the row count, and the default scale factor of 0.2 means 20% dead. On a thousand-row table, that is two hundred tuples and fires constantly. On a billion-row table, it is two hundred million dead tuples before vacuum even starts — by which point the vacuum is enormous, slow, and disruptive. The default is not wrong; it is scale-dependent, and nothing warns you when you leave the scale it was written for.
Finally, understanding what vacuum does not do is what separates a working mental model from a frustrating one. Ordinary vacuum does not return space to the operating system. It marks space reusable by future inserts into the same table. A table that bloated to 100GB and then got a perfect vacuum is still a 100GB file, now mostly empty, and it will stay that way. Returning space requires a rewrite, which requires either an exclusive lock or a tool that carefully avoids one. Teams that expect vacuum to shrink the table conclude vacuum is broken, and then reach for the tool that takes the lock.
The architecture: every piece explained
Dead tuples and the visibility horizon are the foundation. Every tuple carries xmin (the transaction that created it) and xmax (the one that deleted or superseded it). A tuple is dead when its xmax committed and no active snapshot could still see it. That second clause is the horizon: computed as the oldest xmin across all active transactions, all replication slots, all prepared transactions, and — if hot_standby_feedback is on — every query running on every replica. Vacuum can only remove tuples deader than the horizon, which means the horizon is the single number that determines whether vacuum accomplishes anything at all.
The autovacuum launcher and workers are the scheduling layer. The launcher wakes every autovacuum_naptime, checks each table's dead-tuple estimate from the statistics collector against its threshold, and dispatches workers — at most autovacuum_max_workers, which is the setting people raise when vacuum falls behind. That usually fails, because more workers share the same autovacuum_vacuum_cost_limit. The cost limit is a rate budget divided among workers: vacuum accumulates cost points for pages hit, missed, and dirtied, and sleeps when it exceeds the budget. Doubling workers without raising the limit just makes each one half as fast, which is the most common vacuum misconfiguration there is.
Pruning and freezing are two different jobs under one command. Pruning is garbage collection: find dead tuples past the horizon, remove their index entries, mark their line pointers reusable, update the free space map. Freezing is wraparound defence: find live tuples older than vacuum_freeze_min_age and mark them frozen — visible to everyone, forever, immune to XID comparison. Pruning is driven by garbage volume; freezing is driven by transaction-ID age, and it happens whether or not there is any garbage. A read-mostly table with zero dead tuples still needs freezing, which is why autovacuum_freeze_max_age exists and triggers an anti-wraparound vacuum regardless of dead-tuple count.
The free space map is where the reuse-versus-release distinction lives. After pruning, the reclaimed space is recorded in the FSM, and future inserts and updates into that table will use it. This is why a table with steady insert and delete rates reaches a stable size rather than growing forever — the vacuumed space gets reused. But the file does not shrink. The only case where ordinary vacuum returns space is when the dead tuples happen to be at the physical end of the table, where it can truncate. That is luck, not a mechanism, and it is why bloat that has already happened tends to stay.
The rewrite tools handle what vacuum will not. VACUUM FULL rewrites the table compactly and returns the space, while holding an ACCESS EXCLUSIVE lock for the duration — no reads, no writes, on a table large enough to have bloated. It also needs free disk space equal to the compacted table, which is a cruel requirement when the reason you are running it is a full disk. pg_repack achieves the same result by building a copy while triggers capture concurrent changes, then swapping with a brief lock. It needs even more free space and more time, and it is almost always the right answer, because the alternative's lock is measured in hours.
End-to-end flow
Follow a bloat incident. An orders table holds 200 million rows and takes 400 million updates a day as statuses transition. Autovacuum has kept up for a year: dead tuples accumulate, cross the 20% threshold, workers prune, the FSM records the space, subsequent updates reuse it, and the table sits at a stable 180GB. This is the system working exactly as designed, and it produces no signal, which is why nobody has looked at it.
On Tuesday a reporting service deploys a change. It opens a transaction, runs a query, and — through a bug in a connection-pool wrapper — does not commit. The connection sits idle in transaction, holding a snapshot. Nobody notices, because the reporting service works fine; it opens new connections for subsequent queries. One connection, idle, holding a snapshot from Tuesday morning.
Autovacuum continues to run. It fires on the orders table exactly as before, scans the whole table, and removes almost nothing. Every dead tuple created since Tuesday morning is newer than the horizon that idle transaction pins, so every one is potentially visible to it and none can be collected. Vacuum is doing full-table work at full cost and freeing zero pages. The metric that would reveal this — dead tuples not falling after a vacuum completes — is not on anyone's dashboard.
By Thursday the table is 400GB. The FSM has no reusable space, so every update extends the file. Queries slow as scans read twice the pages for the same rows. The index on status has doubled, because entries for dead tuples cannot be removed while the tuples cannot be removed. Someone notices the disk graph, and the first hypothesis is the obvious wrong one: vacuum is not keeping up. They raise autovacuum_max_workers from 3 to 10.
This makes it worse, and the reason is instructive. The cost limit is shared, so ten workers now do the same total work at one-tenth speed each, and all of them still free nothing, because the horizon has not moved. The change costs a round of restarts and buys nothing. The actual diagnosis is one query against pg_stat_activity ordered by xact_start, which shows a transaction open for fifty-one hours. Terminating it moves the horizon forward by two days, and the next autovacuum frees 220GB back into the FSM.
But the file is still 400GB, because vacuum reuses rather than releases. The table will slowly refill its own free space and stay at 400GB indefinitely — twice the size it needs, with every sequential scan paying for it. Getting the disk back needs pg_repack, scheduled for a weekend, with 400GB of free space staged. The permanent fix is three monitors that should have existed beforehand: alert on any transaction open longer than an hour, alert on replication slot lag, and alert on the horizon age itself. Each of these is one query, and any of them would have caught this on Tuesday afternoon rather than Thursday night — which is the difference between a config change and a weekend rewrite.