TopolearnSchema changes are the most common cause of self-inflicted outages on busy databases.
On a busy database a careless ALTER TABLE can take a lock that queues every query behind it, and a schema change deployed at the same moment as code creates a window where old code meets the new schema. The safe method is expand and contract:
1. Expand: add the new structure in a backward-compatible way — a nullable column, a new table, a new index built online. 2. Dual write: deploy code that writes both old and new shapes. 3. Backfill: copy existing data in small batches (thousands of rows per transaction) to avoid long locks, huge transactions and replica lag. 4. Switch reads to the new shape and verify. 5. Contract: stop writing the old shape, then drop it in a later release.
Lock awareness matters as much as the steps:
- Even a fast DDL statement needs a brief exclusive lock; if a long query holds a conflicting lock, the DDL waits and blocks everyone queued behind it. Set a short lock timeout (
SET lock_timeout = '2s'in PostgreSQL) and retry. - Build indexes online:
CREATE INDEX CONCURRENTLY(PostgreSQL),ALGORITHM=INPLACE, LOCK=NONE(MySQL). - Add constraints in two steps where supported:
NOT VALIDthenVALIDATE CONSTRAINT, so validation doesn't block writes. - Renaming a column is a breaking change; treat it as add-new, backfill, switch, drop-old.
Every migration should be reviewed for the locks it takes and how long it runs on production-sized data.
Example
-- Rename users.fullname -> display_name without downtime (PostgreSQL)
CREATE TABLE users (id bigint PRIMARY KEY, fullname text);
SET lock_timeout = '2s'; -- fail fast instead of queueing
ALTER TABLE users ADD COLUMN display_name text; -- expand (metadata only)
-- deploy code that writes both columns, then backfill in batches:
UPDATE users SET display_name = fullname
WHERE id IN (SELECT id FROM users
WHERE display_name IS NULL AND fullname IS NOT NULL LIMIT 5000);
-- ...repeat until 0 rows updated
ALTER TABLE users ADD CONSTRAINT display_name_nn
CHECK (display_name IS NOT NULL) NOT VALID; -- no full scan under lock
ALTER TABLE users VALIDATE CONSTRAINT display_name_nn; -- scans without blocking writes
-- switch reads, stop writing fullname, then in a later release:
ALTER TABLE users DROP COLUMN fullname; -- contractRemember
- Expand, dual-write, backfill, switch reads, contract
- Set a lock timeout so DDL can't stall the whole table
- Backfill in small batches; build indexes and validate constraints online
Watch out for
- Renaming or dropping a column in the same deploy as the code that stops using it.