Database migrations sit at the intersection of schema design, deployment sequencing, and operational risk. Done badly, a migration turns a routine release into an hours-long incident. Done well, the change is invisible to users and reversible in minutes. Most Australian dev teams sit somewhere between those two extremes, usually closer to the first than they'd like to admit.
Why migrations fail in the first place
The root cause is rarely technical. It's sequencing. A team deploys new application code that references a column that doesn't exist yet, or drops a column that an older pod is still reading. The database and the application are temporarily out of sync, and that gap is where incidents live.
Lock contention is the second killer. Renaming a large table, adding a non-nullable column without a default, or rebuilding an index in the foreground can hold an exclusive lock for minutes on a busy PostgreSQL or MySQL instance. A lock held for 90 seconds on a table that receives 500 writes per second is not a minor inconvenience. It's a production outage.
The third failure mode is the rollback that doesn't exist. A team runs a migration, something breaks, and they discover the rollback script was never written, or it was written but not tested. Restoring from a snapshot takes 40 minutes. That 40 minutes is entirely avoidable.
The expand-and-contract pattern
Expand-and-contract is the most reliable zero-downtime migration pattern available. It breaks what looks like a single schema change into three separate deployments.
In the expand phase, you add the new structure alongside the old one. A new column sits next to the old column. A new table sits next to the old table. The old code still works because nothing it depends on has changed.
In the migrate phase, you backfill data into the new structure and update application code to write to both old and new paths simultaneously. Reads still come from the old path.
In the contract phase, once you're confident the new path is correct, you flip reads to the new structure and then drop the old one in a subsequent release.
The price is time and complexity. You're running three deployments instead of one, and you're maintaining dual-write logic for a period. For most teams, that's a worthwhile trade. A migration that takes three weeks to fully complete but never causes downtime is better than a migration that takes one hour and knocks over the service for 20 minutes.
Schema change tooling worth knowing
Tooling does a lot of the heavy lifting here. gh-ost from GitHub is the most widely used online schema change tool for MySQL. It uses a ghost table and binary log streaming to apply changes row-by-row without holding locks on the original table. Percona's pt-online-schema-change does something similar using triggers.
For PostgreSQL, pgroll and Flyway are both worth evaluating depending on your team's migration philosophy. Flyway treats migrations as versioned SQL files checked into version control. pgroll goes further and manages the multi-version schema state explicitly, making expand-and-contract easier to orchestrate without writing custom dual-write logic.
Liquibase is the enterprise-grade option with multi-database support. It's heavier than Flyway, but for teams running migrations across Oracle, SQL Server, and PostgreSQL simultaneously (common in larger Australian enterprises), the abstraction layer earns its weight.
Backfills: the part that takes forever
Backfilling data into a new column or table on a large dataset is where plans fall apart. Running UPDATE orders SET new_column = compute(old_column) WHERE new_column IS NULL against 200 million rows in a single transaction will hold locks, spike I/O, and very possibly time out.
Batch the writes. Process 1,000 rows at a time with a short sleep between batches. This reduces lock contention and gives replication lag time to recover on replicas. Yes, the backfill takes longer. On a table with 50 million rows and a 10ms sleep between 1,000-row batches, you're looking at roughly 8 minutes of wall time. That's fine. The alternative is a 20-minute lock.
Always backfill with a worker process, not a migration script that runs at deploy time. Migration scripts that run at startup should be fast. A backfill that could take 30 minutes should run as a background job, independently monitored, with progress logged.
Sequencing application deploys with schema changes
The ordering rule is simple: schema-first when adding, application-first when removing.
Adding a new column? Deploy the schema change first, then deploy the application code that uses it. If the deploy fails partway, old application code runs against a schema that has an extra column it ignores. No problem.
Removing a column? Deploy the application code that no longer references the column first. Once that's running cleanly, drop the column. Reverse the order, and you're dropping a column that running pods are still reading from.
This sounds obvious. In practice, it gets violated constantly when developers bundle schema changes and application changes into the same release without thinking through the failure modes. CI/CD pipelines that treat database migrations as just another step in the deploy sequence, without separating them from application code, make this violation easy to accidentally commit.
Testing migrations before they hit production
A migration that works on a 10,000-row development database may not work on a 300-million-row production table. This is not a hypothetical. Index builds that take 2 seconds locally take 18 minutes in production. The time estimate matters for planning maintenance windows, but more importantly, the locking behaviour can differ based on row count.
Stage migrations against a production-sized dataset. Clone your production database (or a recent snapshot) to a non-production environment and run the migration there before promoting to production. Tools like pgcopydb for PostgreSQL make this faster than a full dump and restore. Measure the wall time, watch lock waits, and check replica lag before you commit to a production window.
Also test rollbacks. A rollback script that was never run is a rollback script that probably has a bug. Run it after every migration test. Confirm the schema returns to its prior state and the application still starts cleanly against the rolled-back schema.
Avoiding the common mistakes
A few patterns that teams repeat despite knowing better:
- Adding a NOT NULL column without a default value. Most databases require a full table rewrite to backfill the constraint. Add the column as nullable, backfill, then add the constraint separately.
- Renaming columns or tables instead of adding new ones. Renames break running application instances immediately. Add the new name, migrate, drop the old one.
- Forgetting foreign key validation locks. In PostgreSQL, adding a foreign key validates the entire table by default. Use
NOT VALIDfirst, thenVALIDATE CONSTRAINTin a separate transaction that uses a ShareUpdateExclusiveLock rather than a ShareRowExclusiveLock.
The feature flag pattern is useful here too. Separating schema deployment from feature activation means you can apply a schema change days ahead of a feature release, monitor it in production at low load, and only activate the feature flag when you're confident. Feature flags decouple deployment from release, and database migrations benefit from exactly the same logic.
Keeping migrations in version control
Every migration script belongs in version control alongside the application code it relates to. This sounds basic because it is, but teams running ad-hoc schema changes via a database console, without committing the change to a migration file, eventually find themselves with a production schema that nobody can reproduce from scratch. Onboarding new developers becomes a problem. Disaster recovery becomes a guessing game.
The migration history should be the authoritative record of how the schema got to its current state. Each file should be named with a timestamp or sequential number, describe what it changes, and include an accompanying rollback. If a team's migration folder looks like a clean numbered sequence, that's a team that can reproduce their production environment from scratch.
Treat the database as code. It's not a shared mutable resource that gets modified in place. It's a versioned artefact with a known history and a tested upgrade path. That shift in thinking is what separates teams that migrate safely from teams that plan their releases around maintenance windows.