Database migrations at scale
Should this migration run on the payments table?
The idea in 30 seconds
A migration that is correct on a laptop can lock a production table for many minutes, break the application servers still running old code, and quietly write wrong data. Review every migration on a large table for four things: what each statement locks and for how long, what a transaction around them does to that lock, whether old code still works at every step, and whether the data it writes is true.
Helps to know first: Database migrations and schema evolution (coming soon), Locks and locking strategies (coming soon), Database reliability engineering
On this page
The situation
payments is the busiest table in the system: about 400 million rows, 25 GB plus indexes, around 2,000 writes a second at peak. The application deploys as a rolling update: for several minutes, old and new versions of the code run side by side. A colleague’s pull request contains this migration, which the deploy runs before starting the new code.
Review taskPayments: rename amount, widen id, add settlement time
BEGIN;
ALTER TABLE payments RENAME COLUMN amount TO amount_inr;
ALTER TABLE payments ALTER COLUMN id TYPE bigint;
ALTER TABLE payments ADD COLUMN settled_at timestamptz NOT NULL DEFAULT now();
ALTER TABLE payments
ADD CONSTRAINT payments_customer_fk FOREIGN KEY (customer_id) REFERENCES customers (id);
CREATE INDEX payments_settled_at_idx ON payments (settled_at);
COMMIT;Ran it against a copy of staging (40,000 rows): 1.2 seconds. All tests pass.
Would you approve it? Write the review you would leave.
Your call
Model review
Request changes. Each statement is reasonable; together, in one transaction, on this table, they take payments offline for a long time and write false data.
1. The transaction holds the strongest lock for the whole migration. The first statement takes ACCESS EXCLUSIVE on payments, and locks are held until COMMIT. So nothing can read or write payments until the last statement finishes, through the rewrite, the foreign key scan and the index build. Staging’s 40,000 rows say nothing about this; 400 million rows is 10,000 times more.
2. id to bigint rewrites the whole table and every index. In our measurement, this change rewrote a 287 MB, 5-million-row table in 13 seconds. Scaled linearly to 25 GB, that is in the region of 20 minutes, a rough estimate that ignores the extra indexes, but the order of magnitude is the point. It needs to be its own project: add an id_new bigint column, keep it in step for new rows, backfill in batches, build a unique index concurrently, then swap in a short transaction.
3. The rename breaks the old code during the rolling deploy. The moment it commits, every application server still running the old version fails on amount. Use expand and contract: add amount_inr, write both, backfill, switch reads, and drop amount in a later deploy. Or ask whether the rename is worth three deploys at all.
4. DEFAULT now() is fast and wrong. now() is not volatile: it returns the same value, the start of the transaction, for every row. So PostgreSQL stores it once and does not rewrite the table, which makes this statement quick. It also records that every payment in history was settled at the moment this migration ran. Make the column nullable with no default, and backfill real settlement times from wherever they actually live; add NOT NULL afterwards with NOT VALID and VALIDATE.
5. The foreign key scans 400 million rows while blocking writes. ADD FOREIGN KEY takes SHARE ROW EXCLUSIVE on both payments and customers, which conflicts with inserts, updates and deletes, and checks every existing row. Add it NOT VALID, which is instant, then VALIDATE CONSTRAINT in a separate step, which does not block writes.
6. The index blocks writes, and cannot be made concurrent inside this transaction. Use CREATE INDEX CONCURRENTLY as its own migration, outside any transaction block.
7. Nothing bounds the wait. With no lock_timeout, the very first statement can queue behind a long query and stop every payment query behind it before doing any work at all.
What I would ask for instead: split this into separate, independently deployable steps, each with SET lock_timeout = '3s' and a retry:
ADD COLUMN settled_at timestamptz(no default),ADD COLUMN amount_inr numeric(10,2). Instant.- Deploy code that writes both
amountandamount_inr, and setssettled_aton settlement. - Batched backfill of
amount_inrand of realsettled_atvalues. ADD CONSTRAINT payments_customer_fk ... NOT VALID; thenVALIDATE CONSTRAINT.CREATE INDEX CONCURRENTLY payments_settled_at_idx ....- Deploy code that reads
amount_inr; later, dropamount. - The
bigintchange as its own planned project, with an estimate from a full-size copy.
Compare it with yours. Did your review catch the main problems?
How to review a migration
Read it four times, once for each question.
What does each statement lock, and for how long? Look up the lock level, then ask whether it scans or rewrites. A brief ACCESS EXCLUSIVE is fine; one held for a scan of the whole table is an outage.
What does the transaction do to those locks? Locks last until the transaction ends. Wrapping several statements in one transaction gives every statement the longest duration of any of them, at the strongest level of any of them.
Does the code running right now still work? During a rolling deploy, the old code runs against the new schema. Renames, drops and new NOT NULL columns without defaults all break it.
Is the data true? A default fills in every existing row. Ask what the value means for a row created years ago.
Check yourself
The rename on its own is instant. Why does putting it first in the transaction matter?
Show answer
It takes
ACCESS EXCLUSIVEonpayments, which is held untilCOMMIT. Every later statement, including the long rewrite and scans, then runs while the table is completely locked.Why did
ADD COLUMN settled_at ... DEFAULT now()not rewrite the table, and why is that still a problem?Show answer
now()is not volatile: it returns the transaction’s start time for every row, so PostgreSQL stores the value once in the catalog. The problem is meaning: every historical payment now claims to have been settled at the moment the migration ran.What two-step pattern lets you add the foreign key without blocking payments for a full-table scan?
Show answer
Add it with
NOT VALID, which only checks new rows and is instant, then runVALIDATE CONSTRAINT, which scans existing rows under a lock that does not block inserts, updates or deletes.
Finished reading?
Saved in this browser only.
Sources
Where each claim on this page comes from.
- 1.PostgreSQL documentation: ALTER TABLEPrimary· Description: ADD table_constraint, VALIDATE CONSTRAINT; Notes
Lock levels of ALTER TABLE forms; ADD FOREIGN KEY takes SHARE ROW EXCLUSIVE on both tables and scans unless NOT VALID; non-volatile defaults are stored once without a rewrite; most type changes rewrite the table and indexes.
- 2.PostgreSQL documentation: 13.3 Explicit LockingPrimary· §13.3.1 Table-Level Locks; Table 13.2 Conflicting Lock Modes
Locks are held until the end of the transaction; SHARE ROW EXCLUSIVE conflicts with the ROW EXCLUSIVE lock taken by INSERT, UPDATE and DELETE.
- 3.PostgreSQL documentation: CREATE INDEXPrimary· Building Indexes Concurrently
CREATE INDEX CONCURRENTLY cannot run inside a transaction block.
- 4.PostgreSQL documentation: 9.9 Date/Time Functions and OperatorsPrimary· §9.9.5 Current Date/Time
now() returns the start time of the current transaction, the same value throughout it.
- 5.PostgreSQL documentation: 36.7 Function Volatility CategoriesPrimary· §36.7 Function Volatility Categories
now() and the other current-transaction time functions are STABLE, not VOLATILE.
- 6.ParallelChangeSecondaryDanilo Sato (on martinfowler.com)· whole article
Expand, migrate and contract keep old and new clients working through a change.
Go further
What to read next, and why it is worth the time.
- 1.PostgreSQL documentation: 13.3 Explicit LockingPrimary· Table 13.2 Conflicting Lock Modes
Keep this table open when reviewing migrations. Every question about "will this block X" is answered by one cell of it.
Applies to PostgreSQL 18. Draft: not yet fact-checked end to end.