Zero-Downtime Postgres Migrations with Drizzle ORM

Drizzle ORM generates migrations automatically from schema diffs. That's convenient right up until you run a generated migration on a live database with 10 million rows and your API starts timing out. Here's the mental model and the patterns that actually prevent downtime.

The Core Problem

Postgres takes locks when you run DDL. Most of the dangerous ones are ACCESS EXCLUSIVE locks that block all reads and writes for the duration. On a small table this is imperceptible. On a large table with a long-running migration, you're looking at an outage.

Drizzle's generated SQL is correct but not safe-by-default for large tables. You need to understand what it generates and when to override it.

Rule 1: Additive Changes Are Safe

Adding a nullable column, adding a new table, adding an index concurrently — these are safe. The dangerous operations are:

• Adding a NOT NULL column without a default
• Renaming a column
• Dropping a column your app still reads
• Adding an index non-concurrently on a large table
• Changing a column type

For everything dangerous, the pattern is the same: multi-step deploy, not a single migration.

Pattern 1: Adding a NOT NULL Column

Drizzle schema:

// drizzle/schema.ts
export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: text('email').notNull(),
  // Adding this:
  tier: text('tier').notNull().default('free'),
});

What Drizzle generates:

ALTER TABLE users ADD COLUMN tier text NOT NULL DEFAULT 'free';

On Postgres 11+, adding a column with a constant DEFAULT is safe — Postgres stores the default in the catalog and doesn't rewrite the table. But if your default is a function call (DEFAULT gen_random_uuid(), DEFAULT now()), Postgres rewrites every row. Drizzle doesn't warn you about this distinction.

Always check the generated SQL before running it on prod:

bun drizzle-kit generate
cat drizzle/migrations/0042_add_tier.sql
# Read it. Actually read it.

Pattern 2: Column Rename Without Downtime

Drizzle has no native rename support that maps cleanly to zero-downtime. If you change firstName to first_name in your schema and generate a migration, you get:

ALTER TABLE users RENAME COLUMN "firstName" TO first_name;

This runs fast but your deployed app is still reading firstName. You have a gap between migration and deploy.

The zero-downtime sequence:

Step 1 — Deploy a migration that adds the new column and creates a sync trigger:

-- 0043_rename_first_name_step1.sql
ALTER TABLE users ADD COLUMN first_name text;

-- Backfill in batches (do this as a separate script, not inline)
-- See backfill pattern below

CREATE OR REPLACE FUNCTION sync_first_name()
RETURNS TRIGGER AS $$
BEGIN
  NEW.first_name := NEW."firstName";
  NEW."firstName" := NEW.first_name;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_sync_first_name
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION sync_first_name();

Step 2 — Deploy app code that writes to both columns, reads from first_name.

Step 3 — Backfill first_name from firstName for existing rows.

Step 4 — Remove trigger, drop old column.

This is more work than RENAME COLUMN. It's also the only way that doesn't have a window where your app can crash.

Pattern 3: Batched Backfills

Never backfill in a single UPDATE users SET first_name = "firstName". On a large table this holds a lock for minutes and can cause replication lag that cascades into read replica failures.

Use a script that processes in chunks:

// scripts/backfill-first-name.ts
import { db } from '../src/db';
import { users } from '../src/schema';
import { sql, isNull, lt } from 'drizzle-orm';

async function backfill() {
  const BATCH_SIZE = 2000;
  let lastId: string | null = null;
  let totalUpdated = 0;

  while (true) {
    const batch = await db
      .select({ id: users.id })
      .from(users)
      .where(
        sql`${users.firstName} IS NOT NULL AND ${users.firstName} != COALESCE(${users.firstName}, '') AND (${
          lastId ? sql`${users.id} > ${lastId}` : sql`TRUE`
        })`
      )
      .orderBy(users.id)
      .limit(BATCH_SIZE);

    if (batch.length === 0) break;

    const ids = batch.map(r => r.id);
    await db
      .update(users)
      .set({ firstName: sql`"firstName"` })
      .where(sql`id = ANY(${ids})`);

    lastId = ids[ids.length - 1];
    totalUpdated += batch.length;
    console.log(`Backfilled ${totalUpdated} rows...`);

    // Brief pause to reduce lock contention
    await Bun.sleep(50);
  }

  console.log(`Done. Total: ${totalUpdated}`);
}

backfill().catch(console.error);

Run this as a one-off against prod, not as part of the migration file.

Pattern 4: Concurrent Index Creation

Drizzle generates:

CREATE INDEX idx_users_email ON users(email);

This takes a full table lock. For any table over ~100k rows, use:

CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

Drizzle supports this via the schema definition:

export const usersEmailIdx = index('idx_users_email')
  .on(users.email)
  .concurrently();

But always verify the generated SQL includes CONCURRENTLY. It's easy to miss.

Pattern 5: Drizzle Custom Migration Files

When Drizzle generates something unsafe, don't edit the generated file — create a custom migration alongside it:

drizzle/migrations/
  0044_add_index.sql         <- generated, don't touch
  0044_add_index_custom.sql  <- your safe version

Then in your migration runner config, you can either skip the generated file or use drizzle-kit in manual mode:

// drizzle.config.ts
export default defineConfig({
  schema: './src/schema.ts',
  out: './drizzle/migrations',
  dialect: 'postgresql',
  migrations: {
    // Run custom SQL files first
    prefix: 'timestamp',
  },
});

For teams doing this regularly, the pattern that scales: generate the migration, immediately review the SQL, annotate it with a -- SAFE or -- MANUAL REVIEW comment at the top, and require that annotation in code review before a migration runs.

What to Put in CI

# Check for dangerous patterns in new migration files
new_migrations=$(git diff --name-only HEAD~1 HEAD -- 'drizzle/migrations/*.sql')
for f in $new_migrations; do
  if grep -qE 'RENAME COLUMN|DROP COLUMN|ALTER COLUMN.*TYPE|NOT NULL' "$f"; then
    echo "WARNING: $f may require zero-downtime review"
    exit 1
  fi
done

This won't catch everything but it forces a human to sign off on anything that could be destructive.

Summary

Drizzle's migration generation is a productivity tool, not a deployment safety system. The safe production workflow:

1. Generate the migration
2. Read the SQL
3. For additive/safe changes: run it
4. For renames, type changes, or backfills: write a multi-step deployment plan
5. Always use CONCURRENTLY for new indexes
6. Batch backfills externally, never inline in migration SQL