Database Change Management

Almost every CI/CD lesson quietly assumes the thing being deployed is stateless: build an artifact, replace the old one with the new one, and if it goes wrong, redeploy the artifact you just replaced. A database breaks that assumption completely. It is one shared, mutable resource that every version of your application talks to at once, its rows keep changing after the deploy that touched them is long finished, and "redeploy the old schema" is not a rollback — the data has already moved on. This page is about the discipline most pipelines never quite build: how migrations are ordered and tracked, how to change a schema without an outage using the expand/contract pattern, how to keep a schema change safe while a blue/green or canary deploy has two versions of your app running against it at once, and why the backup sitting in a bucket somewhere is not the same claim as "we can restore."

The stateful-workload gap CI/CD quietly assumes away

☺ Like you're 10: Swapping a container is like swapping a lightbulb — old one out, new one in, done. A database is more like the water still running through the pipes while you replace them — you can't just turn it off and swap the pipe.

A container image is immutable and disposable: build it once, and every replica of it is byte-for-byte identical and interchangeable. If a rollout goes wrong, you point traffic back at the previous image and the world returns to exactly the state it was in before, because nothing about the artifact itself changed while it ran. Deployment strategies — rolling, blue-green, canary — are built entirely on that property.

A database has none of it. It is one long-lived, shared, mutable resource that every version of your application reads and writes concurrently during a rollout, and its contents keep changing after any given deploy is finished — new rows get inserted, existing rows get updated, by both old and new code, for as long as both are running. "Roll back the database" doesn't mean what "roll back the app" means: you can revert a schema to a prior shape, but you cannot revert the data that real users generated while the new schema was live, because rolling the schema back doesn't know how to un-write it. That asymmetry — code is disposable, data is not — is the entire reason database change management is its own discipline rather than a bullet point under "deploy the app."

Migration tooling and how ordering actually works

☺ Like you're 10: Every tool needs a way to know which changes have already happened and which haven't — otherwise it might redo one twice, or run two out of order and break everything.

Every migration tool solves the same problem: apply a sequence of schema changes exactly once, in a defined order, and remember which ones have already run — even across many developers, many branches, and many environments that don't share a clock. The mechanisms differ more than you'd expect.

Versioned, timestamp/number-ordered files — Flyway and golang-migrate number migrations directly in the filename and apply them strictly in that order; Rails' ActiveRecord migrations use a creation timestamp for the same purpose. Dependency-graph tools — Alembic (SQLAlchemy) and Django don't rely on filename order at all: each migration file records the specific parent it was written against (Alembic's down_revision, Django's dependencies list), so the true order is a graph you can traverse, not a sort. Deploy/verify/revert scripts — Sqitch tracks a dependency plan independent of any ORM. Declarative, diff-based tools — Atlas and pgroll work the other way around: you declare the schema you want, and the tool diffs it against the schema that's actually there and generates the migration for you, rather than you hand-writing the delta.

Flyway          V1__create_orders.sql   V2__add_status_column.sql   V3__backfill_status.sql
golang-migrate  000001_create_orders.up.sql / .down.sql
Rails           db/migrate/20260214093000_add_status_to_orders.rb        (timestamp-ordered)
Django          orders/migrations/0002_add_status.py
                    dependencies = [('orders', '0001_initial')]          (explicit parent, not a sort)
Alembic         down_revision = '4a1c9f2e0b7d'                          (a linked list of revisions —
                                                                           two heads means a branch
                                                                           merge never happened)

Whichever shape it uses, every real tool tracks applied state in the database itself — Flyway's flyway_schema_history, Alembic's alembic_version, Rails' schema_migrations — usually alongside a checksum of the migration's own contents. That checksum is what stops a silent class of bug: someone edits an already-applied migration file after the fact instead of writing a new one, and the tool refuses to run rather than quietly re-applying a change that doesn't match what it recorded having run.

The ordering hazard that actually bites teams is a merge, not a typo. Two feature branches each add a migration independently, both branches pass CI in isolation, and both merge to main cleanly — but the two migrations were never tested running one after the other, and Flyway-style tools will flag this as out-of-order (a lower-numbered migration applied after a higher one already ran somewhere), while Alembic-style tools will simply refuse to resolve two heads into one line. The fix in both families is structural, not vigilance: a CI check that fails the build if the migration set doesn't linearize cleanly, and a policy of rebasing a migration branch onto the latest main before merging rather than after.

# Flyway: info shows Pending / Success / Missing / Out Of Order for every file
flyway -url=jdbc:postgresql://db/orders info
flyway -url=jdbc:postgresql://db/orders migrate

# Alembic: a merge that produced two branch heads has to be resolved before
# you can safely run anything — "heads" printing more than one line is the tell
alembic heads
alembic upgrade head

Zero-downtime schema changes: the expand/contract pattern

☺ Like you're 10: Add the new thing first and let both the old and new ways work for a while. Only once nothing needs the old way anymore do you take it away.

Most schema changes people reach for instinctively are destructive in one step: rename a column, change its type, add a NOT NULL constraint, drop a column nobody thinks is used anymore. Run any of those as a single statement and you've made an assumption that every piece of code touching that table changed at the exact same instant — which is never true the moment you're running more than one replica, and it's structurally false for the entire duration of any rolling, canary, or blue-green rollout. The fix is to split one destructive change into three additive, individually-safe steps known as expand, migrate, contract (sometimes called the "parallel change" pattern):

• Expand — add the new shape (a new column, a new table) without touching or removing the old one. This is additive only, so it's safe to ship on its own, deployed and baked before any app code depends on it.
• Migrate — backfill existing data into the new shape in small batches, and deploy app code that can read and write both shapes for as long as old code might still be running.
• Contract — once every instance is confirmed running the new code and a bake period has passed with nothing reading the old shape, remove it for good.

Applied to an actual "rename a column" job, the three steps look like this — note that steps 1 and 4 are separate deploys, not two statements in one migration file:

-- UNSAFE, one step: breaks any code still deployed that reads the old name
ALTER TABLE orders RENAME COLUMN status TO order_status;

-- SAFE — spread across separate deploys:
-- 1. EXPAND: additive only, ships and bakes before any app code depends on it
ALTER TABLE orders ADD COLUMN order_status varchar(32);

-- 2. BACKFILL: copy in small batches, never one giant transaction on a live table
UPDATE orders SET order_status = status
WHERE id BETWEEN :batch_start AND :batch_end AND order_status IS NULL;

-- App deploy in between: new code reads/writes order_status; any old code still
-- running keeps using status — the app (or a trigger) keeps both columns in
-- sync for the whole rollout window, see the coordination section below.

-- 3. CONTRACT: only after every instance runs the new code and has baked
ALTER TABLE orders DROP COLUMN status;

Postgres-specific tools have started building expand/contract in as a first-class primitive rather than leaving it to migration-author discipline — pgroll exposes both the old and new column shape simultaneously through versioned views, so old and new application code can each keep querying the shape they were written for without any dual-write code in the app at all, and the contract step is a separate, explicit command run once you're ready.

Coordinating a migration with a blue/green (or canary) app deploy

☺ Like you're 10: If the kitchen has both an old stove and a new stove plugged in, you can't rip either one out until every cook has actually switched over — not just when the schedule says the switch is "supposed" to be done.

Blue-green and canary deploys exist specifically to run old and new app code side by side against the same production dependencies for a window of time — that's what makes an instant, safe rollback possible. But it means the database underneath has to tolerate both versions' queries at once for the entire rollout, and the ordering across the three moving pieces — migration, app deploy, and the eventual contract — has to be deliberate, not incidental:

1. Ship the expand migration first, on its own. It's additive, so both the currently-running old code and the not-yet-deployed new code are unaffected by it. Let it bake — confirm it applied cleanly and, ideally, backfill has started — before touching the app.
2. Roll out the app change (blue-green cutover, or canary ramping 1% → 100%). New code reads/writes the new shape; it must also tolerate rows where the new column is still NULL because backfill hasn't reached them yet. Old code — still live in blue, or in the shrinking canary baseline — keeps using the old shape untouched.
3. Confirm the old code is fully drained — blue scaled to zero, or the canary at 100% with the previous ReplicaSet gone — not just "the deploy pipeline reported success." A rollout that finished five minutes ago can still have in-flight requests or a lagging cache pointed at the old version.
4. Hold a bake period after drain before touching the schema again. This is slack for anything you didn't account for — a queued background job built against the old shape, a report that runs nightly, a downstream service with its own deploy lag.
5. Ship the contract migration as its own deploy, gated separately, only after steps 3 and 4 are both true.

In practice this means the migration and the app deploy are two independently-orderable pipeline stages, not one lockstep unit — a schema-migration job (a Kubernetes Job, a Helm pre-install hook, or a dedicated pipeline stage upstream of the deploy stage) that must succeed before the app rollout begins, and a second, separately-triggered contract stage that a human or a scheduled follow-up kicks off only once drain and bake are confirmed. CI/CD pipelines covers how pipeline stages get sequenced and gated in general; this is that mechanism applied specifically to the one stage that isn't safe to auto-promote on the same clock as everything else. Release Trains & Change Management covers the change-approval side of gating a contract migration behind explicit review rather than letting it auto-fire.

Why migrations lock production, and the tools that avoid it

☺ Like you're 10: Some changes need to rebuild the whole shelf before anyone can add or take anything off it — while that's happening, everyone's stuck waiting.

Expand/contract keeps a change safe to run mid-rollout; it doesn't automatically make any individual step fast. Certain DDL operations still take out heavy locks or rewrite an entire table, and on a large, high-traffic table that's an outage in its own right, independent of whether the change is additive. Lock behavior varies by database version — the specifics below have shifted release to release and are worth checking against whichever version you're actually running — but the general shape holds broadly:

For the MySQL case specifically, two open-source tools converged on the same shape: build a new "shadow" table with the desired structure, copy existing rows across in batches, keep the shadow table in sync with ongoing writes (gh-ost tails the binlog; Percona's pt-online-schema-change uses triggers on the live table), and finish with a brief atomic rename that swaps the shadow table in for the original — seconds of lock instead of minutes-to-hours.

# Postgres: build the index without holding a table-wide write lock
CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);
# a build that fails partway leaves an INVALID index behind — drop and retry:
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_status;

# MySQL: gh-ost builds a shadow table, tails the binlog to replay writes,
# and cuts over with a brief atomic rename instead of locking the live table
gh-ost --host=db01.internal --database=orders --table=orders \
  --alter="ADD COLUMN order_status VARCHAR(32)" \
  --allow-on-master --cut-over=atomic --execute

# ...or Percona's pt-online-schema-change — same shadow-table idea via triggers
pt-online-schema-change --alter "ADD COLUMN order_status VARCHAR(32)" \
  D=orders,t=orders --execute

A migration-review gate catches these before they ever reach a live database. Squawk lints Postgres migrations in CI and blocks the classic footguns — an index without CONCURRENTLY, a NOT NULL column added without a safe default path — and Rails' strong_migrations gem does the equivalent for ActiveRecord migrations, refusing to run ones it recognizes as unsafe unless a developer explicitly overrides it. Treat that lint the same way you'd treat a failing unit test: a required, blocking check in the pipeline, covered in general in Testing in the Pipeline, not a suggestion someone reads after the outage.

Rollback: why a "down migration" rarely saves you mid-incident

☺ Like you're 10: Undoing the shelf you built doesn't undo the groceries someone already put on it — those don't go back in the bag just because the shelf came down.

Most migration tools generate a "down" migration alongside the "up" one, and it's tempting to treat that as a rollback button. It usually isn't. A down migration reverses the schema; it has no idea what to do about the data that new application code already wrote into the new shape while it was live. Drop the new column back out and every value written into it — real user data generated in the minutes or hours the new code was running — is simply gone, with no way for the down migration to know it should have converted it back into the old column first.

-- down-migration for "add order_status, backfill from status"
ALTER TABLE orders DROP COLUMN order_status;
-- This undoes the SCHEMA. It does nothing about the fact that, for the last
-- two hours, new code has been writing values into order_status that were
-- never written to status — that data is just gone the moment this runs.

This is exactly why expand/contract matters for incident response, not just for zero-downtime deploys: because the expand phase is purely additive, the old app code still works correctly against the expanded schema — that was the whole point of doing it in that order. So the actual rollback during an incident is almost always "redeploy the previous app version," the same fast, well-rehearsed mechanism covered in deployment strategies — not "run the down migration." The schema stays exactly where it is; only the app version changes back. A down migration, when it's ever run at all, is a deliberate, separately-reviewed forward change made later, once you're certain nothing needs the old shape anymore — never an automated reflex fired the instant a deploy looks bad.

Backup and restore as a pipeline citizen, not a cron job

☺ Like you're 10: A backup nobody's ever tried to restore is really just a guess that it would work — you don't actually know until you try, on a schedule, not just when something's already on fire.

Backups are usually built as infrastructure — a cron job or a managed snapshot schedule that runs quietly in the background, entirely disconnected from the deploy pipeline that's about to run a risky migration against the same database. That disconnection is the problem: a backup taken six hours ago tells you nothing about the state of the database one second before a contract migration drops a column, and a backup that's never been restored is an assumption wearing the costume of a guarantee. Two mechanisms fix this by making backup/restore part of the pipeline rather than a background process next to it.

The pre-migration snapshot gate

Any migration classified as risky or destructive — a contract step, in particular — should be preceded by a required pipeline stage that takes a fresh snapshot and blocks the migration stage until that snapshot is confirmed AVAILABLE, not merely "requested."

# Required gate before any contract-phase or destructive migration is allowed to run
SNAP="pre-migration-$(date +%Y%m%d-%H%M)"
aws rds create-db-snapshot \
  --db-instance-identifier prod-orders \
  --db-snapshot-identifier "$SNAP"
aws rds wait db-snapshot-available --db-snapshot-identifier "$SNAP"
# only once this returns does the pipeline let the migration stage start —
# a snapshot still in "creating" state is not a backup you can rely on yet

The scheduled restore-and-verify loop

A snapshot proves you can take a backup. It doesn't prove you can restore one — corrupted backups, permission drift, and format changes all pass a snapshot-succeeded check while silently failing a real restore. The fix is a separate, continuously scheduled job — independent of any deploy — that restores the latest backup into a throwaway environment and runs real verification against it, not just a status check.

# Scheduled, deploy-independent: proves backups actually work, not just that
# they exist. Point-in-time recovery via WAL archiving narrows recovery to
# seconds instead of the gap between snapshots.
pgbackrest --stanza=prod-restore-test --type=time \
  --target="$(date -u -d '1 hour ago' +'%Y-%m-%d %H:%M:%S')" restore
psql -d restore_test -c "SELECT count(*) FROM orders;"          -- sanity row count
psql -d restore_test -c "SELECT max(updated_at) FROM orders;"   -- freshness check
# alert the on-call rotation if the restore itself fails, or either check
# comes back wrong or stale — silence here is exactly how backups quietly rot

The tooling differs by engine and platform but the two roles above are the same everywhere: pgBackRest and WAL-G handle Postgres backup plus write-ahead-log-based point-in-time recovery; Percona XtraBackup does the equivalent hot-backup job for MySQL/InnoDB; AWS RDS, Google Cloud SQL, and Azure Database all layer automated snapshots and point-in-time restore on top of the same idea as a managed service; and Velero, paired with CSI volume snapshots, backs up self-hosted stateful workloads running in Kubernetes. Whichever you're on, back the store up encrypted, and scope who can trigger a restore the same way you'd scope any other production credential — see Secrets & Credential Management for the access-control side of that.

Treat both numbers as SLOs in their own right, in the same vocabulary as SLOs, Error Budgets & Toil — and treat the scheduled restore drill above as a standing rehearsal, the database-specific instance of the fire drills covered in Chaos Engineering & Game Days. A restore procedure nobody's run since it was written down is not a procedure; it's a hope with steps.

Database change management is the stateful thread running underneath most of the rest of this course. It's why deployment strategies caveats that old and new code must tolerate one schema together; it's the specific mechanism a well-designed CI/CD pipeline gates the way it gates any other risky, hard-to-reverse stage; it leans on the same identity-and-access discipline as secrets & credential management for who can trigger a restore; and the migration-lint and restore-drill habits here are specific instances of the broader patterns in Testing in the Pipeline and Chaos Engineering & Game Days. Provisioning the database instances and their automated backup schedules in the first place is its own discipline, covered in infrastructure as code; practice the mechanics hands-on in Drill — Roll Back a Bad Deploy and Capstone Part 2 — Infrastructure as Code.