Currently Available: Need a skilled Software Developer for your next project?
Categories
Databases

Why Database Migrations Fail in Production

When a database migration passes in staging but disrupts a live service, the migration usually met different conditions in production. A database migration changes the database’s structure, such as its tables or indexes, or changes data already stored in those tables. Staging is a test environment; production holds the live data and handles real requests.

Staging success shows that a migration worked with the data, traffic, database settings, and application versions tested there. It does not prove that the same change will behave safely under production conditions. To reduce the risk, engineers need to check how the exact database operation handles locks and load, how the change fits a rolling software release, and how they will recover if it fails.

Staging rarely reproduces production conditions

A staging database can have the same table definitions as production and still behave differently. It may contain fewer rows and lack duplicate or null values. It may also receive little concurrent traffic. Production also has transactions that remain open while a migration starts, other services sharing database capacity, and old and new application versions running at the same time.

Those differences affect both whether a migration succeeds and what happens while it runs. A constraint that passes on staging data can fail when production contains a duplicate. A quick schema change on an idle database can wait behind a long-running transaction during peak traffic. A data update that works in a test can compete with production requests for database connections or disk access.

Testing with a volume and mix of data like production helps catch problems that staging would miss. Engineers should also test the specific database engine and version used in production, and assess how the migration behaves under concurrent load. Health New Zealand’s database migration guidance recommends representative data, lock and performance assessment, and bounded batches for large data updates.

Locks can turn a short change into a traffic jam

A database lock controls which other operations can access or change a table at the same time. The database engine and the specific change determine which lock the operation needs. For example, in PostgreSQL 15, many ALTER TABLE operations require an ACCESS EXCLUSIVE lock, which conflicts with ordinary reads and writes. Other ALTER TABLE operations use different locks, so the effect must be checked for the exact command. PostgreSQL’s ALTER TABLE documentation describes the lock requirements.

The delay can matter as much as the time needed to execute the change. If a long-running transaction holds a lock that conflicts with the migration’s requested lock, the migration waits. Requests that need the same table can then queue behind it. Even a brief change can build a long queue if it starts while other work holds a conflicting lock.

MySQL 8.4 has a related risk during online data definition language (DDL), the commands used to change database structures. An online change can allow reads and writes during much of its work, but it still needs an exclusive metadata lock to publish the new table definition. A long-running transaction can delay that lock; transactions arriving afterward can also get stuck waiting. MySQL’s online DDL performance documentation explains these lock and concurrency effects.

“Online” also does not mean that a change uses no resources. MySQL documents operations that rebuild or copy table data, consume disk space, or fail when a lock times out or a temporary resource runs out. Before release, engineers should inspect the SQL and confirm which algorithm the production database engine will use and what locks it will take. They should also decide what to do if the migration waits too long.

Backfills compete with live requests

A backfill fills or transforms values in rows that already exist. A single large update can keep the database busy and use connections and disk resources. If an interruption occurs, resuming safely can be harder. Even a backfill that completes successfully can slow unrelated requests while it runs.

Breaking a backfill into batches with a set size limits the work in each batch, but batching alone does not control the total load. Engineers should track progress and make each batch safe to retry. When database pressure or replication lag rises, they should slow or pause the backfill. Because the right batch size and rate depend on the database and workload, operators need to measure both rather than assume one setting suits every migration.

A GitHub incident shows how migration-related work can affect unrelated features. In an October 2026 incident, bursts of large queries from GitHub Enterprise Importer combined with regular traffic and exhausted connections on a shared database. GitHub reported that about 1.3% of requests failed or timed out at the peak, and about 6,400 organizations experienced at least one failed or slow request. The incident involved migration queries, not a specific schema ALTER TABLE; it illustrates how migration workload can use capacity that live traffic also needs. GitHub said it paused enterprise migrations while mitigating the issue, and planned smaller fixed-size batches and automatic backoff under database strain in its availability report.

Constraints test production’s actual data

A constraint declares a rule that database values must satisfy. A NOT NULL constraint requires a value in every row; a UNIQUE constraint rejects duplicate values. Before adding either, engineers need to check existing production data and consider what application versions still running during the change will write.

Shopify’s 2022 tests of a MySQL shadow-table migration tool found that adding a NOT NULL column without a default could conflict with writes from the existing application. The tests also found a case where adding a unique index could cause data loss when duplicate values existed. Those results apply to the tested MySQL and tool setup, but they show why a successful staging run is not enough: the engineer must verify the data and write behavior relevant to the production change. Shopify recommends checking for duplicates before adding a unique index in its investigation of NOT NULL columns.

Engineers check for invalid values and fix them before adding the constraint. The application must also handle writes made while the migration runs. If old code can still insert a null or duplicate value, the check can pass, but a later write can still make the constraint fail.

Rolling releases expose schema incompatibilities

A rolling deployment replaces application instances over time, so old and new versions run concurrently. If a migration removes a column while an old instance still reads it, that instance can fail. If new code expects a column that the migration has not yet created, the new version can fail instead.

One common approach is to make the change in stages that work with both application versions. First, add the new structure and keep the old one. Then deploy code that works with both. Next, copy or transform existing data in batches and check the result. Once the code uses the new structure, remove the old one only after no running code depends on it. This expand–migrate–contract pattern lets each stage coexist with the application versions running at the time. Engineers still need to test the exact operations on their database engine.

Migration frameworks help track which changes ran, but they do not make every database change transactional or reversible. In Django 6.1, migrations run inside a transaction by default on PostgreSQL and SQLite, while MySQL and Oracle do not support transactions around schema changes through Django. Django warns that a failed MySQL migration can leave changes that require manual cleanup in its migration documentation. MySQL 8.4’s support for atomic DDL lets certain individual operations either complete or roll back under documented conditions. A series of schema and data changes still spans multiple operations rather than one transaction.

Plan recovery before the migration runs

A rollback command does not always restore the data as it was before a migration. If the new application has written data in a new format, reverting only the schema can leave that data unusable. Engineers should decide in advance whether a failed migration can be retried, resumed, reversed, or needs manual repair. They should test that recovery plan and identify what data written after the change it preserves.

Before deployment, the release owner should confirm the following:

  • The exact SQL and its lock, runtime, and resource behavior have been checked for the production database engine and version.
  • Staging data includes relevant production-scale volume and data conditions, including nulls and duplicates where constraints are involved.
  • The migration has been tested with concurrent traffic and long-running transactions, and operators can identify its database load.
  • Large backfills run in restartable batches with progress tracking and a way to slow or pause them.
  • Old and new application versions can run against the schema during each release stage.
  • Operators know how to stop the migration and what recovery requires if it fails after changing schema or data.
What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com

Email updates

Usually a new article and a few links I found interesting.

No spam. Unsubscribe with one click.

Leave a Reply

Your email address will not be published. Required fields are marked *