Expand/contract migration

Expand/contract migration
Type: incident simulation · Topic: Migrations & Releases · Level: L4 · Duration: 10–15 min
Launch: ride postgres start stage-09/06-expand-contract-migration

POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: a release needs to split `app_db.customers.full_name` into `first_name`
and `last_name`. The safe way is an expand/contract migration — add the new columns,
backfill them, run both shapes in parallel during the deploy, and only drop the old
column once nothing reads it. Doing it as a destructive one-shot (drop full_name
immediately) breaks every app version and in-flight query still using it.

How it was found: information_schema.columns showed customers still had only
full_name; the release expected first_name/last_name present and backfilled.

The fix (expand phase only):
  ALTER TABLE customers ADD COLUMN first_name text;
  ALTER TABLE customers ADD COLUMN last_name text;
  UPDATE customers SET first_name = split_part(full_name, ' ', 1),
                       last_name  = split_part(full_name, ' ', 2)
   WHERE first_name IS NULL;
-- full_name is kept for backward compatibility (contract is a later release).

Lesson: expand/contract is the discipline that makes column splits/renames safe
under rolling deploys: expand (add + backfill), keep the old column for
compatibility, switch reads/writes across releases, and contract (drop) only when
the old column is provably unused. Dropping full_name in the same change — or
"migrating" the look-alike analytics_db — is the mistake.

INVESTIGATION HINTS (the staged path to diagnose and fix)
1. The release splits customers.full_name into first_name / last_name in app_db. This must be done as expand → backfill → (later) contract, not as a destructive one-shot. Check the current columns: information_schema.columns for customers.
2. Expand: add first_name and last_name, then backfill them from full_name (split_part). Keep full_name in place — the old app and in-flight reads still depend on it. Don't touch analytics_db (it's a look-alike, not the target).
3. Solve in app_db: ALTER TABLE customers ADD COLUMN first_name text; ADD COLUMN last_name text; UPDATE ... SET first_name = split_part(full_name,' ',1), last_name = split_part(full_name,' ',2). Do NOT drop full_name yet — contracting now is the trap.