Quick answer
A zero-downtime database migration is a compatibility strategy that aims to keep approved user journeys available while schema and application versions overlap. It is not a guarantee of zero locks, zero latency change, or zero failures. The safest common pattern is expand-contract: add a backward-compatible structure, deploy code that tolerates old and new states, backfill in bounded idempotent batches, verify invariants, enforce constraints through supported low-blocking phases, switch reads or ownership, remove old writers, and contract only after no supported version depends on the old shape.
Every PostgreSQL DDL statement has a lock and execution contract. A metadata-only change can still wait for a strong lock; a queued request can affect other sessions; a table rewrite consumes I/O, WAL, disk, and replica capacity. CREATE INDEX CONCURRENTLY allows ordinary writes during much of the build, but it performs multiple scans and waits, cannot run inside a transaction block, and can leave an invalid index on failure.
Plan migrations as state machines with entry conditions, observable progress, stop conditions, and roll-forward or rollback actions. Test old and new application versions concurrently. Bound lock and statement timeouts. Never combine a large backfill, constraint enforcement, read switch, and destructive cleanup into one irreversible deploy.
Shared order-history incident stage
During the order-history incident, the team wants a composite covering index, a normalized business-date field, and improved partition predicates. A hurried migration issued a normal index build while writes were busy. It waited or blocked beyond the intended change budget, adding lock and connection-pool pressure to the query-plan and cardinality regression.
The safer response separates mitigation from structural repair. Operations first restore capacity: pause optional reports and backfills, cancel only reviewed work, bound waits, and reduce retry amplification. The new schema is not introduced until the blocker graph and user SLI support a controlled change window.
The expand phase adds a nullable derived column or compatible structure without requiring every row to be rewritten in the same transaction. Application version N+1 can read the old source of truth and write or derive the new representation according to an explicit consistency protocol. A bounded backfill handles existing synthetic-style data classes while monitoring WAL, locks, pool wait, replica lag, and user tails.
The index is built concurrently under a reviewed lock and duration budget. Validation checks index validity and query behavior. Constraints are added and validated through supported PostgreSQL 18 phases where applicable. Only after all supported application versions use the new contract does a later release stop old writes and remove the obsolete column or index. Database Lock Contention and Long Transactions supplies the operational evidence.
Core mechanism and evidence boundary
Compatibility has three directions. The old application must tolerate the expanded schema. The new application must tolerate rows not yet backfilled and, during rollout, traffic from old writers. Operational tools, jobs, reports, and replicas must also tolerate both states. A migration is unsafe if only the newest request path was tested.
Expand-contract separates additive and destructive changes. Additive does not automatically mean harmless: ALTER TABLE variants acquire documented lock modes, may scan or rewrite data, and can wait behind transactions. Before execution, inspect the exact PostgreSQL 18 command, table size and churn, lock queue, disk, WAL, replica lag, and application deadlines. Set lock_timeout so inability to acquire the initial lock exits within the approved budget.
Backfills are production workloads. Use a stable key, bounded batch size, idempotent predicate, progress checkpoint, rate/concurrency limit, remaining-time budget, and pause control. Commit each approved unit so one transaction does not retain locks or snapshots across the entire table. Do not use offset pagination over a changing table. Validate the missing/invalid population independently of the progress cursor.
Dual writing is not automatically atomic. When old and new fields reside in the same row and transaction, a single database update can maintain both. Cross-table, cross-service, or asynchronous duplication needs a correctness protocol such as an outbox and idempotent consumer. Never hide a failed second write and call the migration complete.
Constraints can be staged. PostgreSQL supports adding some constraints as NOT VALID, which enforces them for later changes while postponing the existing-row scan, followed by VALIDATE CONSTRAINT under its documented lock behavior. Applicability differs by constraint type. A unique constraint often needs a validated unique index; concurrent unique builds have special failure and visibility consequences.
Define each phase with a monotonic compatibility invariant. During expand, no supported reader may fail because the new object exists. During dual-state operation, every accepted write must leave a recoverable source of truth. During backfill, replaying a batch must converge to the same state. Before read switch, the completeness query and reconciliation must be clean. Before contract, no supported binary, job, report, rollback, or operator procedure may reference the old shape.
Roll back application code separately from schema. Additive objects normally remain when an application rollout is rolled back, because immediately removing them would add DDL risk and could break partially deployed instances. A failed backfill is usually paused and corrected or rolled forward; reverting millions of already correct rows may be more dangerous. The runbook must choose the response for each phase before execution.
Schema migration tools often assume one transaction around a file. That is useful for atomic DDL that supports it, but incompatible with operations such as concurrent index creation. Record which steps are transactional, which are resumable, and which need external orchestration. Do not disable transactions for an entire migration merely to accommodate one command.
This article uses PostgreSQL 18 lock modes, concurrent index behavior, constraint validation, and progress views. These are PostgreSQL-specific, not a SQL standard or universal database interface. Migration state, rows backfilled, lock wait, WAL, and replica lag are operational fields, not a stable OpenTelemetry semantic convention. Object names, row keys, tenant IDs, PIDs, and SQL values do not belong in metric labels.
Minimal reproducible PostgreSQL 18 example
This migration sequence reuses the synthetic orders fixture from Database Query Performance for Backend Systems. In a disposable database, suppose the application needs a normalized business_date derived under an approved UTC rule. Expand first:
SET lock_timeout = '500ms';
SET statement_timeout = '5s';
ALTER TABLE orders
ADD COLUMN business_date date;The timeout values are examples. Inspect the exact command behavior on the deployed PostgreSQL version. Deploy compatible code that handles null for old rows and writes the new value for new/updated rows. Backfill bounded key ranges:
WITH batch AS (
SELECT id
FROM orders
WHERE business_date IS NULL
ORDER BY id
LIMIT 1000
FOR UPDATE SKIP LOCKED
)
UPDATE orders AS o
SET business_date = (o.created_at AT TIME ZONE 'UTC')::date
FROM batch
WHERE o.id = batch.id
AND o.business_date IS NULL;The application must define timezone semantics; UTC is not universally correct. The worker records a non-sensitive high-water mark, affected-row count, duration, lock wait, WAL, and errors, then pauses when user or replica guardrails fail. Multiple workers require a concurrency budget and correctness test.
Build the target index as a separate nontransactional operation:
SET lock_timeout = '500ms';
SET statement_timeout = '30min';
CREATE INDEX CONCURRENTLY orders_tenant_status_business_date_idx
ON orders (tenant_bucket, status, business_date DESC, id DESC)
INCLUDE (total_cents);Monitor pg_stat_progress_create_index, pg_locks, WAL, replicas, storage, writes, and validity in pg_index. If the build fails, inspect and remove or repair the invalid object according to the documented state before retrying.
After verifying no null or mismatched rows, stage an applicable check and validation. Test the exact constraint form and lock behavior in staging. A final NOT NULL, read switch, or old-column removal belongs to later reviewed releases, not the same emergency change.
Failure modes and dangerous misconceptions
Calling additive DDL lock-free. Even fast metadata changes acquire locks and can wait. Use a lock budget and inspect queued effects.
One giant backfill transaction. It retains locks and snapshots, creates rollback and replica pressure, and has no practical pause point. Batch by stable keys.
Using offset as progress. Concurrent changes alter positions, causing skips or repeats. Use a stable key plus idempotent missing-state predicate.
Deploying new-only readers before backfill. Mixed rows and old writers remain. Readers need explicit fallback or completeness gates until ownership switches.
Assuming dual writes are reliable. Partial failure can diverge representations. Keep atomic writes together or use a durable protocol and reconciliation.
Building indexes concurrently inside a migration transaction. PostgreSQL rejects that mode, and frameworks may wrap migrations automatically. Use a separate reviewed operation.
Retrying a failed concurrent build blindly. An invalid index can remain and still impose update overhead. Inspect state and cleanup first.
Contracting after the newest deploy. Old instances, jobs, rollbacks, reports, and consumers may still depend on the old structure. Prove absence across supported versions.
Using a feature flag as the only safety mechanism. A flag can select readers or writers, but it does not create missing schema compatibility, reconcile divergent rows, or remove locks. Test both flag states throughout overlap.
Backfilling with unlimited concurrency. More workers can saturate I/O, WAL, locks, pool slots, vacuum, and replicas. Adapt within a hard cap and pause on user guardrails.
Removing the old field immediately after switching reads. Delayed jobs, retries, rollback binaries, and audit processes may still write or read it. Require an observation window tied to actual ownership evidence.
Security, privacy, capacity, and cost implications
Backfills read and rewrite broad data sets that may contain PII or personal data. Use least-privilege workers, parameterized SQL, synthetic logs, encrypted transport, access auditing, and short retention. Never log row values merely to prove progress.
Do not use row key, tenant, user, order, PID, query ID, request ID, trace ID, partition, or raw error as metric labels. Aggregate by bounded migration name/version, phase, environment, database role, and finite outcome. Restricted logs can preserve a non-sensitive batch cursor and correlation.
Capacity includes DDL locks, scans, CPU, I/O, cache displacement, WAL, replica replay, temporary disk, index storage, backups, autovacuum, and application pools. Define guardrails for user tails, error rate, pool wait, lock wait, WAL rate, replica lag, disk, and batch duration.
Compatibility code and dual representations have maintenance cost. Give every temporary path an owner and removal gate. Do not remove it on a calendar alone; remove it when measured completeness and version ownership prove safety.
Testing and production validation
Build a migration matrix covering old application with old schema, old application with expanded schema, new application before backfill, mixed versions during backfill, new readers after completeness, rollback of each application version, and final contract. Assert business invariants and authorization in every state.
Test empty, null, invalid, concurrent update, deleted, newly inserted, and retried batches. Kill a worker mid-batch and verify idempotent recovery. Run multiple workers only after proving nonoverlap and bounded locking. Exercise timeout, lock failure, disk guardrail, replica lag, and pause/resume.
Rehearse DDL against representative table size and concurrent traffic. Capture locks, progress, WAL, storage, replicas, pool wait, user tails, index validity, and constraints. Test invalid concurrent-index cleanup and ensure tooling does not automatically rerun destructive steps.
Production phase gates require fresh evidence: expanded schema visible, compatible versions healthy, new writes complete, backfill population zero, reconciliation clean, index valid, constraints validated, reads switched, old writers absent, rollback policy updated, and user SLI stable. Contract in a separate release after these gates.
Run a restore rehearsal from a backup taken during the overlap phase. The restored schema and data must be intelligible to the documented recovery application version, and the migration controller must determine its phase without guessing from wall-clock time. This protects disaster recovery while two representations coexist.
Operations checklist
- Inventory every application version, job, report, consumer, migration wrapper, and rollback path.
- Specify old/new read and write behavior for each migration phase.
- Inspect exact PostgreSQL 18 lock, scan, rewrite, transaction, and concurrent-operation rules.
- Apply bounded lock and statement timeouts before DDL.
- Backfill with stable keys, idempotent predicates, small commits, rate limits, checkpoints, and pause controls.
- Monitor user SLI, pool and lock wait, WAL, replicas, disk, writes, maintenance, and progress.
- Keep dual writes atomic or use a durable consistency and reconciliation protocol.
- Verify concurrent index validity and clean failed objects before retry.
- Stage constraint validation only for supported constraint types and measured lock behavior.
- Keep PII and high-cardinality identifiers out of logs and metric labels.
- Switch reads and stop old writes only after completeness and mixed-version verification.
- Contract destructively in a later release with a current backup and rollback/roll-forward decision.
Official sources
- PostgreSQL 18: ALTER TABLE — lock levels, validation, and rewrite behavior; accessed 2026-08-04.
- PostgreSQL 18: Building Indexes Concurrently — scans, waits, transaction restriction, and invalid state; accessed 2026-08-04.
- PostgreSQL 18: Explicit Locking — table lock compatibility; accessed 2026-08-04.
- PostgreSQL 18: Progress Reporting — index and maintenance progress views; accessed 2026-08-04.
- Google SRE: Addressing Cascading Failures — bounded work and overload-safe operational changes; accessed 2026-08-04.
Continue the learning path
Before a Spring release depends on the new schema, Spring Boot Integration Testing with Testcontainers Explained shows how to apply production migrations to PostgreSQL and verify committed behavior through a fresh boundary. If lock or compatibility evidence changes during rollout, Production Spring Boot Incident Troubleshooting provides the reversible mitigation and recovery checks needed before roll-forward or rollback.
Plan lock ownership with Database Lock Contention and Long Transactions, create partition lifecycle safely with Table Partitioning and Partition Pruning, and verify the complete incident in Production Slow Query Troubleshooting. Compare roll-forward and rollback decisions in Database Migration Rollback. Continue through System Design or Topics.