Safe Database Migrations: A Practical Plan for Production Changes

A migration can succeed in staging and still disrupt production. A table may be much larger, writes may continue during the change, old application instances may still be running, and a waiting schema lock can turn a short command into a queue of blocked requests.

The safest way to think about a production migration is as a compatibility change across time. The database schema, the deployed application versions, background workers, and operational procedures must agree while the transition is underway. This guide uses PostgreSQL examples, but the planning principles apply to other relational databases. Lock behavior, transactional DDL, online index options, and constraint validation differ by engine and version, so verify them against the documentation for the database you run.

Start with the change’s blast radius

Before writing SQL, identify every consumer of the affected data: web servers, workers, scheduled jobs, reports, integrations, and older application versions that may remain active during a rollout. Check table size, write rate, existing constraints and indexes, replication or logical decoding, and available disk space. A “small” schema change can be expensive when it rewrites a large table or waits for a lock.

Write down the desired end state, the intermediate states, and how you will know each step worked. Estimate the operation on production-like data. Decide who can stop the rollout and what signals trigger a pause: rising database latency, lock waits, replication lag, elevated error rates, or a backfill that is not making progress.

Use expand, migrate, and contract

Suppose an application stores a person’s name in accounts.name, and you want to move to accounts.display_name while traffic continues. Replacing the old column in one deployment assumes every application instance changes at once. A staged transition keeps both versions compatible.

1. Expand: add the new column in a way the current application can tolerate. In PostgreSQL, adding a nullable column without a default is often a straightforward starting point, but still check the lock requirements and the exact server version. Avoid combining unrelated operations into one migration if that forces a stricter lock.

2. Deploy compatible code: make the application able to read the new value and fall back to the old one. During the transition, write both columns where needed. Deploy this code before depending on the new column so old and new application instances can overlap safely.

3. Migrate data: backfill existing rows in bounded batches. Use a stable key range, commit each batch, make the update retryable, and skip rows already filled by live traffic. Throttle based on database load instead of running one huge update that holds locks, generates a large write-ahead log, or competes with normal work.

4. Verify: compare expected and actual row counts, check for nulls or invalid values, and confirm application reads and writes use the new field. Keep monitoring after the backfill; a command completing is not proof that the application is correct.

5. Switch: move reads to the new column only after the data and all active writers are ready. Keep the fallback or dual-write path until old instances, workers, and rollback windows have expired.

6. Contract: remove the old column in a later deployment after confirming no code, job, report, or external consumer depends on it. Destructive cleanup is its own change, with its own review and recovery plan.

The separation is useful because rolling deployments are not instantaneous. At every step, ask whether the old application, the new application, and the current schema can operate together. The same pattern applies to renaming columns, changing data formats, splitting tables, and moving ownership of a field.

Make backfills bounded and observable

A backfill is production workload. Process a limited number of rows per transaction, use a predictable cursor such as an indexed primary key, and make the operation safe to resume. Avoid holding one transaction open for the entire table. Long transactions can retain old row versions, increase storage pressure, and complicate recovery.

Track rows scanned, rows changed, remaining nulls, elapsed time, database load, and replication lag if applicable. Test what happens when the job is interrupted halfway through. If new writes can race with the backfill, design the update predicate so the backfill does not overwrite fresher application data. A dry run or a small canary batch helps expose bad assumptions before the full run.

Plan for locks and indexes

DDL is not automatically harmless because it is short. PostgreSQL documents that many ALTER TABLE forms take an ACCESS EXCLUSIVE lock, which conflicts with ordinary reads; lock requirements vary by subcommand. A waiting DDL statement can also create a line of blocked work behind it. Set an intentional lock timeout where appropriate, run during a suitable window, inspect active transactions, and have a cancellation plan. Test the exact statement on the same PostgreSQL major version you operate.

For a large table, PostgreSQL’s CREATE INDEX CONCURRENTLY can reduce interference with writes, but it has operational costs: it performs multiple scans, waits for relevant transactions, cannot run inside a transaction block, and a failed build can leave an invalid index that needs inspection and cleanup. Confirm how your migration runner handles statements that cannot run transactionally, and verify the index state after the command. Other database engines have different online index features and restrictions.

For constraints on existing PostgreSQL tables, consider adding a supported constraint as NOT VALID and validating existing rows separately when that fits the requirement. This divides enforcement from the table scan, but it is not a universal shortcut: verify which constraint types support it, what locks each step takes, and how your server version behaves.

Design recovery before deployment

A rollback is not always a safe reverse migration. Once new writes use a new representation, dropping the column or reversing a transformation may lose data. Prefer application rollback paths that remain compatible with the expanded schema. For irreversible data changes, define a forward repair or restore strategy before running the migration.

Confirm backups are recent and restorable; a backup that has never been tested is an assumption. Rehearse the migration and recovery steps against a production-sized copy, including the time to execute, lock behavior, disk use, and deployment ordering. If you use replicas, confirm how the change affects replication and failover. Keep destructive cleanup separate so you preserve a practical recovery window.

A release checklist

• List every application, worker, report, and integration that reads or writes the affected data.

• Confirm engine and version behavior for locks, table rewrites, transactional DDL, and index operations.

• Deploy schema additions before code that requires them; preserve compatibility during rolling releases.

• Backfill in bounded, resumable batches and protect concurrent writes from being overwritten.

• Measure progress, errors, lock waits, database load, storage, and replication lag.

• Verify invariants and application behavior before switching reads or enforcing stricter constraints.

• Test rollback, forward repair, and backup restoration; define clear pause and abort signals.

• Remove old schema only after all consumers and recovery paths have moved on.

Safe migrations come from controlling the transition, not from hoping a single SQL command will be quick. Separate schema expansion, application rollout, data movement, verification, and cleanup; make each stage observable and reversible where possible. Then choose engine-specific techniques based on measured behavior and the failure you need to prevent.

Sources

• PostgreSQL: Explicit Locking

• PostgreSQL: ALTER TABLE

• PostgreSQL: CREATE INDEX

• Google Search Central: Creating helpful, reliable, people-first content