PostgreSQL rewrites a 5-million-row table for 10.4 seconds when a new column's default is volatile

The post's author frames the choice around one question: what the new column should hold for rows that already exist ^[22]. A single constant goes into the catalogue, and the statement returns in milliseconds whatever the table's size ^[21]. On the test table the rewrite took about 1,040 times as long as the constant path ^[1]. In a migration file the two statements look equally harmless ^[23].

Volatility is a property of the function, so it has to be looked up. now() is stable, so it takes the fast path ^[7]. Every existing order then gets the same imported_at, the time the migration's transaction started ^[7]. The author notes that this is correct for an import timestamp and wrong for a column meant to record when each row happened ^[7]. Leaving the default off fails on the first row with `ERROR: column "channel" of relation "orders" contains null values` ^[8]. "That is the safe failure. The rewrite is the dangerous one, because it succeeds," the author wrote ^[9].

For a value computed per row, the post works through an orders table where each order takes its region from its customer ^[24]. The sequence:

1. `ALTER TABLE orders ADD COLUMN region text;` is a catalogue change that took 0.7 ms ^[10]. 2. The backfill is an UPDATE joined to customers over 50,000-row id ranges, committing between batches, at about 220 ms a batch ^[12]. 3. `ADD CONSTRAINT orders_region_nn_check CHECK (region IS NOT NULL) NOT VALID` is instant, since it checks only new and updated rows ^[16]. 4. `VALIDATE CONSTRAINT` does the full scan, 534 ms in the test, and the author reports that this scan never holds a lock that blocks reads ^[16][17]. 5. `ALTER COLUMN region SET NOT NULL` then skips its own scan. PostgreSQL has done that since version 12 when a validated check already proves the column has no NULL ^[15]. The helper constraint is dropped last ^[20].

Batching costs wall time. Assuming contiguous ids, 5,000,000 rows make 100 batches, roughly 22 seconds against 19.6 for the single UPDATE ^[3]. I'd pay the extra 2.4 seconds on any table that takes writes, because each row lock lasts one batch, not the whole backfill ^[11][12].

Running SET NOT NULL without the check constraint holds ACCESS EXCLUSIVE for its whole scan ^[13]. On a freshly vacuumed table that scan took 367 to 380 ms over three runs ^[13]. The dead row versions left by the single-statement backfill made it about five times slower ^[4][14].

The post's last rule covers even the 10 ms constant default. An ALTER TABLE waits behind any open transaction that has touched the table, and every query that arrives after it stalls in turn ^[18]. The statement is quick once it holds its lock, so the risk sits in the queue. The author sets lock_timeout on every statement in the sequence for that reason ^[18].

All timings came from PostgreSQL 18.3 in a throwaway container ^[1]. The absolute milliseconds belong to that container and that 356 MB table. What transfers is which path each statement takes, and the ALTER TABLE documentation decides that ^[5]. Two version floors apply. The metadata default arrived in PostgreSQL 11 ^[6], and the scan skip in PostgreSQL 12 ^[15]. The author builds Schemity, a desktop ERD tool, and the post uses it for its examples ^[19]. Every statement above is plain SQL.