Database migrations without downtime: patterns that actually work in production

-- Session 1: an innocent long-running read. Holds ACCESS SHARE.
SELECT count(*) FROM orders;                    -- runs for 90 seconds

-- Session 2: your migration. Wants ACCESS EXCLUSIVE. Waits.
ALTER TABLE orders ADD COLUMN note text;        -- would take 3ms

-- Session 3: an ordinary API request. Wants ACCESS SHARE.
SELECT * FROM orders WHERE id = 42;             -- BLOCKED
-- It does not conflict with session 1. It conflicts with the QUEUED migration.
-- Every request after this one queues too. The pool fills. The API stops.

-- At the top of every migration
SET lock_timeout = '3s';
SET statement_timeout = '30s';

ALTER TABLE orders ADD COLUMN note text;
-- If it cannot get the lock in 3 seconds, it fails with:
-- ERROR: canceling statement due to lock timeout
-- Your API never noticed.

#!/usr/bin/env bash
# Retry the migration until it finds a quiet moment
for attempt in $(seq 1 20); do
  if psql -v ON_ERROR_STOP=1 -c "
        SET lock_timeout = '3s';
        ALTER TABLE orders ADD COLUMN note text;"; then
    echo "applied on attempt $attempt"
    exit 0
  fi
  sleep $(( (RANDOM % 10) + 5 ))
done
echo "could not acquire lock after 20 attempts" >&2
exit 1

  Deploy 1: EXPAND      add the new thing. Old code unaffected.
                        ↓
  Deploy 2: MIGRATE     new code writes both, reads old.
                        ↓
  Backfill              copy old → new, in batches, no lock.
                        ↓
  Deploy 3: SWITCH      new code reads new, still writes both.
                        ↓
  Deploy 4: CONTRACT    stop writing old. Drop it.

  Every arrow is independently deployable and independently reversible.

-- Safe. Since PostgreSQL 11, a non-volatile default does not rewrite the table.
ALTER TABLE orders ADD COLUMN channel text;
ALTER TABLE orders ADD COLUMN channel text DEFAULT 'web';

-- NOT safe: a volatile default must be evaluated per row, so it rewrites
ALTER TABLE orders ADD COLUMN token uuid DEFAULT gen_random_uuid();

-- Do that in three steps instead
ALTER TABLE orders ADD COLUMN token uuid;              -- instant
-- backfill in batches (below)
ALTER TABLE orders ALTER COLUMN token SET DEFAULT gen_random_uuid();

-- 1. Add the check as NOT VALID. Instant - existing rows are not examined.
ALTER TABLE orders
  ADD CONSTRAINT orders_channel_not_null
  CHECK (channel IS NOT NULL) NOT VALID;

-- 2. Validate it. Scans the table under SHARE UPDATE EXCLUSIVE - traffic continues.
ALTER TABLE orders VALIDATE CONSTRAINT orders_channel_not_null;

-- 3. Now SET NOT NULL is cheap: since PG12 the planner uses the valid CHECK
--    to skip the scan. Brief ACCESS EXCLUSIVE only.
ALTER TABLE orders ALTER COLUMN channel SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_channel_not_null;

-- Never this on a live table: SHARE lock, blocks all writes for the build
CREATE INDEX orders_channel_idx ON orders (channel);

-- Always this: SHARE UPDATE EXCLUSIVE, writes continue
CREATE INDEX CONCURRENTLY orders_channel_idx ON orders (channel);

-- Cannot run inside a transaction block. Most migration tools wrap
-- statements in a transaction, so you must opt out explicitly.

-- If it fails it leaves an INVALID index behind. Check and clean up:
SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;
DROP INDEX CONCURRENTLY orders_channel_idx;

-- 1. NOT VALID: instant, but enforced for all new and updated rows
ALTER TABLE orders
  ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers(id) NOT VALID;

-- 2. Validate separately, under a weaker lock
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;

-- And index the referencing column - PostgreSQL does not do this for you
CREATE INDEX CONCURRENTLY orders_customer_id_idx ON orders (customer_id);

-- Deploy 1: add the new column
ALTER TABLE customers ADD COLUMN full_name text;

-- Deploy 2: application writes both columns, reads the old one
-- Backfill: UPDATE customers SET full_name = name WHERE full_name IS NULL;  (batched)
-- Deploy 3: application reads full_name, still writes both
-- Deploy 4: application stops writing name

ALTER TABLE customers DROP COLUMN name;

-- Free: widening a length constraint
ALTER TABLE customers ALTER COLUMN name TYPE varchar(200);   -- from varchar(100)

-- Rewrites the whole table. Do not run this on a large live table.
ALTER TABLE orders ALTER COLUMN id TYPE bigint;

-- Expand and contract instead:
ALTER TABLE orders ADD COLUMN id_big bigint;
-- backfill in batches, keep in sync with a trigger, switch reads, then swap
BEGIN;
  ALTER TABLE orders RENAME COLUMN id TO id_old;
  ALTER TABLE orders RENAME COLUMN id_big TO id;
COMMIT;   -- brief ACCESS EXCLUSIVE, metadata only

-- Keep the shadow column current while you backfill
CREATE FUNCTION sync_id_big() RETURNS trigger AS $$
BEGIN
  NEW.id_big := NEW.id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_sync_id_big
  BEFORE INSERT OR UPDATE ON orders
  FOR EACH ROW EXECUTE FUNCTION sync_id_big();

-- 1. Build the index concurrently - the expensive part, no write blocking
CREATE UNIQUE INDEX CONCURRENTLY customers_email_uniq ON customers (email);

-- 2. Promote it to a constraint. Instant: it reuses the existing index.
ALTER TABLE customers
  ADD CONSTRAINT customers_email_key UNIQUE USING INDEX customers_email_uniq;

-- Batched, resumable, committed per chunk
DO $$
DECLARE
  batch_size int := 5000;
  updated    int;
BEGIN
  LOOP
    UPDATE orders SET channel = 'web'
    WHERE id IN (
      SELECT id FROM orders
      WHERE channel IS NULL
      ORDER BY id
      LIMIT batch_size
      FOR UPDATE SKIP LOCKED
    );

    GET DIAGNOSTICS updated = ROW_COUNT;
    EXIT WHEN updated = 0;

    COMMIT;              -- release locks, let vacuum work, let replicas catch up
    PERFORM pg_sleep(0.1);
  END LOOP;
END $$;

-- Instant: metadata only, no table copy (ADD COLUMN since 8.0.12)
ALTER TABLE orders ADD COLUMN channel varchar(32), ALGORITHM=INSTANT;

-- In place, concurrent DML permitted
ALTER TABLE orders ADD INDEX idx_channel (channel), ALGORITHM=INPLACE, LOCK=NONE;

-- If the server cannot honour the algorithm you asked for, it ERRORS
-- rather than silently falling back to a full table copy. That is the point.
ALTER TABLE orders MODIFY COLUMN total bigint, ALGORITHM=INSTANT;
-- ERROR 1845: ALGORITHM=INSTANT is not supported for this operation

# In CI, before the migration ever reaches staging
npx squawk migrations/0042_add_channel.sql

# migrations/0042_add_channel.sql:3:1: warning: prefer-robust-stmts
#   Wrap statements in a transaction or add IF NOT EXISTS
# migrations/0042_add_channel.sql:7:1: warning: require-concurrent-index-creation
#   Use CREATE INDEX CONCURRENTLY on existing tables