Database Migrations at Scale: How to Change Production Databases Without Breaking Applications

By Ezenwa Christian Chimeremeze

An engineering deep dive into PostgreSQL, schema evolution, backward compatibility, zero-downtime deployments, data integrity, and production database reliability.

A database migration can look deceptively simple.

A developer adds a column, renames a field, changes a data type, creates an index, or introduces a new relationship between tables. The application works locally, the migration runs successfully, and the feature appears ready for deployment.

But production is a different reality.

An application may have thousands of active users, several running application instances, background workers, scheduled jobs, external integrations, and millions of existing database records. Not every component updates at the same moment.

A database migration that works perfectly in development can therefore cause application errors, unexpected locks, inconsistent data, or deployment failures in production.

This is why database migrations deserve more attention than simply writing SQL scripts.

They are an important part of software architecture, deployment engineering, and application reliability.

In this article, I will explore how database migrations work, why production migrations are difficult, and how engineering teams can introduce structural and data changes while keeping applications available.

The examples primarily use PostgreSQL, although many of the underlying principles apply to other relational database systems.

1. Understanding Database Migrations

A database migration is a controlled change to a database's structure or stored data.

These changes may include:

  • Creating tables
  • Adding or removing columns
  • Changing column types
  • Creating indexes
  • Introducing foreign-key relationships
  • Modifying constraints
  • Moving data between tables
  • Updating existing records
  • Removing obsolete structures

Consider an application that initially stores a user's name in a single column.

CREATE TABLE users ( id BIGSERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, email VARCHAR(255) UNIQUE NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW() ); 

Later, the product requires separate first and last names.

A developer might initially consider replacing the name column with two new columns.

ALTER TABLE users DROP COLUMN name; ALTER TABLE users ADD COLUMN first_name VARCHAR(100), ADD COLUMN last_name VARCHAR(100); This looks straightforward. 

However, what happens if the currently deployed application still expects users.name?

Its queries may immediately start failing.

This illustrates an important principle:

A database schema is not independent of the application that uses it.

The database and application form a connected system. Changing one can affect the other.

A migration must therefore consider application compatibility, deployment order, existing data, and operational behaviour.

2. Why Production Database Migrations Are Difficult

Development databases usually have fewer records, fewer concurrent connections, and fewer dependent services.

Production databases are different.

Existing data
A production database may contain millions of records accumulated over several years.

Adding a new field is not necessarily difficult, but deciding what value existing records should receive can be complicated.

For example, introducing a mandatory account_status field requires determining the correct status for existing accounts.

Application compatibility
During a rolling deployment, old and new application versions may run simultaneously.

The old version may expect one schema while the new version expects another.

If the migration breaks the old version before the deployment finishes, active application instances can fail.

Database locks
Some schema operations require locks that temporarily restrict access to database objects.

Depending on the operation and database version, a migration may block queries or wait behind long-running transactions.

Data integrity
Changing existing records can introduce inconsistencies if relationships, constraints, or transformation rules are not carefully considered.

Deployment coordination
A migration may affect web servers, background workers, scheduled tasks, reporting services, and external integrations.

Updating only the main application is not always sufficient.

These challenges explain why production migrations require planning rather than simply executing SQL.

3. Schema Migrations and Data Migrations Are Different

It is useful to distinguish between two major categories.

Schema migrations
Schema migrations change the database structure.

Examples include:
A

LTER TABLE users ADD COLUMN phone_number VARCHAR(30); 

Or:

CREATE TABLE projects ( id BIGSERIAL PRIMARY KEY, name VARCHAR(200) NOT NULL, owner_id BIGINT REFERENCES users(id) ); 

Data migrations
Data migrations transform existing records.

For example, an application may need to convert a legacy status value into a newer format.

UPDATE users SET account_status = 'active' WHERE account_status = 'enabled'; In small databases, this operation may be straightforward. 

In larger databases, updating every record in one transaction can create operational problems.

Long-running transactions can increase resource consumption, delay maintenance, and create substantial database load.

Data migrations should therefore be designed around the volume of records and the application's availability requirements.

4. The Expand-and-Contract Migration Pattern

One of the most useful strategies for backward-compatible database changes is the expand-and-contract pattern.

The principle is simple:

Instead of replacing an existing database structure immediately, introduce the new structure alongside the old one, migrate the application gradually, and remove the old structure only after it is no longer needed.

Consider the earlier example involving a user's full name.

The original database contains:

`name VARCHAR(255)` 

The new design requires:

`first_name VARCHAR(100)` `last_name VARCHAR(100)` 

A safer migration can be divided into stages.

Stage 1: Expand the schema
Introduce the new columns without removing the existing column.

ALTER TABLE users ADD COLUMN first_name VARCHAR(100), ADD COLUMN last_name VARCHAR(100); The old application continues using `name`. 

The database now supports both representations.

Stage 2: Update application behaviour
Deploy an application version that understands both structures.

For example, the application can read the new fields when available while retaining compatibility with existing records.

During the transition, writes must be handled consistently.

One possible approach is to temporarily maintain both representations through carefully controlled application logic.

However, dual writes introduce consistency risks. If one write succeeds and the other fails, the values can diverge.

For important data, the application should use appropriate transactions, reconciliation, or a deliberately designed synchronisation mechanism.

Stage 3: Backfill existing records
Existing records need to be converted.

For a simple illustrative example:

UPDATE users SET first_name = split_part(name, ' ', 1), last_name = NULLIF( substring(name FROM position(' ' IN name) + 1), '' ) 

WHERE first_name IS NULL;
This example assumes a relatively simple naming format. Real-world names are not reliably separable using a single-space rule.

A production migration should use business-approved transformation logic and preserve ambiguous records for review.

Stage 4: Verify the new representation
Check how many records still require migration.

SELECT COUNT(*) FROM users WHERE first_name IS NULL; 

Additional validation should compare old and new representations and identify inconsistent records.

Stage 5: Move application reads to the new fields
Once the new representation is populated and verified, update the application so that it no longer depends on the old column.

All relevant application versions, workers, and integrations must be checked.

Stage 6: Contract the schema
Only after confirming that the old column is no longer used should it be removed.

ALTER TABLE users DROP COLUMN name; 

This final step should happen in a separate deployment, not as an immediate part of the initial migration.

The expand-and-contract pattern creates a controlled transition rather than forcing the database and application to change simultaneously.

5. Understanding PostgreSQL Locks and Migration Safety

PostgreSQL provides powerful transactional capabilities, but database operations have different locking requirements.

A migration that appears harmless can still interfere with production traffic.

For example:

ALTER TABLE users ADD COLUMN phone_number VARCHAR(30); 

This operation is generally metadata-oriented when adding a nullable column without a default, but it still requires a table lock.

The actual impact depends on existing transactions, lock contention, table activity, and the PostgreSQL version.

Developers should understand the locking behaviour of the operations they introduce.

Using lock timeouts
A migration should not wait indefinitely for a required lock.

PostgreSQL provides `lock_timeout`: SET lock_timeout = '5s'; 
ALTER TABLE users ADD COLUMN phone_number VARCHAR(30); 

If the required lock cannot be acquired within the configured period, the statement fails instead of waiting indefinitely.

This allows deployment systems to detect the problem and retry under appropriate conditions.

Using statement timeouts
A statement timeout can also limit how long a database operation is allowed to execute.

SET statement_timeout = '60s'; 

The appropriate values depend on the migration, environment, and operational requirements.

Timeouts are safeguards, not substitutes for understanding the migration's behaviour.

Creating indexes safely
Indexes can improve query performance, but creating them on heavily used tables requires care.

PostgreSQL supports concurrent index creation:

CREATE INDEX CONCURRENTLY idx_users_email ON users(email); 

This allows normal inserts, updates, and deletes to continue during much of the index-building process.

However, CREATE INDEX CONCURRENTLY cannot run inside a transaction block.

It also has additional operational considerations, including longer execution time and the possibility of an invalid index after a failed operation.

Migration frameworks must account for this rather than assuming every migration can run inside one transaction.

6. Safely Migrating Large Datasets

One of the biggest mistakes in database engineering is assuming that an operation tested against a small dataset will behave identically against production data.

Consider:

UPDATE users SET account_status = 'active' WHERE account_status IS NULL; 

This may work perfectly with a few hundred records.

But imagine millions of records.

A large update can produce significant write activity, generate substantial WAL, hold locks for an extended period, and increase replication lag.

A better approach may be to process records in batches.

Batch-based migration
A simplified PostgreSQL approach uses a common table expression:

WITH batch AS ( SELECT id FROM users WHERE account_status IS NULL ORDER BY id LIMIT 1000 FOR UPDATE SKIP LOCKED ) UPDATE users AS u SET account_status = 'active' FROM batch WHERE u.id = batch.id; 

This updates a limited number of records in one execution.

A migration worker can repeat the operation until no eligible records remain.

SKIP LOCKED can be useful when multiple workers process independent batches, but it is not necessary for every migration.

The implementation must also account for:

  • Retry behaviour
  • Transaction boundaries
  • Progress tracking
  • Failed batches
  • Records updated concurrently by the application
  • Final verification

Make migrations resumable
A robust large-data migration should be able to continue after interruption.

For example, the migration process can track:

  • Total records expected
  • Records processed
  • Records remaining
  • Last successfully processed identifier
  • Failed records
  • Completion status

It should not assume that the entire operation will always finish in one execution.

Resumability is especially valuable for large production systems.

7. Transactions, Atomicity, and Migration Design

Transactions are central to relational database reliability.

They allow related operations to succeed or fail together.

For example:

BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT; 

If the operation fails before commit, the transaction can be rolled back.

Database migrations can also benefit from transactions.

However, not every migration operation is transaction-compatible.

PostgreSQL operations such as CREATE INDEX CONCURRENTLY cannot run inside an ordinary transaction block.

Large data transformations may also be better handled through multiple bounded transactions rather than one enormous transaction.

The correct strategy depends on the operation.

A useful rule is to understand whether a migration requires atomic execution, incremental progress, or a specialised deployment procedure.

8. Versioning and Migration History

A database should have a clear migration history.

Without one, developers may struggle to understand which schema changes have already been applied.

Most migration frameworks maintain a migration tracking table.

For example:

001_create_users 002_add_account_status 003_create_projects 004_add_project_owner 005_create_project_indexes 

Each migration represents a known change to the database.

The migration system records which changes have been executed.

Popular migration tools include:

  • Alembic for SQLAlchemy applications
  • Django migrations
  • Flyway
  • Liquibase
  • Prisma Migrate
  • Drizzle ORM migrations

The correct choice depends on the framework and architecture.

Why migration history matters

Migration history provides several benefits:

  1. Reproducible database environments
  2. Consistent development and production schemas
  3. Traceability of structural changes
  4. Better deployment automation
  5. Easier investigation of schema-related failures

Migration files should generally be treated as version-controlled application assets.

Once a migration has been applied to shared or production environments, modifying its historical contents can create inconsistencies.

A new corrective migration is usually safer than silently rewriting an already-applied migration.

9. Rollbacks Are Not Always Simple

Many migration frameworks provide rollback commands.

However, reversing a database change is not necessarily equivalent to restoring the original state.

Consider:

ALTER TABLE users DROP COLUMN middle_name; 

Once the column is removed, its previous data is no longer available in that table.

A rollback migration can recreate the column, but it cannot automatically reconstruct the deleted values.

This is why rollback strategies must be designed before deployment.

Possible recovery approaches include:

  • Restoring from verified backups
  • Point-in-time recovery
  • Reversing compatible schema changes
  • Re-running corrective migrations
  • Restoring data from an external source
  • Rolling back the application while keeping the expanded schema

For destructive migrations, backups and recovery procedures must be verified rather than merely assumed to exist.

Roll-forward versus rollback
In some production systems, rolling forward with a corrective migration is safer than attempting to reverse a partially completed data transformation.

For example, if a new field has been populated incorrectly, a corrective migration may repair the affected records without reverting the entire application.

The choice depends on the failure, available recovery points, and data integrity requirements.

10. Testing Database Migrations Before Deployment

Migration testing should be part of the normal development process.

At minimum, test against representative versions of the database schema and realistic data.

Important test categories include:

Fresh database tests
Can a new database be created by applying the complete migration history?

Upgrade tests
Can an existing database move from the previous schema to the new schema?

Data integrity tests
Do existing records retain their expected relationships and values?

Compatibility tests
Can the old application version continue operating during the compatibility window?

Performance tests
Does the migration introduce excessive execution time, locking, or resource consumption?

Failure tests
What happens if a migration fails halfway through?

A migration that works only when everything goes perfectly is not sufficiently reliable for critical production environments.

Example verification query
Suppose a migration introduces a new required account status.

You can inspect remaining records:

SELECT COUNT(*) AS total_users, COUNT(*) FILTER ( WHERE account_status IS NULL ) AS missing_status FROM users; 

The migration should not proceed to a stage requiring non-null values until the missing records have been appropriately handled.

11. Integrating Database Migrations into CI/CD

A database migration should be part of the application's deployment process.

A simplified pipeline might look like this:

Developer submits changes | v Automated code review | v Unit and integration tests | v Migration validation | v Staging deployment | v Migration verification | v Production deployment | v Post-deployment checks | v Monitoring and reporting 

This sequence is illustrative. The exact order of application and schema deployment depends on the migration strategy.

For an expand-and-contract migration, for example, the schema expansion may need to happen before deploying the application version that uses the new columns.

The contraction happens later, after old application versions and dependencies have stopped using the old structure.

Deployment responsibilities
A reliable deployment system should:

  • Prevent conflicting migration executions
  • Record migration results
  • Capture execution logs
  • Apply appropriate timeouts
  • Support safe retries
  • Detect partial failures
  • Verify the resulting schema
  • Alert operators when migration steps fail

Database migrations should not run independently on every application instance during startup unless the migration system explicitly provides safe coordination.

Otherwise, multiple instances may attempt to execute the same migration simultaneously.

A dedicated migration job or controlled deployment step is often easier to reason about.

12. Observability: Knowing Whether a Migration Is Healthy

Executing a migration successfully is only part of the process.

Developers also need visibility into what happens afterward.

Useful metrics include:

  • Migration execution duration
  • Lock acquisition time
  • Database CPU and memory utilisation
  • Active database connections
  • Query latency
  • Replication lag
  • Number of records processed
  • Number of failed records
  • Application error rates

PostgreSQL provides useful diagnostic views.

For example:

SELECT pid, state, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state <> 'idle'; 

This can help identify active queries and sessions waiting on database resources.

For production operations, developers should also investigate long-running transactions, blocked sessions, and replication health where relevant.

Monitoring allows teams to distinguish between a migration that technically completed and one that has introduced operational problems.

13. Common Database Migration Mistakes

Several mistakes repeatedly create problems in production environments.

  1. Removing old columns too early - The application may still depend on them.

  2. Running enormous data updates without planning - Large transactions can create unnecessary database pressure.

  3. Ignoring lock behaviour - A migration can block application traffic even if the SQL statement looks simple.

  4. Assuming rollback restores deleted data - Recreating a column does not restore the values that were removed.

  5. Skipping realistic testing - A migration tested against ten records may behave differently against ten million.

  6. Running migrations independently on every server - Uncoordinated execution can create deployment conflicts.

  7. Forgetting background workers - Workers may continue using old queries after the main application has been updated.

  8. Failing to verify data transformations - A migration can finish successfully while producing incorrect values.

  9. Combining expansion and destruction in one deployment - This removes the opportunity for a controlled compatibility transition.

  10. Treating database migrations as an afterthought - Schema changes are part of application engineering and deserve the same planning and review as source-code changes.

14. A Practical Production Migration Checklist

Before deploying a database migration, consider the following.

Architecture
Does the new schema support the intended feature?
Have all dependent application components been identified?
Is the change backward-compatible?
Are background workers and external integrations considered?

Data
Is existing data preserved?
Are transformation rules clearly defined?
Can the migration resume after interruption?
Are validation queries available?

Performance
Could the migration acquire restrictive locks?
Is an index operation suitable for concurrent creation?
Are timeouts configured appropriately?
Has the migration been tested against realistic data volumes?

Security
Are permissions appropriate?
Are sensitive values protected?
Are backup and recovery procedures available?

Deployment
Is migration execution coordinated?
Is the deployment sequence correct?
Are application versions compatible during the transition?
Is there a recovery plan?

Verification
Have migration tests passed?
Has the resulting schema been inspected?
Are data integrity checks available?
Will application and database health be monitored afterward?

This checklist is not a replacement for database-specific documentation, but it provides a useful foundation for engineering reviews.

15. Final Thoughts: Database Migrations Are Part of Software Reliability

As software applications grow, their databases must evolve.

New features introduce new relationships, changing business requirements demand different data structures, and increasing workloads create new performance requirements.

The challenge is not simply making these changes.

It is making them without unnecessarily disrupting the systems that already depend on the database.

That requires a combination of architectural planning, backward compatibility, transaction management, data validation, deployment coordination, testing, and observability.

The expand-and-contract pattern offers a practical way to manage many structural changes. Batch processing helps make large data transformations more manageable. Migration versioning improves consistency, while monitoring and recovery planning help protect production systems.

There is no universal migration strategy that works for every database operation.

The correct approach depends on the database engine, schema, data volume, application architecture, deployment model, and availability requirements.

But one principle remains important:

A successful database migration is not merely one that executes without errors. It is one that preserves data integrity, maintains application compatibility, and leaves the system in a healthy, verifiable state.

Database engineering is not just about storing information. It is about ensuring that information remains reliable as software evolves.

###About Me

I'm Ezenwa Christian Chimeremeze, a developer, technical writer, and founder of Geminio OS, an AI-first full-stack development ecosystem. I'm passionate about software engineering, database architecture, artificial intelligence, developer documentation, and automation. I enjoy exploring complex technical challenges, building practical solutions, and documenting what I learn through real-world development experiences.

You can connect with me:

- GitHub: https://github.com/ezenwachristianchimeremeze
- Portfolio: https://ezenwa-christian.netlify.app
- Medium: https://ezenwachristianchimeremeze.medium.com/
LinkedIn:
https://www.linkedin.com/in/ezenwa-christian-chimeremeze-b77115440