What PostgreSQL DDL really locks

Takes SHARE on the table for the whole build. Reads go on; every INSERT, UPDATE and DELETE waits until the index is finished.

Fix: Use CREATE INDEX CONCURRENTLY, outside a transaction.

Takes ACCESS EXCLUSIVE on the index's table. The drop is quick, but it has to wait for every open query on the table, and everything behind it in the lock queue waits with it.

Fix: Use DROP INDEX CONCURRENTLY, outside a transaction.

CONCURRENTLY commits internally between its phases, so PostgreSQL refuses it inside a transaction block. The migration fails at deploy time.

Fix: Run the statement in its own migration with the tool's transaction wrapper switched off.

Checks every row while holding SHARE ROW EXCLUSIVE on both tables, so writes to the child and the parent stop for the length of the scan.

Fix: Add the constraint NOT VALID, then VALIDATE CONSTRAINT in a separate transaction.

Scans the whole table under ACCESS EXCLUSIVE, so reads and writes both stop until it ends.

Fix: Add the constraint NOT VALID, then VALIDATE CONSTRAINT in a separate transaction.

Scans the table under ACCESS EXCLUSIVE to prove there are no NULLs. If a validated CHECK (col IS NOT NULL) already exists, PostgreSQL uses it and skips the scan.

Fix: Add CHECK (col IS NOT NULL) NOT VALID, validate it in a separate transaction, then SET NOT NULL and drop the CHECK. On PostgreSQL 18, ADD CONSTRAINT ... NOT NULL col NOT VALID does the same directly.

Builds the index inside ALTER TABLE, under ACCESS EXCLUSIVE, for as long as the build takes.

Fix: CREATE UNIQUE INDEX CONCURRENTLY first, then ADD CONSTRAINT ... UNIQUE USING INDEX.

Builds the index and proves NOT NULL inside ALTER TABLE, under ACCESS EXCLUSIVE.

Fix: CREATE UNIQUE INDEX CONCURRENTLY first, then ADD CONSTRAINT ... PRIMARY KEY USING INDEX.

A constant default is stored once in the catalog. A volatile one, such as gen_random_uuid(), has to be written to every row, so the table is rewritten under ACCESS EXCLUSIVE.

Fix: Add the column with no default, set the default in a second statement, backfill in batches.

Each of these needs a value in every existing row, so the table is rewritten under ACCESS EXCLUSIVE.

Fix: Add a plain column, attach a sequence as the default, and backfill in batches.

Existing rows have no value for the column, so the statement fails on any non-empty table.

Fix: Add it nullable or with a constant default, backfill, then enforce NOT NULL.

Most type changes rewrite the table and rebuild its indexes under ACCESS EXCLUSIVE. The exceptions are binary-compatible ones, such as varchar to text or a longer varchar.

Fix: Add a new column, write to both, backfill in batches, switch readers, drop the old one.

Both copy the whole table under ACCESS EXCLUSIVE. Neither belongs in a deploy.

Fix: Schedule pg_repack or pg_squeeze, or a maintenance window, outside the migration.

Removes every row and takes ACCESS EXCLUSIVE. Unlike DELETE it also fires no row triggers.

Fix: If the data really is disposable, say why in a pgmg:ignore comment.

Blocks writes to the table, and blocks reads that would have used the index, for the whole rebuild.

Fix: Use REINDEX ... CONCURRENTLY, outside a transaction.

Replaces the view's contents under ACCESS EXCLUSIVE, so nothing can read it during the refresh.

Fix: Add a unique index on the view and use REFRESH MATERIALIZED VIEW CONCURRENTLY.

Takes ACCESS EXCLUSIVE on the partitioned table, which stops all traffic to every partition while it waits for and holds the lock.

Fix: Use DETACH PARTITION ... CONCURRENTLY (PostgreSQL 14 and later), outside a transaction.

DDL that has to wait for a long query sits at the head of the lock queue, and every later query on the table waits behind it. A short lock_timeout turns an outage into a failed, retryable migration.

Fix: Start the migration with SET lock_timeout = '2s'; and retry on failure.

The strong lock from ADD CONSTRAINT is kept until commit, so the validation scan runs under it, and the two-step pattern gains nothing.

Fix: Commit between the two statements, or put VALIDATE in the next migration.

Locks are held until commit. The more tables a transaction holds, the wider the outage and the likelier a deadlock against application transactions that touch them in another order.

Fix: One table per transaction. Split the migration.

The new value cannot be used until the transaction commits. A later statement in the same migration that uses it fails.

Fix: Add the value in its own migration, and use it in the next one.

Old application code still selects and inserts the column until the deploy finishes. The catalog change itself is quick and does not rewrite the table.

Fix: Remove every use from the code, deploy, then drop the column in a later migration.

The old name is gone the moment this commits, while the old code is still running.

Fix: Add the new column, write to both, backfill, switch readers, drop the old one.

The old name is gone the moment this commits, while the old code is still running.

Fix: Rename, and keep a view under the old name until the old code is gone.

Destroys the data and breaks anything still reading the table, and only a backup restore brings it back.

Fix: Stop using the table, deploy, keep it for a while, then drop it with a recorded reason.

Touches every row in one transaction: row locks on the whole table, a burst of WAL for replicas to replay, and dead tuples for vacuum to clean up.

Fix: Backfill in batches of a few thousand rows, each in its own transaction.

Every lock taken by earlier DDL is held until commit, so the table stays blocked for as long as the backfill runs.

Fix: Commit the DDL first and run the backfill separately.

An int key runs out at 2.1 billion, and widening it later is a table rewrite on what is by then the largest table in the system.

Fix: Use bigint GENERATED ALWAYS AS IDENTITY, or uuid.

Stores a wall-clock reading with no zone. It goes wrong the first time a server, a client or a report runs in a different zone.

Fix: Use timestamptz, which stores an instant.

json keeps the raw text and re-parses it on every access. It has no equality operator and no GIN indexing, so GROUP BY, DISTINCT and containment queries on it fail or crawl.

Fix: Use jsonb, unless key order and exact whitespace have to survive.

char(n) pads with spaces, which changes comparisons and sizes, and is no faster than text.

Fix: Use text with a CHECK on length if the length matters.