agent added a column with a default to a 500M-row table and postgres is rewriting the whole table while the app is live

TL;DR: On Postgres 11 or later, adding a column with a constant default is a metadata-only change and finishes instantly - let it run. If the table is actually being rewritten, the default is volatile or the server is older than 11. Cancel it and do it in four steps: add the column nullable, backfill in batches, set the default, then add NOT NULL.

agent added a column with a default to a 500M-row table and postgres is rewriting the whole table while the app is live

1. Check the server version and whether the default is a plain constant.

SHOW server_version;

Expected: you know the major version. If it is 11 or later and the default is a constant like 0 or 'active', the ADD COLUMN is metadata-only - fast and safe, no rewrite.

1. If the table is rewriting (or the version is older than 11, or the default is volatile like now()), cancel the running statement and switch to the staged approach.

Expected: the rewrite stops and the table is back to its original schema.

1. Step one and two: add the column nullable, then backfill in primary-key batches so each batch is a short transaction.

ALTER TABLE big_table ADD COLUMN status text;
UPDATE big_table SET status = 'active' WHERE id BETWEEN 1 AND 100000 AND status IS NULL;

Repeat the UPDATE with advancing ranges until no nulls remain. Expected: each batch commits in seconds, and a final count shows zero nulls.

1. Step three and four: set the default for new rows, then enforce NOT NULL without a full scan using a validated check constraint.

ALTER TABLE big_table ALTER COLUMN status SET DEFAULT 'active';
ALTER TABLE big_table ADD CONSTRAINT status_not_null CHECK (status IS NOT NULL) NOT VALID;
ALTER TABLE big_table VALIDATE CONSTRAINT status_not_null;
ALTER TABLE big_table ALTER COLUMN status SET NOT NULL;

Expected: the constraint validates and the column ends up NOT NULL with a default, with no table rewrite at any step.

Use this when

• Adding a column with a default to a large Postgres table on a live app
• An ADD COLUMN is taking far longer than expected or holding a lock
• You need a zero-rewrite pattern for backfilling a new column
• The default must apply to existing rows, not just new ones

Not for this skill when

• The table is small - a rewrite of a small table is fast and the staged approach is overkill
• You are on Postgres 11+ with a constant default - the plain ADD COLUMN is already instant
• The database is MySQL - instant-add rules differ there; check its own instant DDL support
• You can take a maintenance window - a straight rewrite under an exclusive window is simpler

Variant phrasings

• postgres add column with default rewriting huge table
• alter table add column default taking forever on large table
• how to add not null column with default to 500M row table
• postgres 11 instant add column default explained

Why it happens

Before Postgres 11, adding a column with a default rewrote every row to physically store the value. Version 11 made constant defaults metadata-only: the default lives in the catalog and is filled in at read time. But volatile defaults (now(), random(), anything computed per row) still force a rewrite on every version, and so does any pre-11 server. The rewrite also holds an AccessExclusiveLock for its whole duration, which is what makes the app appear frozen.

Edge cases

• Even the instant metadata-only add takes a brief AccessExclusiveLock. On a very hot table, set a short lock_timeout and retry rather than queueing behind traffic.
• Replicas replay the WAL for the change; a rewrite generates enormous WAL that can lag replicas for hours. The staged approach keeps WAL per batch small.
• VALIDATE CONSTRAINT still scans the table, but it takes a weaker lock than the rewrite and does not block reads.
• If the column must be NOT NULL from the first moment with zero tolerance for nulls, the staged approach still works - the check constraint is validated before the SET NOT NULL, so there is no window where nulls are allowed and present.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_4Beky3FVYlioj8eLNf2uyQ