Database migrations are easy to underestimate.

Adding a column to a development database may take a few seconds, but applying the same change to a production PostgreSQL database with millions of rows, active connections, background jobs, and multiple application versions can be a very different problem.

A migration can change table structures, indexes, constraints, data, permissions, or database behavior. If it is not planned carefully, a seemingly small schema change can cause application errors, long-running locks, deployment delays, or even downtime.

PostgreSQL Migrator 1.0 provides a useful starting point for thinking about migration work as a controlled engineering process rather than simply a collection of SQL statements.

The important question is not only:

“How do I change the schema?”

It is:

“How do I change the schema safely while the application is still running?”

This article walks through a production-oriented PostgreSQL migration strategy, including migration planning, compatibility, locking, data changes, rollback design, deployment sequencing, testing, and operational monitoring.

What Is a PostgreSQL Database Migration?

A database migration is a controlled change to the structure or contents of a database.

Typical migrations include:

A simple migration might look like this:

ALTER TABLE customers
ADD COLUMN preferred_language TEXT;

The SQL is straightforward.

The production question is whether that statement can be executed safely while the application is reading from and writing to the customers table.

Why Production Migrations Are Different

A development database usually has:

Production may have:

This means migration safety depends on both the SQL operation and the environment in which it runs.

For example:

ALTER TABLE orders
ADD COLUMN processing_status TEXT NOT NULL;

The statement may require additional work if existing rows do not have a valid value for the new column.

A safer approach may be to introduce the column first:

ALTER TABLE orders
ADD COLUMN processing_status TEXT;

Then populate existing records:

UPDATE orders
SET processing_status = 'pending'
WHERE processing_status IS NULL;

Finally, once the application and data are ready, introduce the constraint.

The migration becomes a sequence of smaller, controlled changes.

The Expand-and-Contract Migration Pattern

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

Instead of changing the database and application simultaneously, introduce compatibility first.

The general sequence is:

Old application
      |
      v
Expand database
      |
      v
Deploy compatible application
      |
      v
Migrate existing data
      |
      v
Switch application behavior
      |
      v
Contract old schema

This is particularly useful for changes such as column renames.

Suppose the existing column is:

full_name

and the new application wants:

display_name

Instead of immediately renaming the column, add the new column:

ALTER TABLE customers
ADD COLUMN display_name TEXT;

The application can temporarily write both:

full_name     <- existing behavior
display_name  <- new behavior

After all application instances understand display_name, existing data can be migrated and the old column can eventually be removed.

This approach makes rolling deployments much safer.

Design Migrations for Backward Compatibility

During a deployment, different application versions may briefly coexist.

For example:

Application v1
Application v1
Application v2
Application v2

If v2 immediately removes something that v1 still expects, the deployment can break.

A migration should therefore consider at least three states:

  1. Before deployment.

  2. During deployment.

  3. After deployment.

For example, adding a nullable column is often easier to deploy safely than adding a mandatory column that existing application versions do not understand.

The migration should support both old and new application versions for the transition period.

Check the Existing Schema Before Migrating

Never assume the production database exactly matches your development environment.

Before executing a migration, inspect:

For example:

SELECT
    schemaname,
    tablename,
    tableowner
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema');

For table size:

SELECT
    pg_size_pretty(pg_total_relation_size('orders')) AS total_size;

This information helps determine whether a migration is likely to be lightweight or operationally expensive.

Understand PostgreSQL Locking

Lock behavior is one of the most important migration concerns.

An ALTER TABLE operation may require a table lock depending on the specific change.

The problem is not necessarily that the migration takes a long time.

The bigger problem can be that the migration waits for another transaction while holding or waiting for locks that affect application traffic.

A useful diagnostic query is:

SELECT
    pid,
    usename,
    state,
    wait_event_type,
    wait_event,
    query,
    query_start
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_start;

This helps identify active sessions that may be involved in migration delays.

Before running a large migration, understand which operations can block application traffic and how long they may take.

Avoid Large Transactions for Data Backfills

A common mistake is attempting to update millions of rows in a single transaction:

UPDATE orders
SET normalized_status = LOWER(status);

For a very large table, this can create substantial transaction and locking pressure.

A batch-based approach is often safer.

For example:

UPDATE orders
SET normalized_status = LOWER(status)
WHERE id > 0
  AND id <= 10000;

The exact batching strategy depends on the table structure and workload.

A production migration may process data incrementally:

10,000 rows
      |
      v
Commit
      |
      v
10,000 rows
      |
      v
Commit
      |
      v
Continue

This makes progress easier to monitor and reduces the impact of an individual transaction.

Add Indexes Carefully

Indexes can dramatically improve query performance, but creating an index on a large production table requires planning.

A standard index creation can affect concurrent activity depending on the operation.

For suitable cases, PostgreSQL provides concurrent index creation:

CREATE INDEX CONCURRENTLY idx_orders_customer_id
ON orders(customer_id);

The important distinction is that CREATE INDEX CONCURRENTLY has different transactional requirements and operational behavior from ordinary index creation.

It should therefore be tested separately rather than automatically replacing every CREATE INDEX.

Before creating an index, check whether a similar index already exists.

Duplicate indexes increase storage requirements and write overhead without necessarily providing additional value.

Validate Index Usage After Migration

Creating an index is not the same as proving that PostgreSQL uses it.

Use query plans to verify important queries:

EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 1001;

For deeper testing:

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 1001;

EXPLAIN ANALYZE executes the query, so it should be used carefully with production workloads.

The objective is to confirm whether the new index actually improves the query pattern it was designed to support.

Separate Schema Changes From Data Changes

A useful migration design separates structural changes from large data transformations.

For example:

Migration 1
    Add column

Migration 2
    Deploy application support

Migration 3
    Backfill data

Migration 4
    Validate data

Migration 5
    Add final constraint

Migration 6
    Remove obsolete column

This is generally easier to troubleshoot than one large migration that performs every operation simultaneously.

If the backfill fails, you know which stage failed.

If the constraint cannot be added, you can investigate data quality without rolling back unrelated schema changes.

Add Constraints in the Right Order

Suppose a new column must eventually be NOT NULL.

A safer transition can be:

Step 1: Add the column

ALTER TABLE customers
ADD COLUMN account_status TEXT;

Step 2: Populate existing data

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

Step 3: Update the application

Make the application write a valid value for every new record.

Step 4: Validate the data

SELECT COUNT(*)
FROM customers
WHERE account_status IS NULL;

Step 5: Add the constraint

Once the application and existing records are ready:

ALTER TABLE customers
ALTER COLUMN account_status SET NOT NULL;

The exact production strategy should be adjusted for table size and PostgreSQL behavior, but the underlying principle is important: establish data correctness before enforcing the final constraint.

Use Migration Versioning

Production migrations should be ordered and versioned.

A migration directory might look like:

migrations/
├── 001_create_customers.sql
├── 002_add_account_status.sql
├── 003_create_customer_index.sql
├── 004_backfill_account_status.sql
└── 005_add_account_status_constraint.sql

The numbering creates a predictable execution order.

A migration system should also track which migrations have already been applied.

For example:

migration_version
-----------------
1
2
3

This prevents a deployment from accidentally executing the same schema change repeatedly.

Make Migrations Idempotent Where Appropriate

Some operations can safely use conditional statements.

For example:

CREATE TABLE IF NOT EXISTS audit_events (
    id BIGSERIAL PRIMARY KEY,
    event_type TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

However, IF NOT EXISTS should not be used as a blanket solution.

If a table already exists but has an incorrect schema, PostgreSQL will not magically reconcile the difference.

Migration logic should still verify that the database is in the expected state.

Test Migrations With Production-Like Data

A migration that works against 10,000 test records does not automatically work against 100 million production records.

A useful migration test environment should approximate production in areas such as:

For large data migrations, measure:

Rows processed
Rows remaining
Processing rate
Transaction duration
Lock wait time
Database CPU
Database I/O
Replication lag
Application latency

This gives the operations team information that a simple “migration succeeded” result does not provide.

Plan for Rollback

Not every migration can be rolled back with a simple DOWN script.

For example:

DROP COLUMN display_name;

may be technically reversible, but any data written only to that column after deployment could be lost.

This is why rollback planning should distinguish between:

Sometimes the safest strategy is not to immediately reverse the database change.

Instead:

Application rollback
        |
        v
Database remains backward-compatible
        |
        v
Investigate
        |
        v
Correct forward

This is one reason expand-and-contract migrations are valuable.

Monitor Replication During Large Migrations

If your PostgreSQL architecture uses replicas, large migrations and data backfills can affect replication behavior.

A migration that generates substantial WAL activity can increase replication lag.

Monitor the database while the migration is running.

For example:

SELECT
    pid,
    application_name,
    client_addr,
    state,
    sync_state,
    write_lag,
    flush_lag,
    replay_lag
FROM pg_stat_replication;

The exact monitoring strategy depends on the PostgreSQL deployment architecture, but replication should be part of the migration plan whenever replicas serve application traffic.

Use Timeouts to Avoid Waiting Forever

Production migrations should not blindly wait indefinitely for locks.

A session-level statement timeout can provide an operational safety mechanism:

SET statement_timeout = '60s';

Similarly, a lock timeout can limit how long the migration waits for a lock:

SET lock_timeout = '5s';

These settings should be selected based on the application workload and migration requirements.

The goal is to fail predictably rather than allowing an unexpected lock wait to remain unnoticed.

Keep Application Deployments and Migrations Coordinated

A common deployment sequence is:

1. Deploy database expansion
2. Deploy application code
3. Backfill data
4. Switch application behavior
5. Remove obsolete database objects

Another deployment might require:

1. Deploy backward-compatible application
2. Expand database
3. Backfill data
4. Enable new application behavior
5. Contract database

The correct sequence depends on the change.

The important point is that database migrations and application deployments should be treated as one compatibility problem.

Common Migration Mistakes

Changing and Removing a Column Immediately

Renaming or deleting a column in the same deployment as an application change can break older application instances.

Running a Huge Backfill in One Transaction

Large transactions can increase database pressure and make failures more expensive.

Ignoring Existing Locks

A migration can appear simple but remain blocked because another transaction is holding a conflicting lock.

Creating Duplicate Indexes

Duplicate indexes consume storage and add maintenance overhead.

Testing Only on Small Databases

Migration behavior can change dramatically as table size increases.

Assuming Every Migration Is Reversible

Data transformations may not have a safe automatic rollback.

Mixing Schema, Data, and Cleanup Operations

Large all-in-one migrations are harder to troubleshoot and coordinate.

Forgetting Replication

A migration may succeed on the primary while causing unacceptable replica lag.

A Production Migration Checklist

Before running a migration, verify:

[ ] Migration tested on production-like data
[ ] Current schema verified
[ ] Table size checked
[ ] Indexes reviewed
[ ] Lock behavior understood
[ ] Long-running transactions checked
[ ] Application compatibility verified
[ ] Backfill strategy defined
[ ] Timeout strategy configured
[ ] Replication impact considered
[ ] Monitoring enabled
[ ] Rollback or recovery strategy documented
[ ] Migration owner identified
[ ] Deployment window agreed upon

This checklist turns migration execution into a repeatable operational process.

A Safer Migration Example

Suppose an application currently stores:

customers.name

and the new application requires:

customers.first_name
customers.last_name

Instead of immediately removing name, use a staged migration.

Phase 1: Expand

ALTER TABLE customers
ADD COLUMN first_name TEXT,
ADD COLUMN last_name TEXT;

Phase 2: Deploy Compatible Application

The application can continue reading name while beginning to populate the new fields.

Phase 3: Backfill

UPDATE customers
SET
    first_name = split_part(name, ' ', 1),
    last_name = NULLIF(
        substring(name from position(' ' in name) + 1),
        ''
    )
WHERE first_name IS NULL;

The actual transformation logic should be designed around the application's real data quality. Names are a simple example and should not be treated as universally separable into two fields.

Phase 4: Validate

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

Phase 5: Switch Reads

Once the new application is confident in the new columns, it can stop depending on name.

Phase 6: Contract

Only after all dependent application versions have been removed should the old column be considered for deletion:

ALTER TABLE customers
DROP COLUMN name;

This sequence is slower than a single migration but substantially easier to operate safely.

Advantages and Disadvantages of a Staged Migration Strategy

Approach

Advantages

Disadvantages

Single large migration

Simple migration file

Higher operational risk

Expand-and-contract

Safer deployments and rollback options

Requires more steps

Batch backfill

Better control over large datasets

Takes longer

Immediate schema enforcement

Fast structural completion

Can break older application versions

Separate data migration

Easier monitoring and troubleshooting

Requires additional coordination

Conclusion

PostgreSQL migrations become difficult when database changes interact with real application traffic.

The SQL itself is often the easiest part.

The harder questions are whether existing application versions remain compatible, how long locks may be held, how much data must be transformed, what happens to replicas, how the migration will be monitored, and what recovery looks like if something goes wrong.

A production-friendly PostgreSQL migration strategy therefore favors controlled, incremental changes.

Add the new structure first. Deploy compatible application code. Backfill data in manageable stages. Validate the result. Enforce constraints only when the data and application are ready. Remove obsolete structures only after the old application behavior has disappeared.

Whether you use PostgreSQL Migrator 1.0 or another migration framework, the tool should support this engineering discipline rather than replace it.

A safe migration is not simply one that finishes successfully.

It is one that can be executed, observed, recovered from, and understood without putting the entire application at unnecessary risk.