ADD COLUMN Is Instant, Until It Isn't

Adding a NOT NULL column with a default to a 10-million-row Postgres table takes 0.8 ms — a catalog edit that never touches the rows. A volatile default or a type change rewrites the whole table: ~8 seconds. The cliff between them is ~10,000×, and relfilenode proves which is which.

Concept

The document model is sold as schema-on-read: add a field, no migration. The relational model is schema-on-write, which sounds like the opposite — change the schema and the database has to rewrite your data. So how expensive is a relational schema change, really? On a 10-million-row Postgres table, you will predict that adding a NOT NULL column with a default takes seconds (a full rewrite) and watch it come back in under a millisecond — a metadata edit that never touches the rows. Then you will find the changes that do rewrite the whole table, and measure the ~10,000× cliff between them.

Provenance

Designing Data-Intensive Applications, 2nd ed. (Kleppmann & Riccomini, 2025), Chapter 3 — Data Models and Query Languages, §"Schema flexibility in the document model":

A more accurate term is schema-on-read … in contrast with schema-on-write (the traditional approach of relational databases, where the schema is explicit and the database ensures that all data conforms to it when the data is written).

The book frames the relational cost as the migration you must run when the data format changes. Here you measure that cost — and find that for the common case it is essentially zero, while for a specific, knowable set of changes it is the full table rewrite people wrongly assume is the default.

Prerequisites

The surprise is about what a schema change does to the bytes on disk. These help; each line notes where it shows up.

1. SQL DDL — CREATE TABLE, ALTER TABLE … ADD COLUMN, ALTER COLUMN … TYPE. The statements you time.
2. Schema-on-write vs schema-on-read — the book's framing: relational enforces the schema at write time, so changing it may require touching existing data. This exercise measures when it actually does.
3. How a row-store lays out a table — a Postgres table is a heap of rows packed into 8 KB pages on disk. A "table rewrite" means writing every one of those rows out again; that is what makes an ALTER O(table size).
4. relfilenode in pg_class — Postgres records each table's on-disk file identity in its catalog. A rewrite creates a new file, so the relfilenode changes; a metadata-only change leaves it untouched. This is our proof, and it doesn't depend on a stopwatch.
5. Volatile vs. non-volatile functions — clock_timestamp() returns a different value each call (volatile); a literal like 0 is constant. This distinction is exactly what decides whether ADD COLUMN … DEFAULT can skip the rewrite.

Mental model

A Postgres table is a heap file: rows stored as tuples in 8 KB pages. An ALTER TABLE can be one of two completely different operations:

• A catalog edit (metadata-only). The on-disk rows are left exactly as they are; only the table's description in the system catalog changes. Cost is O(1) — the same whether the table has 10 rows or 10 billion.
• A table rewrite. Every row must be written out again in the new physical layout, into a brand-new file. Cost is O(table size).

Since PostgreSQL 11, ADD COLUMN … DEFAULT <constant> is in the first category: the default is recorded in the catalog as a "missing value," and any existing row that lacks the column is simply read back as having that default. No row is touched. The whole exercise is watching which ALTERs land in which category — and confirming it with relfilenode, not just the clock.

Setup

# start Postgres 16 (first run pulls the image)
docker run -d --name ddia-ch3 -e POSTGRES_PASSWORD=study -e POSTGRES_DB=study postgres:16
sleep 3   # let it accept connections

# seed 10M rows (~20 s); the setup may be scripted -- the ALTERs may not
docker cp schema_change_setup.sql ddia-ch3:/tmp/
docker exec ddia-ch3 psql -U postgres -d study -f /tmp/schema_change_setup.sql

# open an interactive session and type the steps below by hand
docker exec -it ddia-ch3 psql -U postgres -d study

In psql, turn on timing first — then make each prediction before running the statement:

\timing on

SELECT relfilenode FROM pg_class WHERE relname = 'events';   -- note the value
ALTER TABLE events ADD COLUMN status text;
SELECT relfilenode FROM pg_class WHERE relname = 'events';   -- changed?

ALTER TABLE events ADD COLUMN priority int NOT NULL DEFAULT 0;
SELECT relfilenode FROM pg_class WHERE relname = 'events';

ALTER TABLE events ADD COLUMN seen_at timestamptz DEFAULT clock_timestamp();
SELECT relfilenode FROM pg_class WHERE relname = 'events';
ALTER TABLE events ALTER COLUMN amount TYPE double precision;
SELECT relfilenode FROM pg_class WHERE relname = 'events';

What you should see

• Nullable add and NOT NULL DEFAULT <constant> add: ~1 ms, relfilenode unchanged — metadata only, cost independent of table size.
• Volatile-default add and column-type change: ~8,000 ms each, relfilenode changed — full table rewrites.
• The cliff between "metadata" and "rewrite" is roughly 10,000× (0.8 ms vs 8,175 ms), on the very same table.

Why

A row-store keeps each table as a heap of tuples in on-disk pages. The only question any ALTER really asks is: do the existing tuples have to change? If not, the operation is a note in the system catalog — O(1), sub-millisecond, and the table keeps its file (its relfilenode). If yes, every tuple is written out into a fresh file — O(table size), and the relfilenode changes because it is literally a new file.

The NOT NULL DEFAULT 0 case is instant because since PostgreSQL 11 the default is stored once as a catalog "missing value" (attmissingval). An existing row that predates the column simply has no bytes for it, and the reader substitutes the stored default on the way out. Ten million rows, one catalog entry, nothing rewritten. That is why Step 2 is faster than Step 1's nullable add and totally independent of row count.

The rewrites in Step 3 fail exactly that "single constant" test. clock_timestamp() is volatile — it must yield a different value per row, so there is no one constant to stash in the catalog; Postgres has to materialize a value into every tuple. And numeric → double precision changes the physical bytes of every amount, so every tuple is rebuilt. Both are O(n), both mint a new relfilenode, both take ~8 s here.

Go deeper

• A no-rewrite type change. Predict: is ALTER COLUMN note TYPE varchar(200) (widening a text type) a rewrite? Try it and check relfilenode — binary-coercible type changes are metadata-only, so not every TYPE change is O(n).
• Backfill without the rewrite. You want per-row values (Step 3a) but without an 8 s lock. Add the column nullable (instant), then UPDATE in batches. Compare total time and, more importantly, the lock behavior.
• SET NOT NULL on an existing column. Predict whether adding a NOT NULL constraint to a column that's already full scans the table. Time it; then try the CHECK … NOT VALID + VALIDATE CONSTRAINT two-step and compare the lock it takes.

Sources: DDIA 2e, Ch. 3, §"Schema flexibility in the document model" · PostgreSQL manual, ALTER TABLE "Notes" and "Function Volatility Categories" · the fast-default optimization landed in PostgreSQL 11.