CodePark/ questions / Migrations & schema changes
START PRACTICINGPRACTICE
Backend question · Databases & SQL › Migrations & schema changes

On a 200 GB table in PostgreSQL 16, what does ALTER TABLE orders ADD COLUMN source text NOT NULL DEFAULT 'web'; do?

It finishes almost instantly: since PostgreSQL 11, a non-volatile default is stored in the catalog and returned for existing rows without rewriting the table

It finishes almost instantly. Since PostgreSQL 11, adding a column with a non-volatile default is a metadata-only change. Non-volatile means the default gives the same value every time, like 'web'. PostgreSQL stores the default in the catalog and returns it for existing rows, so it does not rewrite the table.

It still needs a brief ACCESS EXCLUSIVE lock, so it can wait in a queue behind long queries.

"It rewrites the whole table to fill in 'web'" was true before PostgreSQL 11. Today, only a volatile default still forces a full table rewrite. Volatile means the value can change on each call, such as gen_random_uuid() or clock_timestamp().