Does DuckDB back-fill existing rows when ALTER TABLE ADD COLUMN includes a DEFAULT?

Short answer

Yes. In DuckDB 1.x, ALTER TABLE t ADD COLUMN c INTEGER DEFAULT 0 is a metadata-only operation. The default is recorded in the catalog, and existing rows are not physically rewritten. When you read those rows, DuckDB applies the default logically at scan time, so SELECT c FROM t returns 0 for pre-existing rows even though nothing was written to storage. The default is physically materialized only for rows inserted after the column is added.

This means the operation is effectively O(1) regardless of table size, which is exactly the trade-off you want on a table with hundreds of millions of rows — but it also means the column is not "filled" in storage until you take further action.

What is confirmed vs. assumed

Confirmed behavior for recent stable releases: the add-column-with-default path does not rewrite existing row groups, and new inserts store the default value physically. What I cannot confirm without your environment is the exact release you run, and early pre-1.0 builds had some edge cases around defaults in expressions, so verify on your version (see below) before relying on this in a migration script.

UPDATE vs. CTAS rewrite for back-filling

For a columnar store with MVCC semantics, a single massive UPDATE t SET c = 0 is usually the worse option: it can produce significant write amplification and a temporary disk-space spike, since updated row groups are rewritten while old versions remain until checkpoint/cleanup. Batching the UPDATE reduces peak memory but multiplies the number of rewritten row groups and total runtime.

The generally safer approach on a very large table is a full rewrite:

BEGIN;
CREATE TABLE t_new AS SELECT *, 0 AS c FROM t;  -- or CAST(existing_col AS INTEGER)
-- verify counts and spot-check values here
DROP TABLE t;
ALTER TABLE t_new RENAME TO t;
COMMIT;

CTAS streams through the table once, writes a clean new copy, and lets you validate before swapping. The cost is needing roughly 2x table size in free disk during the rewrite. If disk headroom is tight, a batched UPDATE keyed on a range of your primary key is the fallback — but test peak memory and disk on a representative sample first.

Verifying NULL vs. applied default

Neither DESCRIBE t nor pragma_table_info('t') distinguishes a physically stored value from a metadata-driven default — they only show the column definition including the default expression. There is no documented catalog view that exposes per-row materialization state.

Practical verification instead:

-- Logical view: what queries actually see
SELECT count(*) FILTER (WHERE c IS NULL) AS nulls,
       count(*) FILTER (WHERE c = 0)    AS defaulted
FROM t;

After a bare ADD COLUMN ... DEFAULT (before any back-fill), this returns zero NULLs because the default is applied at read time — so this query verifies logical correctness, not physical state. To confirm physical back-fill completed, compare table size (pragma_database_size()) before and after your UPDATE/CTAS, and check that a fresh insert without the column also picks up the default.

Recommendation

Add the column with the default (instant), then schedule a CTAS rewrite during a maintenance window if you need the values physically stored (e.g., for compression or to later drop the default). If logical application at read time is sufficient for your queries, you may not need to back-fill at all — that is the cheapest correct option. One detail that would change this: if you plan to later add a NOT NULL constraint, a physical back-fill becomes mandatory, which tips the choice firmly toward CTAS.