Safer Database Migrations in Production

A database migration changes the contract between stored data and application code. The risky part is often not the SQL in isolation; it is the period when old and new code, background workers, and the database schema overlap during deployment.

This guide describes a general approach and calls out PostgreSQL examples. Exact lock levels, transactional DDL, index creation, and rollback behavior differ across database engines and versions. Check the documentation for the database you actually operate and follow your team’s release procedure.

Start with the compatibility window

Imagine renaming display_name to name. If the new application reads only name while an older worker still writes display_name, data can be lost or the old worker can fail. A safer rollout often separates the change into compatible stages:

1. Expand: add the new representation while the old one still works.
2. Deploy compatible code: read both or prefer the new field while safely maintaining the old one as needed.
3. Backfill: copy old data in bounded batches and observe progress.
4. Switch consumers: verify every web process, worker, report, and external integration uses the new representation.
5. Contract: remove the old field only after no deployed code or recovery plan depends on it.

Not every change needs all five stages. The point is to plan for overlap instead of assuming every process changes at once.

Check operational impact before applying DDL

For the exact statement, ask:

• Which lock does this database version take, and for how long?
• Can reads or writes block while it runs?
• Does it scan or rewrite the whole table?
• Can it run inside a transaction in this migration tool?
• What happens if it times out or is interrupted?
• Is the table large or actively written? Are there long-running transactions?

In PostgreSQL, many ALTER TABLE forms take an ACCESS EXCLUSIVE lock unless a form documents otherwise; combining subcommands can require the strictest lock among them. A normal index build blocks writes while it runs, whereas CREATE INDEX CONCURRENTLY allows writes but takes longer, performs extra work, and has constraints such as not running inside a transaction block. These are PostgreSQL details, not portable assumptions about every database.

Test the migration against a representative non-production dataset when feasible. Measure duration and watch lock waits, disk space, replication lag, and application errors. A tiny local table is not evidence that a full production table will change safely.

Make backfills restartable and observable

Large data transformations may need to run separately from the schema migration. Process bounded batches, make the operation safe to retry, and record progress. Decide how to handle rows that change during the backfill. Monitor query load and allow it to pause or resume where your tooling supports that.

Validate the result with counts, constraints, or sampled comparisons appropriate to the data. Do not log sensitive row contents while investigating. Add the new application read path only after the data it depends on is available, or make missing values safe during the transition.

Design a realistic recovery plan

A migration tool’s “down” step does not guarantee a safe production rollback. Dropping a newly populated column can destroy user data; restoring a backup may lose writes since that backup. Before rollout, decide whether recovery means reverting code, pausing the migration, running a forward fix, restoring data, or some combination. Identify who can make that decision and which signals trigger it.

For a destructive change, take a verified backup or snapshot according to the team’s policy and understand restore time. Prefer a staged contract step after a healthy observation window so recovery does not require restoring data immediately.

Migration review checklist

• The old and new application versions can coexist through rollout.
• Database engine and version behavior have been checked in official docs.
• Lock duration, table size, write traffic, and timeout behavior are understood.
• Backfill work is bounded, restartable, observable, and validated.
• Secrets and personal data stay out of logs and fixtures.
• Recovery accounts for data written after the migration begins.
• Destructive cleanup waits until all consumers have moved.

Practice

Plan to add a non-null account_id to a large events table. Write an order of operations that supports old application processes during deploy, handles existing rows, checks the constraint safely, and delays any destructive cleanup. Then inspect the relevant migration tool and database documentation for the exact operations your plan requires.