# Database migrations without downtime: expand, migrate, contract

> Why renaming a column can take production down, how expand/contract works, how to backfill in batches, and what to do instead of rolling back.

Published: 2026-02-02 · Source: https://subxcat.com/articles/zero-downtime-migrations · Author: SUBXCAT

While users are few, migrations are simple: stop the app, change the schema, start the new version. A minute of downtime bothers nobody. Then downtime starts costing money, deploys become rolling, and it turns out that a harmless-looking migration can take production down.

## Why it breaks at all

There are two reasons.

The first is that during a deploy, the old and new code run at the same time. New instances are up while old ones finish serving requests. If the migration renamed `name` to `full_name`, the old code spends a few minutes querying a column that no longer exists. And if the new version has to be rolled back, it will be running against a schema it doesn't understand.

The second is locks. Some schema changes lock the table while they run. On a table with a hundred rows you won't notice. On one with several million, queries pile up behind the lock, the connection pool runs dry, and the whole app stops responding even though the migration touched a single table.

## Expand, migrate, contract

The approach that solves the first problem is called expand/contract. A risky change is split into several safe ones, each shipped as its own release. For a column rename:

1. Add the new column `full_name` and leave the old one alone.
2. Ship code that writes to both columns and reads from the old one.
3. Backfill the new column for existing rows.
4. Ship code that reads from the new column.
5. Ship code that stops writing to the old one.
6. Drop the old column.

That's a lot of steps for a rename, and sometimes the honest question is whether the rename is needed at all. But at every step, both the current and the previous version of the code work with the schema, so any release can be rolled back without consequences.

## How not to lock the table

In Postgres, a handful of rules covers most cases:

- Create indexes on live tables with `concurrently`. It takes longer but doesn't block writes. It can't run inside a transaction, so it goes into its own migration.
- Add a check like `check (email is not null)` or a foreign key on a large table in two steps: first with `not valid`, then a separate `validate constraint`, which checks existing rows without blocking writes.
- Set a short `lock_timeout` before migrating. If the table can't be locked within a few seconds, the migration fails instead of hanging and collecting a queue of requests behind it.

```sql
set lock_timeout = '3s';
create index concurrently if not exists orders_user_id_idx
  on orders (user_id);
```

## Backfilling in batches

Filling a new column for a million rows with one `update` holds locks on all of them and bloats the log. Do it in a separate script, not in the migration:

- in batches of a few thousand rows, in primary key order;
- with a short pause between batches so you don't take the whole database for yourself;
- able to resume where it stopped if it's interrupted;
- with a final check of how many rows still have the new field empty.

## Rollbacks

Down migrations often give a false sense of safety. Dropping a column you just added is easy, but whatever data was written into it is gone. Undoing a dropped column is impossible: the data no longer exists.

A more practical rule: code rolls back, the schema only moves forward. If a migration was wrong, you fix it with the next migration. Expand/contract is what makes this work: at any moment the schema is compatible with the last two versions of the code, so rolling back a release is just redeploying the previous artifact.

Before dropping anything, make sure nothing still reads the old column: search the code, the reports, and the analytics scripts someone wrote a year ago and forgot about.

SUBXCAT can go through the migrations you've been putting off and break them into safe steps before your tables get big.