Bloat and Vacuum (in Postgres, that is 😆)

“Vacuum Stories” are pretty much a rite of passage for Postgres Peoples. Mind you, we don’t automatically flinch anymore when you whisper ”VACUUM” into our ears, but it’s still not an automagic thing — You really need to know what’s going on under the hood if you’re doing anything beyond the basics (•)

The underlying issue in Postgres is that when a row needs to be updated,

1. 1. A new version is created that is visible only to the transaction that created it
2. 2. Once the transaction commits, the new row is visible to all subsequent transactions
3. 3. Existing transactions will continue to see the old row
4. 4. When there are no more “existing” transactions, the old row is deleted

The ensuing space-management issues are the proximate cause for most of the vacuum stories out there, and is based on the way Postgres implements MVCC.

PostgreSQL’s approach to this problem is to store new row versions created by UPDATE in basically the same way that it would store a completely new row generated by an INSERT. Once an UPDATE commits, future SQL statements … will see the new row versions, and the old ones will be visible only to statements (or transactions) which are already running. Eventually, there will no longer be anything running which can see the old row versions, at which point they are “dead”. Similarly, when a DELETE commits, there will eventually be nothing running which can see the old row versions, at which point they are likewise “dead”. Similarly, if an INSERT aborts, the new row versions which it inserted go from being visible only to the inserting transaction to being visible to nobody at all, and are thus “dead”, and likewise when an UPDATE aborts, any new row versions which it created are “dead”.

Incidentally, this is a pretty symmetric system, in that a transaction that succeeds (commits) generates just about the same amount of work as one that fail (aborts). I do wonder, however, what percentage of actual workloads are symmetric…

Anyhow, VACUUM (and autovacuum), is the mechanism used to clean up this bloat (••). However, as Robert Haas puts it, “blaming bloat on the deficiencies of VACUUM is like blaming your local garbage collector for the fact that your house is constantly surrounded by dozens of completely full trash barrels. In such a situation, it might be true that the garbage collector should come around a bit more frequently or work a little faster or harder, but maybe part of the problem is YOU. Unless your trash service is exceptionally bad, to have such a large amount of garbage around all the time, you must be generating it at an enormous rate. If you stop throwing so much stuff away, it won’t matter as much how often the garbage collector comes around. It might also help if you put all of the trash in one giant dumpster instead of many separate barrels strewn hither and yon.”

(•) Mind you, that’s true of any database, or, well, anything, now that I think of it 😇
(••) Joe Nelson has an excellent writeup on vacuum/autovacuum in Autovacuum is not the enemy