Exit code 0 from pg_restore means the rows arrived. It does not mean the database is ready to serve traffic. A logical restore leaves two things behind that the planner and the executor both depend on: empty statistics and an empty visibility map. In a measured test published this week by Nikolay Samokhvalov, one join took 32.6 seconds immediately after restore and 23 milliseconds after VACUUM ANALYZE — about 1,400×.

That is the gap this runbook closes. Houston ops teams feel it more than most: a batch window in logistics or energy does not tolerate a database that is technically restored and practically unusable.

Why a freshly restored database is slow

A logical dump carries neither planner statistics (until Postgres 18, below) nor the visibility map. Two failures follow:

1. The planner guesses. With pg_stats empty, the planner uses default selectivity estimates. In the test, a column with 100 matching rows out of 1,000,000 was estimated at 2,713, and a join returning 25,000 rows was estimated at 1.9 million — so a sequential scan plus hash join looked cheap and ran over a 31 GiB heap. After ANALYZE the plan flipped to an Index Only Scan and the query dropped to 6.1 s.

2. The executor reads the heap anyway. An Index Only Scan skips the heap only on pages marked all-visible in the visibility map. A restore starts with the map empty, so the “index only” scan touched 24,983 heap pages from cold disk. VACUUM built the map and heap fetches went to zero — that is the step that took 6.1 s down to 23 ms, and it is the step most runbooks omit.

The Postgres 18 and 19 details that change the runbook

• pg_dump --statistics (18+). Column statistics can travel inside the archive, so the query ran at 6.1 s straight after restore. On 18 the archive carries CREATE STATISTICS definitions without their data; from 19 it carries the data too.
• The imported relallvisible is a trap. The dump records the source’s relallvisible (all ~4.1 million pages in the test) while the new map is empty, so the planner expects zero heap fetches and the scan makes 24,983.
• On Postgres 19, the first SELECT can write. On-access pruning now lets reads set visibility-map bits. On 19rc1 the first query after restore took 110–113 s instead of 32.6 s, because each page change after a checkpoint emitted a full-page image: about 31 GiB of WAL from one read-only query, shipped to every standby and into the archive. On 19, run maintenance before the first query.
• pg_upgrade avoids all of this. Both statistics and the visibility map survive an in-place upgrade, even with --link. If you are moving to 18 or 19, rehearse an in-place upgrade instead of a logical dump before you book the window.

Step 1 — inventory the restored database

Before maintenance, capture what you actually got. Row counts per table, and the sequences, which are the classic post-migration incident when anyone reloaded with --data-only or replayed selective tables:

-- row counts, largest first
select schemaname, relname, n_live_tup
from pg_stat_user_tables
order by n_live_tup desc
limit 20;

-- every sequence and where it stands (null last_value = never called)
select schemaname, sequencename, last_value, increment_by
from pg_sequences
where schemaname not in ('pg_catalog', 'information_schema')
order by 1, 2;

A null last_value means the sequence was never called — harmless after a fresh load, fatal if application inserts then collide with restored ids. Check each one against its table’s max(id) and setval() anything behind.

Step 2 — statistics, including the partitioned parents

Whole-database, parallel, then a second pass for partition parents:

vacuumdb --analyze --jobs 4 --dbname appdb

psql --no-psqlrc --set ON_ERROR_STOP=1 --dbname appdb <<'SQL'
select format('analyze %I.%I', n.nspname, c.relname)
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where c.relkind = 'p'
\gexec
SQL

Autovacuum analyzes leaf partitions and never builds statistics for partitioned parents, and queries against the parent need them. In the test, all 8 partitions were analyzed within 15 s while the parent still had no statistics after 480 s; the parent query used 403,072 buffers before a manual ANALYZE and 4,400 after. Client vacuumdb 14–18 skips parents too, and on 19 --analyze still does, so the explicit ANALYZE above is worth the seconds.

Step 3 — build the visibility map, freeze while you are there

# empty database, fresh window
vacuumdb --analyze --freeze --jobs 4 --dbname appdb

# already serving traffic: skip the truncate lock
vacuumdb --analyze --no-truncate --dbname appdb

# cheaper WAL if built with LZ4 (check pg_config --configure)
PGOPTIONS='-c wal_compression=lz4' vacuumdb --analyze --freeze --jobs 4 --dbname appdb

wal_compression = lz4 cut maintenance WAL from 1.60 GiB to 0.54 GiB in that test, and VACUUM (FREEZE) wrote about the same WAL as a plain VACUUM while sparing the table a later anti-wraparound rewrite. Freezing now is nearly free; freezing later is not. Keep full_page_writes = on either way.

Step 4 — prove integrity and plans, do not assume them

# indexes and heap/index consistency, with the extension installed
pg_amcheck --dbname appdb --heapallindexed --parent-check --jobs 4

# then the queries that matter, on the restored copy
psql --no-psqlrc --dbname appdb -c \
  "explain (analyze, buffers) select ... where ...;"

On every Index Only Scan node check Heap Fetches. A non-zero count after VACUUM means the map is incomplete — the vacuum did not finish, or autovacuum re-dirtied pages afterwards. Compare estimated and actual row counts on join nodes; a factor-of-ten miss means a stale statistic, not a bad query. pg_amcheck is also the cheapest early warning for corrupt pages inside a suspect archive, the failure mode in our pgBackRest page checksum writeup. Run EXPLAIN ANALYZE on reads only.

Step 5 — the cutover gates

• Connection budget. A restored cluster at the old max_connections plus a new pooler tier is how you get “too many clients” at 02:00.
• Extensions. Confirm every CREATE EXTENSION landed, including contrib packages that are easy to forget on a new host (pg_stat_statements, amcheck, pgcrypto).
• Replication. Point standbys and slots at the new cluster before writes cut over, and confirm zero lag under replayed traffic, not an idle connection.
• Observability. Run select pg_stat_reset(); so dashboards start clean, then confirm pg_stat_statements has a sample before declaring success.
• Backups. Re-point the physical backup job at the new cluster and take one verified base backup before the first write. A restore you have not tested is a hypothesis.
• Autovacuum. Large restored tables keep default scale factors; set per-table values before they become the next slow-query ticket.

Fitting it into the migration window

The sequence is short but not instant: on a multi-terabyte database, ANALYZE plus VACUUM with four workers can run for hours, and it must finish before the first application connection. Budget maintenance inside the window, or run it against a pre-cutover copy and cut over with pg_upgrade instead. Write the gate into the runbook with a named owner and an explicit command — “migration complete” is a plan check, not a row count check.

If you would rather not learn this at 02:00, we will review your Postgres cutover runbook before the migration window and rehearse the maintenance steps on a copy of production.