TL;DR: Expand and contract. For an incompatible representation change, keep both forms available during the transition. Add the new shape, deploy code that writes both and reads the old, backfill, switch reads, then remove the old shape in a later release. The early steps preserve code rollback. Contracting the schema removes that option and needs a deliberate recovery boundary.
How to approach it
State the constraint that generates the answer: during any rollout, old and new code run simultaneously, so the schema must be compatible with both. Then give the steps as separate deploys, and be explicit that they are separate.
A strong answer
The constraint is that a rolling deploy means both versions are live at once, sometimes for minutes and sometimes for hours if a rollout stalls. So a migration that renames a column breaks the old code the instant it runs, and rolling back the code does not roll back the schema. That asymmetry is why in-place changes cause outages.
Expand and contract, using a column rename as the example:
Step 1, expand. Add the new column, nullable, with no constraint. Check the engine’s DDL locks even for this additive change. PostgreSQL normally needs an ACCESS EXCLUSIVE lock for ADD COLUMN; set a short lock timeout and retry during a quiet window rather than queuing behind a long transaction.
Step 2, dual write. Deploy code that writes both columns and still reads the old one. Wait until all writers, including old workers and scheduled jobs, write both representations. Otherwise an old process can keep creating missing or stale values during the backfill. The previous reader still works, but returning to an old-only writer can make the new representation stale. Reconcile again before switching reads later.
Step 3, backfill. Populate the new column for existing rows, in batches, with a pause between them. A large update can hold row locks for a long transaction, contend with concurrent updates, and generate enough write-ahead log to delay replicas. It does not inherently block every read or every write on the table. Batch by primary key, keep transactions short, and watch replication lag. Use conditional updates or version checks so a backfill cannot overwrite a newer live write. Reconcile values and missed rows after every writer has adopted the new format; row counts alone do not prove consistency.
Step 4, switch reads. Deploy code that reads the new column and still writes both. If validation fails, return reads to the old column while both representations are still maintained. Check whether bad data has already escaped to downstream consumers.
Step 5, stop writing the old. A deploy that writes only the new column.
Step 6, contract. Drop the old column only after retiring old readers and the releases that need them. Check background jobs, reports and external consumers. Use a documented observation period based on their schedules; elapsed days alone do not establish safety.
These are compatibility checkpoints. They can involve application releases, controlled jobs and configuration changes; the exact number of deploys follows the migration design.
Two mechanics worth naming. Adding a column with a non-null default rewrites the whole table on some engines and versions and is instant on others; know which you are on, because that difference is a locked table for the duration. And long-running migrations must be interruptible, so a change that cannot be stopped halfway is one you cannot abort during an incident.
Use a controlled migration job with explicit concurrency control, failure reporting and recovery instructions. Some frameworks coordinate startup migrations with a lock, so replicas do not inevitably race; a dedicated job makes the migration lifecycle easier to observe without coupling application readiness to it.
Worked example: an old column can exist and still be unsafe
For a fictional customer table, replace display_name with preferred_name while keeping the same value semantics. During the overlap, updated application writers set both columns in the same database transaction. A backfill fills missing new values in short key ranges. If a transform changes semantics or writes cross databases, define conflict handling separately; this example does not establish atomicity for those cases.
Before changing reads, verify every active writer, then reconcile values with null-safe comparison. For this same-value example, preferred_name IS DISTINCT FROM display_name identifies disagreements, including null versus non-null. A count of populated rows would miss the case where both columns contain different names. Investigate mismatches and repeat checks after the backfill has finished.
Now suppose release C stops maintaining display_name. A customer changes their name from Mira to Meera. The new column holds Meera and the retained old column still holds Mira. Rolling back to an old reader would expose stale data even though nobody dropped a column. The recovery boundary was the first new-only write. Before that release, decide whether recovery means restoring dual writes and reconciling backwards, or fixing forward while keeping new readers. A backwards transform may be lossy for a more complex schema change.
Pause the backfill if it breaches the tested replication-lag or query-latency budget. Save its completed range and make resumed batches safe to repeat. Rehearse an interruption between batches and a concurrent user update; both should preserve the user's latest committed value. These checks complement the broader release and deployment distinction.
What interviewers probe next
"How do you roll back step 3?" Stop the job and establish which columns it changed. If it touched only the unused new representation, old readers can continue while that representation is repaired. If the query or trigger modified authoritative data, recovery requires data repair; an unchanged read switch does not protect against that mistake.
"What about a foreign key or a unique constraint?" In PostgreSQL 17, a foreign key can be added with NOT VALID and checked later with VALIDATE CONSTRAINT. A unique constraint cannot use NOT VALID: on an ordinary table, first create a suitable unique index with CREATE UNIQUE INDEX CONCURRENTLY, then attach it with ALTER TABLE ... ADD CONSTRAINT ... UNIQUE USING INDEX. Resolve duplicates before building it and budget for the attachment lock; concurrent index creation does not mean every step is lock-free. Check partitioned-table restrictions and the exact engine version. PostgreSQL documents these separate paths.
"How would you canary a migration?" A shared schema affects all clients of that database. Canary its readers after a compatible expansion; independently isolated tenant databases can support a staged schema rollout. Which is the argument for the read switch being its own deploy.
Common mistakes
Testing only with an empty table and one application version. Rehearse mixed versions, concurrent writes and the locks encountered on representative data.
Using one large backfill transaction without measuring contention, replication impact or cancellation behaviour.
Assuming retaining the old column preserves rollback after its values stop being maintained.