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.
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.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Sharing 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;
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.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Trigger-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;
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.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Requires 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
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.
| Choice | What it buys | What 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;
Enforce automated SQL AST linting.
Enforce automated SQL AST linting with Squawk in CI and require explicit multi-PR separation between Expand and Contract.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Contract 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 NULLdirectly acquiresACCESS EXCLUSIVEfor the entire 50M-row scan
Blue Green Database Compatibility — Normal → Load / Fault → Recovery
Enforce strict Expand-and-Contract schema evolution so N and N-1 application versions can always read and write the shared database concurrently.
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.
Putting Zero-Downtime Blue-Green Database Deployments Together
- Expand: Add
postal_code(nullable) andtax_jurisdiction(DEFAULT 'PENDING'). - Sync: Attach a
BEFORE INSERT OR UPDATEtrigger to keepbilling_zipandpostal_codeidentical. - Contract (v43): After Blue is retired, drop the trigger and
billing_zip.
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.
Make the trade-off yourself.
Practice Check
Test your understanding
When is ACID Transaction Fundamentals the right default for a production team?
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.
