Database field guide · 30

PostgreSQL Vacuum

Cleanup, freezing, visibility, and autovacuum capacity

Operate PostgreSQL vacuum as a correctness and throughput service driven by tuple churn, snapshot horizons, and transaction age.

Franck Pachot7 chaptersPostgreSQL · vacuum · autovacuum · freezing

Build the mental model before choosing the mechanism.

This minibook was AI-generated from Franck Pachot's archived blog posts. Links to the original articles are included for source context and verification.

Vacuum follows MVCC

Updates and deletes leave tuple versions that may remain visible to older snapshots. Vacuum can remove or mark them reusable only after every relevant visibility horizon has passed.

Dead to one snapshot is not removable for all snapshots.
Long transactions delay cleanup across relations.
Vacuum is a consequence of MVCC, not optional housekeeping.

Ordinary vacuum reuses space

VACUUM prunes dead tuples, updates free-space information, cleans indexes when needed, and maintains visibility-map state while normal traffic continues. It usually does not return heap files to the operating system.

Reused space can stabilize a table without shrinking its file.
Index cleanup has its own cost and thresholds.
VACUUM FULL is a rewriting operation with stronger locks.

Autovacuum is a capacity system

Workers, launcher scheduling, cost limits, I/O bandwidth, and table-level thresholds determine whether cleanup keeps pace with change. Defaults describe general-purpose behavior, not every large table.

Tune from tuples changed per unit time.
Reserve enough workers for the active churn set.
Watch queueing and duration, not only last-autovacuum timestamps.

Scale factors grow with the table

A percentage trigger allows more dead tuples as a table grows. For a large, frequently updated relation, a small per-table scale factor and meaningful fixed threshold bound cleanup debt.

Calculate the trigger in tuples.
Tune analyze and vacuum independently.
Partitioning creates separate maintenance units.

Freezing prevents identifier ambiguity

PostgreSQL freezes sufficiently old tuple transaction identifiers so transaction-ID wraparound cannot make old rows appear to be in the future. Anti-wraparound vacuum is correctness work and can override normal throttling.

Track age(relfrozenxid) by database and relation.
Do not cancel anti-wraparound work casually.
Prepared transactions and replication slots can retain horizons.

Visibility work improves reads

Vacuum sets all-visible and all-frozen bits after proving page state. All-visible pages let index-only scans avoid heap visibility checks, connecting maintenance throughput directly to read performance.

Writes clear all-visible state.
Inspect Heap Fetches in index-only plans.
Use visibility-map evidence to test the hypothesis.

Field manual

Concrete mechanics, diagnostic evidence, and executable patterns to carry into a real system.

Calculate table triggers

Autovacuum starts from a fixed threshold plus a fraction of relation tuples. Calculate the effective trigger instead of discussing the scale factor alone.

vacuum_trigger = autovacuum_vacuum_threshold
               + autovacuum_vacuum_scale_factor * reltuples
# Compare this with dead tuples generated per minute and vacuum duration.

Find retained horizons

Old transactions, prepared transactions, replication slots, and standbys can hold back cleanup. Identify the oldest horizon before increasing worker counts.

SELECT pid, now()-xact_start AS age, backend_xmin, state, query
FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_start;
SELECT slot_name, active, xmin, catalog_xmin, restart_lsn
FROM pg_replication_slots;

Monitor vacuum progress

pg_stat_progress_vacuum shows phase, heap progress, and index-vacuum cycles for active workers. Correlate it with I/O and WAL rather than judging duration alone.

SELECT pid, relid::regclass, phase, heap_blks_scanned, heap_blks_total,
       index_vacuum_count, num_dead_item_ids
FROM pg_stat_progress_vacuum;

Set bounded per-table thresholds

Large high-churn tables often need table-specific triggers. Change one relation, then verify dead-tuple trend, duration, and foreground latency.

ALTER TABLE orders SET (
  autovacuum_vacuum_threshold = 5000,
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_analyze_scale_factor = 0.005
);

Source articles

Optional deep dives with the complete experiments and product-version context behind this guide.

The Autovacuum Scale Factor Problem at Scale - Know Your Defaultsdev.to · 2026-05-26 Postgres dead tuple space reused without vacuumdev.to · 2024-03-03 Postgres vs. Oracle access paths II – Index Only Scandbi-services · 2017-08-02 “PostgreSQL resolves uniqueness through heap tuple visibility”dev.to · 2026-07-09