PostgreSQL Pre-Maintenance Checklist

How to use this checklist

Before any planned change: a configuration parameter, a schema migration, a vacuum, an index rebuild, a restart.

It takes about ten minutes and most of its value is in two items — the written rollback and the lock_timeout.

Classify the change before you plan the window

pg_settings.context answers blast radius, rollback and window in one column

`context`	Takes effect	Blast radius	Rollback
`user`	Immediately, per session	One session or one role	`RESET`, or reconnect
`superuser`	Immediately, per session	One session or one role	`RESET`
`sighup`	On reload	Whole cluster	Reload again
`postmaster`	On restart	Whole cluster, plus an outage	Another restart

The most common avoidable mistake is treating a user parameter as a cluster-wide change. work_mem is the usual example: it is almost always a per-role setting and almost always applied globally.

And check nothing is already pending:

SELECT name, setting FROM pg_settings WHERE pending_restart;

A restart in your window applies whatever somebody else left there.

The lock queue is the most common way a change becomes an outage

A queued heavy lock blocks requests that arrive after it

AccessShareLock — what every SELECT takes — conflicts only with AccessExclusiveLock. So readers do not conflict with each other.

They conflict with the queue. A pending AccessExclusiveLock sits in front of every subsequent lock request, so readers arriving after it wait even though what they want conflicts with nothing currently held.

Measured: one idle in transaction session held AccessShareLock on a table; a VACUUM FULL waited for it; every query arriving afterwards waited for the VACUUM FULL. The rewrite itself took 159 milliseconds. The outage lasted eleven minutes and reached services that never touch that table, because the connection pool filled with blocked sessions.

Two pre-checks prevent it:

-- who is holding the table open and doing nothing?
SELECT pid, usename, application_name, now() - state_change AS idle_for
FROM pg_stat_activity WHERE state = 'idle in transaction';

-- and bound your own statement
SET lock_timeout = '5s';

Failing fast and retrying is nearly always better than blocking.

Check what will collide

A scheduled job taking ACCESS EXCLUSIVE during your window will cancel whatever is running. For a vacuum that is expensive in a specific way:

For vacuum work, check the horizon first

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

A vacuum against a pinned horizon runs for hours, exits zero, and removes nothing. The VERBOSE output says so in a specific way: 0 removed, a large not yet removable count, and no new relfrozenxid line at all.

Resolve the holder first, or the window is spent for nothing.

Define the verification before the change

What will be checked, and what result counts as success. Deciding afterwards invites confirmation of whatever happened, and “it seems fine” is not a state anybody can act on later.

Same for the abort criteria. A change with none continues until somebody loses their nerve, which is reliably later than it should be.

Where the numbers come from

The pre-change state is captured, not remembered: pg_settings for the non-default parameters, row counts on the significant tables, EXPLAIN output for the significant queries, and the replication topology with each standby’s position. None of these can be reconstructed afterwards, and they are what the post-maintenance review compares against.

The window size comes from a rehearsal on production-scale data. The vacuum horizon comes from pg_stat_activity, pg_replication_slots and pg_prepared_xacts, checked before any vacuum work, because tuning autovacuum harder achieves nothing while a snapshot is pinned.

Access this needs

Read access to the change record, the runbook the change follows, and the rollback it names. A role holding pg_monitor on every affected node for the pre-change state, and pg_read_all_settings for the configuration this change touches.

Read access to the most recent backup’s record and to the date it was last proven by restore — not the date it was taken.

Nothing in this checklist requires the authority to make the change. It is the gate the change passes, and it is completed by somebody who can say no.

What the review produces

A dated record naming the reviewer, the change, the window, the rollback and its cost, and the disposition of every item — completed before the window opens, not written up afterwards.

Attach the captured pre-change state, because it is the only thing that makes the post-maintenance verification possible. A change whose verification was not defined before it started cannot be verified after it, and that is a blocker rather than an observation.

Sign-off

Reviewer: ________________ Date: ___________
Change owner: _____________ Date: ___________
Service owner: ____________ Date: ___________

Every critical item must pass before the window opens. A failing critical item postpones the window; it is not a risk to carry into it. Record the date, the reviewer, and the name of whoever authorised proceeding with any item unmet.