Vacuum

What VACUUM does

Allow me to outline the duties. VACUUM performs three distinct jobs in a single pass:

• Reclaims dead tuples — scans the table for row versions that are no longer visible to any active transaction and marks that space as available for reuse by future inserts and updates.
• Updates the visibility map — tracks which pages contain only universally-visible rows. This enables index-only scans (the executor can skip heap fetches for pages the visibility map has marked all-visible) and lets future VACUUM runs skip pages that are already clean.
• Freezes old transaction IDs — replaces aging transaction IDs on old rows with a special "frozen" marker. This is what prevents transaction ID wraparound, a failure mode unique to PostgreSQL's MVCC implementation.

-- Standard VACUUM on a single table
VACUUM my_table;

-- VACUUM with verbose output (shows what it did)
VACUUM VERBOSE my_table;

-- VACUUM and update planner statistics in one pass
VACUUM ANALYZE my_table;

Standard VACUUM does not return disk space to the operating system. It marks dead space as reusable within the table file. The file stays the same size on disk, but PostgreSQL will fill those gaps with new data rather than appending to the end. Think of it as clearing rooms for the next guest rather than demolishing the wing.

VACUUM vs VACUUM FULL

This distinction trips up nearly everyone at least once. They share a name, but that is where the resemblance ends.

VACUUM (standard) runs concurrently with normal queries. It acquires only a lightweight lock (SHARE UPDATE EXCLUSIVE) that does not block reads or writes. It marks dead space as reusable but does not physically compact the table. It is safe to run at any time, including production hours.

VACUUM FULL rewrites the entire table to a new file, eliminating all dead space and physically shrinking the table on disk. This requires an ACCESS EXCLUSIVE lock — no other session can read or write the table until the rewrite completes. On a large table, this can mean minutes or hours of downtime.

-- VACUUM FULL rewrites the entire table to reclaim disk space
-- WARNING: acquires an ACCESS EXCLUSIVE lock — no reads or writes
VACUUM FULL my_table;

-- For most cases, prefer pg_repack or pg_squeeze instead
-- They achieve the same result without the heavy lock

The rule of thumb: standard VACUUM is daily housekeeping that should happen continuously. VACUUM FULL is the equivalent of gutting a room and refurnishing it from scratch — an emergency measure for tables that have already bloated beyond what routine cleaning can manage. If you find yourself reaching for VACUUM FULL regularly, the better answer is to fix whatever is preventing standard VACUUM from keeping up. One does not renovate the parlour every week. One tidies it properly the first time.

Autovacuum

PostgreSQL ships with an autovacuum daemon — a member of staff who vacuums the floors on a schedule, unprompted, without being asked. For most tables, autovacuum keeps dead tuple counts low without any manual intervention. It is, if you'll permit me, one of PostgreSQL's better hires.

The trigger formula is straightforward: autovacuum fires when the number of dead tuples in a table exceeds autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor * number of live tuples). With the defaults (threshold 50, scale factor 0.2), a table with 1 million rows gets vacuumed after accumulating roughly 200,050 dead tuples.

-- Key autovacuum parameters (shown with defaults)

-- Minimum dead tuples before a table is vacuumed
autovacuum_vacuum_threshold = 50

-- Fraction of table size added to threshold
autovacuum_vacuum_scale_factor = 0.2

-- How often autovacuum checks for work (seconds)
autovacuum_naptime = 60

-- Max concurrent autovacuum workers
autovacuum_max_workers = 3

-- Cost-based throttling (higher = more aggressive)
autovacuum_vacuum_cost_delay = 2   -- ms pause between batches
autovacuum_vacuum_cost_limit = 200 -- cost units per batch

-- The effective trigger formula:
-- vacuum when dead tuples > threshold + (scale_factor * table rows)
-- For a 1M row table with defaults: 50 + (0.2 * 1,000,000) = 200,050

Autovacuum is also throttled by cost-based delay settings to avoid overwhelming disk I/O. Each vacuum operation accumulates "cost" as it reads and writes pages, and pauses for autovacuum_vacuum_cost_delay milliseconds after reaching the autovacuum_vacuum_cost_limit. A thoughtful arrangement — the cleaning staff pauses when guests are about, so as not to disrupt the proceedings.

When autovacuum falls behind

On write-heavy tables, the default autovacuum settings are too conservative. The staff is diligent, but they have been given a mop for a job that requires a power washer. The symptoms are recognizable:

• Growing dead tuple counts — n_dead_tup in pg_stat_user_tables climbs faster than autovacuum can clean it
• Table bloat — the table is significantly larger on disk than its live data warrants
• Slower sequential scans — dead tuples consume pages that sequential scans still have to read through
• Index bloat — indexes accumulate entries pointing to dead tuples

-- Check vacuum health per table
SELECT
  schemaname,
  relname,
  n_dead_tup,
  n_live_tup,
  round(n_dead_tup::numeric / nullif(n_live_tup, 0) * 100, 2) AS dead_pct,
  last_vacuum,
  last_autovacuum,
  autovacuum_count
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

The fix is almost always per-table tuning rather than changing global settings. Lower the scale factor so vacuum triggers sooner, and optionally raise the cost limit so each vacuum run does more work per wake cycle. You are not changing the staff — you are giving them better instructions.

-- For a write-heavy table, lower the scale factor
ALTER TABLE hot_table SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 1000
);

-- More aggressive freeze to prevent wraparound
ALTER TABLE hot_table SET (
  autovacuum_freeze_max_age = 100000000
);

Key indicators to watch in pg_stat_user_tables: n_dead_tup (current dead tuple count), last_autovacuum (when it last ran), and autovacuum_count (how many times it has run total). If n_dead_tup is consistently high and last_autovacuum is recent, vacuum is running but not keeping up — lower the scale factor. If last_autovacuum is old or null, the threshold may never be reached — lower the threshold.

Transaction ID wraparound

I must be direct about this matter, because it is grave. PostgreSQL uses 32-bit transaction IDs. With a limit of approximately 2.1 billion usable IDs, a busy database can exhaust the range in weeks or months. When the counter wraps around, rows that were committed in the past would suddenly appear to be from the future — effectively invisible. This is not performance degradation. This is catastrophic data loss.

PostgreSQL prevents this through freezing. VACUUM replaces old transaction IDs with a special FrozenTransactionId that is always considered "in the past" regardless of the current counter position. Autovacuum triggers aggressive freezing when a table's oldest unfrozen transaction ID approaches the danger zone (controlled by autovacuum_freeze_max_age, default 200 million).

-- Check how close each database is to wraparound
SELECT
  datname,
  age(datfrozenxid) AS xid_age,
  round(age(datfrozenxid)::numeric / 2147483647 * 100, 2) AS pct_to_wraparound
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

-- Check per-table freeze age
SELECT
  schemaname,
  relname,
  age(relfrozenxid) AS xid_age
FROM pg_stat_user_tables
ORDER BY age(relfrozenxid) DESC
LIMIT 20;

When age(datfrozenxid) approaches 2 billion, you are in serious trouble. PostgreSQL will begin emitting warnings at approximately 40 million transactions remaining. If those warnings go unheeded, it refuses to start new transactions entirely — a protective shutdown that requires manual single-user-mode vacuuming to resolve. The database, in effect, locks the doors and turns away all callers until someone attends to the mess.

Preventing wraparound is simple in principle: make sure autovacuum is running and not blocked by long-running transactions. Long-running transactions hold back the freeze horizon — VACUUM cannot freeze rows that might still be visible to an open transaction. A forgotten BEGIN without a COMMIT in a monitoring session or a stuck replication slot can silently prevent freezing across the entire database. I have seen it happen. A single idle transaction, left open for days by someone who forgot about it, quietly preventing the entire household from being cleaned.

How Gold Lapel relates

Gold Lapel monitors vacuum health as part of its continuous database analysis. It tracks dead tuple ratios, bloat estimates, and transaction ID age across all tables — flagging tables where autovacuum is falling behind before the symptoms reach your queries.

When Gold Lapel detects that a slow query is scanning a bloated table — one where dead tuples are inflating the number of pages the query must read — it factors that into its optimization decisions. A query that looks slow because of a missing index may actually be slow because the table is three times larger than it should be. The distinction matters enormously for choosing the right fix, and it is the sort of thing one only notices if one is paying close attention to the state of every room.

Gold Lapel does not run VACUUM itself. That remains PostgreSQL's responsibility. But it ensures you know which rooms need attention before the dust becomes structural.