Reference · Cheat sheet
Lesson 0002 distilled — who removes dead tuples, when autovacuum fires, and how HOT skips the indexes. Built to print.
Plain VACUUM: removes dead tuples, marks space reusable inside the table. Does not return space to the OS. No blocking lock. Routine.
VACUUM FULL: rewrites the whole table with no dead space → shrinks the file, but takes an ACCESS EXCLUSIVE lock (blocks everything). Last resort, maintenance window only.
Automates VACUUM + ANALYZE. A table is vacuumed when dead tuples exceed:
Defaults: base 50, scale factor 0.2 → ~20% of rows dead. (Recent versions add a Minimum() cap.)
Tuning: big hot tables → lower autovacuum_vacuum_scale_factor (often per-table) so they vacuum sooner.
An UPDATE avoids touching indexes when both hold:
1 · no indexed column changes 2 · room on the same page.
Then: new version chained in-page, no new index entries, and the chain can be pruned on ordinary reads (not just VACUUM).
Raise the hit rate: lower fillfactor below 100 to leave page room. Watch n_tup_hot_upd vs n_tup_upd.
Every index is secondary → a normal new tuple needs a new entry in every index = write amplification + index bloat. HOT is the escape hatch.
| Aspect | InnoDB | Postgres |
|---|---|---|
| Old-version cleanup | auto purge | autovacuum |
| Trigger | continuous | ~20% dead |
| Shrinks file? | n/a | only VACUUM FULL |
| Index-free update | in place | HOT |