evolving-database-schemas

Evolving Database Schemas

The standards own the rules. Release Cutover requires migrations to be explicit, never run at startup, and a release that carries one to wait for an expand, migrate, and verify sequence that keeps the previous version able to read. Test-Driven Development governs the tests each step adds. This skill covers the judgement of sequencing a schema change so that both the old and the new version of the code keep working at every moment.

Two Versions Run at Once

During any rollout the previous version and the new one read and write the same store, and a rollback brings the previous version back. A schema change is safe only when every version that can be running at that moment understands the store. Renaming a column in one step breaks whichever version expects the other name.

Expand, Migrate, Verify, Contract

1. Expand. Add the new shape beside the old: a new nullable column, a new table, a new index built without blocking writes. Nothing reads it yet, and the previous version ignores it. This release is safe to roll back.
2. Migrate. Ship code that writes both shapes, then backfill existing rows in bounded, idempotent batches that can stop and resume. Reads still come from the old shape.
3. Verify. Prove the shapes agree with a deterministic check, such as matching counts or a query that finds no row where they differ, then switch reads to the new shape in its own release.
4. Contract. Only once no running or rollback-eligible version reads the old shape, remove it in a later release. This is the one step that cannot be rolled back, so it waits until the verification has held through a release.

Each step is its own release, and each is proven before the next begins. Collapsing two steps into one release removes the point at which the previous version could still run.