Adding a Required Column to a Busy PostgreSQL Table and What Happens to Old Rows

An online shop wants three new columns on orders. A status that every order must have. A fulfilled_at timestamp. And a public_id uuid to show customers instead of the internal key. All three are NOT NULL. The table has years of orders in it.

On paper that’s three ADD COLUMN lines. In practice each one does something different to the rows already there, and the difference between them is a lock that lasts a millisecond or one that lasts as long as it takes to rewrite the whole table.

A required column needs a default

ForgeSchema blocks a required column with no default on an existing table. There’s no sensible value for the old rows, and PostgreSQL would reject it anyway. Give it a default and the planner accepts it:

- name: status
  type: varchar(20)
  default: "'open'"
- name: fulfilled_at
  type: timestamptz
  default: now()
- name: public_id
  type: uuid
  default: gen_random_uuid()

Then read the explained plan:

./bin/forgeschema plan --explain current.json desired.yaml

Each add_column step says what PostgreSQL will do with existing rows.

The constant default, 'open', is stored once. Old rows read it without being touched. Quick.

The now() default is also stored once, which has a surprise in it. Every existing order gets the time of the migration as its fulfilled_at. An order from 2023 will claim it was fulfilled this afternoon. The plan says so, and that’s probably the moment you decide this column should be nullable after all.

The gen_random_uuid() default is evaluated per row, so PostgreSQL has to rewrite the whole table to give each order its own value. The explanation says that too. On a large orders table, under an exclusive lock, that’s the step that hurts.

Planning around it

Nothing here stops you. It just makes sure the cost is written down before you pay it. You might ship status today, make fulfilled_at nullable, and schedule public_id for a quiet window with a longer timeout:

./bin/forgeschema apply --statement-timeout 20m --timeout 30m reviewed-plan.json

Apply locks only the tables the plan changes, so the rest of the shop keeps working while orders is busy. If any step fails, the whole migration rolls back with PostgreSQL’s reason, and you’re back where you started.

The alternative is finding out about the rewrite from the on-call pager.