Postgres – Schema Migrations Without Downtime

Every schema change takes a lock. On an empty development database you will never notice. On a table with fifty million rows serving live traffic, the same statement is an outage — and the difference is usually one clause.

This post is the changes worth knowing how to do safely, and the setting that makes all of them safer.

The lock that stops everything

Most ALTER TABLE forms take an ACCESS EXCLUSIVE lock, which conflicts with every other lock including the one a plain SELECT takes. While it is held, nothing can read the table.

That is usually fine, because most of these locks are held for microseconds. The danger is the queue. Your ALTER TABLE waits behind a long-running SELECT — and every query that arrives after it queues behind your statement, because a lock request does not jump ahead of one already waiting. A migration that needed a millisecond takes the site down for the length of one slow report.

SET lock_timeout = '3s';
ALTER TABLE bookings ADD COLUMN internal_note text;

Set lock_timeout on every migration. It turns "block the site until this succeeds" into "give up and let me try again in a minute", which is an entirely different kind of bad day. Put it in the migration tool's session setup so nobody has to remember.

Adding a column

ALTER TABLE bookings ADD COLUMN source  varchar(20);
ALTER TABLE bookings ADD COLUMN channel varchar(20) NOT NULL DEFAULT 'web';

Both are metadata-only and instant, at any table size. The second used to rewrite the whole table; since Postgres 11 the default is stored once and applied on read, so it no longer does.

The exception is a volatile default, which has to be computed per row and does rewrite everything:

-- rewrites the table: every row needs its own value
ALTER TABLE bookings ADD COLUMN tracking_id uuid NOT NULL DEFAULT gen_random_uuid();

Do that one in steps: add the column nullable, backfill in batches, then add the constraint.

Adding NOT NULL to an existing column

ALTER COLUMN ... SET NOT NULL scans the whole table under an exclusive lock to prove no NULLs exist. On a large table that is the outage. Two steps avoid it:

-- 1. instant: the rule applies to new and changed rows, existing rows are not read
ALTER TABLE bookings ADD CONSTRAINT ck_source_not_null CHECK (source IS NOT NULL) NOT VALID;

-- 2. backfill the rows that predate it — in batches on a large table, see below
UPDATE bookings SET source = 'web' WHERE source IS NULL;

-- 3. reads the table, but under a lock that does NOT block reads or writes
ALTER TABLE bookings VALIDATE CONSTRAINT ck_source_not_null;

Step 2 is not optional and the order is not negotiable. VALIDATE checks every existing row, so running it before the backfill fails with constraint is violated by some row — and it fails after doing the whole scan. The NOT VALID constraint added in step 1 is what makes the backfill safe: from that moment nothing new can be written NULL, so the set of rows needing repair stops growing.

Since Postgres 12, once that check constraint is validated, SET NOT NULL can use it as proof and completes without a scan — so you can convert it to a real NOT NULL and drop the check:

ALTER TABLE bookings ALTER COLUMN source SET NOT NULL;
ALTER TABLE bookings DROP CONSTRAINT ck_source_not_null;

Building an index without blocking writes

CREATE INDEX CONCURRENTLY ix_bookings_source ON bookings (source);

An ordinary CREATE INDEX blocks writes for its whole duration — minutes, on a large table. CONCURRENTLY makes two passes and lets writes continue. The costs:

• It cannot run inside a transaction block. Migration tools that wrap everything in one need to be told; most have a flag for it.
• It takes roughly twice as long.
• It can fail and leave an invalid index behind — which is not used for queries but is maintained on every write, so it is pure cost until dealt with.

SELECT c.relname AS index
FROM   pg_index i JOIN pg_class c ON c.oid = i.indexrelid
WHERE  NOT i.indisvalid;

The fix is always DROP INDEX CONCURRENTLY and build it again; an invalid index cannot be repaired. Check for one after any failed migration.

Renaming a column

The rename itself is instant. The problem is that old and new application code are both running during a deploy, and no single moment exists when a rename is safe. It takes four deploys:

Tedious, and it is the only way that does not require downtime. The same shape applies to changing a column's type, splitting a column, or moving data to another table — and it is the reason experienced teams pick text and bigint up front.

Backfilling

UPDATE bookings SET source = 'web'
WHERE  id IN (SELECT id FROM bookings WHERE source IS NULL ORDER BY id LIMIT 10000);

Loop until it reports zero rows. Never write the single-statement version: it holds row locks on everything it touches until it commits, generates one enormous WAL burst, and produces millions of dead rows for autovacuum to face at once. ORDER BY id makes concurrent batches deadlock-free.

What a migration tool gives you

Alembic, Flyway, Liquibase, Rails migrations — they differ in syntax and agree on the parts that matter: an ordered, version-controlled list of changes, a table recording which have been applied, and one command that brings any database to the current version.

SELECT version_num FROM alembic_version;

Two rules that apply whichever you use:

• Never edit a migration that has run anywhere. Write a new one. The applied version is the record of what a database actually contains.
• Write the down migration, and expect not to use it. Rolling forward with a fix is nearly always safer than rolling back, because a down migration that drops a column destroys the data written since. Reverting a deploy is the real rollback.

Postgres makes one thing genuinely easier than most databases here: DDL is transactional. A migration containing five statements either applies completely or not at all, so a failure halfway leaves a consistent schema. The exceptions are the ones already mentioned — CREATE INDEX CONCURRENTLY, and anything the tool runs outside a transaction on purpose.

Testing a migration before it runs anywhere

The two things that go wrong in production are the two things a development database cannot show you: how long it takes, and what it locks. Both can be measured beforehand.

-- run it against a restored copy of production, and watch what it holds
BEGIN;
SET lock_timeout = '3s';
ALTER TABLE bookings ADD COLUMN promo_code varchar(20);
SELECT locktype, relation::regclass, mode, granted
FROM   pg_locks WHERE pid = pg_backend_pid() AND relation IS NOT NULL;
ROLLBACK;

Restoring last night's backup for this is the same exercise as verifying the backup, so it costs you one thing and answers two. What you are looking for is an ACCESS EXCLUSIVE on a busy table, and how many seconds it is held — a statement that takes 200 milliseconds on 20,000 rows may take twenty minutes on the real table, and that is the number worth knowing before a Friday.

The checklist

1. SET lock_timeout, always.
2. Additive changes first: new columns and tables are safe and instant.
3. CONCURRENTLY for indexes on a live table, outside a transaction.
4. NOT VALID then VALIDATE for constraints on a populated table.
5. Backfill in batches, ordered by primary key.
6. Destructive changes — dropping a column, dropping a table — last, in a separate deploy, after the code that used them is gone.