PostgreSQL Safe Constraint Migration Generator

Adding a constraint to a large, busy PostgreSQL table with a single ALTER TABLE can block reads or writes for as long as the full-table check takes. This generator writes the low-lock version instead: FOREIGN KEY and CHECK constraints added as NOT VALID and then validated under a SHARE UPDATE EXCLUSIVE lock, NOT NULL through a validated CHECK (PostgreSQL 12–17) or NOT NULL ... NOT VALID (PostgreSQL 18+), and UNIQUE or PRIMARY KEY constraints built with CREATE UNIQUE INDEX CONCURRENTLY plus ADD CONSTRAINT ... USING INDEX. Every step is labelled with the lock it takes, and the lock levels were measured with pg_locks on PostgreSQL 14 and 18. You also get pre-check queries for rows that would fail validation, the single-statement version for comparison, verification queries and a rollback. Partitioned tables are handled too. Everything runs in your browser.

ChangeVersionTableConstraint name

Column(s)References tableReferenced column(s)ON DELETEON UPDATE

lock_timeoutAlso index the FK column(s) CONCURRENTLYPartitioned table

1. Pre-check: rows that would fail

-- Rows that would make VALIDATE fail (child values with no parent row)
SELECT c.*
FROM public.orders c
WHERE c.customer_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM public.customers p
    WHERE p.id = c.customer_id
  )
LIMIT 100;

-- The referenced columns must have a PRIMARY KEY or UNIQUE constraint/index
SELECT indexrelid::regclass, indisunique, pg_get_indexdef(indexrelid)
FROM pg_index
WHERE indrelid = 'public.customers'::regclass AND indisunique;

2. Migration script (run step by step)

-- Low-lock migration for public.orders (target: PostgreSQL 17)
-- Run each step on its own, NOT inside one BEGIN ... COMMIT block.
-- If a step fails with "canceling statement due to lock timeout", wait and re-run it (a CONCURRENTLY build also needs its INVALID index dropped first).
SET lock_timeout = '3s';

-- Step 1: index the referencing column(s) so DELETE/UPDATE on public.customers does not seq-scan public.orders
-- Lock: SHARE UPDATE EXCLUSIVE on public.orders (reads and writes continue). Cannot run inside a transaction block.
CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_customer_id_idx
  ON public.orders (customer_id);

-- Step 2: add the constraint without checking existing rows (new rows are checked immediately)
-- Lock: SHARE ROW EXCLUSIVE on public.orders and public.customers (blocks writes on both briefly, no table scan)
ALTER TABLE public.orders ADD CONSTRAINT orders_customer_id_fkey
  FOREIGN KEY (customer_id) REFERENCES public.customers (id) NOT VALID;

-- Step 3: check the existing rows
-- Lock: SHARE UPDATE EXCLUSIVE on public.orders, ROW SHARE on public.customers (reads and writes continue during the scan)
ALTER TABLE public.orders VALIDATE CONSTRAINT orders_customer_id_fkey;

RESET lock_timeout;

Single-statement version (what to avoid on a busy table)

-- One statement: takes SHARE ROW EXCLUSIVE on public.orders and public.customers
-- and holds it while it checks every existing row. All writes to both tables wait.
ALTER TABLE public.orders ADD CONSTRAINT orders_customer_id_fkey
  FOREIGN KEY (customer_id) REFERENCES public.customers (id);

Rollback

ALTER TABLE public.orders DROP CONSTRAINT IF EXISTS orders_customer_id_fkey;
-- Optional: drop the supporting index from step 1 (keep it if other queries use it)
DROP INDEX CONCURRENTLY IF EXISTS public.orders_customer_id_idx;

3. Verify

-- Constraints on public.orders: convalidated = false means VALIDATE has not run (or failed)
SELECT conname, contype, convalidated, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'public.orders'::regclass
ORDER BY conname;

-- Who is waiting on locks right now (run from another session during the migration)
SELECT pid, wait_event_type, state, pg_blocking_pids(pid) AS blocked_by, left(query, 80) AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

Do more than postgresql safe constraint migration generator — meet Chat2DB

Chat2DB is an AI-powered SQL client for Windows, macOS and Linux. Write SQL in natural language, format and optimize queries automatically, and manage MySQL, PostgreSQL, Oracle and 20+ other databases in one workspace.

How to use

Choose the change (foreign key, CHECK, NOT NULL, UNIQUE or primary key), enter the table and columns, and select your PostgreSQL major version.
Run the pre-check query first and fix any rows it returns, because VALIDATE CONSTRAINT and unique index builds fail on bad data.
Run the migration steps one at a time (not inside a single transaction) in psql or Chat2DB, then confirm the result with the verification queries.

Frequently asked questions

What does NOT VALID do on a PostgreSQL constraint?

ALTER TABLE ... ADD CONSTRAINT ... NOT VALID records a FOREIGN KEY or CHECK constraint without checking the rows that already exist, so the statement finishes almost instantly. From that moment new and updated rows are checked. ALTER TABLE ... VALIDATE CONSTRAINT then scans the existing rows under a SHARE UPDATE EXCLUSIVE lock, which lets SELECT, INSERT, UPDATE and DELETE continue. For a foreign key, the referenced table only gets a ROW SHARE lock during validation. If an old row violates the constraint, VALIDATE fails, the constraint stays NOT VALID and you can fix the data and run VALIDATE again.

How do I add NOT NULL to a big Postgres table without locking it?

ALTER COLUMN ... SET NOT NULL scans the whole table under an ACCESS EXCLUSIVE lock, which blocks even SELECT. On PostgreSQL 12 to 17, first add CHECK (col IS NOT NULL) NOT VALID, run VALIDATE CONSTRAINT, and then SET NOT NULL. PostgreSQL sees the validated check and skips the scan (with client_min_messages = debug1 it logs that existing constraints are sufficient to prove the column has no nulls). Then drop the helper CHECK. PostgreSQL 18 supports ADD CONSTRAINT name NOT NULL col NOT VALID followed by VALIDATE CONSTRAINT directly.

Why should I set lock_timeout before running ALTER TABLE?

Even a short ALTER TABLE has to wait for every transaction that already holds a conflicting lock, and while it waits, all later queries on the table queue behind it. A long-running report or an idle-in-transaction session can therefore turn a one-millisecond catalog change into an outage. SET lock_timeout = '3s' makes the statement give up with 'canceling statement due to lock timeout' instead. Nothing is changed when that happens, so you can retry later. You can also run the generated steps from Chat2DB (https://chat2db.ai/download or https://app.chat2db.ai) while another tab watches pg_stat_activity for blocked sessions.