PostgreSQL Has a Hoarding Problem
Imagine a notebook where you can never erase anything. When you need to update a line, you cross it out and write the new version on the next blank line. When you delete something, you just cross it out. The notebook keeps growing, filled with crossed-out lines that nobody needs anymore.
That's exactly how PostgreSQL works. Due to its MVCC (Multi-Version Concurrency Control) implementation, PostgreSQL never modifies a row in place. Every UPDATE creates a new version of the row. Every DELETE marks the old version as dead. But the dead versions stay right there on disk, taking up space and slowing down scans.
VACUUM is PostgreSQL's cleanup crew. It walks through tables, identifies dead row versions that no active transaction could possibly need, and marks that space as reusable. Without VACUUM, your tables would grow without bound, even if the actual live data stayed the same size.
This isn't optional housekeeping. It's a critical process. If VACUUM falls behind, your database gets slower, larger, and eventually hits a hard wall called transaction ID wraparound, a failure mode that can bring your entire system down.
Why Dead Rows Exist. MVCC in 60 Seconds
PostgreSQL uses MVCC so that readers don't block writers and writers don't block readers. Every row has two hidden columns: xmin (the transaction ID that created this version) and xmax (the transaction ID that deleted or replaced it).
When you UPDATE a row:
- PostgreSQL marks the old version with
xmax= current transaction ID - PostgreSQL creates a new row version with
xmin= current transaction ID - Both versions now exist on disk
When you DELETE a row:
- PostgreSQL sets
xmaxon the existing row - The row is now "dead" but still physically present
Why keep dead rows? Because some other transaction might still be reading them. Transaction 100 started a query before Transaction 105 updated a row. Transaction 100 should see the old version, that's what MVCC guarantees. So both versions must coexist until Transaction 100 finishes.
The live row count is 200,000 on every line. Nothing was inserted and nothing was deleted. All that happened between the lines is three ordinary UPDATE statements touching every row.
Watch the dead count and the file size. Each update leaves behind a complete old version of every row it touched, so the dead count rises by 200,000 each time and the file roughly doubles and then keeps growing. 52 MB becomes 198 MB while the data you actually have stays exactly the same size. That is bloat, and it is not a bug. It is the direct consequence of never modifying a row in place.
Then VACUUM runs and the dead count drops to zero. That part people expect.
The file is still 198 MB, and that part they do not. VACUUM marks the dead space reusable by future inserts and updates in that table. It does not hand the space back to the operating system, so the file does not shrink. A table that ballooned during a one-off bulk update stays large, and future writes fill the gaps instead of extending the file.
Getting the space back is a different operation. VACUUM FULL rewrites the table into a new compact file and does return the space, and it takes an exclusive lock for the duration, which on a large table in production is an outage. pg_repack does the same job without the long lock and is the usual answer.
But once every transaction that could see the old version has completed, the dead row is pure waste. That's where VACUUM comes in.