PostgreSQL Migration Generator

Here's a zero-downtime migration for adding tenant_id to a 25M-row users table on PostgreSQL 14.

Why this approach: A plain ADD COLUMN ... DEFAULT 'system' NOT NULL would take an AccessExclusiveLock and, pre-PG11, rewrite the whole table. On PG11+ the default itself is metadata-only and fast — but forcing NOT NULL in the same statement still requires a full-table validation scan while holding the lock. So we split it: add the column with its default (cheap), enforce NOT NULL via a NOT VALID check + separate VALIDATE (cheap lock, expensive work done without blocking writes), then flip on NOT NULL itself (fast in PG12+, since the validated constraint lets it skip re-scanning), and finally build the index CONCURRENTLY outside any transaction.

-- Migration: add_tenant_id_to_users
-- Generated by PostgreSQL Migration Generator

-- 1. Session safety limits to prevent database lockups
SET statement_timeout = '10s';
SET lock_timeout = '2s';

-- 2. Schema changes

-- 2a. Add the column with a constant default.
--     PG11+ treats this as metadata-only — no table rewrite, near-instant
--     even at 25M rows.
ALTER TABLE users
  ADD COLUMN IF NOT EXISTS tenant_id text DEFAULT 'system';

-- 2b. Enforce NOT NULL safely: add an unvalidated CHECK first.
--     This only takes a brief metadata lock.
ALTER TABLE users
  ADD CONSTRAINT users_tenant_id_not_null
  CHECK (tenant_id IS NOT NULL) NOT VALID;

-- Note: Concurrent index creation must happen outside a transaction
COMMIT;

-- 2c. Validate the constraint. This scans the table but only takes a
--     SHARE UPDATE EXCLUSIVE lock, so reads/writes continue normally.
--     On 25M rows this can take a while — if lock_timeout above causes
--     it to fail under load, rerun during lower traffic or retry with backoff.
ALTER TABLE users
  VALIDATE CONSTRAINT users_tenant_id_not_null;

-- 2d. Now SET NOT NULL. In PG12+, since a validated CHECK already
--     guarantees no NULLs exist, this skips the full-table re-scan
--     and is effectively metadata-only.
ALTER TABLE users
  ALTER COLUMN tenant_id SET NOT NULL;

-- 2e. The CHECK constraint is now redundant with the column's NOT NULL
--     — drop it to keep the schema clean (cheap, metadata-only).
ALTER TABLE users
  DROP CONSTRAINT users_tenant_id_not_null;

COMMIT;

-- 3. Concurrent operations

-- 3a. Build the index without blocking reads/writes on the table.
--     Must run outside a transaction block (hence the COMMIT above).
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_tenant_id
  ON users (tenant_id);

A few things worth knowing before you run this:

lock_timeout = '2s' means any step that can't grab its lock quickly will error out and roll back cleanly rather than blocking writers — safe to retry, but expect possible failures under heavy write load, especially on step 2c/VALIDATE.

CREATE INDEX CONCURRENTLY can occasionally fail partway through and leave behind an invalid index. If that happens, check pg_indexes / pg_index.indisvalid, drop the invalid one, and rerun the CREATE INDEX CONCURRENTLY statement — it's idempotent thanks to IF NOT EXISTS.

• Run each numbered section as its own migration step if your framework wraps each file in its own implicit transaction — the COMMIT; markers are there specifically so CREATE INDEX CONCURRENTLY isn't caught inside one.
• If you hit ERROR: deadlock detected while other migrations are running concurrently, that usually means multiple migrations are touching the same tables in different orders — best avoided by running schema migrations serially.