Does adding a column with a default rewrite the table in PostgreSQL?

Short answer

It depends on the default.

• Constant or non-volatile (DEFAULT 0, DEFAULT 'new', NOT NULL DEFAULT false, DEFAULT now()): no rewrite. PostgreSQL works out the value once, stores it with the column definition, and returns it for every existing row. About 1 ms on a 3-million-row table. This has been true since PostgreSQL 11.
• Volatile (gen_random_uuid(), clock_timestamp(), random()), serial/bigserial, GENERATED … AS IDENTITY and GENERATED ALWAYS AS (…) STORED: every row needs its own value, so PostgreSQL rewrites the whole table and its indexes. On our 3-million-row table that took 1.4 to 4.2 seconds, with reads and writes blocked throughout.

Either way the statement takes an ACCESS EXCLUSIVE lock, so set lock_timeout before running it.

What it locks

ACCESS EXCLUSIVE on the table, for both kinds of default. It blocks SELECT, INSERT, UPDATE and DELETE until the transaction ends. The difference is only how long it’s held: milliseconds for a stored default, the whole rewrite for a volatile one.

serial, bigserial and identity columns also create a sequence; we saw locks on the new sequence too, but nobody else can see it until you commit.

Does it rewrite the table?

Results were the same on 14, 15, 16, 17 and 18. The times scale with table size: a 300 GB table takes the lock for as long as it takes to copy 300 GB.

now() is safe because it returns the same value for the whole transaction: every existing row gets the moment the ALTER TABLE ran. We checked: pg_attribute.attmissingval held one timestamp and the table had one distinct value in that column.

On PostgreSQL 18, a generated column without STORED is virtual (computed when read), and adding one didn’t rewrite the table (2 ms). On 14–17 that syntax is an error; generated columns there are always stored.

Run it safely

Constant default: run it directly, with lock_timeout.

SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN status text NOT NULL DEFAULT 'new';

If the table is busy, it fails with ERROR: canceling statement due to lock timeout instead of making every query wait behind it. Retry after a pause; see adding a column for a retry loop.

Volatile default: split it up so nothing holds the lock for long.

1. Add the column with no default (instant), then set the default for new rows (instant, existing rows untouched):

   SET lock_timeout = '2s';
   ALTER TABLE orders ADD COLUMN public_id uuid;
   ALTER TABLE orders ALTER COLUMN public_id SET DEFAULT gen_random_uuid();
   

2. Fill existing rows in batches, each batch its own short transaction, so row locks are released quickly and autovacuum can keep up:

   UPDATE orders SET public_id = gen_random_uuid()
   WHERE id BETWEEN 1 AND 10000 AND public_id IS NULL;
   -- next: 10001–20000, and so on
   

3. If the column must be NOT NULL, add it without a long lock as described in SET NOT NULL.

If you wanted a serial or identity column, the same pattern works with a plain bigint column, a sequence you create yourself, SET DEFAULT nextval('…') and a batched backfill. You end up with a sequence default rather than an identity column, which behaves the same for inserts.

How we checked

On PostgreSQL 14.24, 15.19, 16.14, 17.11 and 18.6 (Docker on a Mac), each ALTER TABLE ran inside a transaction that was rolled back, 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');   -- data file before
ALTER TABLE seo_mig.big ADD COLUMN c uuid DEFAULT gen_random_uuid();
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');   -- a new number means the table was rewritten
ROLLBACK;

Median of three runs:

The other rows in the first table come from one run each, with the same result on every version. Rewrites also took a SHARE lock on the table alongside the ACCESS EXCLUSIVE one; it adds nothing, since ACCESS EXCLUSIVE already blocks everything.

The stored value for a non-volatile default, on 18.6 (the same shape on every version):

attmissingval: c1 atthasmissing=true {0}
attmissingval: c2 atthasmissing=true {"2026-10-09 10:30:07.106064+00"}
distinct c2 values: 1

In Inlet

The structure editor shows the exact ALTER TABLE before it runs and warns when a change rewrites or scans the table. While a rewrite runs, the Activity monitor shows which sessions are waiting on it.