Database migrations are easy to underestimate.
Adding one column to a development database may take a few seconds. Performing the same change against a production PostgreSQL database with millions of rows, active transactions, replicas, and multiple application versions is a very different problem.
At scale, a migration is not simply a SQL script.
It is a production change that can affect:
Application availability
Query performance
Lock contention
Deployment order
Replication
Rollbacks
Data integrity
Recovery procedures
PostgreSQL Migrator 1.0 provides a structured way to approach PostgreSQL schema and database migration workflows. The important part is not only how to execute a migration, but how to plan, validate, monitor, and recover from it safely.
This article presents a practical migration strategy for production PostgreSQL environments and explains how to think about migrations before running them against a large database.
Why PostgreSQL Migrations Become Difficult at Scale
A migration usually looks simple from the application's perspective:
Application
|
v
Database Schema Change
In production, the real environment is closer to:
Production
|
+---------------+---------------+
| | |
Application A Application B Background Jobs
| | |
+---------------+---------------+
|
PostgreSQL
|
+----------+----------+
| |
Primary Replica
A schema change therefore has to coexist with active application traffic.
For example, adding a column may be safe:
ALTER TABLE customers
ADD COLUMN loyalty_score INTEGER;
But the migration still needs to be evaluated for:
The SQL statement is only one part of the migration.
What Is PostgreSQL Migrator 1.0?
PostgreSQL Migrator 1.0 can be used as part of a controlled database migration workflow.
The general idea is:
Migration Definition
|
v
Validation
|
v
Execution
|
v
Verification
|
v
Application Deployment
A migration system should provide developers with a repeatable way to manage changes instead of relying on manually executed SQL commands.
A typical project might contain:
migrations/
├── 001_create_customers.sql
├── 002_add_email.sql
├── 003_create_orders.sql
└── 004_add_order_status.sql
Each migration represents a known change to the database.
The migration history allows the environment to determine which changes have already been applied.
Why Migration Versioning Matters
Without versioning, teams can lose track of database state.
Consider three environments:
Development
Migration 001
Migration 002
Migration 003
Staging
Migration 001
Migration 002
Migration 003
Production
Migration 001
Migration 002
The application may expect Migration 003 to exist in production, but the database does not yet contain it.
A migration history table can provide a record of applied changes.
Conceptually:
migration_id | applied_at
-------------+-------------------
001 | ...
002 | ...
When a new deployment starts, the migration tool can determine which migrations remain pending.
The Expand-and-Contract Pattern
One of the safest strategies for production database changes is the expand-and-contract pattern.
Instead of making a breaking schema change immediately, introduce the new structure first.
Suppose an application currently uses:
users.name
and the new design requires:
users.first_name
users.last_name
A dangerous migration would be:
Drop name
Add first_name
Add last_name
Deploy application
An older application may still expect name.
A safer process is:
Phase 1
Add new columns
|
v
Phase 2
Deploy application that understands both
|
v
Phase 3
Backfill data
|
v
Phase 4
Switch reads/writes
|
v
Phase 5
Remove old column later
This allows different application versions to coexist during deployment.
Example: Adding a Column Safely
Suppose we need to add:
ALTER TABLE orders
ADD COLUMN fulfillment_status TEXT;
The migration itself may be straightforward.
But the deployment should be planned:
Database
|
+-- Add fulfillment_status
|
v
Application
|
+-- Start writing new field
|
v
Backfill existing records
|
v
Application
|
+-- Start relying on new field
The application should not depend on the new column before the migration has completed successfully.
Adding Nullable Columns
Adding a nullable column is often easier to introduce gradually:
ALTER TABLE orders
ADD COLUMN fulfillment_status TEXT NULL;
The application can initially continue using the old schema.
Later, application code can begin populating the new column.
For example:
UPDATE orders
SET fulfillment_status = 'pending'
WHERE fulfillment_status IS NULL;
For a large table, however, a single massive update can create significant load.
That brings us to backfilling.
Large-Table Backfills
Suppose a table contains:
50 million rows
A migration such as:
UPDATE orders
SET fulfillment_status = 'pending'
WHERE fulfillment_status IS NULL;
may process an enormous amount of data in one operation.
Potential consequences include:
A safer strategy is often batching.
For example:
UPDATE orders
SET fulfillment_status = 'pending'
WHERE id >= 1
AND id < 10000
AND fulfillment_status IS NULL;
Then process the next range:
UPDATE orders
SET fulfillment_status = 'pending'
WHERE id >= 10000
AND id < 20000
AND fulfillment_status IS NULL;
The exact batch size should be determined through testing and production characteristics.
Monitor the Backfill
A migration process should expose enough information to understand its progress.
For example:
Backfill Progress
Processed: 12,400,000
Remaining: 37,600,000
Rate: 8,200 rows/sec
Errors: 0
Monitoring allows operators to determine whether the migration is progressing normally.
It also provides an opportunity to pause the operation if database load becomes excessive.
PostgreSQL Locks and Migration Risk
One of the most important migration considerations is locking.
Some DDL operations can require locks that interfere with concurrent database activity.
For example:
ALTER TABLE accounts
ADD COLUMN risk_score INTEGER;
The impact depends on the specific PostgreSQL operation and version.
Before running a migration, determine:
What lock mode is required?
How long can the operation hold the lock?
What happens if another transaction already holds a conflicting lock?
Can the migration wait indefinitely?
Will application queries be blocked?
A migration that takes milliseconds in an empty test database can behave differently when the production table is under continuous traffic.
Lock Timeouts
Production migration workflows should consider lock timeout behavior.
For example:
SET lock_timeout = '5s';
This prevents a migration from waiting indefinitely for a conflicting lock.
If the lock cannot be acquired within the configured period, PostgreSQL aborts the statement.
This is often preferable to allowing a deployment process to sit blocked while application traffic continues to accumulate behind a database lock.
The appropriate timeout depends on the migration and application requirements.
Statement Timeouts
A migration can also use a statement timeout:
SET statement_timeout = '60s';
This provides a boundary for long-running operations.
However, do not blindly apply the same timeout to every migration.
A schema operation that should finish within seconds may deserve a short timeout, while a controlled data migration may require a different execution strategy.
Transactions and Migration Safety
Many PostgreSQL migrations can be executed transactionally.
For example:
BEGIN;
ALTER TABLE customers
ADD COLUMN risk_score INTEGER;
COMMIT;
If an error occurs before the commit, the transaction can be rolled back.
This is useful for changes that can safely participate in a transaction.
However, not every PostgreSQL operation has identical transactional behavior or operational characteristics.
A migration tool should therefore know whether the migration can be safely wrapped in a transaction.
Do not assume that every migration can use:
BEGIN → Execute Everything → COMMIT
Idempotent Migration Design
Migration scripts should generally be designed so that accidental repeated execution does not produce destructive results.
For example:
ALTER TABLE customers
ADD COLUMN IF NOT EXISTS loyalty_score INTEGER;
The IF NOT EXISTS clause can make certain operations safer.
However, idempotency should not be used as an excuse to hide migration failures.
A migration system should still maintain explicit migration history.
The goal is:
Known Migration
|
v
Applied Once
|
v
Recorded
rather than repeatedly guessing whether the database is already in the expected state.
Migration History
A migration tool typically maintains metadata describing executed migrations.
A conceptual table might look like:
CREATE TABLE schema_migrations (
version BIGINT PRIMARY KEY,
applied_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
When Migration 004 executes successfully:
version | applied_at
--------+-------------------
001 | ...
002 | ...
003 | ...
004 | ...
This makes database state easier to understand.
The exact schema and metadata management depend on the migration tool being used.
Detecting Schema Drift
Schema drift occurs when the actual database structure differs from what the migration history or application expects.
For example:
Expected:
customers
id
name
email
loyalty_score
Actual:
customers
id
name
email
The migration history may indicate that the change was applied even though the schema does not match the expected state.
This is why mature migration workflows should include verification.
Useful checks include:
Pre-Deployment Validation
Before executing a production migration, validate it in an environment that resembles production.
A useful workflow is:
Migration
|
v
Local Test
|
v
CI Test
|
v
Staging Database
|
v
Production-Like Dataset
|
v
Production
Testing against an empty database is not enough.
If production contains 100 million rows, testing against 1,000 rows tells you very little about long-running operations.
Use Production-Size Test Data
A production-like migration test should approximate:
Table size
Index size
Row distribution
Concurrent traffic
Database hardware
Replication configuration
For example:
Production
orders = 80 million rows
Staging test
orders = 80 million representative rows
The goal is not necessarily to copy production data.
Synthetic data can be used to reproduce the scale while protecting sensitive information.
Migration Execution Workflow
A practical production migration can follow this sequence:
1. Review migration
|
v
2. Validate SQL
|
v
3. Test on production-like data
|
v
4. Check locks and dependencies
|
v
5. Create rollback/recovery plan
|
v
6. Execute
|
v
7. Verify schema
|
v
8. Verify application
|
v
9. Monitor
This is much safer than placing a SQL script directly into a deployment pipeline without validation.
Migration Rollbacks
A migration should have a recovery strategy.
Suppose a migration adds:
ALTER TABLE customers
ADD COLUMN risk_score INTEGER;
Removing the column may be straightforward:
ALTER TABLE customers
DROP COLUMN risk_score;
But rollback is not always this simple.
Consider a migration that:
Adds a new column.
Copies data into it.
Changes application behavior.
Deletes an old column.
Once data has been transformed or deleted, reversing the schema change may not restore the original state.
Therefore, migration rollback should be considered separately from application rollback.
Application Rollback
|
v
Previous Application Version
Database Migration
|
v
Recovery Strategy
The two operations do not automatically reverse each other.
Forward Fixes Can Be Safer Than Rollbacks
In some production scenarios, applying a corrective migration is safer than trying to reverse the original migration.
For example:
Migration 005
|
v
Problem discovered
|
v
Migration 006
|
v
Correct schema
This preserves migration history and can be easier to reason about than attempting to reconstruct the exact previous database state.
The appropriate strategy depends on the nature of the failure.
Zero-Downtime Migration Strategy
For applications that cannot tolerate downtime, migrations should be designed around compatibility.
A common sequence is:
Old Application
|
v
Expand Database
|
v
Deploy Compatible Application
|
v
Backfill Data
|
v
Switch Application Behavior
|
v
Contract Database
The application should remain compatible with both schema versions during the transition.
For example:
Version A
reads old_column
Version B
reads old_column + new_column
Version C
reads new_column
Only after Version C is fully deployed should the old column be considered for removal.
Database Migrations in CI/CD
Migration execution can be integrated into a deployment pipeline:
Pull Request
|
v
Unit Tests
|
v
Migration Validation
|
v
Integration Tests
|
v
Staging
|
v
Approval
|
v
Production Migration
|
v
Application Deployment
The exact ordering depends on whether the migration is backward compatible.
For expand-and-contract migrations, database expansion often happens before the application begins using the new schema.
Avoid Running Migrations From Every Application Instance
A common deployment mistake is allowing every application instance to execute migrations at startup.
Imagine:
Pod 1 → Migration
Pod 2 → Migration
Pod 3 → Migration
Pod 4 → Migration
This can create race conditions and unnecessary database contention.
A better architecture is:
Deployment
|
+---- Migration Job
|
v
Database
|
v
Application Instances
The migration executes as a controlled deployment step.
The exact orchestration mechanism depends on the deployment platform.
Migration Observability
A production migration should be observable.
Track:
Migration ID
Start Time
End Time
Duration
Rows Processed
Errors
Lock Wait Time
Replication Lag
Database CPU
Database I/O
For example:
Migration: 005
Status: Completed
Duration: 42 sec
Rows: 2,400,000
Lock wait: 120 ms
Errors: 0
This information becomes valuable when reviewing deployment performance or troubleshooting an incident.
Monitor Replication Lag
If the PostgreSQL environment uses replicas, large data modifications can generate substantial WAL activity.
A backfill might look like:
Primary
|
| Large UPDATE
v
WAL generation increases
|
v
Replica
|
v
Replication lag increases
Monitor replica lag during large migrations.
If replicas fall significantly behind, the migration may need to be slowed down or paused.
The correct threshold depends on the application's read requirements and replication architecture.
Migration Performance Tuning
Migration performance should be treated as a workload problem.
Useful variables include:
Batch size
Transaction size
Index maintenance
Lock duration
Database CPU
I/O throughput
Network traffic
WAL generation
Replica capacity
For example:
Small Batch
|
+-- Lower transaction impact
+-- Lower lock duration
+-- More individual operations
Large Batch
|
+-- Higher throughput
+-- Larger transactions
+-- Greater resource impact
The optimal batch size should be measured rather than guessed.
Common Migration Mistakes
Running Unreviewed SQL in Production
Every production migration should go through code review and testing.
Testing Only Against an Empty Database
Large tables can behave very differently.
Test with production-like data volumes.
Ignoring Locks
A migration can block application traffic if it waits for or holds a conflicting lock.
Performing Huge Backfills in One Transaction
Large updates can create excessive WAL, replication lag, and resource pressure.
Mixing Breaking Schema Changes With Application Deployment
Deploying an incompatible application and schema change simultaneously makes rollback harder.
Running Migrations From Every Application Instance
Use a controlled migration process rather than allowing every replica to execute the same migration.
Assuming Application Rollback Reverts the Database
Database changes often require their own recovery strategy.
Removing Old Schema Too Quickly
Keep backward compatibility until all application versions depending on the old structure have been retired.
Best Practices
When planning PostgreSQL migrations at scale:
Version every migration.
Review migration SQL before production execution.
Test migrations with production-like data volumes.
Understand the lock requirements of every DDL operation.
Use appropriate lock and statement timeouts.
Prefer expand-and-contract for breaking schema changes.
Perform large backfills in controlled batches.
Monitor database CPU, I/O, WAL, and replication lag.
Keep migration execution separate from application startup.
Verify the schema after migration completion.
Maintain an explicit recovery strategy.
Do not assume a database rollback is equivalent to an application rollback.
Keep old schema elements until dependent application versions are retired.
Record migration execution and failure information for auditing and troubleshooting.
A Production Migration Checklist
Before executing a migration against production, verify:
[ ] Migration has been reviewed
[ ] Migration version is unique
[ ] SQL tested locally
[ ] SQL tested in CI
[ ] Tested with production-like data
[ ] Lock behavior understood
[ ] Timeout configured
[ ] Backfill strategy defined
[ ] Rollback/recovery strategy defined
[ ] Replica impact evaluated
[ ] Monitoring configured
[ ] Application compatibility confirmed
[ ] Deployment order confirmed
[ ] Migration owner identified
[ ] Post-migration validation prepared
This checklist can become part of the team's standard change-management process.
Conclusion
PostgreSQL migrations become significantly more complex as databases grow and applications move toward continuous deployment.
The SQL itself is often the easiest part.
The difficult part is ensuring that the change can be introduced without unexpectedly blocking traffic, overwhelming the database, creating replication lag, breaking older application versions, or leaving the team without a reliable recovery path.
PostgreSQL Migrator 1.0 fits into a broader migration discipline built around:
Version
↓
Validate
↓
Test
↓
Plan
↓
Execute
↓
Verify
↓
Monitor
For small databases, a migration may take only a few seconds and require little planning.
For large production databases, the same migration needs to be treated as an operational change with explicit capacity, locking, compatibility, observability, and recovery considerations.
The safest PostgreSQL migration is not necessarily the fastest migration. It is the one that changes the database predictably while keeping the application available, the data consistent, and the recovery path clear.
Join the conversation! Your thoughts help the community grow.