A bad application deploy is usually just embarrassing. You push a fix, the containers restart, and everyone moves on. A bad migration is a different animal: it can hold a write lock on your largest table for eleven minutes, or delete rows that no backup window will easily recover. Migrations sit between the code you control and the data you cannot afford to lose, which is exactly why they attract so many small, avoidable mistakes.

Locking a busy table without realising it

Most relational databases take a strong lock for schema changes. In PostgreSQL, ALTER TABLE asks for an ACCESS EXCLUSIVE lock, which blocks reads as well as writes. The awkward part is the queue. If a long-running SELECT is already holding a lock, the ALTER TABLE waits behind it, and every query arriving afterwards waits behind the ALTER TABLE. A change that takes two milliseconds can still stall traffic for minutes.

Adding a column with a constant default is cheap on PostgreSQL 11 and later, because the default is stored in the catalogue rather than written to every row. On older versions, and on plenty of other engines, the same statement rewrites the entire table. MySQL can perform some operations instantly, such as adding a column at the end of a table on recent InnoDB versions, but many others still copy the data.

Indexes deserve the same suspicion. A plain CREATE INDEX blocks writes for the duration; CREATE INDEX CONCURRENTLY avoids that but takes longer and cannot run inside a transaction. Whichever route you take, give the migration a short lock timeout so it fails fast instead of becoming a traffic jam.

• Set a lock timeout (lock_timeout on PostgreSQL, lock_wait_timeout on MySQL) and statement timeout on the migration connection, then retry.
• Build indexes concurrently, outside a transaction block.
• Split large backfills into batches with a pause between them.
• Run migrations ahead of the deploy that needs them, not during peak traffic.
• Check whether the engine can perform the change in place or must copy the table.

Mixing schema changes with data changes

A migration that adds a column, backfills it, then adds a NOT NULL constraint has three failure modes and one rollback path. Worse, the backfill is usually the slow part: updating forty million rows inside a single transaction bloats the write-ahead log, holds locks, and makes the whole thing all-or-nothing. On MySQL, where data changes are transactional but DDL is not, a failure halfway through can leave you in a genuinely murky state.

Separate the concerns:

1. Add the column as nullable. This is fast and reversible.
2. Run a batched backfill as a job rather than a migration, updating a few thousand rows at a time.
3. Add the constraint or the default once the data is clean.

Each step can then be deployed, observed and paused independently. If the backfill is slow, you find that out without blocking a release.

Treating rollbacks as an afterthought

Some migrations cannot be undone. Dropping a column, deleting rows, or narrowing a type are one-way doors, and a down method that pretends otherwise is worse than no down method at all. Be honest: if a change is irreversible, raise an error in the rollback and document why, so nobody discovers it during an incident.

For anything destructive, build in a delay. Rename rather than drop, keep the old column for a release or two, and remove it in a separate migration once you are confident nothing reads it. If the data itself matters, copy it into an archive table first. Rolling forward with a fix is often a better answer than rolling back a schema change that has already run.

Testing the rollback counts too

A down migration that has never been executed is a guess. Run it against a copy of a realistic database before you need it, and check that the resulting schema matches what the previous release expects.

Ignoring the fact that two versions of the code run at once

Rolling deploys mean old code and new code are both live for a period, both talking to the same database. Rename a column in a single step and the old instances start throwing errors the moment the migration lands. The expand-and-contract pattern exists for this reason: add the new column, write to both, backfill, switch reads, stop writing to the old one, and drop it in a later release. It takes longer and feels fussy, right up until the moment it saves you.

Assuming every statement runs inside a transaction

Many migration tools wrap each migration in a transaction. That is reassuring, but it is not universal. MySQL performs an implicit commit around DDL statements, so a migration that alters a table and then fails on a data update leaves the schema change in place. PostgreSQL will not run CREATE INDEX CONCURRENTLY or VACUUM inside a transaction block at all. Know which of your statements are transactional and which are not; when the answer is mixed, split them into separate migration files so a failure has a clearly defined boundary.

Testing against a database that looks nothing like production

A migration that finishes in twelve milliseconds against a fixture with thirty rows tells you almost nothing. Production has volume, and volume changes everything: index builds take minutes, backfills take hours, and a NOT NULL constraint that passed on test data fails because a few hundred legacy rows contain NULLs. Before shipping, run the migration against a copy of production data, or at least a generated dataset of a similar size and shape, and time it. Hunt for the awkward cases: NULLs, duplicates, values outside the new validation rules, and orphaned foreign keys.

A short pre-flight checklist

• Does this lock a table, and for how long?
• Is the schema change kept separate from the data change?
• Can the previous release still run against the new schema?
• What happens if it fails halfway, and is that state recoverable?
• Has it been run against realistic data, and timed?
• Is there a rollback, or an honest note saying there isn't one?

Migrations are at their best when they are small, boring and reversible. Write them in steps you can deploy separately, give them a lock timeout, and save the destructive parts for a later release. The database will reward you by staying up.

Photo: FreePhotosART / Pixabay