Bad nullable column rollout

Bad nullable column rollout
Type: incident simulation · Topic: Migrations & Releases · Level: L3 · Duration: 10–15 min
Launch: ride postgres start stage-09/04-bad-nullable-column-rollout

POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: a release added `users.plan` in app_db and shipped the new app at the
same time, but the migration only ran `ALTER TABLE users ADD COLUMN plan text`.
Existing rows stayed NULL, the column had no default and was still nullable, so the
new app — which assumes every user has a plan — broke for all existing users.

How it was found: `SELECT count(*) FROM users WHERE plan IS NULL` showed thousands
of NULLs, and information_schema.columns confirmed is_nullable = YES with no
column_default.

The fix (safe rollout): backfill, default, then harden:
  UPDATE users SET plan = 'free' WHERE plan IS NULL;
  ALTER TABLE users ALTER COLUMN plan SET DEFAULT 'free';
  ALTER TABLE users ALTER COLUMN plan SET NOT NULL;

Lesson: adding a column is not a rollout. Backfill existing rows, set a default so
new inserts can't reintroduce NULLs, and only then SET NOT NULL. On a large table,
prefer a `CHECK (plan IS NOT NULL) NOT VALID` → `VALIDATE CONSTRAINT` → `SET NOT
NULL` sequence to avoid a long table-rewrite lock. An index fixes nothing here.

INVESTIGATION HINTS (the staged path to diagnose and fix)
1. A release added users.plan in app_db, but the new app expects every user to have a plan and some rows are NULL. Confirm: SELECT count(*) FROM users WHERE plan IS NULL; in app_db.
2. Adding a column isn't a rollout. Existing rows kept NULL, there's no default, and the column is still nullable — inspect information_schema.columns (is_nullable, column_default) for users.plan.
3. Finish the rollout in app_db: backfill (UPDATE users SET plan='free' WHERE plan IS NULL), set a default (ALTER ... SET DEFAULT 'free'), then ALTER ... SET NOT NULL once there are no NULLs. Don't add an index — that's not the problem.