database-migrations

Add the column as nullable or with a default value, which PostgreSQL 11+ applies instantly without rewriting the table. Never add NOT NULL without a default on an existing table, since that locks the table and rewrites every row.

Use the expand-contract pattern instead of a direct rename. Add the new column, backfill existing rows, deploy code that writes to both columns, switch reads to the new column, then drop the old column in a separate migration.

Prisma generates migrations from schema files with `prisma migrate dev`, Drizzle uses `drizzle-kit generate`, and Kysely uses explicit up/down migration files via kysely-ctl. All three support custom SQL for operations like concurrent indexes.

A plain CREATE INDEX blocks writes on large tables while the index builds. Use CREATE INDEX CONCURRENTLY instead, which allows concurrent writes, though it cannot run inside a transaction block and may need special handling in migration tools.

No, schema and data migrations should be separate. Mixing DDL and DML in one migration creates long transactions, makes rollbacks harder, and increases lock duration on production tables.

Update rows in batches rather than one large transaction. In PostgreSQL, loop over batches with LIMIT and FOR UPDATE SKIP LOCKED, committing between batches; in Django, use bulk_update with a batch size inside a RunPython data migration.