LLM Best Practices

Vacuum

2 min read

View markdown source

Definition

PostgreSQL uses Multi-Version Concurrency Control (MVCC): UPDATE and DELETE do not overwrite rows in place. Instead, they mark the old row version as dead (a “dead tuple”) and write a new version. VACUUM reclaims space occupied by dead tuples and makes it reusable. Without VACUUM, tables grow unboundedly with dead tuples.

VACUUM also:

  • Updates the free space map (FSM) so new inserts can reuse reclaimed pages.
  • Updates the visibility map (VM), marking pages as all-visible. This enables Index Only Scans (which skip the heap) and accelerates future VACUUM runs.
  • Advances the oldest transaction ID (freeze), preventing transaction ID wraparound. A database that hits wraparound goes into emergency read-only mode. VACUUM FREEZE advances freezing more aggressively.

VACUUM vs. VACUUM FULL:

  • VACUUM reclaims dead tuple space for reuse within the table but does not shrink the file on disk. It runs concurrently with reads and writes.
  • VACUUM FULL rewrites the entire table to a new file, compacting it. It holds an exclusive lock and blocks all access. Use only when table bloat is severe and a maintenance window is available.

ANALYZE (often combined as VACUUM ANALYZE) updates table statistics for the query planner.

When it applies

Rely on autovacuum for routine maintenance. Run VACUUM ANALYZE tablename manually after large bulk loads or deletions. Monitor bloat with pg_stat_user_tables.n_dead_tup and n_live_tup. If n_dead_tup / n_live_tup > 0.2 on a large table that autovacuum is not keeping up with, tune autovacuum_vacuum_scale_factor for that table.

Example

VACUUM ANALYZE orders;
 
SELECT relname, n_live_tup, n_dead_tup,
       round(n_dead_tup::numeric / nullif(n_live_tup,0) * 100, 1) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;
 
ALTER TABLE high_churn_table SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_analyze_scale_factor = 0.005
);
  • autovacuum - the background daemon that runs VACUUM automatically.
  • sequential-scan - VACUUM visibility map updates help eliminate unnecessary heap fetches.
  • query-plan - VACUUM ANALYZE updates statistics the planner uses.
  • wal-mode - VACUUM writes to the WAL; heavy vacuuming can increase WAL volume.
  • postgres - the Postgres deep-dive.

Citing this term

See Vacuum (llmbestpractices.com/glossary/vacuum).

Graph View

Backlinks