postgres / safe migrations

updated October 07, 2026

These DDL patterns avoid blocking queries during migrations. I enforce them with a migration linter in Go.

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 (two migrations):

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

CREATE INDEX CONCURRENTLY index_foo_on_bar_id ON foo (bar_id);

-- Migration 2: validate
ALTER TABLE foo VALIDATE CONSTRAINT foo_bar_id_fkey;

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. Rails named keys with a hash, such as fk_rails_<hash>, and a reader had to search the schema to read the error. One migration renamed 90 of those:

ALTER TABLE foo
  RENAME CONSTRAINT fk_rails_0123456789 TO foo_bar_id_fkey;

A rename changes only the catalog, but it holds an ACCESS EXCLUSIVE lock on each table until the transaction commits. The runner's 10s lock_timeout limits each wait. A long job that holds a table can fail the deploy, and the next deploy runs the migration again.

The rename found one column with two foreign keys to the same table. One had ON DELETE CASCADE and one had no action. With two delete actions on one column, the order of their triggers decides what a delete does. I dropped the key with no action.

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. A later edit to that file does nothing on a database that ran it. One change merged a migration, every dev database ran it, and a second change edited the same file. The databases kept the old function, and the tree said the new one. The runner dumps db/schema.sql from the database, so the next three changes each carried a diff that reverted the edit.

Now the runner stores each file's checksum when it applies the file:

ALTER TABLE schema_migrations ADD COLUMN checksum text;

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 and migrate again. A row recorded before the column has a NULL checksum. The runner reads it as unknown and reports nothing it cannot prove.

The runner also 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.

← All articles