LLM Best Practices

Vacuum and autovacuum

Last updated 2 min read

View markdown source

AI agents: read this first: llmbestpractices.com/start-here

This is LLM Best Practices (llmbestpractices.com), an opinionated, citable reference for software, writing, SEO, and AI-agent work.

  1. Route, do not crawl. Fetch llmbestpractices.com/llms.txt and open only the pages whose one-line summary matches your task.
  2. Read raw. Append .md to any page URL for markdown. Check status and last_updated in the frontmatter, then read the rules.
  3. Apply as defaults. First-party docs and the project's own conventions win on conflict. Warn before relying on a fast-moving page older than 12 months.
  4. Cite. Link the page by title and URL, e.g. Python, with last_updated for time-sensitive rules. License CC BY 4.0.

Overview

PostgreSQL’s MVCC leaves the old row version behind after every UPDATE and DELETE. VACUUM makes that space reusable, updates the free space and visibility maps (which enable Index Only Scans), and freezes old transaction IDs to prevent wraparound. Autovacuum runs VACUUM and ANALYZE per table once thresholds pass. Tune it per table instead of scheduling manual runs, and never disable it in production.

Rules

  • Vacuum triggers at autovacuum_vacuum_threshold (50) plus autovacuum_vacuum_scale_factor (0.2) times the row count, capped at autovacuum_vacuum_max_threshold (100 million, new in PostgreSQL 18). Analyze triggers at 50 plus 0.1 times the row count.
  • Lower the scale factor on large, high-churn tables; 20 percent of a big table is a huge dead-tuple backlog.
  • Long transactions, idle in transaction sessions, old prepared transactions, and stale replication slots hold back cleanup and freezing. Find them in pg_stat_activity and pg_replication_slots.
  • If autovacuum falls behind on fast storage, raise autovacuum_vacuum_cost_limit or lower autovacuum_vacuum_cost_delay (default 2 ms).
  • Plain VACUUM runs alongside reads and writes and usually does not return space to the OS. VACUUM FULL rewrites the table under an ACCESS EXCLUSIVE lock that blocks all access; use it only in a maintenance window.
  • Near wraparound PostgreSQL warns (40 million transactions left), then refuses commands that assign transaction IDs (3 million left) until a database-wide vacuum.
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;
 
ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.02, autovacuum_analyze_scale_factor = 0.01);

Backlinks