Canary Upgrades, Performance Comparison, Regression Detection, and Post-Upgrade Runbooks

Learning outcomes

PostgreSQL 18 starts successfully, integrity checks pass, and the application smoke test returns HTTP 200. The team wants to delete PostgreSQL 17 immediately. But an upgrade changes more than storage: optimizer rules, statistics transfer, default settings, asynchronous I/O, JIT behavior, collations, extensions and drivers can all alter workload performance. “Server is up” is the beginning of acceptance, not the end.

1. Capture old and new environment fingerprints before comparing results

SELECT version();SELECT name, setting, sourceFROM pg_settingsWHERE name IN (  'shared_buffers',  'work_mem',  'effective_cache_size',  'random_page_cost',  'effective_io_concurrency',  'io_method',  'jit',  'jit_above_cost',  'max_parallel_workers_per_gather',  'default_statistics_target')ORDER BY name;SELECT extname, extversionFROM pg_extensionORDER BY extname;

A result is not comparable if one side used JIT, different worker/cost settings, missing extension versions or a different storage path without recording that fact.

2. Rebuild/confirm the same logical dataset and business invariants

SELECT count(*) AS orders,       sum(amount) AS amount_sum,       count(*) FILTER (WHERE status='completed') AS completed_orders,       min(order_id) AS min_id,       max(order_id) AS max_idFROM app.ch23_order;SELECT region_code, count(*) AS rows, sum(amount) AS amount_sumFROM app.ch23_orderGROUP BY region_codeORDER BY region_code;

If Chapter 23's schema migration was not performed on the comparison copy, use the pre-migration invariant set instead. Compare equivalent schemas and application contracts; do not call a schema difference a planner regression.

3. Use representative workload classes, not one heroic query

EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS, TIMING OFF, SUMMARY ON)SELECT order_id, status, amountFROM app.ch23_orderWHERE tenant_id=7  AND customer_id=11234ORDER BY created_at DESCLIMIT 20;

EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS, TIMING OFF, SUMMARY ON)SELECT tenant_id, status,       count(*) AS orders,       sum(amount) AS amount_sumFROM app.ch23_orderGROUP BY tenant_id, statusORDER BY tenant_id, status;

BEGIN;UPDATE app.ch23_orderSET amount = amount + 0.01WHERE order_id BETWEEN 1000 AND 1999;ROLLBACK;

The rolled-back write lets you observe locking/WAL/executor behavior without changing final business values. For real benchmark replay, use a disposable copy and application-level transaction mix/concurrency that reflects production.

4. Control statistics before interpreting plan differences

PostgreSQL 18 pg_upgrade can preserve most ordinary optimizer statistics, but not all statistics. Dump/restore may carry statistics only when you deliberately use PostgreSQL 18's statistics options. A new cluster with missing/immature stats is not a fair planner comparison.

SELECT relname,       last_analyze,       last_autoanalyze,       n_live_tup,       n_dead_tupFROM pg_stat_user_tablesWHERE schemaname='app'ORDER BY relname;SELECT stxname, stxkeysFROM pg_statistic_extWHERE stxnamespace='app'::regnamespaceORDER BY stxname;

Run the documented post-upgrade ANALYZE sequence before declaring a regression caused by the new planner. Then store the plan and cardinality estimates from both sides.

5. Cold cache versus warm cache is a test dimension

Do not run old cluster after days of production cache warmup and compare it to the first query on a newly started target. PostgreSQL shared buffers and the operating-system page cache both affect results.

There is no portable SQL command that safely makes the entire OS/storage hierarchy “cold.” Do not fake cold-cache equality by claiming a single DISCARD command flushes the operating-system cache.

6. JIT, parallelism and version defaults can masquerade as regressions

SELECT name, setting, sourceFROM pg_settingsWHERE name IN (  'jit',  'jit_above_cost',  'max_parallel_workers_per_gather',  'parallel_leader_participation',  'effective_io_concurrency',  'io_method')ORDER BY name;

PostgreSQL 18 introduced major I/O/planner/upgrade changes. A plan that differs from the old major is not automatically wrong. First ask whether cardinality, costs, available indexes, JIT/parallel/AIO settings and extension operator classes are comparable.

7. Compare WAL and I/O as rates over the same workload window

SELECT clock_timestamp() AS observed_at,       wal_records, wal_fpi, wal_bytes, wal_buffers_full, stats_resetFROM pg_stat_wal;-- Deliberately use columns common to PostgreSQL 17 and 18.SELECT backend_type, object, context,       reads, read_time,       writes, write_time,       writebacks, extends,       fsyncs, fsync_time,       stats_resetFROM pg_stat_ioWHERE object IN ('relation','wal')ORDER BY object, backend_type, context;

The comparison query intentionally uses pg_stat_io columns shared by PostgreSQL 17 and 18. PostgreSQL 18 adds direct read/write byte counters and WAL I/O rows, while PostgreSQL 17 exposes a different column set. On the old server, the object='wal' predicate simply contributes no WAL rows; compare WAL generation separately through pg_stat_wal. Version-specific richer metrics are useful after the common baseline is established.

Take snapshots before and after an identical replay and subtract. Lifetime totals are incomparable when reset times differ. PostgreSQL I/O counters still need OS/storage corroboration; WAL volume can differ legitimately when physical layout/checkpoint/full-page-write conditions differ.

8. Store local benchmark samples instead of announcing one timing

DROP TABLE IF EXISTS app.ch23_upgrade_sample;CREATE TABLE app.ch23_upgrade_sample (  cluster_label text NOT NULL,  workload_name text NOT NULL,  run_no integer NOT NULL,  observed_at timestamptz NOT NULL DEFAULT clock_timestamp(),  elapsed_ms numeric NOT NULL,  rows_observed bigint,  notes text,  PRIMARY KEY (cluster_label,workload_name,run_no));-- Insert measurements produced by your repeatable harness.-- Do not fabricate values in course material.

SELECT cluster_label, workload_name,       count(*) AS samples,       percentile_cont(0.50) WITHIN GROUP (ORDER BY elapsed_ms) AS p50_ms,       percentile_cont(0.95) WITHIN GROUP (ORDER BY elapsed_ms) AS p95_ms,       min(elapsed_ms) AS min_ms,       max(elapsed_ms) AS max_msFROM app.ch23_upgrade_sampleGROUP BY cluster_label,workload_nameORDER BY workload_name,cluster_label;

The Academy deliberately supplies no fake benchmark rows. Feed this table from actual timed runs on your old/new disposable clusters or from an external harness.

9. Canary gates must be service-specific

A canary is a deliberately limited share of workload or a read-only shadow that exercises the upgraded system before full cutover. Define gates in terms of Service Level Objectives (SLOs) and correctness—not generic PostgreSQL folklore.

The numerical threshold belongs to ServiceHub's production SLO/risk budget. A course cannot honestly prescribe “rollback if p95 rises 10%” for every system.

10. The rollback boundary changes when the new primary accepts writes

Use read-only canary/shadow traffic before the irreversible write cutover where possible. At the write switch, record the exact rollback mechanism and maximum tolerated recovery point, not just “old server retained.”

11. Post-upgrade verification runbook

SELECT version();SELECT extname, extversion FROM pg_extension ORDER BY extname;SELECT c.oid::regcollation,       c.collversion,       pg_collation_actual_version(c.oid) AS actual_versionFROM pg_collation AS cWHERE c.collversion IS DISTINCT FROM pg_collation_actual_version(c.oid)ORDER BY 1;SELECT datname, numbackends, xact_commit, xact_rollback,       deadlocks, temp_bytes, stats_resetFROM pg_stat_databaseWHERE datname=current_database();SELECT count(*) FILTER (WHERE NOT indisvalid) AS invalid_indexesFROM pg_indexWHERE indrelid IN (  SELECT oid FROM pg_class  WHERE relnamespace='app'::regnamespace);

Then complete non-SQL gates: backup and Point-in-Time Recovery (PITR) restore verification, WAL archive, replica/failover health, scheduled jobs, pooler/drivers, monitoring/alerts, log errors, OS package versions, extension packages, secrets/TLS, maintenance automation and capacity headroom.

12. Cleanup only after the rollback/observation window

DROP TABLE IF EXISTS app.ch23_upgrade_sample;DROP TABLE IF EXISTS app.ch23_order CASCADE;

In production, deleting the old cluster, upgrade backup, migration slots, comparison telemetry or temporary compatibility code is itself a later approved change. Retain them until their documented rollback/evidence purpose expires.

Authoritative references

Upgrade, compatibility, locking, and migration behavior is version-sensitive. These PostgreSQL primary sources define the mechanisms used here; always read the exact source/target release notes during a real change.

• PostgreSQL 18 — pg_upgrade
• PostgreSQL 18 — Release 18
• PostgreSQL 18 — EXPLAIN
• PostgreSQL 18 — Monitoring Statistics
• PostgreSQL — Versioning Policy