Zero-Downtime Database Migrations in Production (2026)

A DROP COLUMN that takes 2 seconds in development can lock a production table for 4 minutes in production. Here's how senior engineers handle migrations without downtime.

The core problem

Your database and app code deploy independently. During deployment, two versions of your code run simultaneously. If your migration breaks compatibility with the old version, you have an outage.

Rule: every migration must be backward compatible.

drizzle-kit push vs generate+migrate

Never use drizzle-kit push in production — it applies changes directly without migration files.

Always use:

npx drizzle-kit generate
npx drizzle-kit migrate

Safe vs dangerous operations

Safe (no table locks):

• ADD COLUMN (nullable)
• CREATE TABLE
• CREATE INDEX CONCURRENTLY
• ADD CONSTRAINT NOT VALID

Dangerous (locks entire table):

• DROP COLUMN
• RENAME COLUMN
• ALTER COLUMN SET NOT NULL
• Changing column type

The expand-contract pattern

How to rename a column across 3 phases and 3 separate deploys.

Phase 1 — Add new column:

ALTER TABLE users ADD COLUMN display_name text;

Deploy code that reads display_name, writes to both columns.

Phase 2 — Backfill:

UPDATE users SET display_name = name WHERE display_name IS NULL;

Deploy code that only uses display_name.

Phase 3 — Drop old column:

ALTER TABLE users DROP COLUMN name;

Adding NOT NULL without locking

ALTER TABLE SET NOT NULL blocks all reads and writes. The safe way:

Step 1:

ALTER TABLE users ADD COLUMN display_name text;

Step 2 — Backfill in batches:

UPDATE users SET display_name = name
WHERE id IN (SELECT id FROM users WHERE display_name IS NULL LIMIT 500);

Repeat with 50ms pauses until done.

Step 3:

ALTER TABLE users
ADD CONSTRAINT users_display_name_not_null
CHECK (display_name IS NOT NULL) NOT VALID;

ALTER TABLE users VALIDATE CONSTRAINT users_display_name_not_null;

CREATE INDEX CONCURRENTLY

-- Locks the table — never in production
CREATE INDEX idx_users_email ON users(email);

-- Non-blocking — always use this
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

CI/CD rule

1. Run migrations FIRST
2. Deploy new code AFTER

Never simultaneously. Never deploy first.

---

Full guide with Drizzle ORM examples: https://stacknotice.com/blog/saas-database-migrations-zero-downtime