Recoverable database migrations

Plans schema changes that preserve data and allow old and new application versions to coexist during transition.

database-migrations

See how it helps

When is it useful?

Use when changing populated tables or introducing a replacement column.

Plan replacing a customer-name column without losing values or breaking old workers during deployment.

Read the skill’s original instructions, examples and checks in their authored language.

Details…Show less

Every schema change is a migration file in the repo's tool. Never change production by hand.
In production, migrations only move forward: undoing one is a new migration. Down steps are for development and staging.
Schema changes and data changes are separate migrations.
A migration that ran anywhere shared is never edited; add a new one.
Test against production-sized data: what is instant on 100 rows can lock 10 million.

Name each new table's or field's owner, life cycle and access rules.
The tightest correct type; constraints (unique, foreign key, check) for rules that must always hold.
Data shared by several customers carries its tenant key, in its unique constraints and indexes.
Money as integer minor units or decimal, timestamps in UTC.

Old and new code run side by side during a deploy.

New columns are nullable or have a default. Adding NOT NULL to existing data: add nullable, backfill, then add the constraint.
Indexes on existing large tables are built without blocking writes (CREATE INDEX CONCURRENTLY in Postgres, online DDL elsewhere). These usually cannot run inside a transaction, so give them their own migration and plan for a half-applied state.
Rename or retype in stages (expand, migrate, contract):
1. Expand: add the new column or table; deploy code that writes both.
2. Backfill the existing rows.
3. Migrate: deploy code that reads the new one and still writes both; verify the data matches.
4. Contract: deploy code that uses only the new one; drop the old one in a later migration.
Dropping a column: remove every use from the code, deploy, then drop it in the next release.
Backfill large tables in batches (1,000–10,000 rows), committing each batch, skipping locked rows, resumable from where it stopped, with progress output and a pause when the database is busy. Never one UPDATE over the whole table.
Set a lock timeout so a blocked migration fails instead of stalling traffic.

Each migration has a down step or a note that it cannot be reversed, and the rollback plan is written down.
Destructive steps (DROP, DELETE, lossy type changes) can only be undone from a backup. Before one runs on real data, confirm a recent, restorable backup and get explicit approval. Never run one on shared or production data on your own.
Run up, down and up again locally on realistic data.

Index for the real queries: equality columns first, then the range or sort column. Confirm with EXPLAIN on realistic data; an index that does not change the plan goes.
Every index slows writes. Avoid N+1 queries; paginate large sets by key, not deep offsets.
Prevent read-modify-write races with conditional updates, SELECT ... FOR UPDATE or the right isolation level; keep transactions short, with no network calls inside.

Anti-pattern	Why it fails	Instead
Manual SQL in production	No audit trail, not repeatable	Migration files
Editing a deployed migration	Environments drift	A new migration
NOT NULL without a default	Locks and rewrites the table	Nullable, backfill, then constrain
Blocking index build on a large table	Writes stop during the build	Concurrent or online build
Schema and data in one migration	Long transactions, hard to undo	Separate migrations
Dropping a column before the code stops using it	Errors on the missing column	Remove the code first