Does ALTER TABLE ADD COLUMN lock the table in PostgreSQL?

Short answer

ALTER TABLE orders ADD COLUMN note text takes an ACCESS EXCLUSIVE lock on the table: nothing else can read or write it while the lock is held. But a nullable column with no default doesn’t touch the rows. PostgreSQL records the new column in the table’s definition and reads it as NULL for every existing row. On a 3-million-row table it took about 1 ms on every version from 14 to 18.

What takes production down is the wait. If any transaction is using the table, even a long SELECT, the ALTER TABLE waits for it, and every query that arrives after the ALTER TABLE waits behind it. Set lock_timeout so the migration gives up and retries instead of stalling the queue.

What it locks

• ACCESS EXCLUSIVE on the table. It conflicts with every other lock mode, including the ACCESS SHARE lock that a plain SELECT takes, so both reads and writes wait.
• It’s held until the transaction ends. In autocommit that’s when the statement finishes. If your migration tool wraps several statements in one transaction, the lock stays until COMMIT, so don’t put a slow backfill in the same transaction.

PostgreSQL grants table locks in arrival order. A waiting ACCESS EXCLUSIVE request blocks everyone who comes after it, even sessions whose own locks wouldn’t conflict with the current holder. This is what we saw on PostgreSQL 18.6 with a 6-second SELECT holding the table, the ALTER TABLE behind it, and an ordinary SELECT arriving a second later:

  pid   | blocked_by | wait_event_type | state  |                  query
--------+------------+-----------------+--------+------------------------------------------
 277346 | {}         | Timeout         | active | BEGIN; SELECT count(*) FROM seo_mig.t; S
 277360 | {277346}   | Lock            | active | ALTER TABLE seo_mig.t ADD COLUMN c int
 277372 | {277360}   | Lock            | active | SELECT count(*) FROM seo_mig.t

The second SELECT is blocked by the ALTER TABLE, not by the first SELECT. On a busy table, every new query piles up the same way until connection limits are reached.

Does it rewrite the table?

No. The table’s data file (pg_relation_filenode) didn’t change and the table wasn’t scanned. That holds for any type, as long as the column has no default (or a constant one) and no constraint that needs checking.

What changes the answer:

• A default. A constant default (DEFAULT 0, DEFAULT 'new', DEFAULT now()) is still instant. A volatile one (gen_random_uuid(), clock_timestamp()), serial, identity and stored generated columns rewrite the whole table. See adding a column with a default.
• NOT NULL without a default fails on a table that has rows: ERROR: column "c" of relation "t" contains null values. Add the column nullable, fill it, then follow SET NOT NULL.

Run it safely

1. Set lock_timeout. It limits how long the statement waits for its lock. If the table is busy, the ALTER TABLE fails after two seconds instead of holding up every query behind it:

SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN note text;

ERROR:  canceling statement due to lock timeout

In our test, a SELECT that queued behind an ALTER TABLE with lock_timeout = '1s' waited 0.5 s and then ran; with no lock_timeout, the same SELECT waited until it timed out itself. See canceling statement due to lock timeout.

2. Retry. A lock timeout isn’t a failure of the migration, only a busy moment. Try again after a short pause:

for attempt in 1 2 3 4 5; do
  psql "$DATABASE_URL" -v ON_ERROR_STOP=1 \
    -c "SET lock_timeout = '2s'" \
    -c "ALTER TABLE orders ADD COLUMN note text" && break
  sleep 15
done

3. Look for long transactions first. Anything open on the table makes you wait:

SELECT pid, now() - xact_start AS open_for, state, left(query, 60) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL AND pid <> pg_backend_pid()
ORDER BY xact_start
LIMIT 10;

A session idle in transaction for minutes is the usual culprit; it keeps its locks until it commits or rolls back.

4. Keep the transaction short. One ALTER TABLE, then COMMIT. Fill the new column afterwards, in batches of a few thousand rows, each in its own transaction:

UPDATE orders SET note = '' WHERE id BETWEEN 1 AND 10000 AND note IS NULL;

How we checked

On PostgreSQL 14.24, 15.19, 16.14, 17.11 and 18.6 (Docker on a Mac), in a scratch schema:

CREATE TABLE seo_mig.big (id bigint, n int, s text);
INSERT INTO seo_mig.big SELECT g, g % 1000, md5(g::text) FROM generate_series(1, 3000000) g;
-- 3 million rows, 219 MB

BEGIN;
SELECT pg_relation_filenode('seo_mig.big');   -- data file before
ALTER TABLE seo_mig.big ADD COLUMN c int;
SELECT relation::regclass, mode FROM pg_locks
WHERE pid = pg_backend_pid()
  AND relation IN (SELECT oid FROM pg_class WHERE relnamespace = 'seo_mig'::regnamespace);
SELECT pg_relation_filenode('seo_mig.big');   -- data file after
SELECT seq_scan FROM pg_stat_xact_user_tables WHERE relid = 'seo_mig.big'::regclass;
ROLLBACK;

While a second session held the same ALTER TABLE open on a 1,000-row table, a third ran SET lock_timeout = '1s' and then a SELECT and an INSERT:

For the queue, one session ran BEGIN; SELECT count(*) FROM seo_mig.t; SELECT pg_sleep(6); COMMIT;, a second ran the ALTER TABLE half a second later (it waited 5.5 s on every version), and a third ran a SELECT with lock_timeout = '2s', which failed with canceling statement due to lock timeout on all five. The table above it comes from pg_stat_activity and pg_blocking_pids().

In Inlet

The structure editor shows the ALTER TABLE it will run before you apply it, and warns when a change rewrites or scans the table. If a migration seems stuck, the Activity monitor shows which session blocks which, and lets you cancel or terminate it.