postgres / safe migrations

updated October 29, 2026

These DDL patterns avoid blocking queries during migrations. I enforce them with a migration linter in Go. The runner runs the linter before every migrate and rollback, and CI runs it on every push.

One transaction per file

An up file owns its own BEGIN; and COMMIT; lines. The runner sends such a file as one batch, with its own bookkeeping row inside the same transaction. So a failed statement leaves neither the schema change nor the row that says it ran.

A file without those lines would run one statement at a time. Its bookkeeping row would commit apart from its SQL, and a semicolon inside a string or a function body would split a statement. So the linter fails such a file, with two exceptions:

A CREATE INDEX CONCURRENTLY file, which Postgres refuses to run in a transaction. It must not contain BEGIN; or COMMIT;.
A file with a -- no-transaction: <reason> line, for other SQL that cannot run in a transaction, such as VACUUM.

Adding indexes

Bad (blocks writes):

CREATE INDEX index_foo ON bar (col)

Good (run outside transaction):

CREATE INDEX CONCURRENTLY index_foo ON bar (col)

Adding foreign keys

Bad (blocks writes on both tables):

ALTER TABLE foo ADD COLUMN bar_id bigint REFERENCES bar(id)

Good (three migrations):

-- Migration 1: add FK without validating
BEGIN;
ALTER TABLE foo
  ADD COLUMN bar_id bigint,
  ADD CONSTRAINT foo_bar_id_fkey
    FOREIGN KEY (bar_id) REFERENCES bar(id) NOT VALID;
COMMIT;

-- Migration 2: index, outside a transaction
CREATE INDEX CONCURRENTLY index_foo_on_bar_id ON foo (bar_id);

-- Migration 3: validate
BEGIN;
ALTER TABLE foo VALIDATE CONSTRAINT foo_bar_id_fkey;
COMMIT;

I name a foreign key <table>_<column>_fkey, the name Postgres gives a key it names itself. An error that names the key then says which table and column failed, with no search of the schema.

A column has one foreign key to a table. With two keys and two delete actions, such as ON DELETE CASCADE and no action, the order of their triggers decides what a delete does.

A constraint rename changes only the catalog, but it holds an ACCESS EXCLUSIVE lock on the table until the transaction commits. The runner's 10s lock_timeout limits each wait, so a long job that holds the table fails the migration rather than blocks every query behind it.

Setting NOT NULL

Bad (blocks reads/writes while checking every row):

ALTER TABLE foo ALTER COLUMN bar SET NOT NULL

Good (two migrations):

-- Migration 1: add check constraint without validating
ALTER TABLE foo
  ADD CONSTRAINT foo_bar_not_null CHECK (bar IS NOT NULL) NOT VALID;

-- Migration 2: validate, set NOT NULL, drop redundant constraint
ALTER TABLE foo VALIDATE CONSTRAINT foo_bar_not_null;
ALTER TABLE foo ALTER COLUMN bar SET NOT NULL;
ALTER TABLE foo DROP CONSTRAINT foo_bar_not_null;

Backfilling data

Bad (locks table for duration):

BEGIN;
ALTER TABLE foo ADD COLUMN bar text;
UPDATE foo SET bar = 'default';
COMMIT;

Good (separate migrations, batched updates):

-- Migration 1: add column
ALTER TABLE foo ADD COLUMN bar text;

-- Migration 2: backfill in batches
UPDATE foo SET bar = 'default'
WHERE id IN (SELECT id FROM foo WHERE bar IS NULL LIMIT 10000);
-- repeat until done

Adding check constraints

Bad (blocks reads/writes):

ALTER TABLE foo ADD CONSTRAINT bar_positive CHECK (bar > 0)

Good (two migrations):

-- Migration 1
ALTER TABLE foo ADD CONSTRAINT bar_positive CHECK (bar > 0) NOT VALID;

-- Migration 2
ALTER TABLE foo VALIDATE CONSTRAINT bar_positive;

Removing columns

Bad (errors if app code still references column):

ALTER TABLE foo DROP COLUMN bar

Good: deploy application code without the column references, then drop the column.

Editing a migration after it ran

The runner records each version it applies, so a later edit to that file does nothing on a database that ran it. The database would keep the old schema while the tree says the new one.

So the runner stores each file's checksum in schema_migrations when it applies the file. Before a run, it compares each recorded checksum with the file on disk. A mismatch stops the run. The message names the file and the two fixes: restore the file, or roll back past it, edit it, and migrate again. A row with a NULL checksum reads as unknown, and the runner reports nothing it cannot prove.

The runner writes db/schema.sql only when the run changed the database. When nothing changed and the dump disagrees with the file, the database is wrong, so the runner reports it and leaves the file.

Deleting migrations that production ran

A migration file has no use after production runs it. db/schema.sql records the schema it built, and git history keeps its SQL. A command compares the newest production dump with db/migrate/ and names each version that production has run. I delete those files in a change of their own. The runner reads an empty db/migrate/ as nothing pending, and a fresh database loads db/schema.sql.

← All articles