VACUUM and Autovacuum

Where 27-mvcc-internals.md describes the data structures (tuple header, snapshot, xmin horizon, visibility map), this file covers the maintenance surface: how dead tuples get reclaimed, how relfrozenxid advances, how the autovacuum daemon decides what to vacuum, what the per-table tuning knobs do, and how to read pg_stat_progress_vacuum while a vacuum is running.

The canonical chapter is routine-vacuuming.html, which states the purpose flatly: "PostgreSQL databases require periodic maintenance known as vacuuming."¹ The four reasons VACUUM exists, quoted verbatim:¹

1. To recover or reuse disk space occupied by updated or deleted rows.
2. To update data statistics used by the PostgreSQL query planner.
3. To update the visibility map, which speeds up index-only scans.
4. To protect against loss of very old data due to transaction ID wraparound or multixact ID wraparound.

(1) is what most operators think of when they hear "VACUUM"; (2) is ANALYZE; (3) is the visibility-map maintenance that index-only scans depend on; (4) is freezing, treated in depth in 29-transaction-id-wraparound.md. All four happen in one VACUUM pass and you do not get to opt into only a subset.

Table of Contents

• When to Use This Reference
• Mental Model
• Decision Matrix
• VACUUM Variants
  • Plain VACUUM (LAZY)
  • VACUUM FULL
  • VACUUM FREEZE
  • VACUUM ANALYZE
• VACUUM Grammar and Options
• Autovacuum Architecture
• Autovacuum Trigger Formulas
• Per-Table Tuning
• Cost-Based Vacuum Delay (IO Throttling)
• Memory: maintenance_work_mem and autovacuum_work_mem
• Parallel Vacuum (PG13+)
• Progress Reporting: pg_stat_progress_vacuum
• Per-Version Timeline
• Examples / Recipes
• Gotchas / Anti-patterns
• See Also
• Sources

When to Use This Reference

Reach for this file when:

• An n_dead_tup audit shows a table is bloated and you need to decide whether to wait for autovacuum, run VACUUM manually, run VACUUM FULL, or reach for pg_repack / pg_squeeze.
• You see autovacuum: VACUUM ... (to prevent wraparound) in pg_stat_activity and need to interpret the urgency.
• You need to tune autovacuum per table because one hot table needs aggressive vacuuming and the rest of the cluster does not.
• You are diagnosing why autovacuum is not running on a table (last_autovacuum stays NULL, or far in the past).
• You need to read pg_stat_progress_vacuum mid-run to predict completion time or identify a stuck phase.
• You are choosing between INDEX_CLEANUP, TRUNCATE, PROCESS_TOAST, PROCESS_MAIN, PARALLEL, SKIP_LOCKED, DISABLE_PAGE_SKIPPING, SKIP_DATABASE_STATS, BUFFER_USAGE_LIMIT options on a deliberate VACUUM call.
• You need to size maintenance_work_mem / autovacuum_work_mem and want the rules behind it.

For the table-rewrite operational surface (VACUUM FULL, CLUSTER, pg_repack, pg_squeeze) the deep dive is in 26-index-maintenance.md. For the data structures VACUUM operates on (xmin/xmax, snapshot, visibility map, dead/recently-dead/live distinction), see 27-mvcc-internals.md. For wraparound mechanics specifically (freeze, relfrozenxid, anti-wraparound autovacuum, failsafe, emergency recovery), see 29-transaction-id-wraparound.md. For HOT updates (the in-page optimization that avoids most index maintenance entirely), see 30-hot-updates.md.

Mental Model

Five rules:

1. Plain VACUUM does not shrink the table. It marks dead tuples reusable and may truncate trailing empty pages, but it does not return space mid-relation to the OS. Quoted verbatim: "Plain VACUUM (without FULL) simply reclaims space and makes it available for re-use ... extra space is not returned to the operating system (in most cases); it's just kept available for re-use within the same table."² To reclaim heap bloat at the filesystem level you need VACUUM FULL, CLUSTER, or an online tool like pg_repack / pg_squeeze (26-index-maintenance.md).
2. VACUUM FULL is a rewrite under ACCESS EXCLUSIVE. Quoted verbatim: "This form is much slower and requires an ACCESS EXCLUSIVE lock on each table while it is being processed."² It is correct for one-time reclamation after a deletion of most of a table; it is wrong for routine maintenance.
3. Autovacuum is on by default and is mandatory. Quoted verbatim: "PostgreSQL has an optional but highly recommended feature called autovacuum"¹ — but in practice it is mandatory; the wraparound machinery depends on it. Disabling it cluster-wide is a footgun (gotcha #1). Per-table autovacuum_enabled=false is occasionally valid but still leaves the anti-wraparound path armed: "If false, this table will not be autovacuumed, except to prevent transaction ID wraparound."³
4. autovacuum_vacuum_scale_factor defaults to 0.2 — too lazy for a hot table. Quoted verbatim: "The default is 0.2 (20% of table size) ... the setting can be overridden for individual tables by changing table storage parameters."⁴ On a 100M-row table this means autovacuum waits for ~20M dead tuples before triggering. Lowering it to 0.01 or 0.005 per table on hot tables is the single highest-leverage tuning move.
5. Long-running transactions defeat VACUUM no matter how aggressively you tune it. The xmin horizon (27-mvcc-internals.md#the-xmin-horizon) caps how recently a dead tuple can have been deleted and still be reclaimable. If a backend has been idle-in-transaction for an hour, no VACUUM that finishes today can reclaim anything that became dead in the last hour. Fix the horizon-holder; do not tune autovacuum harder.

Decision Matrix

Three smell signals:

• last_autovacuum advances but n_dead_tup stays high → xmin horizon held back. Run the canonical horizon query from 27-mvcc-internals.md.
• VACUUM FULL is in your routine cron → you are using a write-once table strategy that doesn't fit MVCC; switch to partitioning + DROP PARTITION or fix the underlying churn.
• Autovacuum runs but a hot table is bloated → autovacuum_vacuum_scale_factor default too high for that table; set a per-table storage parameter (recipe 1).

VACUUM Variants

Plain VACUUM (LAZY)

Plain VACUUM — invoked as VACUUM tbl with no FULL keyword — is the only variant safe for routine maintenance and is the variant autovacuum runs. Its operational contract, quoted verbatim:²

Plain VACUUM (without FULL) simply reclaims space and makes it available for re-use ... This form of the command can operate in parallel with normal reading and writing of the table, as an exclusive lock is not obtained.

It takes SHARE UPDATE EXCLUSIVE on the table (does not block SELECT, INSERT, UPDATE, or DELETE; conflicts with ALTER TABLE, DROP TABLE, another VACUUM, autovacuum, ANALYZE). It does the following work in a single pass:

1. Scan heap — visit every page that the visibility map doesn't certify as all-frozen (and, in non-aggressive mode, optionally skip all-visible pages).
2. Prune dead tuples and defragment each page in place; collect dead TIDs to a dead_tuples array.
3. Vacuum indexes — visit each index and remove entries pointing at the collected dead TIDs.
4. Vacuum heap — release the line-pointer slots for the removed tuples back to the free-space-map.
5. Cleanup indexes — index-AM-specific cleanup (e.g., GIN pending-list flush, B-tree page deletion).
6. Truncate heap — if there are empty pages at the end of the relation, take a brief ACCESS EXCLUSIVE and truncate them; otherwise skip.
7. Final cleanup — update pg_class.relfrozenxid, pg_class.relminmxid, pg_class.reltuples, pg_class.relpages, FSM, visibility map.

Verbatim on truncation:²

Specifies that VACUUM should attempt to truncate off any empty pages at the end of the table and allow the disk space for the truncated pages to be returned to the operating system ... Setting this option to false may be useful to avoid ACCESS EXCLUSIVE lock on the table that the truncation requires.

The brief ACCESS EXCLUSIVE for trailing truncation is the only time a plain VACUUM takes a strong lock; use VACUUM (TRUNCATE FALSE) or ALTER TABLE ... SET (vacuum_truncate = false) if even that is unacceptable.

VACUUM FULL

VACUUM FULL is a rewrite of the table into a new physical file. Quoted verbatim:²

VACUUM FULL rewrites the entire contents of the table into a new disk file with no extra space, allowing unused space to be returned to the operating system. This form is much slower and requires an ACCESS EXCLUSIVE lock on each table while it is being processed.

And:²

The FULL option is not recommended for routine use, but might be useful in special cases.

Three operational realities:

• Takes ACCESS EXCLUSIVE — blocks SELECT, INSERT, UPDATE, DELETE, every DDL, every other VACUUM. The application is offline for that table for the duration.
• Requires 2× the table size on disk — verbatim: "This method also requires extra disk space, since it writes a new copy of the table and doesn't release the old copy until the operation is complete."²
• Rebuilds every index on the table as a side effect — much more work than a plain VACUUM, which only removes dead entries.

The right time to run VACUUM FULL is after a one-time DELETE of most of a table, when you are certain no further churn will follow and the application can tolerate the lock. Prefer pg_repack or pg_squeeze (26-index-maintenance.md) for online filesystem reclamation. Progress reports under pg_stat_progress_cluster, not pg_stat_progress_vacuum:⁸

Progress for VACUUM FULL commands is reported via pg_stat_progress_cluster because both VACUUM FULL and CLUSTER rewrite the table, while regular VACUUM only modifies it in place.

VACUUM FREEZE

VACUUM (FREEZE) forces aggressive freeze. Verbatim:²

Specifying FREEZE is equivalent to performing VACUUM with the vacuum_freeze_min_age and vacuum_freeze_table_age parameters set to zero. Aggressive freezing is always performed when the table is rewritten, so this option is redundant when FULL is specified.

When to reach for it: pre-emptively freeze a large mostly-immutable table (e.g., a historical fact table that will rarely be updated again) so that the next time anti-wraparound kicks in, there is nothing left to freeze on that table. Deep dive in 29-transaction-id-wraparound.md.

VACUUM ANALYZE

VACUUM (ANALYZE) runs ANALYZE as part of the same pass. Verbatim:²

Updates statistics used by the planner to determine the most efficient way to execute a query.

ANALYZE samples rows from the heap and rewrites pg_statistic / pg_stats. Running ANALYZE inside VACUUM amortizes the heap scan cost. Autovacuum runs ANALYZE on its own schedule (autovacuum_analyze_threshold + autovacuum_analyze_scale_factor), separate from the VACUUM schedule.

[!NOTE] PostgreSQL 18 VACUUM ONLY tbl and ANALYZE ONLY tbl skip descent into child partitions: "Allow VACUUM and ANALYZE to process partitioned tables without processing their children ... This is enabled with the new ONLY option. This is useful since autovacuum does not process partitioned tables, just its children."⁹

VACUUM Grammar and Options

Two grammars, the parenthesized form preferred. Verbatim:²

VACUUM [ ( option [, ...] ) ] [ table_and_columns [, ...] ]

When the option list is surrounded by parentheses, the options can be written in any order. Without parentheses, options must be specified in exactly the order shown above. The parenthesized syntax was added in PostgreSQL 9.0; the unparenthesized syntax is deprecated.

Always write the parenthesized form. The full option catalog, with verbatim docs descriptions and version provenance:

The INDEX_CLEANUP=AUTO default landed in PG14:¹⁵

Allow vacuum to skip index vacuuming when the number of removable index entries is insignificant ... The vacuum parameter INDEX_CLEANUP has a new default of auto that enables this optimization.

Permissions:²

To vacuum a table, one must ordinarily be the table's owner or a superuser. However, database owners are allowed to vacuum all tables in their databases, except shared catalogs. (The restriction for shared catalogs means that a true database-wide VACUUM can only be performed by a superuser.) VACUUM will skip over any tables that the calling user does not have permission to vacuum.

Hard transaction restriction:²

VACUUM cannot be executed inside a transaction block.

This means a migration framework that wraps every step in a transaction (Rails, Alembic, Flyway) needs an escape hatch (analogous to CREATE INDEX CONCURRENTLY, see 26-index-maintenance.md).

GIN-pending-list flush is a side effect of any VACUUM:²

For tables with GIN indexes, VACUUM (in any form) also completes any pending index insertions, by moving pending index entries to the appropriate places in the main GIN index structure.

This is what gin_clean_pending_list() does in a more targeted way (24-gin-gist-indexes.md).

Autovacuum Architecture

Verbatim from routine-vacuuming.html:¹

The "autovacuum daemon" actually consists of multiple processes. There is a persistent daemon process, called the autovacuum launcher, which is in charge of starting autovacuum worker processes for all databases. The launcher will distribute the work across time, attempting to start one worker within each database every autovacuum_naptime seconds. (Therefore, if the installation has N databases, a new worker will be launched every autovacuum_naptime/N seconds.) A maximum of autovacuum_max_workers worker processes are allowed to run at the same time.

The launcher is one process (visible in pg_stat_activity.backend_type = 'autovacuum launcher'); the workers are short-lived backends (backend_type = 'autovacuum worker', query column shows autovacuum: VACUUM schema.table or autovacuum: VACUUM schema.table (to prevent wraparound)).

The cluster-wide GUCs (verbatim defaults):⁴

autovacuum requires track_counts. Verbatim:¹

When enabled, autovacuum checks for tables that have had a large number of inserted, updated or deleted tuples. These checks use the statistics collection facility; therefore, autovacuum cannot be used unless track_counts is set to true.

Autovacuum is cancellable by a conflicting-lock acquirer — except when it's preventing wraparound. Verbatim:¹

Autovacuum workers generally don't block other commands. If a process attempts to acquire a lock that conflicts with the SHARE UPDATE EXCLUSIVE lock held by autovacuum, lock acquisition will interrupt the autovacuum.

However, if the autovacuum is running to prevent transaction ID wraparound (i.e., the autovacuum query name in the pg_stat_activity view ends with (to prevent wraparound)), the autovacuum is not automatically interrupted.

The (to prevent wraparound) suffix is the operational signal that auto-cancel does not apply and any ALTER TABLE / DROP TABLE / pg_repack that tries to take a stronger lock will block. The fix is to let the wraparound vacuum finish (gotcha #6).

[!NOTE] PostgreSQL 15 "Enable default logging of checkpoints and slow autovacuum operations ... This changes the default of log_checkpoints to on and that of log_autovacuum_min_duration to 10 minutes."¹⁶ On PG15+ clusters you get autovacuum log lines for runs over 10 minutes without any GUC change.

[!NOTE] PostgreSQL 16 "Allow autovacuum to more frequently honor changes to delay settings ... Rather than honor changes only at the start of each relation, honor them at the start of each block."¹⁷ On PG16+ a vacuum_cost_delay change via ALTER SYSTEM + SELECT pg_reload_conf() propagates into a running autovacuum within a page rather than waiting for the next table.

Autovacuum Trigger Formulas

The launcher picks tables to vacuum and analyze by evaluating two (since PG13, three) threshold formulas per table per cycle. All three are stated verbatim in routine-vacuuming.html.

VACUUM trigger (update/delete pressure):¹

vacuum threshold = vacuum base threshold + vacuum scale factor * number of tuples

where the vacuum base threshold is autovacuum_vacuum_threshold, the vacuum scale factor is autovacuum_vacuum_scale_factor, and the number of tuples is pg_class.reltuples.

A table is vacuumed when n_dead_tup ≥ vacuum_threshold. Worked example, default GUCs:

That 20-million-dead-tuple threshold on a 100M-row table is the textbook "why is my big table bloated?" answer. Lower the scale factor per table (recipe 1).

VACUUM trigger (insert pressure, PG13+):¹

vacuum insert threshold = vacuum base insert threshold + vacuum insert scale factor * number of tuples

where the vacuum insert base threshold is autovacuum_vacuum_insert_threshold, and vacuum insert scale factor is autovacuum_vacuum_insert_scale_factor.

PG13 rationale, verbatim:¹⁸

Previously, insert-only activity would trigger auto-analyze but not auto-vacuum, on the grounds that there could not be any dead tuples to remove. However, a vacuum scan has other useful side-effects such as setting page-all-visible bits, which improves the efficiency of index-only scans. Also, allowing an insert-only table to receive periodic vacuuming helps to spread out the work of "freezing" old tuples, so that there is not suddenly a large amount of freezing work to do when the entire table reaches the anti-wraparound threshold all at once.

This trigger uses n_ins_since_vacuum from pg_stat_user_tables. To disable insert-triggering on one table, set autovacuum_vacuum_insert_threshold = -1 as the per-table storage parameter.³

ANALYZE trigger:¹

analyze threshold = analyze base threshold + analyze scale factor * number of tuples

is compared to the total number of tuples inserted, updated, or deleted since the last ANALYZE.

Default scale factor 0.1 means ANALYZE runs at 10% modifications; lower for tables where the planner picks bad plans because of stale stats.

Anti-wraparound trigger:¹

Tables whose relfrozenxid value is more than autovacuum_freeze_max_age transactions old are always vacuumed.

This bypasses the normal scale-factor formula entirely and is the one autovacuum that cannot be auto-cancelled.

Per-Table Tuning

Per-table autovacuum GUCs are set via CREATE TABLE ... WITH (...) or ALTER TABLE tbl SET (key = value). Quoted verbatim from sql-createtable.html:³

Enables or disables the autovacuum daemon for a particular table. If true, the autovacuum daemon will perform automatic VACUUM and/or ANALYZE operations on this table following the rules discussed in Section 25.1.6. If false, this table will not be autovacuumed, except to prevent transaction ID wraparound.

Full per-table parameter catalog:

The headline per-table tuning move is autovacuum_vacuum_scale_factor = 0.01 on hot churn tables (10× more aggressive than default). Combine with a autovacuum_vacuum_threshold floor of a few thousand to prevent over-triggering on tiny tables.

Cost-Based Vacuum Delay (IO Throttling)

VACUUM accumulates "cost" as it reads and writes buffers; when the accumulated cost reaches vacuum_cost_limit, the worker sleeps for vacuum_cost_delay milliseconds. Verbatim:⁵

During the execution of VACUUM and ANALYZE commands, the system maintains an internal counter that keeps track of the estimated cost of the various I/O operations that are performed. When the accumulated cost reaches a limit (specified by vacuum_cost_limit), the process performing the operation will sleep for a short period of time, as specified by vacuum_cost_delay. Then it will reset the counter and continue execution.

The four cost components, with verbatim defaults:⁵

The throttle GUCs:⁵

Manual VACUUM has the delay disabled by default. Verbatim:⁵

This feature is disabled by default for manually issued VACUUM commands. To enable it, set the vacuum_cost_delay variable to a nonzero value. ... When using cost-based vacuuming, appropriate values for vacuum_cost_delay are usually quite small, perhaps less than 1 millisecond.

This is the right call: when you run VACUUM manually, you usually want it to finish quickly. Autovacuum, by contrast, has a 2 ms delay default and runs in the background.

For parallel vacuum (PG13+), the delay is computed per worker:²

For parallel vacuum, each worker sleeps in proportion to the work done by that worker.

[!NOTE] PostgreSQL 14 "Reduce the default value of vacuum_cost_page_miss to better reflect current hardware capabilities."¹⁹ Default vacuum_cost_page_miss dropped from 10 to 2. If you're tuning a PG14+ cluster using PG13-era cookbook values, you're throttling vacuum 5× more than intended.

Memory: maintenance_work_mem and autovacuum_work_mem

VACUUM stores dead-tuple TIDs in memory between scan and index-vacuum phases. maintenance_work_mem is the cap, verbatim:⁵

Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. If this value is specified without units, it is taken as kilobytes. It defaults to 64 megabytes (64MB). Since only one of these operations can be executed at a time by a database session, and an installation normally doesn't have many of them running concurrently, it's safe to set this value significantly larger than work_mem. Larger settings might improve performance for vacuuming and for restoring database dumps.

The 64 MB default is conservatively low for any modern server. A typical production setting is 512 MB to 2 GB.

autovacuum_work_mem overrides per-worker. Verbatim:⁵

Specifies the maximum amount of memory to be used by each autovacuum worker process. ... It defaults to -1, indicating that the value of maintenance_work_mem should be used instead.

Two consequences:

1. Total RAM used by autovacuum can reach autovacuum_max_workers × maintenance_work_mem. With defaults that's 3 × 64 MB = 192 MB — fine. Raise maintenance_work_mem to 2 GB and that becomes 6 GB potentially in use. Verbatim warning:⁵ "Note that when autovacuum runs, up to autovacuum_max_workers times this memory may be allocated, so be careful not to set the default value too high."
2. Pre-PG17 1 GB cap — verbatim from the PG16 docs:⁵ "Note that for the collection of dead tuple identifiers, VACUUM is only able to utilize up to a maximum of 1GB of memory." This silently capped VACUUM at ~178 million TIDs per index-pass regardless of maintenance_work_mem.

[!NOTE] PostgreSQL 17 The 1 GB cap is lifted. Verbatim:²⁰ "New memory management system for VACUUM, which reduces memory consumption and can improve overall vacuuming performance. ... Additionally, vacuum is no longer silently limited to one gigabyte of memory when maintenance_work_mem or autovacuum_work_mem are higher." On PG17+ you can size maintenance_work_mem = 4GB and a single VACUUM pass will use all of it, reducing the number of index-vacuum cycles on a very large table from many to one.

Parallel Vacuum (PG13+)

PG13 introduced PARALLEL n for VACUUM. Verbatim:¹²

Allow VACUUM to process a table's indexes in parallel (Masahiko Sawada, Amit Kapila) ... The new PARALLEL option controls this.

And from the VACUUM syntax page:²

Perform index vacuum and index cleanup phases of VACUUM in parallel using integer background workers. The number of workers used to perform the operation is equal to the number of indexes on the relation that support parallel vacuum which is limited by the number of workers specified with PARALLEL option if any which is further limited by max_parallel_maintenance_workers. An index can participate in parallel vacuum if and only if the size of the index is more than min_parallel_index_scan_size.

Four operational consequences:

• Parallelism only helps the index phase, not the heap scan. A table with one giant index but a small heap will not benefit much. A table with many indexes will.
• PARALLEL cannot be combined with FULL — verbatim: "This option can't be used with the FULL option."²
• Autovacuum does not parallelize, only manual VACUUM (PARALLEL n). If you have a known-bloated table and want to vacuum it faster, you must invoke VACUUM manually.
• PARALLEL 0 disables it — verbatim: "To disable this feature, one can use PARALLEL option and specify parallel workers as zero."²

The relevant GUC is max_parallel_maintenance_workers, default 2, which caps the worker count whatever you ask for.

Progress Reporting: pg_stat_progress_vacuum

pg_stat_progress_vacuum reports one row per actively-running VACUUM (manual + autovacuum). Quoted verbatim:⁸

Whenever VACUUM is running, the pg_stat_progress_vacuum view will contain one row for each backend (including autovacuum worker processes) that is currently vacuuming.

Columns (PG16 names; see PG17 renames below):⁸

The seven phases, verbatim:⁸

[!NOTE] PostgreSQL 17 Three column changes:²¹ "Rename pg_stat_progress_vacuum column max_dead_tuples to max_dead_tuple_bytes, rename num_dead_tuples to num_dead_item_ids, and add dead_tuple_bytes." Plus a new pair for index processing:²² "Allow vacuum to report the progress of index processing ... This appears in system view pg_stat_progress_vacuum columns indexes_total and indexes_processed." Diagnostic queries that joined max_dead_tuples need to be rewritten for PG17.

If index_vacuum_count > 1 in pg_stat_progress_vacuum, the table was too big for one pass and VACUUM had to round-trip through indexes multiple times. The fix on PG≤16 is to raise maintenance_work_mem, accepting the 1 GB cap. On PG17+ the cap is gone and raising maintenance_work_mem removes the multi-pass entirely (recipe 6).

Per-Version Timeline

Examples / Recipes

Recipe 1 — Tune autovacuum aggressiveness on a hot table

The single highest-leverage operational move. Default autovacuum_vacuum_scale_factor = 0.2 means autovacuum waits for 20% of the table to be dead. On a 100M-row table that is 20M dead tuples — far too lazy.

-- 1% scale factor with a 10k-row floor on a hot churn table
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 10000,
  autovacuum_analyze_scale_factor = 0.005,
  autovacuum_analyze_threshold = 5000
);

-- Verify
SELECT relname, reloptions
FROM pg_class
WHERE relname = 'orders';

The threshold floor prevents over-triggering on tiny tables. For an insert-mostly table that you want to freeze opportunistically rather than wait for anti-wraparound:

ALTER TABLE events SET (
  autovacuum_vacuum_insert_scale_factor = 0.01,
  autovacuum_vacuum_insert_threshold = 100000
);

Recipe 2 — Find tables that need attention

The canonical bloat-candidate audit, ordered by dead-tuple ratio. Cross-reference 27-mvcc-internals.md Recipe 5 (n_dead_tup audit) for the longer form.

SELECT
  schemaname || '.' || relname AS table,
  n_live_tup,
  n_dead_tup,
  ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
  last_autovacuum,
  last_autoanalyze,
  autovacuum_count,
  n_ins_since_vacuum
FROM pg_stat_user_tables
WHERE n_live_tup + n_dead_tup > 10000
ORDER BY dead_pct DESC NULLS LAST
LIMIT 20;

If dead_pct is high and last_autovacuum is recent — autovacuum ran but couldn't reclaim. Check the xmin horizon (27-mvcc-internals.md). If dead_pct is high and last_autovacuum is far in the past — autovacuum is not triggering; lower the per-table scale factor (recipe 1).

Recipe 3 — Watch a running VACUUM in real time

SELECT
  v.pid,
  a.query,
  v.phase,
  v.heap_blks_scanned,
  v.heap_blks_total,
  ROUND(100.0 * v.heap_blks_scanned / NULLIF(v.heap_blks_total, 0), 1) AS pct,
  v.index_vacuum_count,
  a.wait_event_type, a.wait_event
FROM pg_stat_progress_vacuum v
JOIN pg_stat_activity a USING (pid)
ORDER BY v.heap_blks_total DESC;

If index_vacuum_count > 1, the table is too big for one pass — raise maintenance_work_mem (recipe 6). If phase = 'vacuuming indexes' and stuck, check pg_stat_activity.wait_event for BufferPin (a query holding a pin on a page that VACUUM wants to clean) or Lock (a conflicting lock).

[!NOTE] PostgreSQL 17 Replace max_dead_tuples/num_dead_tuples with max_dead_tuple_bytes/num_dead_item_ids/dead_tuple_bytes in any diagnostic that quoted them.²¹ Add v.indexes_total, v.indexes_processed to the SELECT to see index-processing progress.

Recipe 4 — Disable trailing truncation on a write-hot table

Trailing-page truncation requires a brief ACCESS EXCLUSIVE. For a table whose application is sensitive to even a sub-second blocking, disable it:

ALTER TABLE realtime_signals SET (vacuum_truncate = false);
-- Same for TOAST side
ALTER TABLE realtime_signals SET (toast.vacuum_truncate = false);

The downside: trailing empty pages stay until the next non-LAZY operation that rewrites the relation (VACUUM FULL, CLUSTER, pg_repack). For most write-heavy OLTP tables this is the right trade.

Recipe 5 — One-off manual VACUUM with parallel workers

-- Big table with several indexes — parallelize the index phase.
VACUUM (PARALLEL 4, VERBOSE, ANALYZE) large_table;

PARALLEL n caps at max_parallel_maintenance_workers and at the count of indexes on the table that exceed min_parallel_index_scan_size. VERBOSE prints per-index and per-phase stats. ANALYZE folds in the statistics refresh.

Recipe 6 — Detect and fix the index_vacuum_count > 1 problem

If pg_stat_progress_vacuum.index_vacuum_count > 1 during a vacuum, the dead-tuple TID store didn't fit in maintenance_work_mem and VACUUM had to round-trip through every index multiple times.

-- Pre-PG17: capped at 1 GB anyway.
-- PG17+: cap is removed.
SET maintenance_work_mem = '4GB';
VACUUM (PARALLEL 4, VERBOSE) huge_archive_table;

[!NOTE] PostgreSQL 17 The 1 GB silent cap is gone:²⁰ "vacuum is no longer silently limited to one gigabyte of memory when maintenance_work_mem or autovacuum_work_mem are higher." On PG17+ this recipe collapses a multi-pass vacuum of a 500 GB table from hours to a single index round-trip.

Recipe 7 — Bloat triage walkthrough

A n_dead_tup-heavy table with autovacuum running:

1. Confirm autovacuum is running on this table — check pg_stat_user_tables.last_autovacuum and autovacuum_count.
2. Check the xmin horizon — if any backend has backend_xmin < (max XID - days*86400*xacts/sec), autovacuum cannot reclaim tuples that became dead after that backend's xmin. Run the diagnostic query from 27-mvcc-internals.md Recipe 2.
3. If horizon is fine, check that autovacuum is finishing — pg_stat_progress_vacuum will tell you. If a vacuum is repeatedly being cancelled by lock conflicts, set lock_timeout lower on the offending statement, not on the vacuum.
4. If autovacuum runs and finishes but bloat stays — the scale factor is wrong for this table size. Apply recipe 1.
5. If heap is bloated but indexes are not — n_dead_tup is wrong as a proxy; check pgstattuple_approx(tbl) for the heap and pgstatindex(idx) for each index (26-index-maintenance.md recipe 10). Decide between routine VACUUM (already running) and online table reorg via pg_repack / pg_squeeze.
6. If pgstattuple says the heap is 80% dead — switch to a one-time online reorg with pg_repack, not VACUUM FULL.

Recipe 8 — Schedule a maintenance window VACUUM ANALYZE with pg_cron

-- Weekly Sunday 02:00 UTC, on tables that need it.
SELECT cron.schedule(
  'weekly-vacuum-analyze-bigtable',
  '0 2 * * 0',
  $$ VACUUM (ANALYZE, PARALLEL 4, BUFFER_USAGE_LIMIT '32MB') public.big_fact_table $$
);

The BUFFER_USAGE_LIMIT '32MB' keeps the maintenance from sweeping shared_buffers clean during the run (PG16+). Cross-reference 98-pg-cron.md. For the VACUUM cannot run in transaction block rule — pg_cron jobs execute outside a transaction, so this works.

Recipe 9 — Inventory tables with autovacuum disabled

SELECT
  n.nspname || '.' || c.relname AS relation,
  c.reloptions
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'p')
  AND (c.reloptions::text ILIKE '%autovacuum_enabled=false%'
    OR c.reloptions::text ILIKE '%autovacuum_enabled=off%')
ORDER BY 1;

Any row here is a table the operator deliberately exempted from autovacuum. Anti-wraparound still runs on it. Each row deserves a code-review comment explaining why.

Recipe 10 — Audit autovacuum cancellations

When autovacuum is repeatedly cancelled by lock conflicts, it never finishes. Watch the logs:

-- postgresql.conf (or ALTER SYSTEM)
log_autovacuum_min_duration = 0      -- log every autovacuum (verbose; revert after diagnosis)

Then grep postgresql.log for canceling autovacuum task lines. Each cancellation is paired with the conflicting lock acquirer's PID. On PG15+ the default log_autovacuum_min_duration = 10min so long autovacuums are logged automatically.¹⁶

Recipe 11 — Process only the TOAST side of a table

A table whose main relation is fine but whose TOAST side is bloated (e.g., a table with one jsonb column where most rows are toasted):

-- PG16+
VACUUM (PROCESS_MAIN false, VERBOSE) tbl;

Or the inverse, when TOAST processing is slow and you want to vacuum only the main relation:

-- PG14+
VACUUM (PROCESS_TOAST false, VERBOSE) tbl;

Recipe 12 — Use vacuumdb for cluster-wide maintenance

vacuumdb is the wrapper used in scheduled jobs:

# Vacuum analyze every database, 4 parallel jobs, 8 parallel index workers per table.
vacuumdb --all --analyze --jobs=4 --parallel=8 --verbose

# PG16+ buffer usage limit
vacuumdb --all --analyze --buffer-usage-limit=64MB

# Post-pg_upgrade: refresh planner stats in stages of increasing fidelity.
vacuumdb --all --analyze-in-stages

--analyze-in-stages runs three passes with progressively higher default_statistics_target (1, 10, full). On PG17+ pg_upgrade preserves statistics, but --analyze-in-stages is still the right post-upgrade catch-all.

Recipe 13 — Pre-emptive freeze of a write-once partition

For a daily-rotation partition that will never be updated again, pre-emptively freeze it after the rotation is complete:

VACUUM (FREEZE, ANALYZE, PARALLEL 4) events_2025_05_10;

This advances relfrozenxid on the partition to "now," so anti-wraparound never has to scan it. Combined with 35-partitioning.md retention, you get bounded freeze cost.

Gotchas / Anti-patterns

1. Disabling autovacuum cluster-wide is operationally fatal. Without autovacuum, freezing never happens, relfrozenxid does not advance, and the cluster will shut down at wraparound. Whatever problem motivated disabling autovacuum (high CPU, IO contention) is better solved by tuning cost limits, raising maintenance_work_mem, or rebalancing workers — never by disabling the daemon.

2. autovacuum_enabled = false on a table still allows anti-wraparound. Verbatim:³ "If false, this table will not be autovacuumed, except to prevent transaction ID wraparound." You cannot fully exempt a table from VACUUM. The setting is correct for "I will run VACUUM on this table manually on my own schedule" but you must actually do that.

3. VACUUM FULL is not a routine maintenance command. It rewrites the table under ACCESS EXCLUSIVE and rebuilds all indexes. For routine reclamation use plain VACUUM (which is what autovacuum runs). For online filesystem reclamation use pg_repack or pg_squeeze.

4. VACUUM FULL followed by VACUUM is redundant. FULL already rewrites the table and freezes aggressively as a side effect. Verbatim:² "Aggressive freezing is always performed when the table is rewritten, so this option is redundant when FULL is specified."

5. The 0.2 default scale factor is too lazy for big tables. Out-of-the-box, a 100M-row table waits for 20M dead tuples before autovacuum triggers. Lower the per-table autovacuum_vacuum_scale_factor aggressively — 0.01 or even 0.005 — on hot tables. Cluster-wide changes are coarse; per-table is precise.

6. autovacuum: ... (to prevent wraparound) cannot be auto-cancelled. A query that takes a conflicting lock will block on it until the vacuum finishes, even if your application has a lock_timeout. The fix is to let it complete, then investigate why anti-wraparound triggered in the first place (was autovacuum_freeze_max_age reached because routine autovacuum was being cancelled? see gotcha #10).

7. Manual VACUUM defaults to vacuum_cost_delay = 0 — no throttle.⁵ If you run VACUUM big_table in business hours expecting it to be polite to other queries, you will be surprised. Either set SET vacuum_cost_delay = '2ms'; before issuing the VACUUM, or rely on autovacuum which already has the throttle.

8. maintenance_work_mem × autovacuum_max_workers is a real RAM commitment. Verbatim:⁵ "when autovacuum runs, up to autovacuum_max_workers times this memory may be allocated, so be careful not to set the default value too high." Setting maintenance_work_mem = 8GB and autovacuum_max_workers = 6 means autovacuum can use 48 GB. Size accordingly.

9. Pre-PG17, VACUUM is silently capped at 1 GB regardless of maintenance_work_mem. Verbatim from the PG16 docs:⁵ "Note that for the collection of dead tuple identifiers, VACUUM is only able to utilize up to a maximum of 1GB of memory." The cap is removed in PG17.²⁰ If you tuned maintenance_work_mem = 8GB on PG16 and saw no speedup, this is why.

10. A long-running transaction or abandoned replication slot defeats VACUUM completely. No autovacuum tuning helps. The fix is upstream — idle_in_transaction_session_timeout, dropping abandoned slots — not in this file. See 27-mvcc-internals.md gotchas #2, #4, and the long-running-offender recipes.

11. VACUUM cannot run inside a transaction block. Verbatim:² "VACUUM cannot be executed inside a transaction block." Migration frameworks that wrap every step in a transaction (Rails, Alembic, Flyway) need the same escape hatch as CREATE INDEX CONCURRENTLY (26-index-maintenance.md).

12. Autovacuum requires track_counts. Verbatim:¹ "autovacuum cannot be used unless track_counts is set to true." track_counts is on by default; if you turned it off (e.g., for pg_stat_kcache experimentation), you turned off autovacuum.

13. Trailing-page truncation takes brief ACCESS EXCLUSIVE. Plain VACUUM's only blocking operation. For ultra-latency-sensitive tables, set ALTER TABLE tbl SET (vacuum_truncate = false) and accept that empty trailing pages stay until the next FULL.

14. pg_stat_progress_vacuum shows only LAZY VACUUM, not VACUUM FULL. Verbatim:⁸ "Backends running VACUUM FULL will instead report their progress in the pg_stat_progress_cluster view." Diagnostic queries that look for a stuck VACUUM by querying pg_stat_progress_vacuum will miss any FULL.

15. VACUUM does not lower relfrozenxid on a partitioned-table parent — only on the partitions. A parent is just a catalog entry; the actual heap is in the child partitions. Anti-wraparound triggers per partition. On PG18+ use VACUUM ONLY parent to run a vacuum on just the parent's catalog row.⁹

16. INDEX_CLEANUP=OFF skips index entries but the TID-store still grows. Don't use OFF as a workaround for "VACUUM is too slow" except in genuine pre-wraparound emergencies. Verbatim:² "This may be useful when it is necessary to make VACUUM run as quickly as possible to avoid imminent transaction ID wraparound." And the immediate next sentence: "However, the wraparound failsafe mechanism controlled by vacuum_failsafe_age will generally trigger automatically to avoid transaction ID wraparound failure, and should be preferred."

17. Replication-slot xmin holds the horizon back cluster-wide. A slot for a subscriber that's been down for a day pins every database's horizon at where the subscriber left off. Bloat will accumulate on every table in every database. Fix the slot (75-replication-slots.md), not the autovacuum.

18. Autovacuum cancellation by lock conflict is silent in default logs. Set log_autovacuum_min_duration = 0 temporarily to see every autovacuum (and every cancellation) in the log. On PG15+ the default 10min catches long runs but not quick cancellations.¹⁶

19. PARALLEL n is for the index phase only — it does not parallelize the heap scan. A table with one giant index gets no parallel speedup. A table with many indexes gets a near-linear speedup.

20. autovacuum_max_workers is restart-only. Verbatim:⁴ "This parameter can only be set at server start." You cannot raise it during an incident.

21. GIN-pending-list flush happens during every VACUUM — a busy GIN-indexed table will have most of its VACUUM time go to GIN cleanup. Tune gin_pending_list_limit per index (24-gin-gist-indexes.md) if VACUUM is slow on GIN-heavy tables.

22. vacuum_cost_page_miss was lowered from 10 to 2 in PG14.¹⁹ If you carried PG13-era cookbook values forward, your throttle is 5× stricter than the modern default.

See Also

• 26-index-maintenance.md — CREATE/REINDEX CONCURRENTLY, pg_repack vs pg_squeeze for online table reorganization, pgstattuple for bloat audit.
• 27-mvcc-internals.md — Tuple header, snapshot, visibility map, dead vs live tuples, xmin horizon (the data structures VACUUM operates on).
• 29-transaction-id-wraparound.md — Freeze process, relfrozenxid / relminmxid advancement, anti-wraparound autovacuum, failsafe, emergency recovery.
• 30-hot-updates.md — Heap-only-tuple updates and the n_tup_hot_upd counter.
• 31-toast.md — The TOAST sidecar that PROCESS_TOAST controls.
• 33-wal.md — WAL records generated by VACUUM (freeze, visibility-map updates).
• 34-checkpoints-bgwriter.md — How vacuum-dirtied pages reach disk.
• 35-partitioning.md — VACUUM ONLY parent vs children on PG18+; per-partition freeze.
• 43-locking.md — SHARE UPDATE EXCLUSIVE (plain VACUUM) vs ACCESS EXCLUSIVE (VACUUM FULL) lock conflict matrix.
• 58-performance-diagnostics.md — pg_stat_user_tables, pg_stat_progress_vacuum, pg_stat_io (PG16+).
• 75-replication-slots.md — How a slot's xmin pins the cluster-wide horizon.
• 98-pg-cron.md — Scheduling periodic VACUUM ANALYZE jobs.

Sources