Adding a column with a non-null DEFAULT to a very large table used to rewrite every row, but modern engines can do it as a near-instant metadata-only change. Explain the mechanism that makes that possible and where it stops working.
answer
- catalog records a 'missing value' for pre-existing rows
- old rows lack the column physically, filled in on read
- new/updated rows store it → lazy conversion
- volatile default (now(), random) → rewrite fallback
- still takes a brief strong lock — queueing matters
basics
~20 sThe engine stores the default in the catalog as the value that existing rows are deemed to have, and materialises it on read for any row physically missing the column. New and updated rows store it for real. It stops being instant when the default is volatile or the change forces a row-format rewrite.
solid answer
~60 sOld behaviour: `ALTER TABLE ... ADD COLUMN c NOT NULL DEFAULT x` had to give every stored row a value, so the engine rewrote the whole table under a strong lock — minutes or hours on a big table, plus double the space. The modern trick is to avoid touching the rows at all. The engine records two things in the catalog: the default for *future* inserts, and a separate "value existing rows had at the moment the column was added". Rows written before the change simply lack the column in their physical layout; when such a row is read, the engine fills in the recorded value. Rows inserted or updated afterwards carry it physically, so the old rows convert lazily and the constraint is satisfied throughout. It requires a constant: the recorded value must be a single evaluated constant, so a volatile default such as the current timestamp or a random value falls back to a rewrite (or is rejected as non-instant). Changes that alter the row format — some type changes, certain column positions or compression settings — also fall back.
code
sql · 6 linesALTER TABLE events ADD COLUMN retries integer NOT NULL DEFAULT 0;
ALTER TABLE events ADD COLUMN retries integer;
UPDATE events SET retries = 0 WHERE id BETWEEN :lo AND :hi;
ALTER TABLE events ALTER COLUMN retries SET DEFAULT 0;
ALTER TABLE events ALTER COLUMN retries SET NOT NULL;go deeper
Know the outcome: on modern engines adding a column with a constant default is near-instant, and old rows get the value without being rewritten.
Explain the catalog-recorded value for pre-existing rows, read-time substitution, and lazy conversion on update; name the volatile-default limitation.
Add version specifics per engine, the fallback plan (nullable add, batched backfill, then default and NOT NULL), and lock-queue hygiene despite the operation being instant.
Set the migration policy: which DDL shapes are allowed on live tables per engine version, how they are gated (lock timeouts, retries, review), and when to reach for an online schema-change tool instead.
## The old, expensive behaviour A stored row is a physical layout of column values. Historically, adding a column that must have a non-NULL value for every row meant every stored row's layout had to change, so the engine rewrote the entire table (and its indexes) under a lock strong enough to exclude everything else. The consequences were an outage proportional to table size, roughly double the disk space during the rebuild, and a large volume of write-ahead log or undo. The folk workaround was to add the column as nullable (cheap, metadata-only), backfill in batches, then add the default and constraint. ## The metadata-only mechanism Modern engines skip the rewrite by separating two meanings of "default": 1. **The current default** — the expression applied to future inserts that omit the column. 2. **The pre-existing-row value** — a single constant, evaluated once at ALTER time, recorded as what every row already stored is deemed to contain. Adding the column then writes only catalog entries. Physically, old rows are simply shorter than the current column count. On read, the engine notices the row lacks the column and substitutes the recorded constant, so the row appears to have always had the value. Any row inserted after the change stores the value physically; any old row that is updated is rewritten in the new layout, dropping its dependence on the recorded value. Conversion is therefore lazy and free — it rides along with normal write traffic and never needs a dedicated pass. Postgres implemented this in version 11 (`pg_attribute.atthasmissing` / `attmissingval`). MySQL 8.0.12 added `ALGORITHM=INSTANT` for adding columns, with similar semantics and its own limits. Oracle did it earlier (11g) for `ADD COLUMN ... NOT NULL DEFAULT`, which is why the same question has different "it depends" answers per engine and per version. ## Where it stops working - **Volatile or non-constant defaults.** The mechanism records one constant for all pre-existing rows. A default like the current timestamp, a random value, or a sequence draw cannot be collapsed to a single meaningful constant for millions of rows, so the engine falls back to a rewrite or refuses the instant path. Note the subtlety: a volatile default still works fine for *future* inserts, evaluated per row; it is only the existing-row fill that cannot be metadata-only. - **Row-format-changing operations.** Type changes, changing compression or storage settings, adding a column in a specific position rather than at the end (MySQL's instant add historically appends), or exceeding limits on how many instant changes a table can accumulate before a rebuild is forced. - **Engines and versions without the feature.** On older releases the nullable-add-then-backfill dance is still the correct plan. ## Locking is still a factor Metadata-only does not mean lock-free. The ALTER still takes a brief strong lock to update the catalog, and that request queues behind any long-running transaction on the table — with every later request queuing behind it. On a busy table this can stall traffic even though the operation itself is instantaneous. Use a short lock timeout with retries. ## What to say in an interview The point that demonstrates understanding is the separation between "default for new rows" (an expression, evaluated per insert) and "value old rows are deemed to have" (a constant, evaluated once at DDL time), plus the lazy conversion on update. From there the failure modes follow naturally: anything that cannot be reduced to one constant, or that changes the physical row layout, forces the old rewrite path.
- Why can't a default of the current timestamp be filled in as a metadata-only change?The optimisation records exactly one constant as the value every pre-existing row is deemed to hold. A volatile expression has no single correct value for rows written at different times, so the engine either evaluates it once and stores a real value in every row via a rewrite, or refuses the instant path. The volatile default still works normally for future inserts, where it is evaluated per row.
- If the change is metadata-only, does that mean it is safe to run at peak traffic?Safer, but not automatically safe. The ALTER still needs a brief strong lock on the table, and that request queues behind any long-running transaction while every subsequent request queues behind the ALTER. On a busy table that can stall reads and writes. Run it with a short lock timeout and retry loop, and check for idle-in-transaction sessions first.
saying these in an interview costs you the question
- Claiming adding a column with a default always rewrites the table, regardless of engine version
- Claiming it is always instant, with no version or default-volatility caveat
- Thinking metadata-only means no lock is taken at all
- Believing old rows are backfilled by a background job rather than converted lazily on update
- Assuming a volatile default cannot be used at all, rather than only failing the existing-row fill