Schema changes without downtime

SQL, indexing and PostgreSQL Senior Python 3.13+ PostgreSQL 17+ Reviewed By Best Latest Apps

Order the steps

Order a zero-downtime rename of users.name to users.full_name.

  • Backfill full_name in small batches
  • Add nullable column full_name
  • Deploy code that writes both columns
  • Drop name in a later release
  • Deploy code that reads full_name only

Choose an answer, then open Show answer below to check it.

Show answer

Answer: B C A E D

  1. Add nullable column full_name Metadata-only; old code ignores it.
  2. Deploy code that writes both columns New rows get both values during the rollout.
  3. Backfill full_name in small batches Copies old rows without long locks.
  4. Deploy code that reads full_name only Old column is now unused.
  5. Drop name in a later release Contract once nothing depends on it.

30-second answer

Use expand and contract: add new things compatibly, move code and data over, then remove the old things in a later release. Know which statements take heavy locks: creating an index blocks writes unless you use CREATE INDEX CONCURRENTLY; adding a CHECK or foreign key scans the table unless you add it NOT VALID and VALIDATE it later. Always set a short lock_timeout, because even a quick ALTER queues every query behind it while it waits.

Practise saying it · 40 s. No recording, just a timer

Explain zero-downtime migrations.

Say your answer out loud, then check which of these points you covered:

  • Expand, migrate, contract
  • CONCURRENTLY for indexes
  • NOT VALID then VALIDATE
  • Batch backfills
  • lock_timeout always

Deep dive

Adding a nullable column, or one with a constant default, is metadata-only in PostgreSQL 11+.

Renaming a column breaks old code still running during a rolling deploy; add the new column, dual-write, backfill in batches, switch reads, then drop.

SET NOT NULL scans the table, unless a validated CHECK (col IS NOT NULL) already proves it (PostgreSQL 12+).

CREATE INDEX CONCURRENTLY can't run inside a transaction block and leaves an INVALID index if it fails; drop and retry.

Add a foreign key without a long lock Ran on PostgreSQL 17.11
SQL · PostgreSQL 17
CREATE TABLE customers (id int PRIMARY KEY);
CREATE TABLE orders (id int PRIMARY KEY, customer_id int);
INSERT INTO customers VALUES (1);
INSERT INTO orders VALUES (10, 1);
SET lock_timeout = '3s';
ALTER TABLE orders ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;
CREATE INDEX CONCURRENTLY orders_customer_idx ON orders (customer_id);
SELECT convalidated FROM pg_constraint
WHERE conname = 'orders_customer_fk';

CONCURRENTLY refuses to run in a transaction
SQL · PostgreSQL 17
CREATE TABLE t (id int);
BEGIN;
CREATE INDEX CONCURRENTLY t_id ON t (id);

Common mistakes

  • Running a long backfill as one giant UPDATE.
  • Dropping a column in the same release that stops using it.
  • Running migrations without lock_timeout.

Senior take & follow-ups

Describe expand and contract, name the lock-heavy statements and their safe forms, and insist on lock_timeout.

Follow-up questions

Think of your answer first, then open the hint.

  1. Why can a fast ALTER still cause an outage?

    Show hint

    It waits for a lock behind a long query, and every new query queues behind it.

  2. How do you backfill 100M rows?

    Show hint

    Small batches by key range, committed separately, with throttling.

Related questions

Suggest a fix if something here is wrong or out of date.