# DDIA Ch. 3: ADD COLUMN is instant, until it isn't

## 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.

### Where to learn them

- **The ALTER TABLE rewrite rules (#1, #3):** the PostgreSQL manual's
  `ALTER TABLE` page, "Notes" — it states which forms require a rewrite and
  which are metadata-only.
- **The fast default (#4–#5):** search "PostgreSQL 11 add column with default"
  — the feature that made a constant default a catalog change (`attmissingval`).
- **Function volatility (#5):** the PostgreSQL manual, "Function Volatility
  Categories" (VOLATILE / STABLE / IMMUTABLE).

If only one is new, make it #4 — relfilenode is how you *see* the difference,
not just time it.

## Environment

Every number below was captured on this exact environment. Timings depend on
your hardware; re-run and the milliseconds will differ, but the *shape* holds
(metadata-only = sub-ms and relfilenode unchanged; rewrite = seconds and
relfilenode changed):

- macOS 26.5.2 (Darwin 25.5.0), arm64
- Docker 29.6.2 running the official `postgres:16` image → PostgreSQL 16.14
- One table, **10,000,000 rows**, ~651 MB heap

The relfilenode *values* in the transcript (16385, 16398, …) are specific to a
fresh database and will differ on your run; only whether they **change** matters.

## 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 `ALTER`s 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
```

## Steps

### Step 1 — a plain column (calibration)

**Predict.** The table has 10,000,000 rows. How long to add a nullable column
with no default?

```
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?
```

**Observe.**

```
 relfilenode
-------------
       16385
Time: 0.764 ms
ALTER TABLE
Time: 1.101 ms
 relfilenode
-------------
       16385
Time: 0.236 ms
```

**1.1 ms**, and the relfilenode is unchanged (16385 → 16385). A nullable column
is obviously just a catalog note — nothing to backfill. Nobody is surprised
yet. Fix the relfilenode **16385** in your head as "the data has not moved."

### Step 2 — a NOT NULL column with a default (the surprise)

**Predict.** Now the one people are sure about: add a **`NOT NULL`** column
**with a default value** to all 10,000,000 rows. Every existing row needs a
value in the new column — surely Postgres has to write all of them. How long?
(Most engineers say "seconds — it's a full rewrite; that's why you avoid this in
production.")

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

**Observe.**

```
ALTER TABLE
Time: 0.805 ms
 relfilenode
-------------
       16385
Time: 0.120 ms
```

**0.805 ms** — *faster* than the nullable add, on a `NOT NULL DEFAULT` over ten
million rows. And the relfilenode is **still 16385**: not one row was rewritten.
The default was recorded once, in the catalog. This is the whole point — the
migration everyone dreads is a metadata edit, and its cost does not depend on
the size of the table at all.

### Step 3 — the changes that *do* rewrite (until it isn't)

**Predict.** Two more changes on the same table. (a) A column whose default is
**`clock_timestamp()`** — a value that differs for every row. (b) Changing a
column's **type** from `numeric` to `double precision`. Metadata-only again, or
full rewrites? And how long?

```
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';
```

**Observe.**

```
ALTER TABLE
Time: 8174.816 ms (00:08.175)
 relfilenode
-------------
       16398
Time: 0.255 ms
ALTER TABLE
Time: 8305.048 ms (00:08.305)
 relfilenode
-------------
       16404
Time: 0.281 ms
```

Both take **~8.2 seconds**, and the relfilenode **changes each time** (16385 →
16398 → 16404): each is a full rewrite into a new file. A per-row-varying
default can't be a single catalog constant, and a type change alters every
tuple's bytes — so both must touch all ten million rows.

## 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.

**The boundary — "relational = rigid" is a caricature.** Schema-on-write does
not mean every change is a costly migration. The *common* changes — add a
column, even `NOT NULL`, even with a default — are metadata edits that are
effectively free at any scale. What is expensive is a specific, enumerable set:
per-row-varying backfills, most type changes, and a few constraint additions.
So the real skill the book's framing points at isn't "avoid schema changes in
relational databases"; it is "know which `ALTER`s are metadata-only and which
rewrite" — and you never have to guess, because `relfilenode` tells you.

### Go deeper

1. **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).
2. **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.
3. **`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.