What they're testing
Whether you've run a migration on a real system, and whether you think about the window where old and new code run side by side.
The short answer~30 seconds
You don't rename it. You do expand–contract: add the new column, have the next release write both, backfill in batches, switch reads to the new column, and only in a later release drop the old one. The reason it must be multi-step is that during a rolling deploy there is always a window where old and new code hit the same database — so the schema has to be compatible with BOTH.
The long answer
The step people get wrong is the backfill. UPDATE users SET new_col = old_col across 200M rows is one enormous transaction: it holds locks, inflates the WAL, and in Postgres leaves 200M dead tuples that autovacuum then fights for hours. The correct shape is batching by primary key, a few thousand rows per batch, committing each, sleeping between them, and watching replica lag so you can pause when replicas fall behind.
One engine detail is worth stating: ALTER TABLE … ADD COLUMN with a DEFAULT is instant on Postgres 11+, because the default is stored in the catalog rather than written into every row. Before that it rewrote the entire table under an ACCESS EXCLUSIVE lock — that is, downtime. Knowing where that boundary falls is the difference between a 5ms migration and a 40-minute outage.
The senior-level addition is the lock queue. Even a fast DDL must ACQUIRE ACCESS EXCLUSIVE, and if a long query is running, your DDL waits — and every query arriving after it queues behind the DDL. A 5ms ALTER can block a table for two minutes that way. The guard is a short lock_timeout with a retry, rather than letting it wait indefinitely.
The contract step needs its own discipline: drop the old column only once you're certain no running version still reads it — meaning after the rollback window has closed. Dropping too early turns an ordinary rollback into an incident, because the restored build queries a column that no longer exists.
Batched backfill, with a brake
-- Lặp cho tới khi hết hàng, mỗi vòng là một transaction riêng
WITH batch AS (
SELECT id FROM users
WHERE new_email IS NULL AND email IS NOT NULL
ORDER BY id
LIMIT 5000
FOR UPDATE SKIP LOCKED
)
UPDATE users u SET new_email = u.email
FROM batch b WHERE u.id = b.id;
-- Giữa các lô: nghỉ, và dừng nếu bản sao tụt quá xa
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes
FROM pg_stat_replication;What they'll ask next
?What about adding NOT NULL to the new column?
Not directly — it scans the whole table to verify, under a lock. The cheaper path: add CHECK (col IS NOT NULL) NOT VALID (instant), run VALIDATE CONSTRAINT (a weaker lock), then set NOT NULL, at which point Postgres trusts the validated constraint and skips the scan.
?Creating an index on a live table?
CREATE INDEX CONCURRENTLY. It doesn't block writes, in exchange for taking longer, not being usable inside a transaction, and possibly leaving an invalid index behind on failure — so check pg_index.indisvalid afterwards and drop it if it's broken.
?How do you know nothing still reads the old column?
Don't guess. Rename the old column to old_email_deprecated one release ahead, or turn on pg_stat_statements and search for queries still mentioning it. Wait for silence for a few days before dropping.
These lose points
- Answering "run
ALTER TABLE … RENAME COLUMN, it's instant". It is instant, and it breaks every old instance still running that second. - Never mentioning rollback. A one-way migration is a migration you won't dare deploy on a Friday evening.
- Backfilling with one giant UPDATE. On a 200M-row table that's an incident, not a migration.
These score well
- Separating "deploy the code" from "run the migration" unprompted, and stating the safe order for each kind of change.