Zero-Downtime Database Migrations in Production (2026)

ever use drizzle-kit push in production. It applies schema changes directly to the target database without generating migration files, making rollbacks impossible and audit trails nonexistent. Always generate explicit migration files.

npx drizzle-kit generate
npx drizzle-kit migrate

Step 1: Add the new column as nullable

ALTER TABLE tenant_accounts ADD COLUMN primary_contact TEXT;

PostgreSQL allows this operation without blocking concurrent writes because it only modifies the system catalog metadata. Existing rows receive NULL for the new column.

Step 2: Update application logic Deploy application code that writes to both columns but reads exclusively from primary_contact. This ensures data consistency while maintaining backward compatibility.

// Drizzle ORM schema definition
import { pgTable, text, varchar } from 'drizzle-orm/pg-core';

export const tenantAccounts = pgTable('tenant_accounts', {
  id: varchar('id', { length: 26 }).primaryKey(),
  legacyEmail: text('legacy_email'),
  primaryContact: text('primary_contact'),
  updatedAt: timestamp('updated_at').defaultNow().$onUpdate(() => new Date()),
});

// Dual-write insertion logic
async function createTenantAccount(db: Database, payload: TenantPayload) {
  await db.insert(tenantAccounts).values({
    id: generateId(),
    legacyEmail: payload.email,
    primaryContact: payload.email, // Write to both
    updatedAt: new Date(),
  });
}

Phase 2: Backfill & Validate

Once the new column exists and dual-write logic is active, you must populate historical data. Running a single UPDATE on a large table will generate massive WAL (Write-Ahead Log) volume, trigger autovacuum storms, and potentially exhaust connection limits.

Step 1: Batch backfill with controlled pacing

-- Execute repeatedly until affected rows = 0
UPDATE tenant_accounts 
SET primary_contact = legacy_email 
WHERE id IN (
  SELECT id FROM tenant_accounts 
  WHERE primary_contact IS NULL 
  LIMIT 500
);

Insert a 50–100ms pause between batches. This prevents lock escalation, reduces WAL pressure, and leaves headroom for production traffic.

Step 2: Enforce data integrity without blocking PostgreSQL's SET NOT NULL requires an exclusive lock. Instead, use a CHECK constraint with the NOT VALID clause. This validates new writes immediately but skips scanning existing rows.

ALTER TABLE tenant_accounts
ADD CONSTRAINT chk_primary_contact_not_null 
CHECK (primary_contact IS NOT NULL) NOT VALID;

Step 3: Validate existing data safely

ALTER TABLE tenant_accounts VALIDATE CONSTRAINT chk_primary_contact_not_null;

VALIDATE CONSTRAINT scans the table in shared mode, checking each row against the constraint without blocking reads or writes. If any row violates the constraint, the operation fails safely, leaving the schema unchanged.

Phase 3: Contract (Remove Legacy)

After confirming the new column is fully populated, validated, and exclusively used by the application, remove the legacy column.

Step 1: Remove dual-write logic Deploy application code that only reads and writes to primary_contact.

Step 2: Drop the legacy column

ALTER TABLE tenant_accounts DROP COLUMN legacy_email;

At this point, the table is small enough (or traffic low enough) that the exclusive lock duration is negligible. The migration is complete.

Architecture Rationale

• Separate Deploys: Each phase requires an independent deployment. This isolates risk and allows verification at every step.
• Nullable First: Adding columns as nullable avoids immediate validation overhead and prevents write failures during the transition window.
• Batch Backfills: Controlled pacing prevents resource exhaustion and maintains SLA compliance during data migration.
• Constraint Validation: NOT VALID + VALIDATE decouples schema enforcement from table scanning, enabling zero-downtime integrity checks.
• CI/CD Ordering: Migrations must execute before application code deployment. Running them simultaneously guarantees dual-version incompatibility.

Pitfall Guide

1. Using drizzle-kit push in Production

Explanation: push applies schema diffs directly without generating migration files. It bypasses version control, makes rollbacks impossible, and provides no audit trail. Fix: Always use generate to create timestamped SQL files, then migrate to apply them. Store migration files in your repository.

2. Deploying Application Code Before Schema Changes

Explanation: If new code expects a column that doesn't exist yet, or tries to read a renamed column, queries fail immediately. Rolling updates guarantee both versions run concurrently. Fix: Enforce strict CI/CD ordering: run migrations first, verify success, then deploy application code. Use deployment gates or pipeline stages to prevent race conditions.

3. Skipping Batch Backfills on Large Tables

Explanation: A single UPDATE on a table with millions of rows generates excessive WAL traffic, triggers aggressive autovacuum, and can exhaust disk I/O or connection pools. Fix: Implement bounded batch updates with explicit LIMIT clauses and inter-batch delays. Monitor pg_stat_activity and pg_stat_progress_vacuum during execution.

4. Forgetting CONCURRENTLY for Index Creation

Explanation: Standard CREATE INDEX acquires a ShareLock, blocking writes. On high-traffic tables, this causes immediate request queuing and timeout cascades. Fix: Always use CREATE INDEX CONCURRENTLY. It builds the index in multiple passes, allowing concurrent DML operations. Accept the longer build time in exchange for zero downtime.

5. Applying SET NOT NULL Directly

Explanation: ALTER COLUMN SET NOT NULL requires an AccessExclusiveLock, halting all table operations until the entire table is scanned. Fix: Use the CHECK (col IS NOT NULL) NOT VALID pattern followed by VALIDATE CONSTRAINT. This enforces new writes immediately while scanning existing data safely.

6. Assuming Dual-Write Logic is Permanent

Explanation: Leaving dual-write code in production increases write latency, complicates debugging, and creates technical debt. It should only exist during the transition window. Fix: Schedule a follow-up deployment to remove dual-write logic immediately after Phase 2 completes. Track cleanup tasks in your deployment checklist.

7. Ignoring Connection Pool Limits During Migrations

Explanation: Migration scripts and batch backfills consume database connections. If your application pool is already near capacity, migrations will queue or fail, causing deployment timeouts. Fix: Temporarily increase pool limits during migration windows, or run migrations against a read replica with promotion logic. Monitor pg_stat_activity to ensure headroom exists.

Production Bundle

Action Checklist

• Generate migration files using drizzle-kit generate instead of push
• Verify all new columns are added as nullable before enforcing constraints
• Implement dual-write logic in application code during Phase 1 deployment
• Execute batch backfills with explicit LIMIT and inter-batch delays
• Apply CHECK ... NOT VALID constraints before running VALIDATE CONSTRAINT
• Use CREATE INDEX CONCURRENTLY for all production index additions
• Enforce CI/CD pipeline order: migrations execute before application deployment
• Schedule dual-write cleanup deployment immediately after backfill completion

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
Table < 100k rows	Standard ALTER + single deploy	Lock duration is negligible; overhead of phased approach outweighs benefit	Low (faster deployment)
Table > 1M rows, high traffic	Expand-Contract + batch backfills	Prevents exclusive locks, maintains SLA, allows safe rollback	Medium (requires 3 deploys)
Adding non-nullable column	NOT VALID constraint + VALIDATE	Enforces integrity without blocking writes during scan	Low (zero downtime)
Renaming column	Expand-Contract with dual-read/write	Maintains backward compatibility across rolling updates	Medium (temporary code complexity)
Adding index to active table	CREATE INDEX CONCURRENTLY	Avoids ShareLock that blocks DML operations	Low (slightly longer build time)

Configuration Template

// drizzle.config.ts
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './src/db/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
  migrations: {
    table: '__drizzle_migrations',
    schema: 'public',
  },
  verbose: true,
  strict: true,
});

# .github/workflows/deploy.yml (CI/CD ordering example)
name: Deploy with Zero-Downtime Migrations

on:
  push:
    branches: [main]

jobs:
  run-migrations:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-node@v4
        with:
          node-version: 20
      - run: npm ci
      - name: Apply Database Migrations
        run: npx drizzle-kit migrate
        env:
          DATABASE_URL: ${{ secrets.PROD_DATABASE_URL }}

  deploy-application:
    needs: run-migrations
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - name: Deploy Application
        run: ./scripts/deploy.sh
        env:
          DEPLOY_TOKEN: ${{ secrets.DEPLOY_TOKEN }}

Quick Start Guide

1. Initialize Drizzle Kit: Run npx drizzle-kit init to generate drizzle.config.ts. Configure your PostgreSQL connection string and schema path.
2. Define Schema: Create your table definitions using drizzle-orm/pg-core. Ensure new columns default to nullable during transition phases.
3. Generate Migration: Modify your schema, then run npx drizzle-kit generate. Review the generated SQL file to verify it contains only safe operations.
4. Apply to Staging: Run npx drizzle-kit migrate against your staging database. Verify dual-write logic functions correctly with both old and new application versions.
5. Promote to Production: Execute the same migration command in your CI/CD pipeline before deploying application code. Monitor pg_stat_activity during execution to confirm no lock contention occurs.