Blue-green and database rollback strategies

Capsule overview

You already have the complete set of techniques (capsules 02-06) for evolving a schema with no downtime. But there are two critical operational topics we haven't tackled yet and that come up constantly in architecture discussions: blue-green deployment with a DB (when does it actually work? is it the "definitive solution" it's sometimes sold as?) and database rollback strategies (what happens when a migration has a bug and you need to go back?). This capsule closes the module covering these realistic limits.

You're going to learn why blue-green with a DB only works with additive-only changes (adding nullable columns, adding tables) — any rename, drop, or type change breaks the model. You're going to understand the rollback window at each phase of expand-contract: the first phases are rollback-friendly, the last one closes the door with no possible loss. And you're going to have a framework to decide between the three rollback strategies (code rollback, schema rollback, forward fix) according to the incident's context.

By the end you'll have the realistic limits of the techniques you learned. Knowing what does NOT work saves you from adopting solutions that look magical but break, and it prepares you to answer with confidence the hardest question of migration response: "we have a bug, do we roll back?"

Mental model: blue-green works for stateless apps, not for stateful DBs

Blue-green deployment is a well-known pattern for stateless apps: you have two identical environments ("blue" in production, "green" prepared in parallel), you deploy the new version to green, you validate, you switch the load balancer so it sends traffic to green, and blue stays as an immediate fallback. If something goes wrong, you redirect to blue. Rollback in seconds.

For stateless apps, the pattern works because neither environment "has state": both are identical replicas behind a balancer. The only difference is the code version.

The problem with DBs: the database is state. You don't have identical "blue DB" and "green DB". You have ONE DB with production data. If you modify the schema, you modify it for both codebases (blue and green). There's no isolation per environment.

This creates two constraints for blue-green with a DB:

1. The schema has to be compatible with both codebases simultaneously. If your schema goes from "it has a title column" to "it doesn't have a title column", the blue code breaks the moment you deploy green with the new schema.
2. The schema change has to be additive-only. Adding things (nullable columns, tables, indexes) is compatible with both codebases. Removing or renaming isn't.

Blue-green's "rollback in seconds" promise breaks the moment your change includes any destructive operation on the schema. And almost any non-trivial schema evolution eventually includes destructive operations (column renames, drops of obsolete tables, type changes).

That's why the reality is: blue-green is a technique for deploying code, not for deploying schema. The schema keeps evolving with expand-contract (capsule 03), and blue-green applies only to the code between the phases.

Which changes are blue-green-compatible

Additive-only changes (blue-green-compatible)

Destructive changes (they break blue-green)

The rule: blue-green allows "one direction" of change

• If you go from "X doesn't exist" to "X exists" → additive, blue-green-friendly.
• If you go from "X exists" to "X doesn't exist" or "X changed" → destructive, it requires a full expand-contract.

That's why the complete evolution pattern is still expand-contract, where each phase is individually additive (blue-green-compatible between phases), and only the final contract is destructive. The destructive window gets contained to a single operation at the end, after all the coexistence.

Worked example: a rename using blue-green between phases

Let's see how blue-green applies to the tasks.title → tasks.name rename case (seen in capsule 03), demonstrating that blue-green works between phases but does NOT let you skip the expand-contract.

Phase 1: ADD COLUMN name (additive — blue-green-friendly)

ALTER TABLE tasks ADD COLUMN name VARCHAR;

• Blue code (old, uses title): keeps working, ignores name.
• Green code (new, preparing to use name): can read name (NULL) without breaking.
• A blue-green deploy here works perfectly. You can prepare green with code that already expects name, validate it works against the schema (with name = NULL), and switch the load balancer.

Phase 2: dual-write (code updated to write to both)

Code deploy:

• Blue code: writes to title only.
• Green code: writes to title AND name.

Here blue-green works between the blue and green code. The schema is stable (both columns exist, both are nullable). You switch the load balancer to green, validate, keep blue as a fallback.

Phase 3: backfill (a standalone operation)

UPDATE tasks SET name = title WHERE name IS NULL in batches.

• The code that's running (blue or green): isn't affected by the backfill (it's just a data UPDATE).
• A blue-green deploy between the old and new code still works.

Phase 4: swap reads (the code reads from name)

Code deploy:

• Green code (previous): reads from title, writes to both.
• Green' code (new): reads from name, writes to both.

Blue-green between green and green'. The schema is stable.

Phase 5: stop dual-write (the code writes only name)

• Green' code (previous): reads from name, writes to both.
• Green'' code (new): reads from name, writes only to name.

The schema is still stable. Blue-green between green' and green''. If you roll back to green' after this deploy, there's a gap: the rows inserted/updated during green'' have title = NULL (because green'' didn't write it). If the rollback makes green' read title, those rows lose the data.

Here the rollback window starts to degrade. Blue-green is still possible but with a caveat.

Phase 6: DROP title (destructive — it breaks blue-green)

ALTER TABLE tasks DROP COLUMN title;

• Green'' code (which only uses name): keeps working.
• Green' code or earlier: THEY BREAK. Code that expected title doesn't find the column.

After this DROP, blue-green back to green' or earlier does NOT work. The schema is incompatible with the earlier code. If there's an incident, you can't redirect the load balancer back — you'd need a schema rollback (recreating the column).

Conclusion: blue-green works great between the phases of expand-contract. The final destructive phase (Phase 6) is the moment where blue-green's "magic" runs out. After that, the only recovery is a forward fix (more on this below).

The rollback window by expand-contract phase

Here's the complete matrix for the "ADD COLUMN NOT NULL to a large table" case (capsule 03):

Reading the matrix:

• Phases 1 and 2: rollback is free. Any problem, just roll back.
• Phase 3: a code rollback still works. The schema allows both code versions.
• Phase 4 (contract): a code rollback BREAKS. You need to first do a schema rollback (SET NULLABLE), then a code rollback. Delicate coordination.

Operational rule: the "easy" rollback window closes at the contract phase. That's why many teams wait days or weeks between Phase 3 and Phase 4, watching metrics to make sure the new code has no bugs before closing the window.

The three rollback strategies

When a migration has problems, there are three possible paths. Each has its context.

Strategy 1: code rollback (the most common)

What: redeploy the previous version of the code (with kubectl rollout undo, a GitOps revert, a blue-green switch to the previous environment).

When it applies: the schema migration succeeded but the new code has a bug. The schema is still compatible with both codebases.

Example: you deployed app v2.0 which uses the new priority column. It turns out it has a bug in the prioritization logic. You roll the app back to v1.9. The column stays in the DB, unused, harmless.

Advantages:

• Fast (seconds to minutes).
• Doesn't touch the DB.
• No risk of data loss.

Disadvantages:

• Only works if the schema is compatible with both codebases.
• Doesn't apply after the final contract (where the schema changed destructively).

Strategy 2: schema rollback (rare but sometimes necessary)

What: run alembic downgrade -1 (or several) to revert the schema migration.

When it applies: the schema migration caused problems (a prolonged lock, data corrupted by some script, etc.) and you need to restore the previous state.

Example: you ran a migration that did an incorrect backfill (it assigned priority = 0 to tasks that should have had priority = 5 per business rules). You need to erase the backfill and redo it correctly.

Advantages:

• Restores the schema's previous state.
• Useful when the problem is in the operation itself.

Disadvantages:

• More complex and riskier than a code rollback.
• It may require additional downtime.
• It isn't always possible without loss (e.g. reverting a DROP COLUMN doesn't recover the data).
• You have to coordinate with app code that may assume the new schema.

A pattern for a safe schema rollback:

# Step 1: put the app into maintenance (if it isn't strictly 24/7)
# or deploy a version that tolerates both schemas

# Step 2: run the downgrade
alembic downgrade -1

# Step 3: verify the state
alembic current

# Step 4: redeploy the app with compatible code

Strategy 3: forward fix

What: instead of reverting, write and deploy a new migration that FIXES the problem.

When it applies: reverting is impossible or riskier than moving forward. The problem is identifiable and the solution is clear.

Example: you discovered the backfill assigned priority = 0 when it should have been 5 for tasks created in a certain date range. You write a new migration that runs UPDATE tasks SET priority = 5 WHERE created_at BETWEEN ... AND ...; and deploy it.

Advantages:

• It applies when a rollback is destructive (losing data when reverting a DROP).
• It applies after the final contract.
• More aligned with the "always move forward" philosophy.

Disadvantages:

• It requires identifying the bug and designing the fix under pressure.
• It isn't always clear what fixes it.
• The team can lose confidence in migrations if forward-fixes are frequent.

The decision matrix

Schema deployment patterns with feature flags

An advanced technique for reducing risk: combining expand-contract with feature flags that control when the code uses the new schema.

Setup:

• Schema: advances with expand-contract (Phase 1: ADD COLUMN, Phase 2: backfill, Phase 3: ...).
• Code: uses the new column ONLY if the feature flag is on.

# app/api/tasks.py
from feature_flags import is_enabled

@router.post("/tasks")
async def create_task(title: str, priority: int = 0, db: ...):
    task = Task(title=title)
    # The feature flag controls whether we use the new column
    if is_enabled("use_priority_column"):
        task.priority = priority
    db.add(task)

Benefits:

• You can deploy the new code with the feature off.
• Enable the feature gradually: 1% of tenants, 10%, 50%, 100%.
• If you find a bug, turn the feature off instantly with no code rollback.
• Once you've confirmed in production that it works, remove the feature flag and the fallback code.

Cost: additional complexity. More code (an if/else branch). More state (which tenants have the feature on/off). Not for every change — only for the risky ones.

When it's worth it:

• Changes affecting critical business logic.
• Changes where a code rollback would be destructive (e.g. the new app already wrote to the new column, a rollback would leave inconsistency).
• Releases with many changes where you want to isolate one specific one.

Why does this matter in real work?

1. It saves you from adopting "magic solutions" that don't work. There are vendors selling blue-green with a DB as "risk-free deployment, instant rollback". The reality is that it only works for a specific class of changes. Knowing the limits saves you from implementing expensive tooling that ends up breaking.

2. It gives you a framework to decide under pressure. When there's an incident and the lead asks you "rollback or not?", the decision matrix gives you an articulated answer instead of an improvised one.

3. It's what's expected at senior architecture level. Senior interview questions include: "how do you roll back a migration that dropped a column?". The correct answer involves understanding that it is NOT simple, and that a forward fix is sometimes the only viable path. Knowing that sets you apart.

4. It closes the module with realism. The previous modules taught you techniques. This capsule teaches you the limits of those techniques. Knowing where they end prevents you from applying them badly.

Traps and common mistakes

Mistake 1 (conceptual): assuming blue-green solves "every" rollback

Symptom: the team adopts blue-green as "the solution for zero-downtime" and stops investing in expand-contract. Until one day they need to drop a column and blue-green doesn't apply.

Why it happens: blue-green blog posts show the easy case (a stateless code deploy). They don't always explain the limits for schema.

How to tell: does your change include a DROP, RENAME, or ALTER TYPE? If so, blue-green does NOT save you — you need a full expand-contract.

How to fix it: understand blue-green as a complement to expand-contract (a code deploy between the phases), not as a replacement.

Mistake 2 (operational): rolling back the schema without coordinating with the code

Symptom: a dev runs alembic downgrade -1 to revert a migration. The column disappears. The app that was already deployed using that column starts throwing 500 errors.

Why it happens: the intuition is "rollback = go back to the previous state". But the schema's "previous state" can be incompatible with code that's already running.

How to tell: before a schema rollback, ask: "which version of the app is running? is it compatible with the schema I'm about to go back to?"

How to fix it: a schema rollback requires coordination: first roll the app back to a compatible version, then roll back the schema, then confirm.

Mistake 3 (conceptual): forward-fix as a culture generates instability

Symptom: the team never rolls back. Every problem gets resolved with a forward-fix. After months, the system accumulates 50 small forward-fixes, hidden complexity, technical debt.

Why it happens: a forward-fix feels "more positive" than a rollback. But sometimes a rollback is the correct answer and postponing it creates more damage.

How to tell: does your team ever roll back? If the answer is "almost never", you're either too conservative or too optimistic.

How to fix it: treat rollback as a valid option, not a sign of failure. Forward-fix when a rollback is destructive, not as the default.

Mistake 4 (conceptual): assuming the final contract closes ALL rollback

Symptom: a dev believes that after the final contract, there's no way to revert the change. They accept the bug as "it's too late now" and live with the problem.

Why it happens: the closing of the "automatic" rollback window gets confused with the closing of all possible recovery.

How to tell: after the contract, a rollback is complex but possible. A forward-fix is the tool. And sometimes "rollback" means another migration that adds a new column with data derived from the old one before dropping it.

How to fix it: understand that the rollback window refers to "easy rollback with no coordination". Recovery is always possible, it just becomes more laborious.

Mistake 5 (operational): not measuring the "drift" between schema and code

Symptom: after several migrations, nobody knows exactly which columns each table has in production vs what the SQLAlchemy model says. Strange errors appear because there's drift.

Why it happens: an expand-contract with many phases can leave "intermediate" columns (e.g. title and name coexist during the rename) that stick around longer than planned. With no regular audit, nobody notices them.

How to tell: are there validation scripts comparing the DB schema with the SQLAlchemy model? If not, you're vulnerable to drift.

How to fix it: run alembic check (or the equivalent) regularly. Consider the Squawk linter in CI. And plan the cleanup of "intermediate" columns as part of the process of every rename (don't leave it for "when there's time").

Exercises

Exercise 1: classify changes by blue-green compatibility

For each change, indicate whether it's blue-green-compatible (the old and new code can coexist) or not.

1. Add the column users.bio TEXT NULL.
2. Change the type of tasks.points from INTEGER to BIGINT.
3. Add a new table task_attachments.
4. Rename users.username to users.handle.
5. Add an index CONCURRENTLY on tasks.priority.
6. Make tasks.priority NOT NULL (assume the backfill is already complete).
7. Remove the column tasks.legacy_status (nobody uses it anymore according to metrics).
8. Change the DEFAULT of tasks.status from 'open' to 'pending'.
9. Add the foreign key tasks.project_id REFERENCES projects(id).
10. Partition the audit_logs table by month.

Exercise 2: design the rollback window for a complex change

Product asks you to remove the column users.legacy_role (nobody uses it anymore according to a code grep). The table has 5M rows, in 24/7 production. Design the complete plan including which phases are rollback-friendly and which one closes the window.

-- Optional: add an explicit comment
COMMENT ON COLUMN users.legacy_role IS 'DEPRECATED 2026-05-02. Will be dropped in N weeks. See JIRA-1234.';

def upgrade():
    op.execute("SET lock_timeout = '5s'")
    op.execute("ALTER TABLE users DROP COLUMN IF EXISTS legacy_role")


def downgrade():
    op.execute("SET lock_timeout = '5s'")
    # Recreation WITHOUT data — the information was lost on the drop
    op.execute(
        "ALTER TABLE users ADD COLUMN IF NOT EXISTS legacy_role VARCHAR NULL"
    )

Exercise 3: apply the rollback decision matrix

For each scenario, indicate which rollback strategy you'd apply and why:

1. You deployed app v2.0 which uses the new tasks.priority column. You detect that the sort-by-priority logic has a bug that shows tasks in the wrong order.
2. You ran a backfill of tasks.priority = 0. You discover that for tenant Acme's tasks, they should have been backfilled to 5 (a specific business rule).
3. You ran DROP COLUMN users.legacy_role. A customer reports they needed that data for an integration nobody had documented.
4. The migration add_priority_column failed midway. State: the column exists but alembic_version wasn't updated.
5. The migration set_priority_not_null ran but now you find out there are old endpoints (not migrated) that still do INSERTs with no priority and get a 500 error.

Exercise 4: implement a feature flag for a risky migration

For the case of "adding a tasks.priority column with a change to the ordering logic", implement the feature-flag pattern that controls whether the app uses the new column.

# app/feature_flags.py
"""Simple feature flags based on env vars.

In real production you'd use LaunchDarkly, Flagsmith, or similar.
For this capsule, env vars are enough.
"""
import os


def is_enabled(flag_name: str, default: bool = False) -> bool:
    """Returns True if the feature flag is on."""
    env_var = f"FF_{flag_name.upper()}"
    value = os.environ.get(env_var, str(default).lower())
    return value.lower() in ("true", "1", "yes", "on")


def is_enabled_for_tenant(flag_name: str, tenant_id: int) -> bool:
    """Returns True if the feature flag is on for this tenant.

    Allows a gradual rollout: a list of tenant IDs in an env var.
    """
    if is_enabled(flag_name):
        return True  # Enabled globally

    # A list of specific tenants
    env_var = f"FF_{flag_name.upper()}_TENANTS"
    enabled_tenants = os.environ.get(env_var, "").split(",")
    return str(tenant_id) in enabled_tenants

# app/api/tasks.py
from fastapi import APIRouter, Depends
from sqlalchemy import select, desc
from sqlalchemy.ext.asyncio import AsyncSession

from app.db.models import Task
from app.feature_flags import is_enabled_for_tenant
from app.auth import get_current_tenant_id


router = APIRouter()


@router.post("/tasks")
async def create_task(
    title: str,
    priority: int = 0,
    db: AsyncSession = Depends(...),
    tenant_id: int = Depends(get_current_tenant_id),
):
    task = Task(tenant_id=tenant_id, title=title)

    # Feature flag: does this tenant use the new column?
    if is_enabled_for_tenant("use_priority_column", tenant_id):
        task.priority = priority

    db.add(task)
    await db.flush()
    return task


@router.get("/tasks")
async def list_tasks(
    db: AsyncSession = Depends(...),
    tenant_id: int = Depends(get_current_tenant_id),
):
    stmt = select(Task).where(Task.tenant_id == tenant_id)

    # Feature flag: use the new ordering logic?
    if is_enabled_for_tenant("use_priority_ordering", tenant_id):
        # The new logic: by priority desc, then created_at
        stmt = stmt.order_by(desc(Task.priority), desc(Task.created_at))
    else:
        # The old logic: only created_at
        stmt = stmt.order_by(desc(Task.created_at))

    stmt = stmt.limit(50)
    result = await db.execute(stmt)
    return list(result.scalars().all())

# Initially: the feature is off for everyone
# (no env vars set, default False)

# Enable it for tenant Acme (ID 100) first
export FF_USE_PRIORITY_COLUMN_TENANTS="100"
export FF_USE_PRIORITY_ORDERING_TENANTS="100"
# Restart the app

# Validate for 24-48h with metrics. If it's fine, expand:
export FF_USE_PRIORITY_COLUMN_TENANTS="100,200,300,400,500"

# If everything's fine, enable it globally:
export FF_USE_PRIORITY_COLUMN="true"
export FF_USE_PRIORITY_ORDERING="true"

# After a few weeks with the feature on for everyone, remove
# the feature flags from the code (cleanup).

Exercise 5: postmortem of a real case

Read this fictional postmortem and analyze:

Incident: On 2026-05-01, we ran the migration add_user_status_column which added users.status TEXT NOT NULL DEFAULT 'active' to the users table with 12M rows. The migration took 2.5 minutes. During that time, the API was returning 500 errors on all the auth endpoints. PG version: 14.

After the migration, we discovered there's an external script (legacy, maintained by another team) that runs every night doing INSERT INTO users (email) VALUES (...) without going through our API. That script started failing the next night with the error "null value in column status". We found out 18 hours later from a customer report.

We made the decision to roll back. We ran ALTER TABLE users ALTER COLUMN status DROP NOT NULL. The column still exists but now accepts NULL. The external script worked again. But now we have 2k rows with status = NULL that we need to clean up.

Questions:

1. What did they do wrong in the original migration?
2. Was the chosen rollback reasonable? What other option was there?
3. How do you prevent it in the future?

Summary and next step

In this capsule you learned:

• Blue-green with a DB only works with additive-only changes. ADD COLUMN nullable, CREATE TABLE, CREATE INDEX CONCURRENTLY are blue-green-friendly. DROP, RENAME, ALTER TYPE, SET NOT NULL aren't.
• Blue-green is a complement to expand-contract, not a replacement. It works between the additive phases of expand-contract; the final destructive contract closes the option.
• The rollback window closes at the final contract. The first phases (expand, backfill, code swap) are rollback-friendly. The final SET NOT NULL closes the door.
• Three rollback strategies: code rollback (the most common, schema-compatible), schema rollback (rare, requires coordination), forward fix (when a rollback is destructive or impossible).
• Feature flags + expand-contract is the advanced pattern for risky changes: granular rollout control, "instant" rollback with no redeploy.
• Drift between schema and code is the silent risk: intermediate columns from the expand-contract that stick around longer than planned, with no documentation.

Before moving on you should be able to:

• Classify any schema change as blue-green-compatible or not, with justification.
• Map the rollback window of each expand-contract phase.
• Apply the decision matrix of the 3 rollback strategies according to the incident's context.
• Design a feature-flags + expand-contract pattern for a risky change.
• Articulate why blue-green isn't the "magic solution" that solves every schema deployment.
• Identify and prevent drift between schema and code in your codebase.

Next capsule — Project: a live zero-downtime migration. You've learned all the techniques and all the operational patterns of the module. Now you're going to run the complete flow from start to finish on your mini-project: adding tasks.priority NOT NULL DEFAULT 0 to a table with 1M rows, while wrk runs in the background measuring that no request fails. You're going to produce the real RUNBOOK-MIGRATION.md, the 3 Alembic files with their upgrade/downgrade, the standalone backfill script, and the benchmarks that demonstrate 0 measured downtime. It's the closing that internalizes everything in the module and prepares you for the integrative project of module 8.

Resources

1. Martin Fowler — BlueGreenDeployment — the classic reference for the pattern, with a discussion of the limits for a DB.
2. GitLab — Database review and rollback strategy — how GitLab handles migration rollback in practice.
3. PlanetScale — Why we don't do blue-green deployments for schema — a technical analysis of why blue-green doesn't scale for schema.
4. LaunchDarkly — Feature flags for database migrations — the feature-flags + expand-contract pattern.
5. Stripe — Online migrations at scale — how Stripe orchestrates migrations in production at global scale.
6. Heroku — Designing forward-compatible migrations — a practical checklist.
7. PostgreSQL — Backup and restore for point-in-time recovery — for the cases where "rollback" means restoring from a backup.

Module 5 — SQL Patterns for Production APIs Guide

Next capsule: The module project — running a live zero-downtime migration with measured traffic and a documented RUNBOOK.