Skip to main content
← PROBLEMS / ENGINEERING
An end-to-end domain learning journey

Zero-Downtime Blue-Green
Database Deployments.

A payments service deploys via Blue-Green behind an AWS ALB sharing a single PostgreSQL 15 database. Release v42 (Green) runs a Flyway migration that renames billing_zip to postal_code and adds a non-nullable tax_jurisdiction VARCHAR(16) NOT NULL column without a default. During Green validation, 12% of requests fail with a serialization bug, so the on-call engineer flips the ALB target group back to Blue (v41). Immediately, Blue fails with 100% HTTP 500s (column billing_zip does not exist and null value in column tax_jurisdiction violates not-null constraint). Design a zero-downtime, instant-rollback Blue-Green schema evolution and deployment pipeline.
Start the journey5 stages
Zero-Downtime Blue-Green Database Deployments architecture sketch diagram
FIRST PRINCIPLES. PRODUCTION SCALE.
The idea to keep with you

A payments service deploys via Blue-Green behind an AWS ALB sharing a single PostgreSQL 15 database. Release v42 (Green) runs a Flyway migration that renames `billing_zip` to `postal_code` and adds a non-nullable `tax_jurisdiction VARCHAR(16) NOT NULL` column without a default. During Green validation, 12% of requests fail with a serialization bug, so the on-call engineer flips the ALB target group back to Blue (v41). Immediately, Blue fails with 100% HTTP 500s (`column billing_zip does not exist` and `null value in column tax_jurisdiction violates not-null constraint`). Design a zero-downtime, instant-rollback Blue-Green schema evolution and deployment pipeline.

From patterns to decisions.

A payments service deploys via Blue-Green behind an AWS ALB sharing a single PostgreSQL 15 database. Release v42 (Green) runs a Flyway migration that renames `billing_zip` to `postal_code` and adds a non-nullable `tax_jurisdiction VARCHAR(16) NOT NULL` column without a default. During Green validation, 12% of requests fail with a serialization bug, so the on-call engineer flips the ALB target group back to Blue (v41). Immediately, Blue fails with 100% HTTP 500s (`column billing_zip does not exist` and `null value in column tax_jurisdiction violates not-null constraint`). Design a zero-downtime, instant-rollback Blue-Green schema evolution and deployment pipeline.

01
ENGINEERING · STAGE 01L4 · FUNDAMENTALS

Green's Flyway migration mutated the.

Green's Flyway migration mutated the shared PostgreSQL schema destructively, breaking Blue's SQL assumptions and making rollback impossible without a database restore.

ChoiceWhat it buysWhat it costs
Trade-off 1Sharing one DB across Blue and Green saves replication cost but binds both colors to a single schema

Step 1

Identify that RENAME COLUMN billing_zip TO postal_code broke all Blue SELECT and INSERT queries.

Step 2

Identify that ADD COLUMN tax_jurisdiction NOT NULL (without default) rejected every Blue INSERT.

Step 3

Note that even without a rollback, Blue and Green overlap for 60s during ALB connection draining.

Alternative 1

Use logical replication to a separate Green database if destructive schema rewrites are unavoidable

Failure Mode 1

Running Flyway undo drops tax_jurisdiction and destroys data written by Green

-- Breaking DDL that violates N / N-1 compatibility
ALTER TABLE invoices RENAME COLUMN billing_zip TO postal_code;
ALTER TABLE invoices ADD COLUMN tax_jurisdiction VARCHAR(16) NOT NULL;
02
ENGINEERING · STAGE 02L5 · APPLIED DESIGN

Use a pre-deploy Expand migration.

Use a pre-deploy Expand migration with additive columns and a BEFORE INSERT OR UPDATE sync trigger, followed by a post-stabilization Backfill/Validate and a v43 Contract.

ChoiceWhat it buysWhat it costs
Trade-off 1Trigger-based sync keeps N and N-1 consistent at the cost of a small per-write PL/pgSQL invocation

Expand

Add postal_code (nullable) and tax_jurisdiction (DEFAULT 'PENDING').

Sync

Attach a BEFORE INSERT OR UPDATE trigger to keep billing_zip and postal_code identical.

Contract (v43)

After Blue is retired, drop the trigger and billing_zip.

Alternative 1

Application-level dual-write in Green + coalesced reads, which still requires a backfill for Blue writes

Failure Mode 1

Writing NULL explicitly to postal_code on an UPDATE when billing_zip is still non-null can revert the value unless handled carefully

ALTER TABLE invoices
  ADD COLUMN IF NOT EXISTS postal_code VARCHAR(16),
  ADD COLUMN IF NOT EXISTS tax_jurisdiction VARCHAR(16) DEFAULT 'PENDING';

CREATE OR REPLACE FUNCTION sync_invoices_postal_code() RETURNS TRIGGER AS $$
BEGIN
  IF NEW.postal_code IS NULL THEN NEW.postal_code := NEW.billing_zip; END IF;
  IF NEW.billing_zip IS NULL THEN NEW.billing_zip := NEW.postal_code; END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
03
ENGINEERING · STAGE 03L6 · SCALE & TRADE-OFFS

Always run Expand migrations as.

Always run Expand migrations as an independent pre-deployment pipeline job before Green containers start, never inside the application startup hook.

ChoiceWhat it buysWhat it costs
Trade-off 1Requires CI runner network access to the private VPC database (e.g., via self-hosted runner or SSM)

Step 1

Avoid race conditions when 20 Green pods boot simultaneously and contend on Flyway's schema lock.

Step 2

Ensure migration failures abort the pipeline before any Green pod enters the ALB target group.

Step 3

Keep container startup fast (<5s) and deterministic.

Alternative 1

Kubernetes PreSync Hook / ArgoCD Job with backoffLimit: 1 before rollout

Failure Mode 1

Startup-coupled migrations that time out cause CrashLoopBackOff across all Green pods

jobs:
  migrate-expand:
    runs-on: ubuntu-latest
    steps:
      - run: squawk db/migration/V42__*.sql
      - run: flyway -url=$DB_URL -lockRetryCount=3 migrate
  deploy-green:
    needs: migrate-expand
    steps:
      - run: ecs deploy --service payments-green --image v42
04
ENGINEERING · STAGE 04L6 · SCALE & TRADE-OFFS

Backfill 'PENDING' rows in throttled.

Backfill 'PENDING' rows in throttled 1,000-row batches, then use ADD CONSTRAINT ... NOT VALID + VALIDATE CONSTRAINT before setting NOT NULL.

ChoiceWhat it buysWhat it costs
Trade-off 1`VALIDATE CONSTRAINT` takes longer than a locked scan because it yields to concurrent writers, but prevents downtime

Step 1

Verify 0% of Blue traffic remains and backfill any tax_jurisdiction = 'PENDING' or NULL rows.

Step 2

Add CHECK (tax_jurisdiction IS NOT NULL) NOT VALID (instant metadata lock).

Step 3

Run VALIDATE CONSTRAINT under SHARE UPDATE EXCLUSIVE so live reads/writes continue uninterrupted.

Alternative 1

Leave the validated CHECK constraint in place permanently; it enforces identical data integrity

Failure Mode 1

Running ALTER COLUMN tax_jurisdiction SET NOT NULL directly acquires ACCESS EXCLUSIVE for the entire 50M-row scan

SET lock_timeout = '2000ms';
ALTER TABLE invoices ADD CONSTRAINT chk_tax_jurisdiction_nn
  CHECK (tax_jurisdiction IS NOT NULL) NOT VALID;
ALTER TABLE invoices VALIDATE CONSTRAINT chk_tax_jurisdiction_nn;
05
ENGINEERING · STAGE 05L7 · STAFF / PRINCIPAL

Enforce automated SQL AST linting.

Enforce automated SQL AST linting with Squawk in CI and require explicit multi-PR separation between Expand and Contract.

ChoiceWhat it buysWhat it costs
Trade-off 1Contract PRs require a manual override flag (`-- squawk-ignore`) reviewed by a DBA or staff engineer

Step 1

Add Squawk to GitHub Actions on every PR touching db/migration/.

Step 2

Ban rename-column, drop-column, ban-drop-table, and adding-not-nullable-field by default.

Step 3

Require pg_stat_statements verification showing zero queries referencing the old column before approving a Contract PR.

Alternative 1

Test every PR in CI by running v(N-1) integration tests against the v(N)-migrated database

Failure Mode 1

Relying on manual code review alone inevitably misses subtle lock or N-1 incompatibilities

- name: Lint SQL Migrations
  uses: sbdchd/squawk-action@v1
  with:
    files: db/migration/*.sql
    fail-on-violations: true

Theory & Tips and Tricks

Practical heuristics & interview traps

  • 01Interview anchor: Enforce strict Expand-and-Contract schema evolution so N and N-1 application versions can always read and write the shared database concurrently.
  • 02Trade-off tip: Requires CI runner network access to the private VPC database (e.g., via self-hosted runner or SSM)
  • 03Watch out for: Running ALTER COLUMN tax_jurisdiction SET NOT NULL directly acquires ACCESS EXCLUSIVE for the entire 50M-row scan
Behavioral Progression · Blue Green Database Compatibility

Blue Green Database Compatibility — Normal → Load / Fault → Recovery

Send Request
1. Normal Request Ingress
Traffic: 1x baseline · Latency (p99): 4 ms
Process State
2. Core Execution & State Lookup
Traffic: 1x baseline · Latency (p99): 9 ms
Inject 10x Spike
3. Scale Spike / Failure Injection
Traffic: 10x burst · Latency (p99): 185 ms

Enforce strict Expand-and-Contract schema evolution so N and N-1 application versions can always read and write the shared database concurrently.

Synthesis / One connected design

Putting Zero-Downtime Blue-Green Database Deployments Together

Enforce strict Expand-and-Contract schema evolution so N and N-1 application versions can always read and write the shared database concurrently.

A candidate architecture, not a universal answer

Putting Zero-Downtime Blue-Green Database Deployments Together

Step 1
Step 2
Step 3
  1. Expand: Add postal_code (nullable) and tax_jurisdiction (DEFAULT 'PENDING').
  2. Sync: Attach a BEFORE INSERT OR UPDATE trigger to keep billing_zip and postal_code identical.
  3. Contract (v43): After Blue is retired, drop the trigger and billing_zip.
Backfill `'PENDING'` rows in throttled 1,000-row batches, then use `ADD CONSTRAINT ... NOT VALID` + `VALIDATE CONSTRAINT` before setting `NOT NULL`.
Interview Readiness Check

Backfill 'PENDING' rows in throttled 1,000-row batches, then use ADD CONSTRAINT ... NOT VALID + VALIDATE CONSTRAINT before setting NOT NULL.

Walk through bottlenecks, back-of-the-envelope math, and failure recovery.

Practice / Explain the consequences

Make the trade-off yourself.

Practice Check

Test your understanding

Question 1 of 3

When is ACID Transaction Fundamentals the right default for a production team?

Key takeaway

Zero-Downtime Blue-Green Database Deployments

Enforce strict Expand-and-Contract schema evolution so N and N-1 application versions can always read and write the shared database concurrently.