medium riskservice affecting~60 min

Runbook: Vacuum a Large Table in a Maintenance Window

1 · Prerequisites

Confirm every item is in place before any state change.

The table, its size, and the reason it needs a manual vacuum rather than being left to autovacuum
A window long enough for the whole pass, sized from a measurement rather than an estimate
Confirmation that no scheduled DDL will run during the window, because a conflicting lock cancels the vacuum and it keeps no progress
A database connection that will survive the window, and a second one for watching progress
Knowledge of what is holding the vacuum horizon, because a vacuum cannot remove rows that something still needs
Authority to raise the cost limit for the duration, since the default throttle makes a large table take much longer

2 · Pre-checks

Read-only diagnostic commands. If any of these don't match expected output, stop and investigate further.

· Confirm the table actually needs it. SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, autovacuum_count FROM pg_stat_user_tables WHERE relname = :table; A high n_dead_tup that never falls is the signature of a vacuum that is not completing.
· Check what is holding the freeze and cleanup horizon. Nothing removable is removed while an old snapshot exists: SELECT 'open transaction' AS holder, pid::text, age(backend_xmin) FROM pg_stat_activity WHERE backend_xmin IS NOT NULL UNION ALL SELECT 'prepared transaction', gid, age(transaction) FROM pg_prepared_xacts UNION ALL SELECT 'replication slot', slot_name, greatest(age(xmin), age(catalog_xmin)) FROM pg_replication_slots WHERE xmin IS NOT NULL OR catalog_xmin IS NOT NULL ORDER BY 3 DESC NULLS LAST;
· If something is holding the horizon, resolve that first. A vacuum run against a pinned horizon completes successfully and removes nothing. It will report 0 removed and a large not yet removable count, and the hours are wasted.
· Check for scheduled DDL in the window. A statement needing ACCESS EXCLUSIVE on the table — ALTER TABLE, DETACH PARTITION, REINDEX without CONCURRENTLY — cancels the vacuum, which then starts from the beginning of the table next time.
· Measure or estimate the duration. If a previous full pass was timed, use that number. If not, pg_stat_progress_vacuum during this run gives you the number for next time, and that is itself a reason to run it.
· **Check pg_wal headroom.** A large vacuum generates WAL, and on a cluster with a marginal WAL volume that matters.
· Confirm the maintenance settings. SELECT name, setting FROM pg_settings WHERE name IN ('maintenance_work_mem','vacuum_cost_delay','vacuum_cost_limit','vacuum_failsafe_age') ORDER BY name;

3 · Procedure

Execute each step in order. Verify the expected output of a step before moving to the next.

1**Raise maintenance_work_mem for the session.** SET maintenance_work_mem = '1GB'; A larger value lets vacuum hold more dead tuple identifiers per pass, which reduces the number of index scans — often the dominant cost on a large table.
2Remove the cost throttle for the window. SET vacuum_cost_delay = 0; The default throttle exists to keep autovacuum unobtrusive during normal operation. In a maintenance window it is only making the pass longer.
3**Run the vacuum with VERBOSE, so the result is readable.** VACUUM (VERBOSE, ANALYZE) events; Include ANALYZE unless you have a specific reason not to; fresh statistics after a large cleanup are almost always worth having.
4**Do not use VACUUM FULL unless you have decided to rewrite the table.** It takes ACCESS EXCLUSIVE for its whole duration and every reader queues behind it. Plain VACUUM does not block reads or writes.
5Watch progress from the second session. SELECT pid, phase, heap_blks_total, heap_blks_scanned, round(100.0 * heap_blks_scanned / nullif(heap_blks_total,0), 1) AS pct, index_vacuum_count, dead_tuple_bytes, max_dead_tuple_bytes FROM pg_stat_progress_vacuum;
6**Read phase to understand what it is doing.** scanning heap, vacuuming indexes, vacuuming heap, cleaning up indexes. A vacuum that cycles through the index phases repeatedly is running out of maintenance_work_mem and doing several passes, which index_vacuum_count counts.
7Do not cancel it unless you must. A cancelled vacuum keeps nothing; the next run starts at the beginning of the table. On a table that needs hours, a repeatedly cancelled vacuum never completes at all.
8Read the completion output, not the exit status. Look for tuples: N removed, M remain, 0 are dead but not yet removable and new relfrozenxid: X, which is N XIDs ahead of previous value.
9**Treat a missing new relfrozenxid line as a failure.** Its absence means relfrozenxid did not advance, which means something was holding the horizon for the whole run. The vacuum succeeded and accomplished nothing.
10**Check not yet removable in the output.** A large number there means dead rows were found and could not be reclaimed, again because of a held horizon.
11Give the table its own autovacuum settings afterwards. ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_threshold = 10000); Vacuuming more often makes each run smaller, which is what makes it survivable in a normal night.
12Record the duration and the output. That duration is the window this table needs, and it is the number that tells you whether autovacuum can ever complete on it unaided.

4 · Verification

Confirm the procedure actually fixed the problem.

✓The VERBOSE output contains a new relfrozenxid line with a non-zero XIDs ahead of previous value. That line is the proof the work landed.
✓The output shows 0 are dead but not yet removable, or a small number. A large figure means the horizon was pinned and the pass reclaimed little.
✓n_dead_tup for the table has fallen and stays low: SELECT relname, n_dead_tup, n_live_tup, last_vacuum FROM pg_stat_user_tables WHERE relname = :table;
✓age(relfrozenxid) for the table has fallen: SELECT relname, age(relfrozenxid) FROM pg_class WHERE relname = :table;
✓The table size has stopped growing while the row count is flat. Plain vacuum makes space reusable inside the file and does not shrink it, so flat is the success condition and smaller is not expected.
✓The log contains no canceling autovacuum task for this table during or after the window.
✓The duration is recorded and compared against the quiet window available. If it exceeds it, that is a capacity fact to plan around rather than to discover again next quarter.

5 · Rollback

If verification fails, undo the procedure in reverse order.

↶A plain VACUUM cannot be rolled back and does not need to be. It removes only row versions that no transaction can see.
↶If the vacuum is cancelled, nothing is kept and nothing is damaged; the next run starts from the beginning. That is the cost, and it is why cancelling is a real decision on a large table.
↶Session-scoped settings — maintenance_work_mem, vacuum_cost_delay — revert when the session ends. If they were set with ALTER SYSTEM instead, ALTER SYSTEM RESET <param>; SELECT pg_reload_conf();.
↶Per-table autovacuum settings are reversed with ALTER TABLE events RESET (autovacuum_vacuum_scale_factor, autovacuum_vacuum_threshold);
↶If a VACUUM FULL was started by mistake and a lock queue has formed, cancel it: SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE query LIKE 'VACUUM FULL%';. The queue drains immediately and the table is left as it was.
↶If the window was exceeded and the vacuum was cancelled, record how far it got from pg_stat_progress_vacuum. That percentage is what tells you how much larger the next window needs to be.

6 · Escalation

When the runbook isn't enough, contact:

· The horizon is held by something you cannot resolve — a long-running report, a prepared transaction, an abandoned replication slot: escalate to its owner. Running the vacuum anyway wastes the window and the I/O.
· The vacuum does not fit in any available window: escalate to whoever owns the maintenance schedule. A table that needs four hours of uninterrupted vacuuming and gets twenty minutes a night will never complete, and the deficit compounds.
· The log shows canceling autovacuum task for this table on a regular cadence: escalate to whoever owns the conflicting job. That is why the table reached this state, and vacuuming it once does not stop it recurring.
· Disk pressure is the reason for the vacuum: escalate before considering VACUUM FULL. Plain vacuum does not return space to the filesystem, and a rewrite during business hours is an outage.
· The table has reached a wraparound warning: escalate immediately and check the horizon holders first. Vacuuming harder cannot advance a horizon that something else is pinning.
· Repeated full vacuums are being scheduled to keep a table usable: escalate to the data owner. That is a treadmill, and the underlying question is the table's churn rate and its autovacuum settings.

Plain VACUUM does not block reads or writes. It can run against a live table, and usually should.

What it cannot survive is interruption.

A cancelled vacuum keeps nothing

There is no checkpoint inside a vacuum

The next run starts at the beginning of the table.

A table needing four hours of uninterrupted vacuuming, getting twenty minutes a night before a scheduled job takes an ACCESS EXCLUSIVE lock, never completes. The deficit compounds — one measured estate reached 1.1 billion dead tuples and a terabyte of bloat, one night at a time.

The log records each cancellation:

LOG:  sending cancel to blocking autovacuum PID 4302
ERROR:  canceling autovacuum task

pg_stat_user_tables does not. autovacuum_count and last_autovacuum record that a worker started, and there is no column that records whether it finished.

Before you start: what is holding the horizon?

SELECT 'open transaction' AS holder, pid::text AS what, age(backend_xmin) AS xid_age
FROM pg_stat_activity WHERE backend_xmin IS NOT NULL
UNION ALL
SELECT 'prepared transaction', gid, age(transaction) FROM pg_prepared_xacts
UNION ALL
SELECT 'replication slot', slot_name, greatest(age(xmin), age(catalog_xmin))
FROM pg_replication_slots WHERE xmin IS NOT NULL OR catalog_xmin IS NOT NULL
ORDER BY 3 DESC NULLS LAST;

A vacuum against a pinned horizon succeeds and removes nothing

It will run for hours, exit zero, and report:

tuples: 0 removed, 4821992 remain, 3910442 are dead but not yet removable
removable cutoff: 41880231, which was 14002119 XIDs old when operation ended

Note what is missing from that output: there is no new relfrozenxid line, because relfrozenxid did not move.

Resolve the horizon holder first. Otherwise the window is spent for nothing.

Settings for the window

SET maintenance_work_mem = '1GB';   -- fewer index passes
SET vacuum_cost_delay = 0;          -- no throttle in a window
VACUUM (VERBOSE, ANALYZE) events;

maintenance_work_mem determines how many dead tuple identifiers vacuum can hold at once. Too small, and it makes several passes over every index — which index_vacuum_count in the progress view counts, and which is usually the dominant cost on a large table.

Watch it, from a second session

SELECT pid, phase,
       heap_blks_total, heap_blks_scanned,
       round(100.0 * heap_blks_scanned / nullif(heap_blks_total,0), 1) AS pct,
       index_vacuum_count,
       dead_tuple_bytes, max_dead_tuple_bytes
FROM pg_stat_progress_vacuum;

This is how you tell a vacuum that is working slowly from one that is not working. phase cycles through scanning heap, vacuuming indexes, vacuuming heap, cleaning up indexes; an index_vacuum_count above 1 means maintenance_work_mem was not enough for a single pass.

Read the completion, not the exit status

tuples: 15000 removed, 100000 remain, 0 are dead but not yet removable
new relfrozenxid: 100766, which is 100013 XIDs ahead of previous value

Two things to check, and one of them is a line’s presence:

Signal	Healthy	Wasted
`new relfrozenxid` line	Present, large delta	Absent
`not yet removable`	`0`	A large number

Blast radius

Action	Reversible?	What it costs if wrong
Plain `VACUUM`	Yes, harmless	I/O and WAL for its duration
Cancelling it	No progress kept	The whole pass; the next starts from zero
`SET maintenance_work_mem` high	Session-scoped	Memory, for one session
`SET vacuum_cost_delay = 0`	Session-scoped	I/O impact during the window
`VACUUM FULL`	Yes, cancel it	`ACCESS EXCLUSIVE` on the table and a queue behind it
Per-table autovacuum settings	Yes, `RESET`	More frequent, smaller vacuums — usually the point

Afterwards

Give the table settings that let a normal night’s work fit in a normal night:

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold    = 10000
);

And write down how long the pass took. That number tells you whether autovacuum can ever complete on this table unaided — and if it cannot, that is a capacity fact to plan around rather than rediscover.