The requirement sounded simple.
We needed to add a new column to a production table.
The table had more than a billion rows.
The business couldn’t afford hours of downtime.
And we couldn’t simply stop the application, modify the database, migrate the data, and start everything again.
That approach might work on a small system.
At billion-row scale, it becomes an operational risk.
The database was serving live transactions.
Multiple application instances were reading and writing the table.
Replication was running.
Background jobs depended on the same data.
And customers expected the system to remain available.
The migration therefore wasn’t just a database task.
It was a production architecture problem.
The question wasn’t:
“How do we add a column?”
It was:
“How do we change the schema while the system is still running?”
That changed the entire approach.
1. The Change We Needed to Make
The table contained transaction records.
Conceptually:
transactions
id
customer_id
amount
status
created_at
We needed to introduce a new attribute:
settlement_reference
The requirement itself was straightforward.
The operational environment wasn’t.
The table contained more than a billion records.
Millions of transactions were being read and written.
Several application versions might exist during deployment.
And the database was replicated to downstream systems.
A naive migration could create:
Application
↓
Database Lock
↓
Migration
↓
Backfill
↓
Release
↓
Resume
That was unacceptable.
We needed the application to remain available throughout the process.
2. The Old Way
The traditional approach would have been something like:
1. Stop application traffic
2. Lock the table
3. Alter the schema
4. Migrate existing data
5. Restart application
It looks simple.
The problem is that production databases aren’t empty spreadsheets.
A billion-row table means that seemingly simple operations can involve enormous amounts of work.
Depending on the database engine and exact operation, a schema change can involve:
- Locks
- Disk I/O
- Table rewrites
- Index changes
- Replication effects
- Transaction log growth
- Long-running transactions
- Increased storage latency
- Connection pressure
- Replication lag
The exact behavior varies by database technology and version.
But the architectural concern remains the same:
A large schema operation can compete with the workload that keeps the business running.
3. The Real Problem: Old and New Code Must Coexist
The biggest realization was that the database wasn’t the only thing changing.
The application was changing too.
During a normal deployment, there can be a period where both old and new application versions are running:
Load Balancer
│
┌───────────┼───────────┐
▼ ▼ ▼
Old App New App Old App
│ │ │
└───────────┼───────────┘
▼
Database
The old application doesn’t know about the new column.
The new application does.
That means the schema needs to support both versions during the transition.
This is the fundamental idea behind the expand/contract pattern.
Don’t make one enormous incompatible change.
Make a sequence of smaller, compatible changes.
4. The Expand/Contract Pattern
Our migration became:
Expand
↓
Add compatible schema
↓
Deploy new application
↓
Dual-write
↓
Backfill existing data
↓
Validate
↓
Switch reads
↓
Stop using old representation
↓
Contract
↓
Remove old schema
Each step had a purpose.
More importantly:
Every intermediate state had to be safe.
That was the architectural requirement.
5. Step One: Expand
The first step was to introduce the new schema without requiring the application to use it.
Conceptually:
Before:
transactions
├── id
├── customer_id
├── amount
├── status
└── created_at
After:
transactions
├── id
├── customer_id
├── amount
├── status
├── created_at
└── settlement_reference
At this point, the existing application could continue operating.
The new column existed.
But the old application didn’t depend on it.
This was important.
We had changed the database without immediately changing the application’s behavior.
That gave us a safe intermediate state.
6. Why the First Step Had to Be Backward Compatible
Imagine we did this instead:
Deploy new schema
↓
Old application breaks
↓
Production incident
That’s exactly what we wanted to avoid.
The expanded schema needed to remain compatible with the existing application.
That meant thinking about things such as:
- Whether the new column could initially be nullable
- Whether defaults would create expensive work
- Whether indexes were required
- Whether constraints could be added safely
- Whether the database operation itself would acquire problematic locks
The exact migration technique depends on the database engine.
There isn’t one universal SQL statement that is safe for every database.
The architectural principle is more general:
Introduce compatibility before introducing dependency.
7. Step Two: Deploy the New Application
Once the schema could safely support the new code, we deployed an application version capable of understanding the new field.
But we didn’t immediately switch every request to the new behavior.
We used a staged rollout.
For example:
New application behavior
1% traffic
↓
5%
↓
25%
↓
50%
↓
100%
At each stage we monitored the system.
We looked at:
- Error rates
- Latency
- Database load
- Replication lag
- Transaction failures
- Data consistency
- Application logs
- Business metrics
The migration wasn’t treated as a one-time database command.
It was treated as a production deployment.
8. Step Three: Dual-Write
The next stage was to make the application write the new representation while remaining compatible with the old one.
Conceptually:
New Transaction
│
├──────────► Old representation
│
└──────────► New representation
For example:
Old field / representation
+
New settlement_reference
This allowed new transactions to begin populating the new structure.
But dual-write introduces its own risk.
Two writes create two opportunities for inconsistency.
For example:
Write old value
↓
Success
Write new value
↓
Failure
Now the representations disagree.
That means dual-write must have an explicit consistency strategy.
Depending on the system, both writes might be performed within the same database transaction, or another mechanism may be appropriate.
The important point is:
Dual-write is not automatically safe simply because both writes happen in application code.
You have to define what happens when one side succeeds and the other doesn’t.
9. Step Four: Backfill the Billion Rows
New transactions were now populating the new column.
But we still had more than a billion existing records.
We couldn’t simply execute:
UPDATE transactions
SET settlement_reference = ...
and hope for the best.
A massive operation like that could generate enormous database work.
Instead, we used controlled batches.
Conceptually:
Rows 1 - 10,000
↓
Backfill
↓
Validate
Rows 10,001 - 20,000
↓
Backfill
↓
Validate
...
The batch size and strategy depended on the database and workload.
The important part was that the backfill could be controlled.
We wanted the ability to:
- Pause it
- Resume it
- Slow it down
- Speed it up
- Monitor its impact
- Stop it without taking the application offline
The backfill became a workload we could operate rather than one enormous database event.
10. The Backfill Had to Respect Production Traffic
A billion-row backfill doesn’t happen in isolation.
The database was still serving customers.
So we monitored its impact continuously.
For example:
Normal traffic
+
Backfill workload
↓
Database
↓
Observe:
CPU
I/O
Locks
Latency
Replication lag
Storage
If production latency increased, the backfill rate could be reduced.
If replication lag increased beyond an acceptable threshold, we could pause.
This is an important operational principle:
A migration should be a controllable workload, not an uncontrolled event.
11. Step Five: Validate Before Switching
After the backfill, we didn’t immediately change all reads.
We validated.
We wanted to know:
Does the new data exist?
Is it complete?
Is it correct?
Does it match the expected old representation?
Are there unexpected nulls?
Are there mismatches?
Are downstream systems healthy?
For critical financial data, validation becomes particularly important.
A successful migration isn’t:
“The SQL command finished.”
A successful migration is:
“The data is correct and the application can safely depend on the new representation.”
12. Step Six: Switch Reads
Once confidence was high, we changed the application to read from the new representation.
We used a feature flag to control the transition.
Conceptually:
Feature Flag
│
┌──────────┴──────────┐
▼ ▼
Old Read New Read
│ │
└──────────┬──────────┘
▼
Response
This gave us a valuable capability:
We could change behavior without redeploying the entire system.
If the new path behaved unexpectedly, we could disable the feature and return to the old read path while investigating.
That made the migration reversible at the application-behavior level.
13. Rollout Was Gradual
We didn’t necessarily switch every customer at once.
We could stage the rollout:
1%
↓
5%
↓
10%
↓
25%
↓
50%
↓
100%
At each stage we checked:
- Read latency
- Error rate
- Data mismatches
- Database performance
- Customer-facing behavior
- Business metrics
This gave us multiple opportunities to stop.
A staged migration turns:
One enormous decision
into:
Many smaller decisions
That’s much easier to operate safely.
14. Step Seven: Stop the Old Write Path
Once the new representation had been validated and reads had switched, we could eventually stop writing the old representation.
But we didn’t immediately delete anything.
First:
New representation
↓
100% reads
↓
100% writes
Then we observed the system.
We wanted to make sure no hidden process was still depending on the old structure.
That could include:
- Background jobs
- Reporting queries
- ETL pipelines
- Administrative tools
- Data exports
- Scheduled scripts
- Older application versions
This was one of the reasons the migration couldn’t be treated as a single deployment.
Dependencies often exist outside the main application.
15. Step Eight: Contract
Only after the old representation was no longer needed did we begin the final cleanup.
This is the contract phase.
Conceptually:
Expand
↓
Use both
↓
Migrate
↓
Switch
↓
Stop old dependency
↓
Contract
The old schema could now be removed.
Importantly, this was a separate change.
That gave us another safety boundary.
We weren’t combining:
Schema change
+
Application change
+
Data migration
+
Old data deletion
into one enormous operation.
We separated them.
16. Why We Didn’t Treat Rollback as “Just Restore the Database”
One of the biggest mistakes in database migrations is assuming rollback means:
“Restore the database backup.”
That may be technically possible.
But in a live production system, restoring a large database can be operationally expensive and can introduce its own data-loss or consistency challenges depending on the recovery point and architecture.
Instead, we designed the migration so that application behavior could be rolled back independently.
For example:
New application behavior
↓
Problem detected
↓
Disable feature flag
↓
Return to old read path
The expanded schema could remain in place.
That was okay.
We didn’t need to immediately reverse every database change.
This is one of the major advantages of expand/contract:
Rollback doesn’t have to mean undoing the entire migration.
Sometimes the safest rollback is simply to stop depending on the new schema while leaving the compatible schema in place.
17. The Feature Flag Became a Safety Mechanism
We normally think of feature flags as tools for product releases.
They can also be extremely useful during infrastructure changes.
For a migration, a feature flag can control:
New writes
New reads
New validation
New processing path
That gives operators control over behavior while the migration is happening.
The architecture becomes:
Database
│
▼
Application
│
▼
Feature Flag
│
├── Old behavior
│
└── New behavior
This doesn’t make a migration risk-free.
It gives you a mechanism for limiting exposure.
18. The Hidden Risk: Replication
One of the things we watched closely was replication.
A large backfill can generate substantial database activity.
That activity can affect replicas.
Conceptually:
Primary
│
├──── Replication ────► Replica 1
│
└──── Replication ────► Replica 2
If the primary generates changes faster than replicas can process them, replication lag can increase.
That can affect:
- Read freshness
- Failover behavior
- Reporting
- Downstream consumers
- Recovery assumptions
So database migration monitoring can’t stop at:
“The primary database looks healthy.”
You need to understand the whole data path.
19. The Hidden Risk: Long-Running Transactions
Another concern was transaction duration.
A huge migration inside one transaction can create operational problems.
Depending on the database engine, long-running transactions can affect:
- Lock retention
- Transaction logs
- Vacuum/cleanup behavior
- Storage utilization
- Replication
- Recovery time
That is another reason controlled batching can be useful.
Instead of:
1 huge transaction
we can often use:
Small transaction
Small transaction
Small transaction
...
with monitoring between batches.
Again, the exact technique depends on the database technology.
The architectural principle is to bound the operational impact of each step.
20. The Financial System Made This More Serious
This wasn’t just a large table.
It contained financial transaction data.
That changed our risk model.
A bad migration could cause more than downtime.
It could cause:
- Incorrect transaction state
- Missing records
- Incorrect reporting
- Reconciliation failures
- Duplicate processing
- Audit problems
- Customer-facing inconsistencies
Availability matters.
But financial correctness matters even more.
We therefore treated the migration as a controlled production change rather than a routine database maintenance task.
21. The Migration Became an Architecture Exercise
At first, the requirement sounded like:
“Add one column.”
But the real change involved:
Database
+
Application
+
Deployment
+
Data migration
+
Feature flags
+
Observability
+
Rollback
+
Reconciliation
That is an architectural change.
The database and application are not independent systems.
They form a coupled runtime.
Changing one changes the assumptions of the other.
That’s why schema evolution deserves the same design discipline as API evolution.
22. What We Did Not Do
We didn’t lock the billion-row table for hours.
We didn’t stop the entire application and hope the migration completed before customers noticed.
We didn’t run one enormous backfill transaction.
We didn’t immediately delete the old schema after switching reads.
We didn’t assume a successful migration command meant the migration was successful.
And we didn’t assume that a database rollback was always the safest recovery strategy.
Instead, we created compatibility first.
Then migrated.
Then validated.
Then switched.
Then cleaned up.
23. The Pattern We Use Now
The migration pattern became:
EXPAND
│
▼
Add compatible schema
│
▼
Deploy compatible code
│
▼
Dual-write
│
▼
Backfill existing data
│
▼
Validate
│
▼
Switch reads
│
▼
Stop old dependency
│
▼
CONTRACT
│
▼
Remove old schema
Each stage has a different purpose.
Each stage can be monitored.
Each stage can be stopped.
And most importantly:
The system remains operational throughout the migration.
24. When to Use Expand/Contract
This pattern becomes particularly valuable when:
- Tables are very large
- Systems require high availability
- Multiple application versions coexist
- Database changes affect live traffic
- Deployments are distributed
- Data must be backfilled
- Rollback needs to be safe
- Financial correctness matters
It isn’t the only migration strategy.
Some databases provide specialized online schema-change capabilities.
Some changes are inherently cheap.
Some maintenance windows are perfectly reasonable.
The important point is to understand the operational characteristics of the specific change rather than assuming every ALTER TABLE behaves the same way.
25. The Questions I Ask Before a Production Migration
Before changing a production schema, I ask:
Can the old application run safely against the new schema?
Then:
Can the new application run safely while the old version still exists?
Then:
What happens if the backfill stops halfway?
Then:
How do we detect data mismatches?
Then:
What happens if replication falls behind?
Then:
Can we disable the new behavior immediately?
Then:
What is our rollback strategy?
And finally:
How do we know the old schema is no longer being used?
If those questions don’t have clear answers, the migration isn’t ready.
26. The Bigger Architectural Lesson
A schema isn’t just a collection of columns.
It is a contract between the application and the data layer.
Changing that contract can affect:
- Application code
- Queries
- Services
- Background jobs
- Reports
- Integrations
- Data pipelines
- Operational tooling
That means schema evolution is architecture.
And architecture changes need planning.
The safest migration isn’t necessarily the fastest migration.
It’s the migration where every intermediate state is:
Compatible. Observable. Controllable. Recoverable.
Final Thought
The database didn’t care that it was 2AM.
It didn’t care that customers were waiting.
It didn’t care that the business needed the system online.
A billion-row table was still a billion-row table.
The only thing we could control was how we changed it.
We could treat the migration as a single dangerous operation:
Stop
↓
Change
↓
Hope
↓
Restart
Or we could treat it as a sequence of controlled architectural changes:
Expand
↓
Deploy
↓
Backfill
↓
Validate
↓
Switch
↓
Contract
The second approach requires more planning.
But it turns a potentially catastrophic production event into a series of manageable steps.
That is the real lesson.
Schema changes are architecture changes. Plan them like deployments.
And when the database contains billions of production records, “we’ll just migrate it during maintenance” isn’t a strategy.
It’s a risk.
If your migration requires downtime, your migration is wrong. Not hard—wrong.