How Do You Change a Live Database Schema While Old and New Application Versions Are Both Running?

Changing a database schema in development often feels instantaneous:

change the model
→ run the migration
→ deploy the application
→ continue working

Production rarely behaves that way.

A new application version usually does not replace every old instance at once. During a rolling deployment, old and new binaries may serve traffic simultaneously. Background workers may restart later. Queued jobs may have been created by an older version. Read replicas can lag. A backfill may still be moving historical data while the new application has already started writing a different representation.

That creates a compatibility window in which the production system may look like this:

Application v1
      ↓
   Database
      ↑
Application v2

Both versions need the database to make sense at the same time.

Now imagine version 2 expects:

users.display_name

while version 1 still expects:

users.full_name

A migration such as:

ALTER TABLE users
RENAME COLUMN full_name TO display_name;

may be logically correct for the new application and operationally disastrous for the old one. If your orchestrator intentionally keeps old instances alive until the new deployment proves healthy, the schema migration has just undermined the deployment strategy that was supposed to reduce risk.

That is the important shift in perspective:

A production schema migration is not merely a database change. It is a compatibility protocol between application versions, persistent data, background workers, replicas, and deployment infrastructure.

I have worked with deployment pipelines that combine schema migrations, application releases, smoke tests, and multiple data stores. The part that matters most is not whether the migration command exits successfully. It is whether the system remains correct while the migration is incomplete.

That middle state is where zero-downtime schema evolution becomes interesting.

The Compatibility Window Is the Real Migration

Suppose a service runs ten application instances and begins rolling out version 2.

For a period of time, production may contain both versions:

This is normal and usually desirable. Rolling deployments provide gradual replacement, health verification, easier rollback, and less risk than replacing the entire fleet at once.

The consequence is that the database often needs to support:

old application
+
new application

simultaneously.

That means a schema change can be perfectly valid in its final form and still be unsafe to deploy directly.

The important question is not only:

Will v2 work against the new schema?

It is also:

Will v1 still work after the first migration?

Will v2 understand rows written by v1?

Will old workers understand data written by v2?

Can the application roll back while this schema exists?

Can a paused backfill resume safely tomorrow?

A production migration is therefore defined as much by its intermediate states as by its final state.

Destructive Changes Fail Earlier Than You Expect

Dropping a supposedly unused column is one of the simplest examples.

Suppose version 1 contains:

@Entity
public class PullRequest {

    @Column(name = "legacy_status")
    private String legacyStatus;
}

The team concludes that legacy_status is no longer used in business logic, so the migration removes it:

ALTER TABLE pull_requests
DROP COLUMN legacy_status;

The DDL succeeds.

Then an ORM-generated query still does something equivalent to:

SELECT
    id,
    title,
    legacy_status,
    created_at
FROM pull_requests
WHERE id = ?;

The application fails even though nobody intentionally wrote new code that reads the old field.

This is not merely theoretical. GitHub reported two incidents in August 2025 involving production migrations that dropped columns application code was believed not to use directly, while ORM behavior still referenced those columns in certain queries. The resulting failures affected several production features, and GitHub temporarily restricted drop-column operations while strengthening safeguards.

The important lesson is subtle:

“Our new code does not use the column” is not enough evidence that the column can be removed.

Before destructive cleanup, I want confidence that the old representation is no longer required by:

old application versions
ORM metadata
background workers
scheduled jobs
queued work
analytics and exports
replica consumers
administrative tools
rollback procedures

That is why destructive changes belong at the end of a migration.

Not the beginning.

Expand Before You Contract

A much safer general strategy is expand and contract.

Instead of forcing the system through one instantaneous transformation:

OLD ─────────────────► NEW

introduce an intermediate representation that both worlds can understand:

OLD
 ↓
EXPANDED
 ↓
MIGRATED
 ↓
NEW

Conceptually:

The principle is straightforward:

Expansion should usually be backward compatible. Contraction happens only after old dependencies are gone.

Consider a column rename from:

full_name

to:

display_name

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

ALTER TABLE users
ADD COLUMN display_name VARCHAR(255);

The database temporarily contains both:

id
email
full_name
display_name

Old code still understands:

full_name

and new code can begin understanding:

display_name

Nothing has been destroyed.

Make the schema additive

The first migration should ideally create a new possibility without immediately imposing a new requirement.

That is why I would not necessarily begin with:

ALTER TABLE users
ADD COLUMN display_name VARCHAR(255) NOT NULL;

Every existing row currently lacks that value, and older application versions do not know they are expected to populate it.

Instead:

ALTER TABLE users
ADD COLUMN display_name VARCHAR(255);

creates the new representation without invalidating historical data or old writers.

This staging matters for constraints too. Shopify's Database Migrations team has documented how seemingly simple NOT NULL changes on live MySQL tables can interact with defaults, uniqueness, strict SQL modes, existing rows, and online schema-change tooling in surprising ways.

The first migration should create possibility.

The stronger invariant can come later.

Teach new code to understand both representations

Version 2 can read the new field while falling back to the old one:

function getDisplayName(user: User): string {
  return user.displayName ?? user.fullName;
}

Conceptually:

if new representation exists:
    use it
else:
    use old representation

This gives the new application backward compatibility with historical rows.

That matters because the schema may be expanded long before every existing record has been migrated.

Write both representations during the transition

New application versions can temporarily keep both columns current:

await db.users.update({
  where: { id: userId },

  data: {
    fullName: input.name,
    displayName: input.name,
  },
});

During a mixed deployment:

v1 writes old field
v2 writes old + new fields

The transition looks like:

New data begins populating both representations naturally, but historical rows remain incomplete.

That is where the backfill begins.

Backfill historical data gradually

A naïve backfill might be:

UPDATE users
SET display_name = full_name
WHERE display_name IS NULL;

On a table containing 500 rows, that may be entirely reasonable.

On a table containing hundreds of millions of rows, it can produce long-running transactions, heavy write I/O, replication lag, WAL or binlog growth, lock pressure, cache churn, higher connection usage, and increased CPU demand.

A safer approach is to migrate bounded ranges:

UPDATE users
SET display_name = full_name
WHERE id > :last_id
  AND id <= :next_id
  AND display_name IS NULL;

The migration can then operate more like:

batch
→ measure
→ yield
→ batch
→ measure
→ yield

A simple worker might look conceptually like:

const BATCH_SIZE = 1000;

while (true) {
  const rows = await findNextBatch(BATCH_SIZE);

  if (rows.length === 0) {
    break;
  }

  await migrateBatch(rows);

  await sleep(100);
}

The exact sleep value should not become cargo-cult configuration.

The important property is:

Migration throughput should yield to production traffic.

Avoid OFFSET for large backfills

Naïve pagination can also become increasingly expensive:

SELECT *
FROM users
ORDER BY id
LIMIT 1000
OFFSET 10000000;

As the offset grows, the database may have to traverse increasing amounts of data simply to reach the next batch.

Keyset-style progress is usually a better fit when the schema and ordering permit it:

SELECT id
FROM users
WHERE id > :last_processed_id
ORDER BY id
LIMIT 1000;

Now:

last_processed_id

is also a natural checkpoint.

That makes the worker easier to pause, resume, and reason about after a process restart.

Make the backfill idempotent

Production migrations should assume interruption.

A backfill may stop because of:

process crash
deployment
operator pause
database failover
load-based throttling
maintenance

A migration that assumes it will execute exactly once from beginning to end is fragile.

Prefer operations that remain safe when repeated:

UPDATE users
SET display_name = full_name
WHERE id = :id
  AND display_name IS NULL;

If the row was already migrated, rerunning the step leaves it alone.

The safer mental model is:

I may stop anywhere
and continue later.

That property becomes extremely valuable once the migration lasts hours or days instead of seconds.

Backfills Are Production Traffic

A migration may be described organizationally as "background work."

The database does not care.

To the database, a backfill consumes the same kinds of finite resources as customer traffic:

connections
CPU
I/O
locks
buffer cache
replication bandwidth

GitHub reported a May 2026 outage in which a routine online schema migration against a large, heavily accessed table ran successfully for hours before normal production traffic increased toward a busier period. The combined load exhausted database connection capacity, increased query contention, and produced cascading timeouts across dependent features. GitHub's follow-up included lower-traffic migration windows, dynamic throttling, automated pauses based on database health, and improved migration-specific monitoring.

That is a much more useful model than:

migration is running

The better model is:

migration has a resource budget

Throttle against database health, not only time

A simple backfill might use:

1000 rows
sleep 100 ms
1000 rows
sleep 100 ms

That is already better than one enormous transaction.

But database load at 3

A more adaptive worker can subordinate itself to live production health:

while (hasMoreRows()) {
  const health = await databaseHealth();

  if (
    health.cpu > 75 ||
    health.connectionUsage > 70 ||
    health.replicationLag > MAX_REPLICATION_LAG
  ) {
    await sleep(5000);
    continue;
  }

  await migrateNextBatch();
}

The migration now behaves like a low-priority tenant of the production system.

Customer traffic wins.

You can take this further with automatic pause conditions around p99 query latency, connection utilization, replica lag, lock wait time, or write pressure.

The point is not that every migration needs a sophisticated control loop.

The point is that migration speed should be constrained by the health of the shared resource it is consuming.

Measure migration state, not merely worker health

A worker can report:

job completed successfully

while the migration itself remains incomplete or inconsistent.

I care more about state-oriented measurements such as:

rows_total
rows_migrated
rows_remaining
mismatch_count
backfill_rate
estimated_completion

alongside database health:

DB CPU
DB connection usage
replication lag
lock wait time
query latency
application error rate

For example:

Migration progress:       73%
Rows remaining:           28.4M
Mismatch count:           0
DB connections:           61%
Replica lag:              1.2s
p99 application query:    84ms

That tells me much more than:

migration process is alive

The job is an implementation mechanism.

The state of the data is the migration.

Validate Before the New Representation Becomes Authoritative

Suppose the backfill reports:

100%

I still would not immediately delete the fallback path.

First verify the state.

For the column example:

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

Expected:

0

Then compare representations:

SELECT COUNT(*)
FROM users
WHERE display_name IS DISTINCT FROM full_name;

Depending on the migration, validation can also include:

checksums
sampled record comparisons
shadow reads
old-vs-new application output comparisons
mismatch counters
domain-specific invariants

Stripe used this kind of staged approach during a large migration involving hundreds of millions of subscription objects: dual writing, historical backfills, comparison experiments, gradual read and write transitions, and delayed removal of the old representation.

That leads to another useful principle:

A completed backfill is not the same as a validated backfill.

Switch reads gradually

Once validation looks good, the new representation can become authoritative.

That still does not require an all-at-once cutover.

A feature flag can keep code deployment separate from behavior activation:

if (flags.readDisplayName) {
  return user.displayName;
}

return user.fullName;

Rollout might then proceed through:

1%
5%
25%
50%
100%

while monitoring:

errors
null rates
data mismatches
latency

The application already knows how to read the new schema, but production decides independently when that behavior becomes active.

That separation gives the migration another valuable control surface.

If the new read path produces unexpected results, the flag can often be disabled without rolling back the entire binary.

Stop writing the old representation

Once:

new reads are stable
+
backfill is complete
+
old application versions are gone

the current application can stop maintaining:

full_name

Now the system looks like:

The old representation is no longer active.

But I still would not necessarily drop it immediately.

Rollback may still depend on it.

Rollback Determines When Cleanup Is Safe

Application rollback sounds simple:

v2 broken
→ deploy v1

But version 1 may still require:

full_name

If the migration already dropped that column, the old binary is no longer a valid rollback target.

That gives us an important rule:

A deployment is not safely reversible if the schema no longer supports the version you intend to roll back to.

It is useful to think about the migration in two phases.

Reversible phase

old representation available
new representation available

Rollback is relatively straightforward because both versions still have the structures they require.

Irreversible phase

old representation removed

Now recovery may require:

forward repair

instead of:

redeploy old binary

Destructive cleanup therefore deserves a different review threshold from additive expansion.

I would contract the schema only when conditions such as these are true:

old application instances = 0
old workers = 0
old jobs that depend on the schema = 0
old code references = 0
rollback window expired
new data validated

Only then:

ALTER TABLE users
DROP COLUMN full_name;

The full migration becomes:

That may require several deployments.

That is not wasted ceremony.

The extra steps are buying compatibility and reversibility.

Online DDL Still Has Operational Cost

Column migrations are not the only changes that need this kind of thinking.

Consider an index:

CREATE INDEX idx_orders_customer
ON orders(customer_id);

Logically, this is an additive change.

Operationally, it can still interfere with production.

PostgreSQL documents that a normal CREATE INDEX can block writes while the index is being built. On a heavily used production table, that may be unacceptable.

A live system may instead use:

CREATE INDEX CONCURRENTLY idx_orders_customer
ON orders(customer_id);

This allows writes to continue during creation, but the tradeoff is more work, a longer build, and additional failure behavior.

The useful equation is:

less blocking
≠
free

An online migration still performs scans, consumes CPU and I/O, interacts with replicas, and competes with production workloads.

Constraints can be staged too

Suppose the desired final schema includes stronger validation.

PostgreSQL supports workflows in which constraint installation and validation can be separated. For example:

ALTER TABLE orders
ADD CONSTRAINT orders_customer_fk
FOREIGN KEY (customer_id)
REFERENCES customers(id)
NOT VALID;

Then later:

ALTER TABLE orders
VALIDATE CONSTRAINT orders_customer_fk;

The initial step can prevent new invalid data without immediately scanning the entire existing table. Historical validation happens separately.

That illustrates a broader migration principle:

When the database supports it, separate “prevent new invalid state” from “prove all historical state is already valid.”

Large schema changes become easier to operate when enforcement and historical verification do not have to occur in one enormous step.

ORMs and Background Workers Extend the Compatibility Window

A migration plan can look perfect from the perspective of the primary API and still be unsafe because the API is not the only schema consumer.

ORMs hide database assumptions

Consider:

@Entity
public class User {

    private UUID id;

    private String email;

    private String fullName;
}

Even after business logic stops calling:

user.getFullName();

the ORM may still include the field in generated selects, expect it during result hydration, or retain it in model metadata.

That is the class of problem exposed by GitHub's 2025 drop-column incidents.

Schema cleanup therefore requires more than:

grep -R "full_name"

Generated SQL is part of the compatibility surface too.

Workers may outlive the application deployment

Suppose every API instance is now on version 2.

That sounds like the compatibility window has ended.

But what about:

scheduled reports
Kafka consumers
SQS workers
cron jobs
data exports
admin tools
long-running batch processes

A queue may still contain work created by version 1. A delayed job may execute hours later. A worker may restart from an older deployment artifact. An export pipeline may access a read replica whose schema expectations differ from the API's.

The real compatibility window is therefore not:

until the application rollout finishes

It is:

until every code path capable of touching that schema understands the new world.

That can be significantly longer than the visible deployment.

Dual Writes Create a Temporary Consistency Problem

Dual writing sounds simple:

await writeOldModel(data);
await writeNewModel(data);

But suppose:

writeOldModel = success
writeNewModel = failure

The two representations have now diverged.

If both fields exist in the same transactional database, update them in one transaction where possible:

await db.transaction(async (tx) => {
  await tx.updateOldRepresentation(data);
  await tx.updateNewRepresentation(data);
});

That keeps the transitional representations aligned within one transactional boundary.

If the two models live in separate systems, the migration has become a distributed-write problem.

Now mechanisms such as these may become necessary:

transactional outbox
event replay
reconciliation
repair jobs
idempotent consumers

A migration can therefore temporarily increase the consistency burden of the system.

That is another reason to keep the dual-write period finite. Transitional complexity should exist long enough to make the migration safe, not become the permanent architecture by accident.

Treat the Migration as a State Machine, Not a File

Migration frameworks often encourage a mental model such as:

20260927_rename_full_name.sql

That naming is useful for versioning database changes.

It can also make a production migration feel more atomic than it actually is.

A more realistic process may be:

Migration A
→ expand schema

Deploy A
→ understand both representations

Deploy B
→ dual-write

Backfill
→ migrate historical state

Validation
→ prove equivalence

Behavior rollout
→ switch reads

Deploy C
→ stop old writes

Wait
→ close rollback window

Migration B
→ remove old schema

The migration is therefore better understood as a state machine.

Each state has different compatibility guarantees and different rollback options.

Thinking this way also changes review conversations. Instead of asking:

Does this migration SQL look safe?

you can ask:

What state are we entering?

Which binaries work in that state?

Which writers are allowed?

What can still be rolled back?

What proves it is safe to advance?

Those are much stronger operational questions.

Test Every Intermediate State That Production Can Reach

A migration test environment should not validate only:

new schema + new application

The risky combinations are the transitional ones:

old schema + new application

expanded schema + old application

expanded schema + new application

mixed old/new application fleet + expanded schema

new application + partially backfilled data

new schema + stale worker

The migration is safest when those intermediate states are intentional.

A useful rule is:

Every intermediate state that can exist in production should be considered a supported state during the migration.

For a larger change, I like making the compatibility contract explicit.

The dangerous combination is immediately visible:

v1 + final schema = incompatible

Therefore:

the final destructive migration cannot run
while v1 can still exist in production

This is much clearer than:

“Make sure the migrations run in the correct order.”

It also avoids hiding important compatibility information behind symbols or emoji. The table states the deployment contract directly.

Schema Migrations Are Distributed Deployments

It may sound strange to describe a database migration as a distributed-systems problem.

Look at the participants:

Those components do not change state simultaneously.

They observe the migration at different times. Some cache schema or ORM metadata. Some have delayed work. Replicas may lag. A worker may restart hours later. An old binary may briefly return during rollback.

The migration therefore has to tolerate temporal skew between components.

That is distributed-system behavior.

The schema is shared state.

Application versions are independent participants with different expectations about that state.

The migration protocol has to preserve compatibility while those participants move from one interpretation to another.

Once you see the problem that way, patterns such as expand-and-contract, compatibility windows, feature flags, dual writes, idempotent backfills, and delayed destruction stop looking overly cautious.

They become the coordination mechanisms required to change a shared contract without stopping the system.

The Schema Is an API

We are usually cautious when changing:

REST APIs
Kafka event schemas
public SDK interfaces

because some consumer may still depend on the old contract.

A production database schema deserves the same respect.

Its consumers may include:

your API
another service
an ORM
a background worker
an ETL pipeline
an analytics query
an admin tool
a read replica consumer

Dropping a column is therefore conceptually similar to removing a field from a public API.

Maybe every consumer has migrated.

Maybe one has not.

The database does not know the difference until that consumer makes a request and fails.

Treating schema evolution as API evolution leads naturally to better questions:

Who consumes this field?

Which versions are still deployed?

What compatibility do we promise during rollout?

How do we deprecate the old representation?

How do we know nobody depends on it anymore?

When does the breaking change become safe?

That mindset is more useful than thinking of a migration as a private implementation detail inside the database.

The Invariants I Want

For production schema evolution, I care about guarantees such as:

Every application version serving traffic
can use the active schema.

Old application instances must remain functional
when expansion begins.

New application versions must understand
historical data.

Backfills must be resumable and idempotent.

Migration work must not consume unlimited
production database capacity.

The new representation must be validated
before becoming authoritative.

Application rollback must remain possible
through the defined rollback window.

Destructive cleanup must wait until all old
readers and writers are gone.

Every production-reachable intermediate state
must have defined compatibility.

Migration health and migration progress must be
observable independently from application deployment.

Those are stronger guarantees than:

migration command exited 0

because a successful SQL statement says very little about whether the surrounding deployment remained safe.

The migration is successful only when the system moves from the old contract to the new one without losing compatibility, correctness, observability, or the rollback guarantees we intended to keep.

Final Thought

Database migrations become dangerous when we imagine them as instantaneous.

They are not.

A production migration has a beginning, a middle, and an end. During the middle, old code may still be running while new code has already arrived. Historical rows may still use the old representation while newer rows contain both. Backfills may be active. Replicas may lag. Workers may be processing older jobs. Rollback may still be necessary.

That intermediate state is where most of the engineering lives.

The safest migrations therefore do not force the entire system to jump directly from one world to another. They expand first, preserve compatibility, move data gradually, observe the impact, validate the new representation, shift behavior deliberately, wait through the rollback window, and only then remove the old world.

That can feel excessively cautious when the actual migration is only:

ALTER TABLE ...

But the SQL is not the system.

The system is every process that depends on the schema while that SQL is changing its contract.

So the real question is not:

“Will this migration execute successfully?”

It is:

“Can every version of the system remain correct while the migration is incomplete?”

If the answer is yes, you have something much more useful than a migration that works.

You have a migration that can be safely operated.

References

• GitHub — GitHub Availability Report: August 2025. Two production incidents were caused by dropped database columns that GitHub's ORM still referenced, demonstrating how destructive schema changes can break code even when developers believe the column is unused.

• GitHub — GitHub availability report: May 2026. A routine online schema migration on a large, heavily accessed table combined with normal traffic to saturate connection capacity and trigger cascading timeouts. GitHub's follow-up included dynamic migration throttling, load-aware circuit breakers, and improved migration monitoring.

• Stripe Engineering — Online migrations at scale. Stripe describes migrating hundreds of millions of subscription objects using staged dual writes, historical backfills, production comparison experiments, incremental read/write transitions, and delayed cleanup of the old representation.

• Shopify Engineering — Safely Adding NOT NULL Columns to Your Database Tables. Shopify's Database Migrations team explores backward compatibility and data-loss risks when adding constraints to live MySQL tables through online schema-change tooling.

• PostgreSQL Documentation — CREATE INDEX. Explains the operational difference between normal and concurrent index creation: standard index builds block writes, while concurrent builds allow them at the cost of additional work and complexity.

• PostgreSQL Documentation — ALTER TABLE. Documents staged constraint validation using NOT VALID and VALIDATE CONSTRAINT, allowing enforcement for new data to be separated from validation of existing rows.