A station cloakroom clears dead slips. The rack never gets shorter.
That’s VACUUM: dead row versions become free space in their page, listed in the free space map for this table’s new rows.
100M
dead tuples autovacuum waits for on 500M rows
The default trigger is 50 + 20% of rows. At 2,000 updates a second, that’s 14 hours between runs. A per-table 1% runs every ≈42 minutes.
Autovacuum is running on a 500 GB table. Why does each pass take almost four hours?
The throttle
All workers share one cost budget, so more workers just split it. Raise the cost limit instead.
Vacuum runs but removes nothing? Something holds the horizon
- Long or idle-in-transaction sessions
- Replication slots whose consumer is gone
- Standbys with
hot_standby_feedbackon - Prepared transactions from a crashed coordinator
Already 400 GB. How do you shrink it?
Bad: VACUUM FULL
Every read and write waits for the whole rewrite.
Good: pg_repack
Copies rows online, then swaps. Two brief locks, and ≈2× the table in free disk.
Transaction IDs wrap. Old rows must be frozen first.
IDs are 32 bits, compared on a circle. About 2.1 billion transactions later, an unfrozen row looks like the future, so it turns invisible.
MySQL has no VACUUM. Does InnoDB escape all this?
In InnoDB
Purge threads clean undo, but long transactions still grow the history list. 6-byte IDs last ≈89 years at 100,000 writes/s — no wraparound in practice.
Remember
VACUUM makes space reusable; it doesn’t shrink files. Tune big tables, clear horizon holders, watch XID age, repack online.