Database Migrations Without Fear

There's a particular flavor of dread that comes with changing a database that real users are hitting right now. The code you can roll back in seconds. The data — the orders, the accounts, the thing your whole company runs on — you can't un-spill. So a schema change that felt trivial on your laptop turns into a held breath in the deploy channel: did that just lock the table? is the app still up? did I lose anything?

That dread comes from not knowing what the change is actually doing to the live system. This guide fixes that. We'll build the mental model of what a migration is, learn the one pattern that lets you change live data without downtime, and name the specific migrations that bite people — so you can see them coming instead of discovering them in an incident.

How to read this

About to ship a scary schema change right now? Skip to Phase 3: The Dangerous Migrations and check the cheat-card at the top against what you're about to run.
Want migrations to finally make sense? Read in order — each phase builds on the last. Phase 1 gives you the mental model, Phase 2 the safe pattern, Phase 3 the landmines.

The phases

What a Migration Is — a versioned, ordered change to your schema, checked into source control and applied identically in every environment. The "git for your schema" mental model, up/down (apply/rollback), and an annotated example migration.
Doing It Safely on Live Data — the golden pattern: additive changes first, backfill the data, then switch. The expand/contract (parallel-change) approach that keeps the running app working during a rename or type change.
The Dangerous Migrations — the ones that bite: long locks on big tables, dropping or renaming columns the running app still uses, and non-nullable columns without defaults. Plus the two things you always want first: a rollback plan and a backup.

Database-engine specifics (how Postgres vs. MySQL differ on locking, online-DDL tools like pg_repack, gh-ost, or pt-online-schema-change) are deliberately deferred. This guide teaches the patterns that hold across engines; the engine-specific tooling is a follow-up once the patterns are second nature.