During a rolling deploy, two versions of your application run at once. That single fact invalidates most of how schema changes are written: renaming a column breaks the old version still serving traffic, dropping one breaks it immediately, and adding a NOT NULL column without a default breaks the old version's inserts.
The discipline that solves it is expand-migrate-contract: every change is decomposed into steps, each of which is compatible with both the previous and next version of the code. It takes three deploys instead of one, and it means you never take an outage for a schema change again.
A rename, done correctly
-- Deploy 1: EXPAND. Add the new column. Old code ignores it.
ALTER TABLE invoices ADD COLUMN customer_reference text;
-- Application writes BOTH columns, reads the old one.
-- Deploy 2: MIGRATE. Backfill in bounded batches, not one statement.
-- A single UPDATE over 40M rows holds locks and bloats WAL until something breaks.
UPDATE invoices SET customer_reference = customer_ref
WHERE customer_reference IS NULL AND id BETWEEN $1 AND $2; -- loop, with a pause
-- Application now reads the new column, still writes both.
-- Deploy 3: CONTRACT. Only once no running version references the old column.
ALTER TABLE invoices DROP COLUMN customer_ref;
-- Constraints get the same treatment: validate without a long exclusive lock.
ALTER TABLE invoices ADD CONSTRAINT ref_present
CHECK (customer_reference IS NOT NULL) NOT VALID; -- instant, no table scan
ALTER TABLE invoices VALIDATE CONSTRAINT ref_present; -- scans, but takes a weak lockThe NOT VALID trick is worth knowing precisely. Adding a validated constraint scans the whole table under a lock that blocks writes; adding it as NOT VALID and validating separately takes a weaker lock and lets traffic through. The end state is identical.
The locks that surprise people
In PostgreSQL, adding a column with a constant default is fast, but adding an index without CONCURRENTLY blocks writes for the duration of the build. The genuinely nasty case is lock queueing: a migration waiting for an exclusive lock blocks every query that arrives behind it, so a change that would have taken 200ms takes down the table because one long-running transaction was already holding a conflicting lock.
- Always set a short lock_timeout for migrations and retry. Failing fast is better than queueing the entire application behind you.
- Build indexes concurrently, and check for invalid indexes afterwards — a failed concurrent build leaves one behind.
- Batch every backfill, with a pause between batches, and make it resumable. Watch replication lag while it runs.
- Never combine a schema change and a data change in one transaction on a large table.
- Test migrations against a production-sized copy. Timings on a 10,000-row development database predict nothing.
Your deploy is not atomic, so your schema change cannot assume it is. Two versions of the code will meet the database at the same time.
The organisational half matters as much as the SQL. Contract steps get forgotten, because by then the feature works and nobody is motivated. Track them explicitly — a ticket created at expand time, scheduled for the release after next — or you accumulate a schema full of columns nobody dares delete because nobody can prove they are unused.