How Do You Perform a Zero-Downtime PostgreSQL Schema Migration?

Problem and Scope

A user service runs on PostgreSQL, and its users table contains 200 million rows. username TEXT NOT NULL has a unique index. Every online request, background job, and event consumer currently reads and writes it. The team wants to rename the field to handle; during the migration, both columns represent exactly the same business value.

The service has 80 application instances, and a rolling deployment takes 30 minutes. Background workers may restart later than the web instances. Normal traffic cannot be paused, and there is no maintenance window. Existing SLOs remain in force: the migration must throttle or stop if write p99, lock waits, replication lag, or database load crosses production thresholds. The goal is to complete the rename while giving every phase an explicit invariant, entry gate, exit gate, and rollback path.

The 200 million rows, 80 instances, and 30-minute rollout are interview assumptions. “Zero downtime” means no planned outage while protecting existing SLOs; it does not mean ignoring short locks, failed attempts, or throttling. The core skill is splitting a database change into several forward- and backward-compatible releases, so this is a backend question. Sharding, cross-database migration, and multi-primary conflicts are outside the first-round scope.

What the Interviewer Is Evaluating

The first signal is whether the candidate recognizes the compatibility problem. If the team directly runs RENAME COLUMN username TO handle, old instances still address username while new instances know only handle. At least one version fails during the 30-minute mixed-version window. A fast database statement does not make the application rollout zero-downtime.

The second signal is separating schema migration, data migration, and code cutover. A safe design usually follows expand-and-contract: add a structure that old code can ignore, deploy compatible code, backfill and validate in batches, switch reads and writes, and only then remove the old structure. Each step should be independently deployable, observable, and retryable. A 200-million-row update and destructive DDL do not belong in one deployment transaction.

The third signal is understanding PostgreSQL lock and scan boundaries. Different ALTER TABLE subcommands require different locks, and PostgreSQL takes ACCESS EXCLUSIVE unless the documentation says otherwise. Adding a nullable column with no default does not rewrite the table, but it still needs a lock. If that lock request queues behind a long transaction, later requests can queue behind it. NOT VALID, VALIDATE CONSTRAINT, and CREATE INDEX CONCURRENTLY reduce the effect on concurrent writes, but each has distinct locks, load, and failure-recovery behavior.

Finally, the interviewer is looking for invariant-driven gates. A strong answer does more than list “add a column, dual-write, backfill, drop a column.” It explains when every writer is compatible, how missed writes are detected, why the backfill is idempotent, when reading only the new column becomes safe, what each phase can roll back to, and when the old column stops being a practical recovery path.

Clarifying Questions

• Where are all the writers? Inventory web services, workers, scheduled jobs, administrative scripts, database functions, triggers, CDC, ETL, BI queries, views, and external integrations. One unupgraded writer can continually recreate inconsistency.
• Must the columns remain exactly equal during migration? In this problem, yes: it is a pure rename. If handle also introduces normalization, new case rules, or user-selected values, conflict handling becomes a different data-migration problem.
• How is username uniqueness implemented? The new column needs an equivalent unique index or constraint. Confirm that case sensitivity, collation, null behavior, and any partial predicate do not change.
• Can application and database changes ship separately? They must. If the schema and application can only deploy as one indivisible operation, the team cannot advance through several compatibility phases.
• How long may the migration run? Backfilling 200 million rows may take hours or days. The design needs pause, resume, completion, and old-column retention policies that survive multiple releases.
• What does rollback mean? Rolling back application code, stopping a backfill, restoring old-column writes, and recovering a dropped column are four different operations. A normal application rollback is no longer safe after the old column is removed.

30-Second Answer

“I would not rename in place. I would use expand-and-contract. First add a nullable handle with a short lock_timeout; if the lock is unavailable, fail and retry. Compatibility release A still reads username but writes both columns atomically. After all 80 instances and every worker are upgraded, backfill rows with handle IS NULL in small, idempotent primary-key batches. Monitor nulls, mismatches, write errors, locks, p99, replication lag, WAL, and autovacuum throughout. After backfill, build the new unique index concurrently, add a NOT VALID check, validate it separately, and then set NOT NULL. Release B prefers handle with a username fallback and keeps dual writes. After observation, read only handle, then stop old-column writes. Retain the old column across the full rollback window and drop it only after code, jobs, views, and consumers no longer reference it. A failed gate leaves the system in its current compatible phase; dropping the column is not treated as an ordinary rollback point.”

Step-by-Step Solution

Define Migration Invariants and Gates First

Before executing DDL, write four invariants:

1. During mixed versions, username remains the rollback source of truth. handle may be null, but a non-null value must equal username.
2. After the compatibility release reaches every writer, each new write must update both columns in one transaction. One column cannot succeed without the other.
3. Reads cannot detach from the old column until null and non-null mismatch counts are zero, all writers are compatible, and the new index and constraints are valid.
4. Schema contraction cannot begin until production code, background jobs, views, reports, CDC, and rollback builds no longer require username.

Record the deployment version, start time, owner, progress cursor, dashboards, and exit conditions for every phase. The migration runner should hold a lease or PostgreSQL advisory lock so two executors cannot advance the same step concurrently. Every step also needs a unique version and durable success record, allowing a restart to resume rather than replay the whole migration.

Phase One: Expand the Schema Without Changing Behavior

Rehearse the DDL against a production-sized copy and inspect long transactions, lock queues, and disk headroom. Add a nullable column with no default:

BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE users ADD COLUMN handle text;
COMMIT;

A nullable column without a default does not rewrite 200 million rows, but ALTER TABLE still needs a strong lock. lock_timeout fails an attempt that cannot acquire the lock quickly, allowing the deployment system to retry later with jitter. Identify abnormal long transactions before execution and observe who is blocking whom during execution. Waiting indefinitely is unsafe because a queued strong lock can cause later table requests to pile up behind it.

Old applications ignore handle after this step, so application rollback remains independent. Do not combine the addition with NOT NULL, a volatile default, a unique constraint, and a full update. That would couple metadata change, table scans, index construction, and data write amplification into one high-risk operation.

Phase Two: Deploy Compatible Writers While the Old Column Remains the Read Source

Release A behaves as follows:

• Reads continue to use only username, so user-visible behavior is unchanged.
• User creation and updates write the same value to username and handle in one SQL transaction.
• A failure on either column rolls back the whole transaction; dual writes are not two asynchronous requests.
• Metrics record dual-write attempts, failures, and mismatches without logging real usernames.

During the release A rollout, instances that have not upgraded still write only username, so fresh handle IS NULL rows are temporarily valid. That exception closes only after all 80 web instances, workers, scheduled jobs, and independent consumers report a compatible version. If the team cannot account for every writer—for example, a third-party program writes directly to the database—a temporary database trigger can synchronize the columns. The tradeoff is hidden write behavior, extra cost, and more complex replication, CDC, and incident diagnosis. Treat such a trigger as migration infrastructure with a removal date.

Phase Three: Backfill Idempotently by Primary-Key Range

Start the background migration only after every writer is compatible. Use primary-key keyset ranges rather than OFFSET, and keep every batch in a short transaction:

UPDATE users
SET handle = username
WHERE id > $1
  AND id <= $2
  AND handle IS NULL;

The handle IS NULL predicate makes the batch safely retryable and avoids overwriting a value already written by an online request. Persist the last completed primary-key range. The runner can stop after any batch and continue from the last confirmed range after restart. Begin with one worker, then tune batch size and delay from measurements without promising fixed throughput in advance.

After every batch, watch write p99, database CPU, lock waits, replication lag, WAL, disk, dead tuples, and autovacuum. Reduce the batch size or pause as soon as any measure approaches its production threshold. The objective is stable completion within the SLO. A single 200-million-row transaction is unjustified merely because the script is shorter.

Run two independent validations at the same time:

SELECT count(*) FROM users WHERE handle IS NULL;

SELECT count(*)
FROM users
WHERE handle IS DISTINCT FROM username;

The first count should fall steadily to zero. The second uses IS DISTINCT FROM to capture both null differences and unequal non-null values. Any nonzero result blocks cutover and is investigated by primary-key range. The backfill must not silently overwrite a non-null conflict because that could hide an old writer or incorrect new logic that is still active.

Phase Four: Add the Index and Constraints

Because username is unique, the new column needs an equivalent unique index. After backfill and duplicate validation, execute this outside a transaction block:

CREATE UNIQUE INDEX CONCURRENTLY users_handle_key
ON users (handle);

A concurrent build allows normal writes to continue, but it does more work, scans the table twice, and waits for relevant transactions. It still consumes CPU and I/O. A failed build can leave an INVALID index behind. Recovery must inspect the catalog, remove the failed artifact, and retry after correcting the cause; a command failure alone does not prove that the database returned to its original state.

Establish non-nullness in phases as well:

ALTER TABLE users
  ADD CONSTRAINT users_handle_not_null
  CHECK (handle IS NOT NULL) NOT VALID;

ALTER TABLE users
  VALIDATE CONSTRAINT users_handle_not_null;

ALTER TABLE users
  ALTER COLUMN handle SET NOT NULL;

ALTER TABLE users
  DROP CONSTRAINT users_handle_not_null;

NOT VALID begins enforcing the check for later writes without immediately scanning old rows. VALIDATE CONSTRAINT then checks existing rows with a lower lock level. A valid CHECK proves there are no nulls, allowing the later SET NOT NULL to skip its usual full-table scan. Each DDL statement still gets a short lock wait, a separate execution step, and production monitoring. “Concurrent” and “no rewrite” do not mean “free.”

Phase Five: Cut Over Reads, Then Stop Old-Column Writes

Release B prefers handle, falls back to username when null, and continues atomic dual writes. The gate says nulls should already be zero, but the fallback preserves compatibility for rollback and isolates unexpected data. Start with a canary, expand gradually, and compare new-column and old-column results, errors, and business metrics.

After the observation window, release C reads only handle while continuing to write both columns. A read-path problem can still roll back to B or A because username remains current. Only after an observation period that covers background jobs, low-frequency endpoints, and a complete deployment cycle should release D stop writing username.

Stopping old-column writes changes rollback semantics. A later rollback to a build that knows only username first requires restoring dual writes and reverse-backfilling values created during the gap. Directly rolling back the application would expose stale data. Put this requirement in the runbook and deployment gate so incident handling does not depend on an engineer remembering it.

Phase Six: Delay Contraction

Before removal, use code search, query logs, dependency catalogs, and the consumer inventory to prove that username has no readers. Remove old indexes, constraints, triggers, and view dependencies first, then drop the column in a separate release:

ALTER TABLE users DROP COLUMN username;

Dropping the column crosses a destructive boundary. Even if PostgreSQL does not immediately rewrite the table, old applications, schema caches, views, and external queries can fail immediately. The removed data is also unavailable to an ordinary application rollback. The drop should occur at least one full rollback-retention window after read and write cutover, separately from the code release that stops using the column. A backup is disaster recovery, not a low-latency deployment rollback.

Example of a Strong Answer

“The main risk is compatibility during the 30-minute rolling deployment. I would split the change into expansion, compatible writes, backfill and validation, read cutover, and delayed contraction.

First I add nullable handle with a short lock_timeout; if the lock is not available, the attempt fails and retries. A nullable no-default column does not rewrite the table, but the DDL still takes a strong lock, so I inspect long transactions and the lock queue. Release A still reads username, and every writer updates both columns in one database transaction. The backfill starts only after all 80 instances, workers, and consumers have upgraded.

The backfill uses short primary-key range transactions with UPDATE ... WHERE handle IS NULL, persists progress, and is safe to retry. Its rate follows production p99, replication lag, WAL, dead tuples, and autovacuum. The null count must reach zero, and handle IS DISTINCT FROM username must remain zero. A non-null conflict is investigated rather than overwritten.

After the data gate, I build the equivalent unique index with CREATE UNIQUE INDEX CONCURRENTLY and handle any leftover invalid index after failure. I add CHECK ... NOT VALID, validate it separately, and then set NOT NULL. Release B prefers the new column with an old-column fallback and keeps dual writes. Release C reads only the new column but still dual-writes. Release D stops old-column writes only after a full observation cycle.

Every phase has a rollback path. Before read cutover I can roll the application back directly. While reading the new column but still dual-writing, I can return to old reads. After old writes stop, I must restore dual writes and reverse-backfill before an old application is safe. Finally, after code, jobs, views, CDC, and reports no longer reference username and the rollback window has passed, I drop it in a separate release. A failed gate leaves the system in a compatible state rather than advancing to destructive contraction.”

Common Mistakes

• Renaming the column in place → Old and new instances require different names during the rollout → Use a new column and multiple compatible releases.
• Updating 200 million rows in one transaction → The transaction amplifies WAL, locks, replication lag, bloat, and recovery time → Use short, idempotent primary-key range batches.
• Starting the backfill as soon as dual writes begin → Instances that have not upgraded can still create new nulls → Wait until every writer is compatible before making zero nulls a gate.
• Implementing dual writes as two requests → A timeout can update only one column → Update both atomically in one database transaction.
• Checking only handle IS NULL → Unequal non-null values escape detection → Also check IS DISTINCT FROM and investigate conflicts.
• Treating ADD COLUMN as lock-free → Metadata DDL still needs a table lock, and a queued DDL request can magnify blocking → Use short lock waits, retries, and lock/transaction monitoring.
• Building the unique index normally → A large-table index build can block writers for an unacceptable period → Use CONCURRENTLY and handle extra load and invalid-index recovery.
• Dropping the old column immediately after backfill → Low-frequency workers, views, schema caches, or rollback builds may still need it → Delay contraction across a complete observation and rollback window.
• Treating a backup as a rollback button → Restoring a 200-million-row database is much slower and riskier than an application rollback → Preserve an online-compatible structure until the destructive boundary.
• Promising a “zero-impact migration” → DDL, indexes, and backfill all consume locks or resources → Promise no planned outage, protect the SLO, and pause before thresholds are exceeded.

Follow-Up Questions

Why not add handle with a default value?

PostgreSQL can avoid a table rewrite for a non-volatile constant default, but no constant expresses “copy this row's existing username.” Even a physically fast default still needs a DDL lock and does not solve old instances writing only the old column. This migration still needs compatible writers and a data backfill. Whether future inserts need a default is a business-semantic decision, not a substitute for the rollout plan.

What if handle must be lowercased and made unique again?

That is no longer a pure rename. Define one normalization function and conflict policy, then count collisions in lower(username) on a replica or offline job. Decide whether to preserve a value, append a suffix, or require user action. Store original values and conversion status during backfill, and create the unique index only after conflicts reach zero. Online writes and the backfill must use the same normalization implementation.

What if all writers cannot upgrade together?

For an external system that cannot migrate quickly, add a temporary database trigger that copies an old-column write to the new column and records usage so the remaining caller can be found. The trigger should reject requests that provide conflicting values. Evaluate recursion, replication, and CDC behavior. Once every caller has migrated, disable and observe before removing the trigger so hidden business logic does not become permanent.

What if replication lag keeps increasing during backfill?

Pause new batches and let replicas catch up instead of adding workers to chase progress. Inspect batch size, commit cadence, WAL rate, long queries, and autovacuum. Resume with smaller batches and less concurrency. If reads rely on replicas, replication lag is already a user-visible risk; the backfill completion date is subordinate to the production SLO.

Can CREATE UNIQUE INDEX CONCURRENTLY simply be rerun after failure?

Do not rerun it blindly. An invalid index with the same name may remain in the catalog, and a concurrent unique build may already have enforced uniqueness against other transactions during a failed phase. Inspect index validity, identify the data or resource failure, remove the failed index according to the runbook, and then rebuild. The command also cannot run inside a regular transaction block, so the migration tool must support that execution mode.

Which phase is hardest to roll back?

After old-column writes stop, the old value begins to lag. After the old column is dropped, both data and schema cross a destructive boundary. The former requires restoring dual writes and reverse-backfilling before an old build is safe. The latter usually requires a forward repair or backup restoration. Keep these actions in separate releases and keep the old column current for a sufficiently long observation window.