Run ALTER TABLE users ADD COLUMN status TEXT NOT NULL DEFAULT 'active' on a live table with a few million rows and older Postgres versions will rewrite the whole table under an ACCESS EXCLUSIVE lock. Every request hits the table, every request blocks. On modern Postgres (11+) a constant default is fast — but plenty of migrations still lock hard: adding a NOT NULL without a default, changing a column type, adding a foreign key. Here’s how to change a live table without an outage.

The core idea: expand, migrate, contract

Never do “rename column” or “change type” as one atomic step against a live table. Split it into three deploys:

1. Expand — add the new thing alongside the old thing.
2. Migrate — backfill data, and update application code to write to both, then read from the new one.
3. Contract — remove the old thing, once nothing references it.

As three separate deploys, the pattern looks like this:

flowchart LR
    A["Expand\nadd new column"] --> B["Migrate\nbackfill + dual write"]
    B --> C["Migrate\nswitch reads to new column"]
    C --> D["Contract\ndrop old column"]
  

This takes three deploys instead of one migration. That’s the cost. The benefit is zero downtime and an easy abort at any step.

Example: renaming a column

Say users.email needs to become users.email_address because someone hates the old name enough to justify three deploys.

1. Expand

ALTER TABLE users ADD COLUMN email_address TEXT;

Adding a nullable column with no default is fast at any table size — it’s metadata-only, no table rewrite.

Deploy application code that writes to both columns on every insert/update, and still reads from email.

2. Backfill and switch reads

Backfill in batches, not one giant UPDATE:

UPDATE users SET email_address = email
WHERE id BETWEEN 1 AND 10000 AND email_address IS NULL;

Loop that in batches of a few thousand with a short sleep between batches so you don’t saturate I/O or bloat the WAL. Once backfilled, deploy code that reads from email_address instead of email, while still writing both.

3. Contract

Once you’ve confirmed nothing reads or writes email anymore (check logs, check any other services touching this table):

ALTER TABLE users DROP COLUMN email;

Indexes: never CREATE INDEX plain

CREATE INDEX takes a lock that blocks writes for the duration of the build. On a large table that can be minutes. Always:

CREATE INDEX CONCURRENTLY idx_users_email_address ON users(email_address);

CONCURRENTLY takes about twice as long and doesn’t block writes. If it fails partway (it can, from a deadlock), it leaves an invalid index behind — check pg_indexes and DROP INDEX the invalid one before retrying.

Adding a NOT NULL constraint without the lock

In recent Postgres, you can validate a constraint without holding the table lock for the whole check:

ALTER TABLE users ADD CONSTRAINT email_address_not_null
  CHECK (email_address IS NOT NULL) NOT VALID;

ALTER TABLE users VALIDATE CONSTRAINT email_address_not_null;

The first statement is instant — it only applies to new rows. The second scans existing rows but takes a much lighter lock than a full rewrite, and doesn’t block reads/writes the whole time.

The failure mode to watch

The dangerous migration isn’t the one that fails. It’s the one that succeeds but leaves the app broken for the deploy window between “migration ran” and “new code deployed.” If your migration and your code deploy aren’t atomic — and with expand-contract they never are — the old code has to keep working against the new schema for at least a few minutes. Test that explicitly: run the migration, then hit the app with the previous release’s code before you deploy the new one.

Takeaway

Any migration that changes what a column means — not just adds one — costs three deploys if you want zero downtime. Budget for it up front instead of finding out mid-incident that the fast version locks the table for ten minutes.