PostgreSQL / Advanced: evidence and reliable operations / Lesson 18

Operations, migrations and reliability

Connect query correctness to controlled PostgreSQL service changes. Review version compatibility, transactional DDL exceptions, connection budgets and reversible cutover evidence.

About 4 min readContent reviewed 2026-10-25

On this page

Lesson goals

Plan upgrades and schema evolution for exact versions.
Use evidence to bound locks, connections and recovery risk.

Read the release you actually run

This course is reviewed against PostgreSQL 18 documentation on 2026-10-25; the official pages currently identify 18 as current and 19 as development/prerelease. A feature in a development manual is not automatically available on your production server. PostgreSQL major upgrades need a supported migration method such as logical dump/reload or pg_upgrade, with extension and collation compatibility review. Minor updates follow a different procedure, and each release note can contain required actions. Never copy a data directory across major versions because the SQL protocol looks familiar.

Inspect effective settings before proposing changes

SHOW reads settings visible to the session. Connection limits, timeout policy and search paths interact with application pools and migrations, so record effective values rather than relying on an example configuration file. Role- or database-specific settings may override broader defaults. These observations do not prove that a pool is sized appropriately or that a request deadline is enforced at every layer. Configuration changes require their own reviewed deployment and restart/reload classification; this course makes no live server changes.

sql Illustrative SQL; expected results are reasoned, not measured.

SHOW server_version;
SHOW max_connections;
SHOW statement_timeout;
SHOW lock_timeout;
SHOW search_path;

Values depend on the deployed server and session. No recommended universal numeric tuning is implied.

Expand, verify and contract in stages

For a renamed field, add a compatible new representation, deploy code able to handle both, backfill in bounded batches and validate convergence before switching reads. Remove the old field only after all callers and rollback requirements are satisfied. PostgreSQL supports transactional ordinary DDL, but CREATE INDEX CONCURRENTLY and several administrative commands have transaction-block restrictions. Concurrent index creation can leave an invalid index after failure, so its runbook must inspect completion state rather than equate command start with a usable index.

Check NULL-aware migration agreement

Assume old_label and new_label should have the same comparison semantics during a synthetic migration. IS DISTINCT FROM detects unequal values including one-sided NULLs. A zero mismatch count is necessary under that contract, but does not prove byte identity when collations or normalization policies make different text compare equal. Use a stronger explicitly designed comparison when provenance requires it. The query is read-only and demonstrates the acceptance condition without performing the backfill or dropping either column.

sql Illustrative SQL; expected results are reasoned, not measured.

SELECT COUNT(*) AS mismatches FROM quay_lab.customers
WHERE old_label IS DISTINCT FROM new_label;
SELECT customer_id, old_label, new_label FROM quay_lab.customers
WHERE old_label IS DISTINCT FROM new_label
ORDER BY customer_id LIMIT 20;

Expected after a correct synthetic backfill: zero mismatches and no sample rows under the stated equality policy.

Define a last safe rollback point

A connection pool must bound concurrent database work, clean failed transactions and respect the service timeout policy. Raising max_connections alone can amplify memory and contention rather than improve throughput. Before cutover, rehearse restore time, query behavior, privileges, extension availability and write routing. Once the destination accepts unique writes, switching back without reconciliation can lose history. Fence writers, document ownership and retain protected recovery artifacts. Reliability comes from explicit change criteria and tested recovery, not a claim that one upgrade tool or green health endpoint proves every data invariant.

Common errors and fixes

A concurrent index exists but is invalid

Why it happens: The concurrent build failed before completing its required stages.
How to fix it: Inspect catalog validity and follow the documented recovery/rebuild procedure.
Prevent it next time: Verify usable index state after deployment, not only object presence.

A major upgrade is attempted by swapping binaries only

Why it happens: Minor-update assumptions were applied to incompatible major storage versions.
How to fix it: Use a supported upgrade path rehearsed with extensions and backups.
Prevent it next time: Record exact source/destination versions and read intervening release notes.

Rollback loses destination-only writes

Why it happens: The plan changed endpoints without reconciling divergent history.
How to fix it: Fence writers and reconcile using a tested recovery or reverse-transfer path.
Prevent it next time: Define rollback eligibility before the first destination write.

Check your understanding

1. Why does PostgreSQL transactional DDL not justify placing CREATE INDEX CONCURRENTLY inside BEGIN?

Show worked solution

Transactional support is command-specific. Concurrent index creation has a multi-transaction implementation and cannot run inside an ordinary explicit transaction block. Use the documented deployment sequence and inspect index validity after completion or failure. Do not generalize from CREATE TABLE to every administrative operation merely because both affect database structure.

2. A team plans to reverse a migration by restoring the old endpoint after a day of new writes. What must the review demand?