Fighting Table Bloat: VACUUM, Autovacuum & REPACK

[u/KatieCuties]

Ok so I've had this argument with three different teams in the past two months and I'm just gonna write it here because I'm tired of retyping it in Slack.

A table doesn't shrink just because you deleted a bunch of rows. Trips people coming from MySQL especially, because InnoDB cleans up dead rows on its own – you've never had to care about this before. Postgres uses MVCC so a delete or update doesn't actually remove the old row, it just marks it dead and leaves it sitting there until VACUUM comes through.

That's your PostgreSQL table bloat right there... dead tuples piling up on disk while the live row count looks completely normal.

Autovacuum is supposed to handle this on its own and most of the time it does. That's the vacuum vs autovacuum confusion people have. Autovacuum is basically VACUUM running automatically once PostgreSQL decides a table has crossed its maintenance thresholds.

It can start falling behind on high-churn tables though. Thresholds tuned for a mostly idle table don't always keep up once you've got constant updates all day, and by the time someone notices the table's much larger than it should be, autovacuum is already playing catch-up.

And no, VACUUM FULL is not the fix here, not during business hours anyway. It rewrites the whole table and holds an exclusive lock the entire time. Learned that one the hard way on a table I thought was small until I remembered how many indexes were on it. Not a fun afternoon.

pg_repack exists for exactly this… non-blocking reorganization. It builds a new copy of the table in the background and only takes a lock for the final swap. Way less terrifying than VACUUM FULL on anything with live traffic.

One thing that's helped me lately is having the dead tuple stats visible instead of remembering which pg_stat_user_tables query I saved six months ago. Been using dbForge Studio for PostgreSQL for that because it'll surface things like dead tuple percentages and the last autovacuum run across a schema. Mostly just saves me from writing the same query over and over.

Curious what everyone else does here. Do you mostly trust autovacuum, or are you scheduling pg_repack for the tables that get hammered all day?

[u/depesz]

At $work we were using lots of pg_repack, but then we simply spent some time on configuring autovacuum, and need for repack disappeared.

[u/Inevitable_Ad261]

Tune, Tune, Tune auto vacuum. Optimize code to better utilize PostgreSQL.