Zero-downtime migrations in Postgres

A migration that works on your laptop can take a production database offline. The statement is the same. The difference is ten million rows and a thousand queries a second that all want the same table. Here are the rules we follow to change a schema with nobody noticing.

Why migrations cause outages

Most schema changes need a lock on the table. PostgreSQL's strongest lock, ACCESS EXCLUSIVE, blocks every read and write. If the change is quick, nobody notices. If it rewrites the table, everything waits.

There is a second trap. Your ALTER TABLE must wait for running queries to finish before it gets its lock. While it waits, every new query queues behind it. One slow report can turn a fast migration into a full stop.

Rule 1: set a lock timeout

Always tell the database to give up if it cannot get the lock quickly.

SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN note text;

If the lock is not free within three seconds, the statement fails and the queue clears. You try again a minute later. A failed migration is a small problem. A blocked table is an incident.

Rule 2: add columns the safe way

Adding a nullable column is instant. Adding a column with a constant default is also instant on modern PostgreSQL, because the default is stored in the catalog and not written to each row.

ALTER TABLE users ADD COLUMN plan text DEFAULT 'free';

What is not safe is adding a column with a default that must be computed for each row, such as now() or gen_random_uuid() on older versions, or adding NOT NULL in the same statement on a large table.

Rule 3: backfill in batches

To fill a new column for existing rows, never run one giant UPDATE. It holds locks for minutes and bloats the table. Update a few thousand rows at a time.

UPDATE users SET plan = 'free'
WHERE id IN (
  SELECT id FROM users
  WHERE plan IS NULL
  LIMIT 5000
  FOR UPDATE SKIP LOCKED
);

Run it in a loop with a short pause until it changes zero rows. Each batch commits on its own, so replicas keep up and other queries are not blocked.

Small batches are slower in total and faster for everyone else. That is the trade you want.

Tomás Rivera

Rule 4: add constraints in two steps

A NOT NULL or a foreign key must check every row, under a lock. Split the work: add the constraint without checking old rows, then validate it separately.

ALTER TABLE users
  ADD CONSTRAINT users_plan_not_null CHECK (plan IS NOT NULL) NOT VALID;

ALTER TABLE users VALIDATE CONSTRAINT users_plan_not_null;

The first statement is instant, and new rows are checked from that moment. The second scans the table with a light lock that does not block reads or writes.

The same pattern works for foreign keys.

ALTER TABLE orders
  ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;

ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;

Rule 5: build indexes concurrently

A plain CREATE INDEX blocks writes for as long as it runs.

CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);

The concurrent form takes about twice as long and lets writes continue. It cannot run inside a transaction, so configure your migration tool to allow that.

Rule 6: expand, then contract

Never rename or drop something the running code still uses. Make every change in steps that are each safe to deploy and safe to roll back. To rename a column from fullname to display_name:

1. Expand: add the new column.
2. Write both: deploy code that writes to old and new.
3. Backfill: copy old values to the new column in batches.
4. Read new: deploy code that reads the new column.
5. Contract: stop writing the old column, then drop it.

- SELECT id, fullname FROM users WHERE id = $1;
+ SELECT id, display_name FROM users WHERE id = $1;

It is five deploys for what looks like a one-line change. At every step, the old code and the new code both work, which means a rollback is always possible.

Changes to avoid on large tables

A checklist for every migration

• Is there a lock_timeout?
• Does any statement rewrite the table or scan it under a strong lock?
• Does the old version of the application still work after this runs?
• Can we roll back the code without rolling back the schema?
• Has it been tested against a copy with production-sized data?

The idea behind the rules

Every rule comes from one idea: keep strong locks short and do the slow work under weak locks. If you remember only that, you can work out the safe way to make almost any change.