You’ve been there. It’s 2 AM. Queries that flew yesterday are crawling today. You check the logs — autovacuum ran on schedule. The table isn’t bloated. The indexes look fine. And yet, something is choking the life out of your database, and you can’t name it.
Here’s what nobody told you: vacuum isn’t a table-level event. It’s a page-level war. And you’ve been fighting it with a sledgehammer when you needed a scalpel.
Vacuum doesn’t care about your table. It cares about the 8 KB pages underneath — and most of them are already clean.
Think about what actually happens when PostgreSQL vacuums a table. It doesn’t sweep through and magically “clean” the whole thing. It walks page by page, looking for dead tuples — the ghost rows left behind by UPDATEs and DELETEs. On a table with millions of rows, maybe a few hundred pages actually have dead tuples worth removing. The rest? Pristine. Untouched. Already fine.
But traditional vacuum doesn’t know that. Or rather, it didn’t used to care. It would scan, lock, and churn through pages that needed nothing — burning I/O, acquiring visibility map bits, and holding locks that your queries were waiting on. You weren’t optimizing. You were paying taxes on clean pages.
The biggest lie in PostgreSQL tuning is that vacuum is a monolithic background janitor. It’s not. It’s a per-page negotiation, and most teams are negotiating blind.
The shift — and this is the part that changes everything — is understanding the visibility map. PostgreSQL tracks, at the page level, whether all tuples on a page are visible to all transactions. If a page is marked all-visible, vacuum can skip it entirely. No scan. No lock. No I/O. This isn’t an optimization. It’s a completely different execution path.
This is why two tables with identical row counts can have wildly different vacuum performance. One has scattered updates — dead tuples spread across thousands of pages, forcing vacuum to touch every one. The other has clustered writes — dead tuples concentrated in a small range, letting vacuum clean up fast and move on. Same table size. Same row count. Radically different cost.
Your vacuum cost isn’t determined by how big your table is. It’s determined by how your writes are distributed across pages.
So what do you actually do with this? You stop asking “is my table bloated?” and start asking “where are my dead tuples concentrated?” You look at the visibility map. You look at fillfactor settings that control how packed pages are before UPDATEs spill to new pages. You look at whether your update patterns are hot — hitting the same rows repeatedly — or cold, spreading dead tuples everywhere.
A table with fillfactor 90 on a hot-update workload keeps dead tuples on the same pages, concentrated and easy to clean. The same table with default fillfactor 100 scatters dead tuples across the entire table, forcing vacuum to walk every page to find a handful of dead rows. One setting. Order-of-magnitude difference in vacuum cost.
Tuning vacuum isn’t about running it more often. It’s about making sure that when it runs, it touches the fewest pages possible.
The teams that get this right don’t fight vacuum. They design their schemas and write patterns so that vacuum barely has to work. They set fillfactor to leave room for HOT updates — updates that stay on the same page and don’t touch indexes at all. They batch deletes so dead tuples cluster together instead of scattering. They monitor the visibility map, not just table size.
The teams that get this wrong? They crank up autovacuum frequency, hoping more vacuum equals less bloat. It doesn’t. More vacuum on a scattered-dead-tuple table just means more I/O, more locking, more disruption — for diminishing returns. They’re polishing a dirty floor instead of stopping the thing that’s making it dirty.
More vacuum is not better vacuum. Better writes are better vacuum.
If you’re a DBA or a developer and you’ve never looked at the visibility map for your hottest tables, you’re flying blind. Run pg_visibility. Look at how many pages are all-visible versus not. If a large table is mostly all-visible but vacuum still takes forever, your dead tuples are scattered — and no amount of autovacuum tuning will fix that. Only schema and write-pattern changes will.
The page is where the war is won. Not the table. Not the database. The page. Eight kilobytes at a time, that’s where PostgreSQL lives or dies. Understand that, and you stop fighting vacuum. You start designing for it.
Stop tuning vacuum like it’s a background process. Start treating it like what it is: a per-page cost that your schema design is quietly controlling.
FAQ
Q: Isn't autovacuum already smart enough to skip clean pages?
A: It is — if the visibility map is properly maintained. But the visibility map only gets set when a page becomes all-visible, and that depends on your write patterns. If dead tuples are scattered, vacuum still has to walk pages to discover there's nothing to clean. The skip happens after the cost, not before it.
Q: So what do I actually change tomorrow morning?
A: Check the visibility map on your top 5 hottest tables using pg_visibility. If you see a high ratio of all-visible pages but slow vacuum times, lower fillfactor to 90 on tables with heavy UPDATE workloads. This concentrates dead tuples and enables HOT updates that skip index maintenance entirely.
Q: Is table-level vacuum tuning completely useless then?
A: Not useless — but it's the blunt instrument. Autovacuum thresholds and cost limits matter, but they're downstream of the real problem. If your dead tuples are scattered across millions of pages, no autovacuum setting will make that cheap. Schema design wins. Tuning just determines how expensive the loss is.