---
title: "Zero-Downtime PostgreSQL Schema Migrations: Expand/Contract vs Blue-Green"
published: true
description: "A hands-on guide to two patterns for shipping PostgreSQL schema changes without downtime — with production SQL, Kotlin code, and CI/CD pipeline examples."
tags: postgresql, architecture, devops, cloud
canonical_url: https://blog.mvpfactory.co/zero-downtime-postgresql-schema-migrations-expand-contract-vs-blue-green
---
## What we are building
By the end of this tutorial, you will understand two battle-tested patterns for deploying PostgreSQL schema changes with zero downtime: **expand/contract** and **blue-green (shadow schema)**. You will walk away with production SQL you can paste into your migration files, a Kotlin advisory lock wrapper, and a GitHub Actions pipeline that gates deployments on schema validation.
Let me show you a pattern I use in every project — and the one I save for when things get structural.
## Prerequisites
- PostgreSQL 11+ (non-volatile defaults matter here)
- A migration tool: Flyway, Liquibase, or Alembic
- Basic familiarity with DDL and transactions
- A CI/CD pipeline (GitHub Actions examples below)
## Step 1 — Expand/contract for everyday migrations
This pattern splits a breaking change into three separate deployments. Here is the minimal setup to get this working.
**Expand** — add the new column without blocking reads or writes:
sql
ALTER TABLE orders ADD COLUMN customer_email TEXT;
**Migrate** — backfill in batches to avoid long-running transactions:
sql
UPDATE orders
SET customer_email = customers.email
FROM customers
WHERE orders.customer_id = customers.id
AND orders.id BETWEEN :start AND :end;
**Contract** — drop the old column only after all application code has moved over:
sql
ALTER TABLE orders DROP COLUMN customer_name;
Each phase is a separate deploy. Your application dual-writes during the migration window, reading from the new column with a fallback to the old. This handles 80% of migration scenarios.
## Step 2 — Blue-green for structural rewrites
When you need an atomic cutover — column type changes across large tables, primary key modifications, table splits — blue-green at the database level uses a shadow schema and view switching.
Create the target schema, sync data with a trigger, then swap atomically:
sql
CREATE SCHEMA green;
CREATE TABLE green.orders (
id BIGINT PRIMARY KEY,
customer_email TEXT NOT NULL,
amount NUMERIC(12,2),
created_at TIMESTAMPTZ DEFAULT now()
);
sql
CREATE OR REPLACE FUNCTION sync_orders() RETURNS TRIGGER AS $$
BEGIN
INSERT INTO green.orders (id, customer_email, amount, created_at)
VALUES (NEW.id, NEW.customer_email, NEW.amount, NEW.created_at)
ON CONFLICT (id) DO UPDATE SET
customer_email = EXCLUDED.customer_email,
amount = EXCLUDED.amount;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
sql
CREATE OR REPLACE VIEW public.orders AS SELECT * FROM green.orders;
This is the PostgreSQL equivalent of the ghost table pattern that `pt-online-schema-change` and `gh-ost` popularized in MySQL. Tools like `pgroll` and `pg-osc` automate it for PostgreSQL.
## Step 3 — Lock concurrent migrations with pg_advisory_lock
Regardless of which pattern you pick, concurrent migrations from multiple CI runners can corrupt state. Here is the gotcha that will save you hours — wrap every migration in an advisory lock:
kotlin
fun withMigrationLock(dataSource: DataSource, block: () -> T): T {
dataSource.connection.use { conn ->
conn.prepareStatement("SELECT pg_advisory_lock(12345)").execute()
try {
return block()
} finally {
conn.prepareStatement("SELECT pg_advisory_unlock(12345)").execute()
}
}
}
withMigrationLock(dataSource) {
flyway.migrate()
}
## Step 4 — Gate deployments in CI/CD
Your pipeline should never deploy application code before the schema is ready:
yaml
jobs:
migrate:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v4
- name: Acquire advisory lock and migrate
run: |
psql "$DATABASE_URL" -c "SELECT pg_advisory_lock(12345);"
flyway -url="$JDBC_URL" migrate
psql "$DATABASE_URL" -c "SELECT pg_advisory_unlock(12345);"
- name: Validate schema
run: |
psql "$DATABASE_URL" -c "
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'orders'
AND column_name = 'customer_email';" \
| grep -q 'customer_email' || exit 1
deploy:
needs: migrate
runs-on: ubuntu-latest
steps:
- name: Deploy application
run: kubectl rollout restart deployment/api
The `deploy` job depends on `migrate`. If the expected column does not exist, the pipeline fails before deployment.
## Gotchas
- **`CREATE INDEX CONCURRENTLY` cannot run inside a transaction.** Your migration runner must support non-transactional statements. Flyway uses `executeInTransaction=false`, Liquibase uses `runInTransaction="false"`. Miss this and your index creation grabs a write lock on the entire table.
- **Blue-green costs 2x table storage during sync.** The shadow schema is a full copy. Budget for it or you will run out of disk mid-migration.
- **The docs do not mention this, but** skipping advisory locks is a silent data corruption vector. I have seen teams lose data because two CI runners executed migrations simultaneously. Lock acquisition should be the first line of every migration job.
- **Do not drop old columns too early.** If any running pod still references the old column, you will get runtime errors. Wait for a full rollout cycle before the contract phase.
## When to use which
| Criteria | Expand/contract | Blue-green |
|---|---|---|
| Complexity | Low–medium | High |
| Rollback | Drop new column | Switch view back |
| Storage overhead | Minimal | 2x during sync |
| Best for | Additive changes, renames | Type changes, large restructures |
| Team skill required | Moderate SQL | Deep PostgreSQL internals |
## Conclusion
Default to expand/contract. It is simpler, uses less storage, and works with standard migration tools. Save blue-green for structural rewrites where incremental steps are not feasible. Use `pg_advisory_lock` and CI/CD schema validation gates no matter which pattern you pick. Concurrent migrations are a problem you only want to solve once — before it costs you data.
Top comments (0)