postgres / safe migrations

updated January 23, 2026

These DDL patterns avoid blocking queries during migrations. Enforce them with a migration linter.

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;

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 code that no longer uses the column first, then drop.

← All articles