database-migration-safety

Add the column as nullable with no default, which is a metadata-only operation in PostgreSQL. For defaults on PostgreSQL 11+, ADD COLUMN DEFAULT is also instant. On older versions, use the expand-contract pattern with a backfill in batches.

Use CREATE INDEX CONCURRENTLY in PostgreSQL, which builds the index without blocking reads or writes. It cannot run inside a transaction, so disable transaction wrapping in your migration tool for that statement.

Expand-contract splits a breaking schema change into three deployments: add the new structure alongside the old, backfill data and switch reads, then remove the old structure. Each phase is a separate migration and deployment.

Prisma wraps migrations in transactions by default, which prevents CREATE INDEX CONCURRENTLY from running. Use a raw SQL migration or the appropriate escape hatch to execute concurrent index creation outside a transaction.

ALTER COLUMN SET NOT NULL scans the entire table to validate existing rows, holding an AccessExclusiveLock during the scan. Instead, add a CHECK constraint as NOT VALID, validate it separately, then set NOT NULL, which is instant in PostgreSQL 12+.

Avoid operations that rewrite or fully scan large tables, such as ALTER COLUMN TYPE, ADD CONSTRAINT without NOT VALID, or plain CREATE INDEX. If lock duration exceeds 5 seconds in staging tests with production-sized data, use expand-contract or schedule off-peak.