Zero-downtime database schema migration using Expand-Contract pattern

Zero-downtime database schema migration: the Blue-Green pattern

The most common cause of downtime during deployments is schema changes. An ALTER TABLE on a 200 million row table in PostgreSQL holds an AccessExclusiveLock for several minutes. During that time, write queries queue up, the connection pool fills, and the application returns 500 errors. For high-load e-commerce, such downtime can cost millions. We specialize in zero-downtime migrations and use Expand-Contract—the only reliable pattern proven on tables exceeding 1 TB. It allows schema changes without stopping the application. In e-commerce, every minute of downtime can cost up to $5,000, so it's critical to avoid locks.

Blue-green database migration is not two separate databases with synchronization. In reality, it means expanding the schema so both old and new application versions can work simultaneously, reading from a single database. We offer a full cycle: audit, design, implementation, and testing. Get a consultation on zero-downtime migration — contact us to evaluate your project.

Why Expand-Contract is the only reliable pattern

The fundamental principle of zero-downtime schema migration is called Expand-Contract (or Parallel Change). It consists of three phases, each deployed separately.

Phase Expand – extend the schema without breaking old code:

-- Add a new column as nullable (!) 
ALTER TABLE orders ADD COLUMN payment_method VARCHAR(64); 

-- Or rename by adding new columns 
ALTER TABLE users ADD COLUMN first_name VARCHAR(255); 
ALTER TABLE users ADD COLUMN last_name VARCHAR(255); 

-- Create index CONCURRENTLY – without table lock 
CREATE INDEX CONCURRENTLY idx_orders_payment_method ON orders(payment_method);

After this, deploy application version v2 that writes to both old and new columns simultaneously. The old version v1 continues to work—it doesn't know about the new columns, but that's fine because they are nullable.

Phase Migration – backfill data in the background:

-- Fill new columns in batches without blocking the table
DO $$
DECLARE
    batch_size INT := 10000;
    last_id BIGINT := 0;
    max_id BIGINT;
BEGIN
    SELECT MAX(id) INTO max_id FROM users;
    WHILE last_id < max_id LOOP
        UPDATE users
        SET first_name = split_part(name, ' ', 1),
            last_name = trim(substr(name, strpos(name, ' ')))
        WHERE id > last_id AND id <= last_id + batch_size AND first_name IS NULL;
        last_id := last_id + batch_size;
        PERFORM pg_sleep(0.1); -- short pause between batches
    END LOOP;
END $$;

Phase Contract – remove old after full transition:

-- Only after v1 is completely decommissioned
ALTER TABLE users DROP COLUMN name;
ALTER TABLE orders ALTER COLUMN payment_method SET NOT NULL;

Where it breaks: common mistakes and solutions

The most frequent mistake is adding NOT NULL without a DEFAULT in the Expand phase. PostgreSQL before version 11 would rewrite the entire table. With PostgreSQL 11+, ADD COLUMN NOT NULL DEFAULT 'value' works instantly (the value is stored in the system catalog), but only if DEFAULT is static, not a function call.

The second pitfall is unique indexes. CREATE UNIQUE INDEX CONCURRENTLY can fail with a duplicate key error if data already contains duplicates. Then the index remains in INVALID state – it must be dropped, data cleaned, and creation retried.

The third problem is foreign keys. ALTER TABLE ADD CONSTRAINT FOREIGN KEY in PostgreSQL takes ShareRowExclusiveLock on both tables and fully scans the child table. On large tables, this takes minutes. The solution is to create FK with NOT VALID, then validate separately:

-- Step 1: add FK without checking existing rows (fast)
ALTER TABLE orders ADD CONSTRAINT fk_orders_users FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;
-- Step 2: validate in the background (does not block DML, only takes ShareUpdateExclusiveLock)
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_users;

How to avoid locks when adding foreign keys

Use a two-step approach: first NOT VALID, then separate validation. This reduces lock time from minutes to milliseconds. Validation scans the table but does not interfere with normal operations – only a short ShareUpdateExclusiveLock to update the dictionary.

Automating checks in CI/CD

In the pipeline before deployment, run static analysis of migrations. The tool squawk (for PostgreSQL) checks SQL files for dangerous patterns:

---
# .github/workflows/db-migration-check.yml
- name: Check migrations safety
  run: |
    squawk db/migrations/*.sql \
      --exclude=prefer-text-field \
      --pg-version=14.0
---

Squawk detects ADD COLUMN NOT NULL without DEFAULT, DROP INDEX without CONCURRENTLY, ALTER TABLE RENAME, and other operations requiring AccessExclusiveLock. Compared to manual review, Squawk finds 90% of problems 10 times faster.

For automated testing of the migration itself, spin up a copy of the production database (via pg_dump/restore or logical replication) and run the migration there before applying to prod.

What our work includes

When ordering a blue-green migration, we provide:

1. Audit of current schema – identify bottlenecks, dependencies, table sizes.
2. Migration plan – decompose into Expand-Contract phases with time estimates.
3. Migration implementation – write SQL scripts, backfill procedures, rollback scripts.
4. Testing – run on staging with production-scale data.
5. Documentation – description of the process, runbook for operators, postmortem template.
6. Post-deployment support – error monitoring, observe database metrics.

Implementation process

For simple changes (adding nullable column, new index) – 2-3 days including testing. For complex table restructuring with tens of millions of rows – up to 2-3 weeks including backfill and monitoring.

We will evaluate your project within one business day. Contact us – we will find the optimal migration plan with a zero-downtime guarantee. Our experience: dozens of successful PostgreSQL migrations in highload projects. Reach out to discuss your task.

According to PostgreSQL documentation, ALTER TABLE with NOT VALID minimizes locks (see ALTER TABLE in PostgreSQL Documentation).