Does ALTER COLUMN SET DEFAULT lock the table in PostgreSQL?

Short answer

ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'new' takes an ACCESS EXCLUSIVE lock and finishes in about a millisecond on any size of table. It changes what future INSERTs get when they leave the column out; existing rows keep whatever they had, including NULL. DROP DEFAULT behaves the same way.

Unlike adding a column with a default, the kind of default doesn’t matter here: SET DEFAULT gen_random_uuid() is equally instant, because nothing is computed for existing rows.

The one thing to guard against is the lock queue: set lock_timeout.

What it locks

ACCESS EXCLUSIVE on the table, until the transaction ends. While it’s held, SELECT and INSERT from other sessions wait. Held for a millisecond, that’s harmless. But if a long query or an idle transaction is using the table, SET DEFAULT waits for it, and every query that arrives after it waits too. The ADD COLUMN page shows that queue on a real server.

Does it rewrite the table?

No. The data file is unchanged and nothing is scanned. Existing rows aren’t touched:

CREATE TABLE seo_mig.sd (id int, status text);
INSERT INTO seo_mig.sd VALUES (1, NULL);
ALTER TABLE seo_mig.sd ALTER COLUMN status SET DEFAULT 'new';
INSERT INTO seo_mig.sd (id) VALUES (2);

set default: 1=NULL, 2=new

To give existing rows the new value, update them yourself, in batches.

Run it safely

Set lock_timeout and retry:

SET lock_timeout = '2s';
ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'new';

If it fails with ERROR: canceling statement due to lock timeout, nothing changed; wait a few seconds and run it again. See lock timeout.

Fill existing rows separately, each batch its own short transaction:

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

Then, if you want, make it NOT NULL without a long lock: see SET NOT NULL.

Combining steps. Several ALTER COLUMN actions in one ALTER TABLE take the lock once:

SET lock_timeout = '2s';
ALTER TABLE orders
  ALTER COLUMN status SET DEFAULT 'new',
  ALTER COLUMN created_at SET DEFAULT now();

Don’t add a SET NOT NULL or a type change to the same statement unless you mean to: those scan or rewrite the table while the lock is held.

How we checked

On PostgreSQL 14.24, 15.19, 16.14, 17.11 and 18.6 (Docker on a Mac), in a rolled-back transaction on a 3-million-row table:

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;

BEGIN;
SELECT pg_relation_filenode('seo_mig.big');
ALTER TABLE seo_mig.big ALTER COLUMN n SET DEFAULT 0;
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');
SELECT seq_scan FROM pg_stat_xact_user_tables WHERE relid = 'seo_mig.big'::regclass;
ROLLBACK;

We also ran ALTER COLUMN n DROP DEFAULT and ALTER COLUMN s SET DEFAULT gen_random_uuid()::text. For blocking, a second session held ALTER TABLE seo_mig.t ALTER COLUMN a SET DEFAULT 0 open while a third ran a SELECT and an INSERT with lock_timeout = '1s'.

The existing-rows check above gave the same result on all five versions.

In Inlet

The structure editor shows the ALTER COLUMN … SET DEFAULT statement before it runs, and warns when a change rewrites or scans the table. If it’s stuck waiting for its lock, the Activity monitor shows the session in the way.