Zero-downtime migrations: changing a live database
Renaming a column is one line:
alter table users rename column phone to phone_number;On your laptop that runs in a second. In a live system it looks different: the deploy takes a few seconds, and during that time the old code is still asking for the phone column. Which no longer exists.
The result is a burst of errors and broken pages.
The root of the problem is that you cannot change code and database at the same time. There is always a window where the two disagree.
The answer is to structure the change so that every intermediate state also works.
The expand/contract pattern
The core idea is simple: instead of one breaking change, you make several non-breaking steps.
There are three phases:
- Expand — add the new thing while keeping the old. The database supports both.
- Migrate — move the code to the new thing, move the data.
- Contract — remove the old thing.
A deploy happens between phases. And crucially, at every phase old and new code can run side by side.
Let us walk through concrete cases.
Renaming a column
Let us redo the example from the top, properly.
Step 1. Add the new column.
alter table users add column phone_number text;The new column is empty and nothing breaks. Old code keeps using phone.
Step 2. Write to both.
Change the code so writes go to both columns while reads still come from the old one.
await db.update(users).set({
phone: value,
phoneNumber: value,
})Deploy. New records now exist in both columns.
Step 3. Backfill the old data.
update users set phone_number = phone
where phone_number is null and phone is not null;On a large table this needs to be done in batches — more on that below.
Step 4. Switch reads to the new column.
The code now reads phone_number but still writes to both. Deploy. Watch for a few days.
Step 5. Stop writing to the old column. Deploy.
Step 6. Drop the old column.
alter table users drop column phone;Six steps instead of one line. It seems like a lot, but every step is safe and you can roll back at any point.
Adding a NOT NULL column
This is one of the most common traps:
alter table orders add column status text not null;That statement fails immediately, because existing rows have no value. Even adding a default needs care — in older PostgreSQL versions it rewrote the whole table and locked it for a long time.
In modern versions a simple constant default is fast. But if the value is volatile (now() or a random number, say), the table gets rewritten anyway.
The safe sequence is:
-- 1. Add it nullable (fast)
alter table orders add column status text;
-- 2. A default for new rows
alter table orders alter column status set default 'pending';
-- 3. Backfill old rows in batches
-- 4. Add the constraint without checking
alter table orders add constraint orders_status_not_null
check (status is not null) not valid;
-- 5. Validate separately (does not block)
alter table orders validate constraint orders_status_not_null;The last two steps matter. A constraint added with not valid does not check existing rows — it applies instantly. validate then runs separately and does not block writes to the table.
Building an index without blocking
A plain create index blocks writes for the whole build. On a large table that means minutes.
The correct form:
create index concurrently idx_orders_created_at
on orders (created_at);concurrently is slower but does not block writes.
There are two traps. First, it cannot run inside a transaction, so your migration tool has to execute it separately. Second, it can fail and leave an invalid index behind:
select indexrelid::regclass from pg_index where not indisvalid;Make that check a habit after migrations. An invalid index is never used by queries, but it takes space and slows writes down.
Backfilling a large table in batches
One update on a million-row table means a long transaction, a big lock, and replication lag.
The right way is small batches:
update users
set phone_number = phone
where id in (
select id from users
where phone_number is null and phone is not null
limit 5000
);Run that in a loop until the affected row count reaches zero. A short pause between batches helps — the database breathes and replicas catch up.
Batch sizes between 1000 and 10000 usually work well. The exact number depends on row width and load — measure it once.
Cap your lock wait
One line saves you from the worst case:
set lock_timeout = '3s';Without it, here is what can happen: alter table waits for a long-running query to finish. While it waits, new queries queue up behind it, because it has already requested a lock. The whole table jams, and that chain of locks stops the entire application.
With lock_timeout the migration simply errors out and you retry it later. An error beats a downed site.
I put this line at the top of every migration file.
The rollback plan
Every migration needs an answer to one question: what do I do if a problem shows up after the deploy?
The biggest benefit of expand/contract is that it makes that answer easy. In the early phases the answer is simply to roll the code back. The database supports both versions, so the old code keeps working.
Rollback only becomes hard after the final step — dropping the old column. Which is why I always delay that step: I run it after the new version has been stable for several days.
That step is never urgent. A leftover column does you no harm, while removing it too early is a plausible reason to be woken at night.
A practical checklist
After writing each migration, verify:
- Does this change break the old code? If yes, split it into phases.
- Does the migration file set
lock_timeout? - Is the index built with
concurrently? - Is the large update batched?
- Is the
not nullconstraint added vianot valid+validate? - Is there a rollback plan, and has it been tested?
The last question matters most. Before shipping to production, run the migration against a copy of the database with a comparable amount of data. Everything is fast on an empty local database, and that breeds false confidence.
These steps look tedious. They start looking very different after the first time a migration stops a live system.