How PostgreSQL DDL Interacts with Active Transactions

When you change the schema of a busy PostgreSQL database, each data definition language (DDL) command determines what PostgreSQL does and which other sessions wait. DDL commands are SQL statements that change database objects, such as ALTER TABLE and CREATE INDEX. Some commands update table information without checking every row; others scan or rewrite data. Either kind can wait for active transactions or block database activity.

A table lock determines which operations can use a table at the same time. PostgreSQL can make a quick change to table information wait for a conflicting lock, and it holds each lock until the transaction ends. Before deploying, check what lock the command needs and what data work it does. Keep the transaction that runs the command as short as possible. The lock guidance follows PostgreSQL 18 documentation. Check the documentation for your version because lock behavior varies by command and subcommand.

A fast change can still wait for a lock

In PostgreSQL 18, ALTER TABLE takes an ACCESS EXCLUSIVE lock unless a particular subcommand specifies a weaker lock. This lock conflicts with every table-level lock, including the ACCESS SHARE lock used by ordinary SELECT statements. So an ALTER TABLE command that needs ACCESS EXCLUSIVE waits for conflicting activity to finish. Ordinary reads also wait while PostgreSQL holds that lock. When one ALTER TABLE statement includes multiple subcommands, PostgreSQL uses the strictest lock any of them requires, as the ALTER TABLE documentation explains.

A command's lock needs and data work are separate. A command can skip scanning or rewriting a large table and still need a lock that conflicts with other table activity. PostgreSQL normally holds table locks until the transaction ends. So even a quick command can keep blocking other sessions if the migration leaves its transaction open to do unrelated work. The explicit locking documentation describes these lock conflicts and how long transactions hold locks.

Check whether PostgreSQL scans or rewrites the table

Adding a column without a default, or with a default PostgreSQL evaluates once for the command (a non-volatile default), avoids rewriting existing rows. PostgreSQL evaluates the default when it runs the command and stores the result in table metadata, the information it keeps about the table, for existing rows. For example, adding a column with DEFAULT now() uses a single value evaluated at command time for the existing rows, even when the table is large.

Other changes make PostgreSQL do more work. When you add a new column with a volatile default such as clock_timestamp(), PostgreSQL rewrites the table and its indexes. Adding a stored generated column or an identity column also makes PostgreSQL rewrite the table and its indexes. Changing an existing column’s type normally rewrites the table and indexes, with documented exceptions. Rewrites take time and can temporarily require as much as twice the disk space.

A scan checks existing rows but does not necessarily rewrite them. Adding a CHECK or NOT NULL constraint ordinarily makes PostgreSQL scan the table to verify that the rows meet the constraint. For constraints that need this check, you can add the constraint as NOT VALID and validate it later. The first command skips the scan; the later validation scans the table and takes a SHARE UPDATE EXCLUSIVE lock. Check the exact subcommand in the PostgreSQL ALTER TABLE documentation before combining it with other changes.

Choose how to build an index

A regular CREATE INDEX scans the table and blocks writes while it builds the index, although reads can continue. CREATE INDEX CONCURRENTLY avoids blocking concurrent inserts, updates, and deletes, but PostgreSQL performs two table scans and waits for certain existing transactions to finish. Those scans use CPU and disk input/output (I/O), so a concurrent build can take longer and slow other activity. PostgreSQL allows only one concurrent index build on a table at a time, and the command cannot run inside a transaction block. See the PostgreSQL CREATE INDEX documentation for these tradeoffs.

If a concurrent build fails, PostgreSQL can leave an invalid index behind. Queries do not use that index, but updates can still incur overhead from it. After a failed build, check the index state, then drop the invalid index or rebuild it concurrently as appropriate.

Keep transactions around DDL short

A transaction groups supported SQL changes so PostgreSQL can commit them together or roll them back together. The transaction also determines how long PostgreSQL holds locks acquired by those changes. Commit promptly after lock-sensitive DDL. Do not leave the transaction open while the migration runs unrelated queries, waits for manual checks, or does other work. PostgreSQL’s transaction tutorial explains how transactions either commit all their changes or roll them all back.

For a short change, an explicit transaction can also set a local lock-wait limit:

BEGIN;
SET LOCAL lock_timeout = '2s';  -- Example only; choose a limit for your deployment
ALTER TABLE orders ADD COLUMN status text DEFAULT 'new';
COMMIT;

lock_timeout limits how long a statement waits to get a lock; it does not limit how long PostgreSQL spends scanning or rewriting data. If the timeout cancels the DDL, the migration attempt has failed. Use pg_locks to identify sessions holding or waiting for locks, address the blocking conditions, and retry deliberately instead of waiting indefinitely. PostgreSQL documents the setting in its client connection defaults. The pg_locks view shows locks that active processes hold or await, as described in the lock information view.

Run a concurrent index build outside an explicit transaction block:

CREATE INDEX CONCURRENTLY orders_customer_id_idx
    ON orders (customer_id);

Plan for the extra workload and monitor this build separately from migration steps that run inside transactions.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com