Safe database migrations start before the deploy

1. Start safe database migrations with the transition

Consider a hypothetical application renaming notification_email to delivery_email. The value keeps exactly the same meaning; we’re only changing how the code refers to it. The web application uses it, a worker sends messages from it, and an administrator tool can update it. That third writer is easy to miss if we inspect only the request handlers.

Write down which deployed versions read and write each field. Include scheduled jobs and older workers that may remain alive after the web deployment finishes. A rename that works with the new application can still break a running process that expects the old column to exist.

The expand-and-contract pattern separates adding a compatible structure from removing the old one. Prisma’s migration guide demonstrates that staged idea. The exact data movement and deployment steps need to fit your application; the pattern’s name by itself doesn’t establish that a particular release is safe.

Reference: Prisma: Expand-and-contract migrations

For this example, I’d keep the original field available while introducing the replacement. That gives us room to move the writers and readers deliberately. Before implementing it, name the source of truth at each step and identify which application versions are valid recovery targets. Those answers belong in the migration review.

2. Give each phase a condition for moving forward

Here’s the sequence I would review for the hypothetical column change. It assumes both fields live in the same database and represent the same value. A change in business meaning needs its own mapping and conflict rules. Copying two columns does not settle those decisions.

For the two writes, atomic means they succeed or fail together in a database transaction. Two independent writes with an error handler between them can leave different values behind. If a writer spans separate systems, stop and design the consistency and recovery behavior for that boundary; this example doesn’t cover it.

Keep the removal step explicit. Leaving compatibility code forever creates another thing someone has to understand later. Removing it too early cuts off the recovery path. I’d assign the cleanup an owner and a condition, then review those conditions before the destructive change.

3. Make the backfill work while data changes

A backfill is ordinary production work competing for database resources. Give it a stable way to track progress, bounded batches, and a safe resume path. If the process stops halfway through, the next run should have a clear starting point and a defined treatment of rows it may encounter again.

In our example, first confirm that every live writer maintains both fields. Copy from the current source value within the database, with an update strategy that coordinates correctly with concurrent writes. Exporting values and writing them back later could overwrite a newer address with an older one. Test that interleaving deliberately before choosing the implementation.

Batch size needs evidence. Start with a bounded rehearsal using representative data, observe execution time and application latency, and choose pause conditions. A batch of a thousand small rows and a batch of a thousand large rows do not create identical work. I want the job to report its progress and explain a pause without someone having to inspect its process from scratch.

Validate actual values, including the meaning of nulls and empty strings, rather than relying only on matching row counts. For this rename, compare the two fields and investigate mismatches before switching readers. Exercise a user update during the backfill and verify which value survives. That specific test tells us more than a successful run against data nobody is changing.

4. Read the locking behavior of the actual statements

A short migration file can still have a large operational effect. PostgreSQL documents the lock levels of ALTER TABLE subcommands; unless a form states otherwise, it takes an ACCESS EXCLUSIVE lock. Review the exact statements your migration tool will execute and the database version they will run against.

Reference: PostgreSQL: ALTER TABLE lock requirements

Long-running transactions and live traffic belong in the rehearsal. If the migration waits for a lock, decide how long that wait is acceptable and what the operator should do when it exceeds the limit. Repeatedly restarting the same command without understanding the blocker can turn a bounded attempt into an extended disruption.

PostgreSQL’s lock_timeout limits individual lock-acquisition waits; statement_timeout limits statement duration. They answer different questions. Configure appropriate limits for the migration session, or transaction where applicable, and check how the runner handles failures. A timeout needs a recovery step, including clearing an aborted transaction before further work.

Reference: PostgreSQL: Lock and statement timeouts

Index work has its own rules. CREATE INDEX CONCURRENTLY cannot run inside a transaction block, and a failed concurrent build can leave an invalid index behind. Inspect the resulting state before retrying. A runner that wraps every migration in one transaction needs a different execution path for that operation.

Reference: PostgreSQL: Concurrent index builds

I want these details settled while the team has time to read and test. The deploy window should have a reviewed sequence and a person responsible for deciding whether to continue.

5. Rehearse recovery from the state you will actually have

An application rollback changes the running code. It doesn’t automatically undo data written by the newer version. In our example, a recovery release during the reader switch should still maintain both fields. Returning to a much older writer could let them diverge again and would require a new reconciliation before resuming the switch.

After removing the old column, assume old binaries that require it are no longer valid recovery targets. Decide whether the recovery will be a forward fix, a reviewed data repair, or a restore. If restore is the plan, establish its expected duration and which newer writes would need recovery or reconciliation.

A successful backup check does not replace a restore rehearsal. PostgreSQL’s backup verification documentation explicitly recommends test restores and checking that the restored database works and contains the expected data. Include the application’s critical workflows in that rehearsal.

Reference: PostgreSQL: Backup verification and test restores

Reader and writer inventory:
Compatible versions at each phase:
Source of truth and concurrent-write rules:
Backfill progress, resume, and pause behavior:
Data comparisons and workflow checks:
Lock behavior and execution limits:
Recovery target before and after removal:
Restore rehearsal and data-loss implications:
Release owner and cleanup condition:

This is the kind of preparation I’m happy to spend time on. It gives the team concrete ways to move forward and a clear point to stop if the evidence changes. That makes the migration easier to operate and the next one easier to review.

References and further reading

• Prisma: Expand-and-contract migrations
• PostgreSQL: ALTER TABLE lock requirements
• PostgreSQL: Lock and statement timeouts
• PostgreSQL: Concurrent index builds
• PostgreSQL: Backup verification and test restores