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 CREATE INDEX CONCURRENTLY in PostgreSQL, which builds the index without blocking concurrent writes. Note that CONCURRENTLY cannot run inside a transaction block, so most migration tools need special handling for it.

Prisma generates migrations from schema diffs 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.

Use the expand-contract pattern: add the new column, backfill existing data, deploy code that writes to both columns, switch reads to the new column, then drop the old column in a separate migration. Never rename directly in production.

Updating all rows in one transaction holds locks for the duration of the update. Batch the updates into chunks of a few thousand rows using FOR UPDATE SKIP LOCKED, committing between batches to release locks.

No, deployed migrations are immutable. Editing them causes schema drift between environments because other databases already applied the original version. Always create a new forward migration to correct issues.