Running database migrations without locking a production table

Written By: BrainSoft In Backend

A migration that takes four seconds on your laptop can hold a lock for four minutes on a production table with real traffic. During those minutes every insert, update and select on that table queues behind the DDL. Requests time out, the connection pool fills, and the whole app looks broken even though only one table is affected.

The fix is to split one scary migration into several small, boring ones. Add columns as nullable, build indexes concurrently, backfill in batches outside the transaction, then tighten constraints once the data is clean. Here is the order I use and why each step is safe.

1. Take an explicit lock timeout so a blocked migration fails instead of piling up.

Before anything else, set a timeout on the migration session. If the DDL cannot get its lock within a couple of seconds, you want an error you can retry, not a queue of waiting queries behind it.

SET lock_timeout = '3s';
SET statement_timeout = '30s';

ALTER TABLE orders ADD COLUMN shipped_at timestamptz;

2. Add the column as nullable, with no default.

On modern Postgres, adding a nullable column with no default is a metadata-only change. It takes a brief exclusive lock, but only for the catalog update. Avoid DEFAULT now() on the same statement on older versions, because that rewrites the table. Add the default later if you need one.

3. Build indexes with CONCURRENTLY, outside a transaction.

A regular CREATE INDEX blocks writes for the whole build. The concurrent variant takes longer and can fail, leaving an invalid index, but it does not block writes. It also cannot run inside a transaction block, so your migration tool needs to know that.

CREATE INDEX CONCURRENTLY idx_orders_shipped_at
  ON orders (shipped_at);

If it fails, drop the invalid index and try again. Check with a quick query against pg_index where indisvalid is false.

4. Backfill in small batches, committing between them.

Do not run one UPDATE orders SET shipped_at = ... over millions of rows. That holds row locks for the whole statement and bloats the table. Loop instead, updating a few thousand rows at a time, and let each batch commit.

UPDATE orders
SET shipped_at = created_at
WHERE id IN (
  SELECT id FROM orders
  WHERE shipped_at IS NULL
  LIMIT 5000
);

Run that from a script with a short sleep between iterations so replication and autovacuum can keep up. Watch replication lag while it runs.

5. Add the constraint only after the data is clean.

Once every row has a value, you can set NOT NULL. On Postgres 12 and later, adding a check constraint with NOT VALID and validating it separately keeps the lock short. If you need the column to be required, do it in two statements.

ALTER TABLE orders
  ADD CONSTRAINT orders_shipped_at_not_null
  CHECK (shipped_at IS NOT NULL) NOT VALID;

ALTER TABLE orders
  VALIDATE CONSTRAINT orders_shipped_at_not_null;

The validate step scans the table but only takes a weak lock, so reads and writes continue. That is the whole trick: keep every individual lock short, and do the slow work outside the lock.

What to check before you run it

A few things bite people even when the plan is right. Confirm the migration tool you use does not wrap everything in a single transaction by default, because CREATE INDEX CONCURRENTLY will fail inside one. Check that no long-running read transaction is holding an old snapshot, since that blocks the concurrent index build from finishing. And make sure the backfill query has an index that supports it, or each batch will do a sequential scan.

It also helps to run these against a restored copy of production with realistic data volume. If you want help setting up a migration pipeline that handles this automatically, our services cover DevOps and database work.

When a plain migration is fine

Not every change needs this treatment. Adding a new table, adding a nullable column, or creating an index on a small table can go through a normal migration. The careful path is for tables that are large, hot, or both. Knowing which is which saves you from over-engineering the easy ones.

Frequently asked questions

#Backend