Planned maintenance and its resource cost · PostgreSQL

Maintenance is not free, and each operation spends a different currency. Scheduling it well means knowing which.

The four currencies

Operation	I/O	Locks	WAL	Space
`VACUUM`	Moderate	`SHARE UPDATE EXCLUSIVE`	Low	None
`VACUUM FULL`	Full rewrite	`ACCESS EXCLUSIVE`	Full rewrite	Second copy
`ANALYZE`	Sampled read	`SHARE UPDATE EXCLUSIVE`	Minimal	None
`REINDEX`	Rebuild	`ACCESS EXCLUSIVE`	Full index	New index
`REINDEX CONCURRENTLY`	Rebuild ×2 passes	`SHARE UPDATE EXCLUSIVE`	Full index	Both indexes
`CREATE INDEX`	Full scan	`SHARE` — blocks writes	Full index	New index
`CREATE INDEX CONCURRENTLY`	Two scans	`SHARE UPDATE EXCLUSIVE`	Full index	New index
Anti-wraparound freeze	Full scan	`SHARE UPDATE EXCLUSIVE`	Very high	None

The bolded cells are what makes an operation unschedulable during business hours.

What the locks actually cost

Destructivethe locks VACUUM FULL holds while running— Captured on 18.6 from a third session during a rewrite of a 4,100 MB table.

$ SELECT l.pid, l.mode, l.granted, c.relname FROM pg_locks l
JOIN pg_class c ON c.oid=l.relation WHERE c.relname LIKE 'big_bloat%';

 pid | mode                | granted | relname
-----+---------------------+---------+----------------
245 | AccessExclusiveLock | t       | big_bloat_pkey
245 | ShareLock           | t       | big_bloat
245 | AccessExclusiveLock | t       | big_bloat
247 | ShareLock           | t       | big_bloat
247 | AccessExclusiveLock | t       | big_bloat_pkey
(5 rows)

Lesson VIII-06 measured what that does to ordinary traffic: a plain SELECT count(*) issued one second later, with lock_timeout = 4s, timed out. ACCESS EXCLUSIVE conflicts with everything, including reads.

And a lock request queues everything behind it, which lesson IX-05 measured directly: a plain SELECT was refused after 6 seconds while the only granted lock was another plain SELECT — the queue, not the holder.

The concurrent forms are not free, they are differently expensive

CREATE INDEX CONCURRENTLY and REINDEX CONCURRENTLY avoid the blocking lock. In exchange:

They make two passes over the table instead of one.
They wait for older transactions to finish between passes, so a single long-running transaction can stall them indefinitely.
REINDEX CONCURRENTLY holds both indexes on disk at once.
They can fail and leave an invalid index behind.

That last one is the operational trap:

-- after a failed CONCURRENTLY build, find the wreckage
SELECT c.relname, i.indisvalid
  FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
 WHERE NOT i.indisvalid;

An invalid index is not used by the planner and is maintained on every write — the worst of both. It must be dropped and rebuilt, and nothing tells you it is there unless you look.

WAL is a currency people forget

Read-only / Safefreezing a small table— Captured on 18.6. An anti-wraparound vacuum on a 3.5 MB table.

$ -- WAL generated by the freeze

445 full page images, 3.6 MB of WAL to freeze a 3.5 MB table

More WAL than the table. Every frozen page is a full page image, as lesson XII-01 established. On a terabyte cluster reaching wraparound age, that is a terabyte of WAL — which must flow through pg_wal, into the archive, and to every standby, with lesson XVII-02’s consequences if any of them cannot absorb it.

A VACUUM FULL of a large table has the same shape: it rewrites every page, so it generates WAL proportional to the table.

Schedule for abandonment, not just for completion

The useful question is not “how long will this take” but “what happens if I have to stop it at minute 40?”

Operation	Interrupted at 40 minutes
`VACUUM`	Fine. Progress to that point is kept
`ANALYZE`	Fine
`CREATE INDEX CONCURRENTLY`	Leaves an invalid index. Drop it
`REINDEX CONCURRENTLY`	Same
`VACUUM FULL`	All work lost. Space and I/O spent for nothing
`ALTER TABLE` with a rewrite	All work lost, and it held the lock throughout
`pg_upgrade`	Depends on mode; `--link` may leave the old cluster unusable

The rows where interruption loses everything are the ones that need a window long enough for the whole operation plus margin — and a decision, made in advance, about what you do if the window runs out.

pg_stat_progress_vacuum, pg_stat_progress_create_index and pg_stat_progress_cluster report how far along an operation is, which turns “should we let it finish?” from a guess into arithmetic.

SELECT p.pid, p.phase,
       pg_size_pretty(p.heap_blks_total * 8192::bigint)   AS total,
       pg_size_pretty(p.heap_blks_scanned * 8192::bigint) AS scanned,
       round(100.0*p.heap_blks_scanned/NULLIF(p.heap_blks_total,0),1) AS pct
  FROM pg_stat_progress_vacuum p;

Ordering

When several operations are needed, the order changes the total cost:

VACUUM first — it may make the rest unnecessary.
ANALYZE after any large data change, so the planner is not working from stale statistics.
REINDEX only where bloat is measured, not on a schedule. Lesson VIII-06 measured a table with zero dead tuples and 49.96% free space — bloat is not dead tuples.
VACUUM FULL last, and rarely. It is the only one that returns space unconditionally, and the most expensive way to do it.

Why VACUUM FULL is a last resort, and what to do instead

VACUUM FULL rewrites the table into a new file and swaps it in. That is why it needs an ACCESS EXCLUSIVE lock, a second copy of the data, and WAL proportional to the whole table.

The alternatives, in order of preference:

Fix the cause. Bloat comes from dead tuples that vacuum could not remove, and lesson VII-05 measured why: a held snapshot. A cluster that bloats repeatedly has a long-transaction problem, and rewriting the table treats the symptom on a schedule forever.

Tune autovacuum for that table. Lesson VIII-03 measured a default table left permanently holding 67,206 dead tuples because it never reached its threshold, against a tuned table holding zero. Per-table settings are frequently the whole fix.

pg_repack, an extension, rewrites a table with only brief exclusive locks at the start and end, using triggers to capture changes during the rebuild. It needs the same second copy of the data and it does not block traffic for the duration. This course did not test it, and it is the standard answer where a VACUUM FULL window cannot be found.

Accept the bloat. A table that is 40% free space and stable is wasting disk and not much else. Lesson VIII-06’s measurement — zero dead tuples and 49.96% free — describes a table that had already been vacuumed and simply had space in it. Rewriting it buys disk and costs an outage.

The case where VACUUM FULL is genuinely right is narrow: a one-off event created enormous bloat, the table will not regrow into it, a maintenance window exists, and the space is needed. Outside that, it is usually the expensive answer to a question that had a cheaper one.

What to take from this

Four currencies: I/O, locks, WAL, space. Each operation spends a different mix.
ACCESS EXCLUSIVE conflicts with reads. Measured: a plain SELECT timed out after 4 s against a VACUUM FULL.
Concurrent forms trade blocking for two passes, waiting on old transactions, double space, and invalid indexes on failure.
WAL is a real cost: 3.6 MB to freeze a 3.5 MB table.
Ask what happens if you abandon the operation, not just how long it takes. Use the pg_stat_progress_* views.
VACUUM → ANALYZE → REINDEX where measured → VACUUM FULL rarely.

Cross-course references

Linux for Production Sysadmins — Part LXIV (Rolling maintenance) covers sizing a window from a measurement, and Part LXXIII (Change management) covers recording what it will cost before it is approved.
Ansible for Production Sysadmins — Part XLVIII (Maintenance windows and rollback) and Part XXX (Host targeting and blast radius) cover running the work across an estate without doing all of it at once.