Why does a blue green deployment break at the database?
A blue-green deployment duplicates the application, not the data. In a monolith you run two copies of the app, blue (current) and green (new), behind a router. Both point at one database. The router can move traffic in seconds. The schema cannot be swapped that way, and the rows written in the meantime cannot be swapped back.
Walk through the timeline of a single release:
- Blue only. The schema matches what blue expects.
- Both live. Green starts, runs its health checks, and takes some or all traffic. Blue is still running, often still serving requests or background jobs. Both versions read and write the same tables.
- Green only. Blue is idle but kept warm as the rollback target.
- Flip back to blue. Green misbehaves, and traffic returns to blue.
Step 4 is where things break. When blue resumes, it reads rows that green wrote. If green dropped a column blue selects, blue fails. If green wrote values into a new column blue ignores, or stored a format blue cannot parse, blue fails or silently corrupts data. The router did its job, and the rollback still failed.
So the unit of safe deployment is not the application version. It is the pair of versions that must coexist, together with the one schema they share. The traffic switch does not give you rollback safety. A schema that both versions can run against, before and after either one has written data, does.
+--------------------+
| Router / LB |
| moves traffic in s |
+--------------------+
| |
traffic % | | traffic %
v v
+----------------+ +----------------+
| Blue (v1) | | Green (v2) |
| current app | | new app |
+----------------+ +----------------+
| ^ | ^
write | | read write | | read
v | v |
+-------------------------------------+
| ONE shared database / schema |
| not duplicated, not swapped back; |
| rows written by v2 stay here |
+-------------------------------------+
Step 1 Blue only 2 Both live 3 Green only 4 Back to blue
Traffic v1 ---------> v1 + v2 ----> v2 ---------> v1
Schema [====== must run v1 AND v2, incl. rows v2 wrote ======]
^
v1 reads rows v2 wrote: safe only if the
schema still works for v1
Blue and green share one database, so the schema must support both versions from the overlap window through a flip back to blue, including rows green has already written.
How does expand contract migration work?
Expand-contract breaks one incompatible change into a sequence of compatible ones. Each step ships as its own release, so at every point the running version and the version you might roll back to can both work against the current schema.
- Expand. Add the new column, table or index without touching anything existing. New columns are nullable or have defaults, so the old code's inserts keep succeeding.
- Migrate. The application writes to both old and new structures. A backfill job copies historical rows in batches so it does not hold long locks.
- Switch reads. Once the backfill is verified, reads move to the new structure. Writes still go to both.
- Contract. Drop the old structure only when no running version, and no version you might roll back to, still reads or writes it.
Feature flags keep these steps separate from deploys. Dual-write, the read source and removal of the old path each get their own toggle. You can then turn on dual-write after the binary is already live, or move reads back to the old structure without redeploying. The schema release and the traffic switch stay independent events.
The new code must be backward compatible with old data, and the old code must be forward compatible with the expanded schema. Blue has to tolerate columns it has never seen, enum values it does not recognize, and nullable fields it leaves empty. In practice, that means:
- Use explicit column lists instead of
SELECT *. - Configure ORM mappings to ignore unknown columns.
- Handle unrecognized enum values with a defined fallback instead of an exception.
-- Renaming customers.full_name to display_name with expand-contract (PostgreSQL).
-- Each block below ships as its own release. Flags are flipped separately from deploys.
-- Release 1: EXPAND. Nullable, no default, so blue's INSERTs that omit it still succeed.
ALTER TABLE customers ADD COLUMN display_name text;
-- Release 2: MIGRATE. Green ships with dual-write code behind flag write_display_name.
-- Turn the flag on after the binary is live, then backfill in small batches
-- to keep locks short. Rerun until it reports UPDATE 0.
UPDATE customers
SET display_name = full_name
WHERE id IN (
SELECT id FROM customers
WHERE display_name IS NULL AND full_name IS NOT NULL
ORDER BY id
LIMIT 1000
);
-- Verify before switching reads. Expect 0.
SELECT count(*) FROM customers
WHERE display_name IS DISTINCT FROM full_name;
-- Release 3: SWITCH READS with flag read_display_name. Writes still go to both columns.
-- Both versions name their columns explicitly, so the added column never changes
-- the result shape that either version's row mapping expects.
-- blue: SELECT id, email, full_name FROM customers WHERE id = $1;
-- green: SELECT id, email, display_name FROM customers WHERE id = $1;
-- Release 4: CONTRACT. Run only when the dual-write flag is off and no running
-- or rollback-eligible version reads or writes full_name.
ALTER TABLE customers DROP COLUMN full_name;
A column rename split into four separately released steps (expand, batched backfill under dual-write, flag-controlled read switch, and contract), using explicit column lists so blue and green both tolerate the intermediate schema.
Worked example: renaming a column across every release
Take a concrete change: renaming customers.email to customers.contact_email. A direct RENAME COLUMN would break whichever version still references the old name. Instead, spread the change over four releases.
R1: Expand. Run ALTER TABLE customers ADD COLUMN contact_email VARCHAR(255) NULL before deploying. The new column is nullable, so R0 can ignore it. R1 writes to both columns and still reads email. After R1 is fully live, a separate backfill job copies email into contact_email in primary-key batches, for example 1,000 rows per batch with a short pause between them (illustrative assumption). It records the last processed key after each batch, so a failed run resumes from that point. Run the backfill only after every instance writes to both columns. Otherwise R0 could insert rows that the backfill has already passed.
R2: Flip reads. Once the backfill is verified, a toggle switches reads to contact_email. R2 still writes to both columns, so R1 remains a valid rollback target.
R3: Stop writing the old column. This is the point of no return for R1. Any row written after R3 deploys has a stale email, and R1 would read that stale value.
R4: Contract. Run ALTER TABLE customers DROP COLUMN email. After the drop, R2 can no longer run because it writes to email.
| Release | Schema state | Writes | Reads | Rollback target allowed |
|---|---|---|---|---|
| R1 | Both columns, backfill running | email + contact_email |
email |
R0 |
| R2 | Both columns, backfilled | email + contact_email |
contact_email |
R1 |
| R3 | Both columns | contact_email only |
contact_email |
R2 only |
| R4 | email dropped |
contact_email |
contact_email |
R3 only |
# R1 backfill: copy customers.email -> contact_email in primary-key batches.
# Start only after every instance runs R1 (dual writes), so no R0 instance
# can insert a row behind the cursor.
# Assumes a seeded checkpoint row:
# INSERT INTO backfill_progress (job, last_id) VALUES ('customers_contact_email', 0);
import time
import psycopg2
JOB = "customers_contact_email"
BATCH_SIZE = 1000 # illustrative assumption
PAUSE_S = 0.2 # illustrative assumption: pause between batches to spare the primary
conn = psycopg2.connect("dbname=app user=backfill host=db.internal")
# Resume from the last committed checkpoint instead of starting over.
with conn, conn.cursor() as cur:
cur.execute("SELECT last_id FROM backfill_progress WHERE job = %s", (JOB,))
last_id = cur.fetchone()[0]
while True:
# One transaction per batch: the copy and the checkpoint commit together,
# so a crash never skips rows and never advances past uncommitted work.
with conn, conn.cursor() as cur:
cur.execute(
"SELECT MAX(id) FROM (SELECT id FROM customers WHERE id > %s "
"ORDER BY id LIMIT %s) AS batch", (last_id, BATCH_SIZE))
upper = cur.fetchone()[0]
if upper is None:
break # no rows left past the cursor
# IS NULL keeps rows that R1 already dual-wrote untouched.
cur.execute(
"UPDATE customers SET contact_email = email "
"WHERE id > %s AND id <= %s AND contact_email IS NULL", (last_id, upper))
cur.execute("UPDATE backfill_progress SET last_id = %s WHERE job = %s", (upper, JOB))
last_id = upper
time.sleep(PAUSE_S)
conn.close()
This is a resumable R1 backfill that copies email into contact_email in primary-key batches, committing each batch together with its checkpoint so a failed run picks up where it stopped.
Which schema changes are backward compatible and which break the old version?
The test for every change is whether the version still serving traffic can read and write correctly against the schema after the change. The answer is often no, even for changes that look small.
| Change | Old version still works? | Why |
|---|---|---|
| Add nullable column | Yes | Old inserts omit it, and old reads ignore it |
| Add column with default | Yes | The database fills the value for old inserts. On some engines this rewrites the table, so check the cost |
| Add table | Yes | Old code never references it |
| Add index, built non-blocking | Yes, where the engine supports it | A blocking build stalls writes from both versions |
| Drop or rename column or table | No | Old queries name something that no longer exists |
Add NOT NULL without default |
No | Old inserts fail |
| Tighten constraint (unique, check, foreign key) | No | Writes the old code considers valid are rejected |
| Change type or semantics | No | Old code misparses or misinterprets values |
| Add enum value | No | Old code reads a value it has no branch for |
| Change serialized payload in a column | No | The old deserializer fails or silently drops fields |
| Change cache entry shape | No | Both versions read the same keys |
| Change queued job format | No | Jobs enqueued by one version are run by the other |
| Change stored procedure or trigger | No | It fires on writes from both versions |
Background workers deserve extra attention. They often drain long-running jobs before restarting, so they can run the old version well after web traffic has fully switched. Your compatibility window is set by the slowest consumer, not by the load balancer cutover.
Some changes cannot practically be made compatible. Reusing a column for a different meaning is one example. Switching primary keys from sequential integers to UUIDs is another, because foreign keys, URLs and caches all embed the old identity. Treat these as new columns or new tables with an explicit migration rather than as in-place edits.
How do you design for rollback safety?
Rollback safety is a property you design in. It has a defined window, and that window closes. Pick it explicitly, for example "we can return to blue for 48 hours after cutover." Then make the schema honor it. The rule that follows: the contract step for a change ships no earlier than the release after the window closes. While blue might take traffic again, every column, constraint and table it reads must still exist and still hold data it understands.
Schema shape is the easy half. Data that green writes during the overlap is the hard half. Suppose green writes only to full_name while blue reads first_name and last_name. A flip back serves rows with empty or stale fields. Down migrations rarely fix this. They were written before production traffic existed, they usually drop the new column, and dropping it discards whatever green recorded. Roll back the code, not the schema. Accept that rollback is never free: after green writes in a new shape, or after contract runs, flipping back loses information or fails outright.
Dual-writing narrows the gap but adds its own failure mode: divergence. One write path can throw after the other succeeds. A batch job, admin endpoint or raw SQL path can also skip the new column. Catch this with reconciliation queries that compare old and new representations and count mismatches. These are not cheap. Someone has to write a check per migration. On large tables the checks need batching or a replica to avoid load. They also need alert thresholds and an owner for every mismatch they find.
How do you run migrations separately from the application deploy?
Run migrations as their own pipeline stage, after build and before the traffic switch. Gate the green deploy on that stage succeeding; if it fails, the pipeline stops and blue keeps serving. Make every migration idempotent: guard each step with existence checks and record applied versions, so a retry after a partial failure converges instead of erroring.
Ordering protects against out-of-step failures in both directions. A migration can assume code that has not shipped yet. For example, it might add a NOT NULL column with no default, and blue's inserts start failing. Code can also assume a migration that failed, such as green querying a column that was never created. The pipeline gate prevents the second case. Only review prevents the first.
Use lock-aware DDL. Set a short lock timeout so a statement that cannot acquire its lock fails quickly rather than queueing behind a long transaction and blocking every query behind it. Avoid operations that rewrite a hot table.
Backfills on large, hot tables are the expensive part. A single UPDATE holds locks, generates a burst of write volume that drives replication lag, and saturates I/O. Batching by key range, committing each batch, and pausing between batches avoids that. The cost is that the backfill becomes a slow, long-running operational task. It needs to be resumable, it needs monitoring, and someone has to own it.
Review gate for migration PRs
- Can the live version run against this schema?
- Can the previous version?
- What happens to rows written during the overlap?
- Which release is allowed to contract?
- Do automated checks flag destructive DDL such as drops, renames, type changes, and new
NOT NULLconstraints?
#!/usr/bin/env bash
# Migration stage (PostgreSQL): runs after build, before the traffic switch.
# Any non-zero exit stops the pipeline, so green never deploys and blue keeps serving.
set -euo pipefail
DB_URL="${DATABASE_URL:?set DATABASE_URL}" # e.g. postgres://migrator@db.internal:5432/app
BATCH=5000
PAUSE=0.5
# Fail fast rather than queue behind a long transaction and block every query behind us.
export PGOPTIONS="-c lock_timeout=3s -c statement_timeout=60s"
psql_q() { psql "$DB_URL" -X -q -t -A -v ON_ERROR_STOP=1 -c "$1"; }
# Expand step: idempotent, nullable, no default. Blue's inserts keep working
# and the change does not rewrite the table.
psql_q "ALTER TABLE orders ADD COLUMN IF NOT EXISTS region_code text;"
# Batched backfill by key range. Each psql call autocommits, so locks are held
# per batch only. Resumable: a rerun starts at the first unfilled row.
max_id=$(psql_q "SELECT coalesce(max(id), 0) FROM orders;")
start=$(psql_q "SELECT coalesce(min(id), $max_id + 1) FROM orders WHERE region_code IS NULL;")
while [ "$start" -le "$max_id" ]; do
end=$((start + BATCH))
psql_q "UPDATE orders SET region_code = legacy_region
WHERE id >= $start AND id < $end AND region_code IS NULL;"
echo "backfilled ids [$start, $end) of $max_id"
start=$end
sleep "$PAUSE" # let replicas and disk catch up between batches
done
# Rows blue inserts after max_id was read still have NULL region_code.
# Rerun this script after the switch, or dual-write in code, before any
# release adds NOT NULL or drops legacy_region (the contract step).
A PostgreSQL migration stage script that makes an idempotent expand change under a short lock timeout, then backfills in resumable, paced key-range batches, and notes which rows the overlap leaves for a later pass.
When should you avoid expand-contract, and what are the alternatives?
Expand-contract fits a specific situation: a monolith where every instance shares one database, the business needs zero-downtime deploys, and you want rollback to be a routine traffic switch rather than a recovery project. Outside that situation, you may be paying for more than you get.
The cost shows up in development. A single logical change becomes three or more releases (expand, migrate and dual-write, contract), and these can be spread over days or weeks. Someone has to coordinate the ordering across teams that deploy the same artifact. Contract steps are easy to forget once the feature works, so they need to be tracked like any other backlog item. Otherwise dead columns and stale triggers pile up. The payoff is fewer emergency fixes, rollbacks that work when you trigger them, and normal deploys that never need a maintenance window.
The approach also wears down over time. One careless DROP or an unguarded NOT NULL in an otherwise compatible release breaks rollback for that deploy. Its safety depends on review habits and migration linting, not on the pattern alone.
| Alternative | When it fits | Main cost |
|---|---|---|
| Maintenance window | Changes that cannot be made compatible, such as large type rewrites or table splits | Planned downtime and user-visible interruption |
| Full duplicate database per color | Read-mostly systems or small datasets | Data sync during the overlap, write conflicts, and a risky cutover. This is usually worse for write-heavy monoliths, which is why it is not the default |
| Deploy without rollback guarantees | Low-traffic internal tools, early-stage products | Recovery means fixing forward under pressure |
How OneEnv can help
Disclosure: OneEnv is the product behind this blog.
OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.
Applied to blue green deployment database, this is where it helps:
Saves time
- Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
- One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
- Changing a shared variable is one edit and one review instead of editing every service's
.envby hand.
Saves cost
- Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
- Less duplicated configuration to maintain: one shared definition inherited by many services.
- A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
- OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.
These are qualitative benefits, not measured results — how much you save depends on how many services share the value.
Want to try it? Start at oneenv.io.