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 TABLECREATE INDEX CONCURRENTLYADD CONSTRAINT NOT VALID
Dangerous (locks entire table):
DROP COLUMNRENAME COLUMNALTER 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
