Part 10 · 3 chapters · ~18 min

Operating Postgres

The configuration knobs that matter with reasons, the pg_stat views and what each tells you, lock modes, pg_locks and lock queues, the ACCESS EXCLUSIVE trap, safe schema migrations by lock taken, backups with pg_dump, pg_basebackup, pgBackRest and WAL-G, and upgrades with pg_upgrade or logical replication.

Configuration and the stat views

setting	starting point	reason
`shared_buffers`	25% of RAM	Postgres' own cache; the OS cache does the rest
`effective_cache_size`	50-75% of RAM	planner hint for index costing
`work_mem`	16-64 MB, per sort/hash node	avoid disk spills without OOM across many backends
`maintenance_work_mem`	512 MB - 2 GB	faster vacuum and index builds
`max_wal_size`, `checkpoint_timeout`	several GB, 15 min	fewer checkpoints and full page writes
`random_page_cost`	1.1 on SSD	the planner should like indexes on fast storage
`autovacuum_*`	lower scale factors on big tables, higher cost limits	vacuum keeps up
`idle_in_transaction_session_timeout`, `statement_timeout`	set them	protect vacuum and locks from forgotten sessions

view	tells you
`pg_stat_activity`	who is running what, waiting on what
`pg_stat_user_tables` / `_indexes`	scans, tuples, dead tuples, vacuum times, index use
`pg_statio_*`	buffer hits versus reads per table and index
`pg_stat_statements`	the workload, normalised
`pg_stat_replication`, `pg_replication_slots`	replica lag and retained WAL
`pg_stat_io` (16+)	IO by backend type and context

Locks and safe migrations

code

-- who blocks whom
SELECT blocked.pid, blocked.query, blocking.pid AS blocking_pid, blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) b(pid) ON true
JOIN pg_stat_activity blocking ON blocking.pid = b.pid;

operation	lock / cost	safe pattern
ADD COLUMN (nullable or with a constant default)	ACCESS EXCLUSIVE, metadata only (11+)	lock_timeout + retry
ADD COLUMN with a volatile default	rewrites the table	add nullable, backfill in batches, then set default
CREATE INDEX	SHARE: blocks writes	CREATE INDEX CONCURRENTLY
ADD FOREIGN KEY / CHECK	validates every row under lock	ADD ... NOT VALID, then VALIDATE CONSTRAINT
SET NOT NULL	full scan under lock	add a NOT VALID CHECK (col IS NOT NULL), validate, then SET NOT NULL (uses it)
ALTER COLUMN TYPE	usually a rewrite	new column, dual write, backfill, switch

THE ACCESS EXCLUSIVE TRAP

a harmless ALTER waits behind a long query, and everything else queues behind the ALTER

swipe the figure sideways, or tap expand for full screen

1/4

the long reader

An analytics query has held ACCESS SHARE on the table for four minutes. That is fine: readers do not block readers or writers.

a long query holds a weak lockharmless on its own

Backups and upgrades

tool	kind	use
`pg_dump`	logical (SQL or custom format), per database	small databases, migrations, partial copies; slow restore at scale
`pg_basebackup`	physical copy of the cluster	seeding standbys; base for PITR
pgBackRest	physical, incremental and differential, parallel, verified, with WAL archiving	production backup and PITR
WAL-G	physical with WAL archiving to object storage, delta backups	cloud-native PITR

Upgrades: pg_upgrade --link rewrites the catalogue for the new major and hard-links data files: minutes of downtime regardless of size, then ANALYZE (statistics are not carried over before 18). For near-zero downtime, build the new version as a logical replica, let it catch up, then switch writes over. Always test the restore, and time it: restore time is the real RTO.