Zero-Downtime PostgreSQL Migrations on Kubernetes

The Problem

You push a migration that adds a NOT NULL column to a table with 50 million rows. PostgreSQL acquires an ACCESS EXCLUSIVE lock. Every query blocks. Your API returns 504s for 3 minutes.

The Rules

1. Never add a NOT NULL column without a default — PostgreSQL 11+ handles this without rewriting the table.
2. Never drop a column in the same release that stops using it — deploy code that ignores the column first.
3. Never rename a column — add new, migrate data, update code, drop old. Three releases.
4. Always add indexes CONCURRENTLY — CREATE INDEX CONCURRENTLY doesn't lock the table.
5. Separate migration execution from application startup.

Flyway + Init Containers

apiVersion: apps/v1
kind: Deployment
spec:
  template:
    spec:
      initContainers:
        - name: migrate
          image: flyway/flyway:10
          args: ["migrate"]
          env:
            - name: FLYWAY_URL
              value: jdbc:postgresql://db:5432/myapp
      containers:
        - name: api
          image: myapp:latest

The init container runs Flyway, waits for success, then the API starts.

Safe Migration Patterns

Adding a Column

-- Safe: nullable column, no lock
ALTER TABLE orders ADD COLUMN discount_code VARCHAR(50);
 
-- Safe in PG 11+: non-null with default
ALTER TABLE orders ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'pending';

Adding an Index

-- Safe: non-blocking
CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status);

Renaming a Column (3-Phase)

Phase 1: Add new column, backfill data Phase 2: Deploy code that writes to both, reads from new Phase 3: Drop old column in next release

The Checklist

• Does this migration acquire ACCESS EXCLUSIVE lock?
• Are all indexes created CONCURRENTLY?
• Is it backward compatible with currently deployed code?
• Tested on production-sized dataset?
• Is there a rollback migration?

Following these rules, we ran 200+ migrations on a 500GB database without downtime.