The hidden cost of a simple DEFAULT
Adding a column to a Postgres table is normally a near-instant operation. The database doesn't rewrite existing rows; it just updates the system catalog with the new column's metadata. New rows get written with a value for the column, while old rows simply return NULL when queried.
ALTER TABLE users
ADD COLUMN credits bigint;
Add a DEFAULT clause, though, and the picture changes completely:
ALTER TABLE users
ADD COLUMN credits bigint NOT NULL DEFAULT 0;
Suddenly Postgres must go back and populate the default value into every existing row to maintain data integrity. That requires a full rewrite of the table and all its indexes. On small tables this happens quickly enough to go unnoticed, but on large relations the rewrite takes real time—and throughout the process, the table is held under an ACCESS EXCLUSIVE lock. That's the coarsest lock granularity Postgres offers; it blocks every other operation, including plain SELECT queries.
The danger here is that nothing in the SQL syntax hints at the cost. A column addition with a default looks nearly identical to one without, and newcomers often discover the difference only after triggering a minor operational incident.
Why NOT NULL columns became an uphill battle
This limitation had a direct consequence: you couldn't cheaply add a NOT NULL column to an existing table. A non-null column needs a value for every row by definition, so adding one to a non-empty table requires a DEFAULT—which triggers the expensive rewrite.
The workaround was to add the column as nullable, backfill values with a migration, then run SET NOT NULL. But even that final step isn't cheap: verifying the constraint requires a full table scan and still takes an ACCESS EXCLUSIVE lock. The scan is less expensive than a rewrite, but the practical result was the same—at scale, you often just didn't bother adding non-null columns at all.
Data integrity versus operational reality
Strong schema design is one of the main arguments for a relational database over a document store or key/value system. Typed columns, CHECK constraints, and foreign keys all guarantee that data stays in a known, valid state. That integrity is what makes querying and application development predictable.
But the guarantee only holds if you can actually apply the constraints. For Postgres operators running large tables, adding a non-null column with a default was frequently impractical—forcing a choice between data integrity and availability. That tradeoff was a gaping hole in Postgres's operational story.
The Postgres 11 improvement
Postgres 11 closes that gap. The new behavior makes ALTER TABLE .. ADD COLUMN with a non-null default fast by deferring the work: instead of rewriting the table, Postgres records the default value in the catalog and applies it to existing rows as they are scanned. New rows get the default at insert time, as expected. The table rewrite is eliminated, and so is the long hold on the ACCESS EXCLUSIVE lock.
The result is that you can now have both strong integrity constraints and strong operational guarantees. The design tradeoff that made non-null columns risky at scale is gone.
Under the hood
The implementation adds two fields to pg_attribute, the system catalog table that tracks every column in the database:
atthasmissing: Set totruewhen missing default values exist for existing rows.attmissingval: Stores the missing value itself.
As scans return rows, Postgres checks these fields and returns the missing value where appropriate. If the table is later rewritten for any reason, the database takes the opportunity to materialize the default in every row and clears both fields.
There is one important caveat: the optimization only works for defaults that are constants or non-volatile function calls. A volatile function like random() won't be stored in attmissingval, so adding such a default still forces a rewrite. A call like DEFAULT now() works fine, though—the transaction's current value of now() is stored in attmissingval, existing rows inherit that value, and new rows get a fresh now() as they are inserted.
The change is conceptually simple, but its implementation touches many parts of the system where missing values must be considered. The full patch is available in the PostgreSQL source repository for those interested in the details.
