database-migrations

Add the column as nullable or with a default value, which PostgreSQL 11+ applies instantly without rewriting the table. Avoid NOT NULL without a default on existing tables, since that forces a full table rewrite and lock.

Use the expand-contract pattern: add the new column, backfill existing rows, deploy code that writes to both columns, then drop the old column in a later migration. Never rename directly on a live production table.

Prisma uses migrate dev and migrate deploy with a declarative schema. Drizzle generates migrations via drizzle-kit generate. Kysely uses kysely-ctl with explicit up and down TypeScript migration files, giving the most manual control.

A plain CREATE INDEX blocks writes while the index builds on large tables. Use CREATE INDEX CONCURRENTLY instead, which allows concurrent writes, but note it cannot run inside a transaction block.

No. Deployed migrations are immutable; editing them causes schema drift between environments. Always create a new forward migration to correct or extend a change that already ran.

Run the backfill as a separate data migration using batched updates, for example loops of 5000-10000 rows with FOR UPDATE SKIP LOCKED in PostgreSQL or Django bulk_update in chunks. Avoid single-transaction updates across the whole table.