Production schema changes are not completed by running a migration and deploying matching code. In a continuously deployed system, old and new application versions overlap. Requests may reach either version, background workers can lag behind, and a rollback may reintroduce code built for the previous schema.
A safe migration must therefore support a compatibility window in which multiple application versions can read and write the same database. The expand-and-contract pattern creates that window: add the new structure first, migrate behavior and data incrementally, and remove the old structure only after it is no longer needed.
This approach takes more releases than an immediate rename or type change, but it turns a tightly coupled deployment into a sequence of reversible steps.
The core invariant: every release must work with the current schema
Zero downtime does not mean that every database operation is non-blocking. It means the service remains available and correct throughout the change.
During a migration, account for all database consumers:
- Web services and API instances
- Background workers and scheduled jobs
- Event consumers that may process delayed messages
- Administrative scripts and data pipelines
- Rollback versions retained by the deployment platform
The compatibility window must be at least as long as the maximum lifetime of any old consumer, including the time needed to detect a problem and roll back.
A useful rule is:
Add before use, migrate before switching, and stop using before removing.
Avoid combining additive and destructive changes in one release. A deployment that both starts using a new column and removes the old one cannot safely coexist with its predecessor.
The expand-and-contract lifecycle
A typical migration has five phases:
flowchart LR
A[Expand schema] --> B[Deploy compatibility release]
B --> C[Backfill existing data]
C --> D[Switch reads and writes]
D --> E[Contract old schema]
1. Expand the schema
Add new columns, tables, indexes, or constraints without changing existing behavior. New fields should normally begin as nullable or have a safe default so that old application versions can continue inserting rows.
2. Deploy a compatibility release
Deploy code that understands both representations. Depending on the migration, it may write both forms while continuing to read the old form.
Wait until this release has reached every relevant process. A rolling deployment is not complete merely because the API fleet is updated; workers, jobs, and separately deployed services matter too.
3. Backfill historical data
Populate the new representation in bounded, observable batches. The backfill should be idempotent and safe to pause or restart.
4. Switch application behavior
Move reads to the new representation, often behind a feature flag. Continue maintaining the old representation while rollback remains possible.
5. Contract the schema
After the rollback window closes and no consumer depends on the old representation, remove old writes, compatibility logic, and finally the obsolete schema.
Worked example: safely renaming a column
Suppose a profiles table contains name, and the application wants the clearer name display_name.
A direct rename is unsafe:
ALTER TABLE profiles RENAME COLUMN name TO display_name;
As soon as this statement runs, old instances querying name fail. Deploying application code first is equally unsafe because new instances would query a column that does not yet exist.
Treat the rename as adding a new column, migrating to it, and later dropping the old column.
Step 1: add the new column
ALTER TABLE profiles
ADD COLUMN display_name text;
Leaving the column nullable allows old versions to continue creating profiles without supplying it.
Even additive DDL requires operational care. Some database engines and versions can add a nullable column as a metadata-only operation, while others may rewrite the table or take stronger locks. Test the exact statement against a representative database, configure a short lock timeout where supported, and monitor lock waits during execution.
Step 2: deploy a bridge release
The bridge release continues reading name but writes both columns in one transaction:
function updateProfile(id, newName):
begin transaction
update profiles
set name = newName,
display_name = newName
where id = id
commit
The real implementation should use parameterized SQL; the pseudocode emphasizes the behavior rather than a specific client library.
Writing both values in one database transaction prevents partial updates. However, application-level dual writes do not make an old instance write the new column. During the rolling deployment, old instances still update only name. For that reason, keep name authoritative until every old writer has drained.
If old writers may remain active for a long time, a temporary database trigger can mirror writes reliably across application versions. Triggers add hidden behavior and operational complexity, so they should be documented, tested, monitored, and removed after the transition. For most services, completing the bridge rollout before backfilling is simpler.
Step 3: backfill in bounded batches
Once all writers run the bridge release, copy historical values. In PostgreSQL, a worker could repeatedly execute a batch like this:
WITH batch AS (
SELECT id
FROM profiles
WHERE display_name IS NULL
ORDER BY id
LIMIT 5000
FOR UPDATE SKIP LOCKED
)
UPDATE profiles AS p
SET display_name = p.name
FROM batch
WHERE p.id = batch.id;
Commit after each batch and continue until no rows remain. SKIP LOCKED permits multiple workers without making them wait on the same rows, although one worker is often sufficient and easier on the database.
The IS NULL predicate makes the backfill restartable and avoids overwriting values already written by the application. For more complex transformations, record progress using a stable key or a dedicated migration table rather than relying on offsets, which become inefficient and unstable as rows change.
Backfill controls should include:
- A configurable batch size and delay between batches
- Statement and lock timeouts
- Retry handling for transient failures
- Progress counts and an estimated remaining row count
- Database CPU, replication lag, transaction duration, and lock monitoring
- A kill switch that pauses work without losing progress
Do not run an unbounded update over a large table during peak traffic. It can create long transactions, retain dead rows, expand transaction logs, increase replica lag, and compete with user requests.
Step 4: validate before switching reads
A completed job is not proof that the data is correct. Validate both completeness and equivalence:
SELECT count(*)
FROM profiles
WHERE display_name IS NULL;
SELECT count(*)
FROM profiles
WHERE display_name IS DISTINCT FROM name;
IS DISTINCT FROM is useful in PostgreSQL because it compares nullable values predictably. Other databases have equivalent null-safe comparison techniques.
For a semantic transformation, compare domain-specific invariants rather than requiring exact equality. Sample records, verify counts by partition or tenant, and inspect recent writes separately from old rows.
Step 5: switch reads to the new column
Deploy a release that reads display_name while continuing to write both columns:
function getProfile(id):
row = select display_name from profiles where id = id
return row.display_name
A feature flag can separate deployment from activation. Roll out new reads to internal traffic or a small percentage of requests, watch errors and mismatch metrics, and then increase exposure.
A fallback such as COALESCE(display_name, name) can improve resilience during backfill, but it does not detect stale non-null values. Validation and complete writer coverage remain necessary.
Step 6: enforce the invariant
Once the application uses display_name, the database can enforce that it is populated. On PostgreSQL, a staged check constraint avoids validating every historical row while initially adding the constraint:
ALTER TABLE profiles
ADD CONSTRAINT profiles_display_name_present
CHECK (display_name IS NOT NULL) NOT VALID;
ALTER TABLE profiles
VALIDATE CONSTRAINT profiles_display_name_present;
Constraint and online-validation capabilities differ by database. Confirm locking behavior for the engine and version in production. Converting the column itself to NOT NULL may require an additional operation; retaining a validated check constraint can also be acceptable when its semantics are sufficient.
Step 7: contract only after rollback is no longer required
First deploy code that stops writing and reading name. Confirm through query telemetry, code search, and consumer ownership records that no dependency remains. Then remove the column in a later change:
ALTER TABLE profiles
DROP COLUMN name;
Dropping the column ends compatibility with old binaries. Do it only after those binaries cannot be redeployed, delayed jobs have expired, and the rollback plan no longer depends on them.
Release compatibility is a design artifact
Write down which application release supports which schema phase. For the example above:
| Release | Reads | Writes | Safe schema |
|---|---|---|---|
| R1 | name |
name |
Old or expanded |
| R2 bridge | name |
Both | Expanded |
| R3 switch | display_name |
Both | Expanded and backfilled |
| R4 cleanup | display_name |
display_name |
Expanded or contracted |
This matrix clarifies rollback boundaries. R3 can roll back to R2 because both columns are maintained. After dropping name, rolling back to R2 is no longer possible without restoring schema and potentially reconstructing data.
Compatibility also applies to events. If a worker processes messages produced before the migration, it may encounter an older payload shape even when all running binaries are new. Schema evolution for messages and schema evolution for tables must be planned together.
Rollback planning: reverse behavior, not destructive DDL
Rollback should be designed before the first migration runs. Separate three concerns:
- Application rollback: Can the previous binary run against the current schema?
- Behavior rollback: Can a flag switch reads back to the old representation?
- Data recovery: If new writes are incorrect, how will affected rows be identified and repaired?
Additive schema changes usually do not need to be reversed during an incident. If a new release fails after adding a column, leave the unused column in place and roll back the application. Automatically dropping it during rollback creates additional risk and may destroy data written by the new version.
Backfills should also rarely be “rolled back” blindly. If the new column is unused, leaving copied data in place is harmless. If the transformation was wrong, stop the job, correct the logic, and rerun an idempotent repair with an auditable scope.
Before contraction, define explicit exit criteria:
- No old application or worker versions are running
- Rollback no longer requires the old schema
- Backfill and consistency checks pass
- New-path error rates and latency are healthy
- Replicas and downstream consumers have caught up
- Database backups and restore procedures meet recovery requirements
Common variations and their risks
Changing a column type
For a risky type change, add a new column with the target type, dual-write a validated conversion, backfill, switch reads, and remove the original column later. An in-place ALTER COLUMN TYPE may rewrite a large table or hold disruptive locks.
Reject or quarantine values that cannot be converted. Silent truncation is not a migration strategy.
Adding an index
Index creation can consume substantial I/O and block writes, depending on the database. Use the engine's online or concurrent index-building facility when appropriate. For example, PostgreSQL supports CREATE INDEX CONCURRENTLY, but it has transaction restrictions and can leave an invalid index after failure. Treat index construction as an independently monitored operation, not incidental startup work.
Splitting a table or moving data stores
Dual writes across two independent databases cannot be made atomic with a normal local transaction. Partial failure, retries, and ordering must be expected. Prefer a transactional outbox or change-data-capture pipeline: commit the source update and an event atomically, then project the change to the destination asynchronously.
Keep the source authoritative until lag is controlled, reconciliation passes, and the destination has demonstrated it can serve production reads.
Operational practices that make migrations routine
Keep schema migrations separate from application startup. Startup-time migrations allow multiple instances to race and can turn a database lock into a fleet-wide outage.
For each migration, maintain a small runbook containing:
- The compatibility matrix and deployment order
- Expected lock and table-rewrite behavior
- Backfill commands, rate limits, and stop conditions
- Validation queries and success thresholds
- Dashboards and alerts to watch
- Rollback actions for each phase
- Named owners for application, database, and downstream systems
Finally, rehearse high-risk migrations on production-like data. Row counts alone are insufficient; realistic indexes, row widths, traffic, replication, and long-running transactions determine operational behavior.
Conclusion
Zero-downtime schema evolution is primarily a compatibility problem. Expand the schema without invalidating old code, deploy a bridge release, backfill gradually, validate the result, switch behavior reversibly, and contract only after the rollback window closes.
The extra releases are deliberate safety boundaries. They keep old and new versions interoperable, make failures observable before they become destructive, and allow continuously deployed services to evolve their data model without making a single deployment carry all the risk.
