Managing index locking during zero-downtime migrations

Question

When migrating a small application using Prisma, a multi-phase deployment is often required to ensure high availability. While the prisma migrate dev workflow allows for manual SQL review, the actual execution of schema changes can still trigger table locks depending on the database engine.
The primary concern is how Prisma handles the creation of new indices on large tables. If the generated migration script uses a standard CREATE INDEX statement, the database may lock the table for writes until the operation completes, potentially causing downtime that contradicts the goal of a zero-downtime transition.
Does Prisma support the native generation of CONCURRENTLY index creation for PostgreSQL within its migration files? Are there specific configurations in the schema.prisma that prevent the engine from locking tables during these operations?

Tasadduq Burney · Answer

Prisma does not emit CREATE INDEX CONCURRENTLY By default, Prisma Migrate generates plain CREATE INDEX (or CREATE UNIQUE INDEX) statements for PostgreSQL. These acquire a SHARE lock that blocks writes until the index build finishes. There is no schema.prisma attribute or datasource setting that switches index creation to a concurrent mode. Manual workflow for zero‑downtime index creation Generate the migration file without applying it: npx prisma migrate dev --create-only Edit the SQL: Open the generated migration.sql in prisma/migrations/_/ and replace every CREATE INDEX (or CREATE UNIQUE INDEX) with CREATE INDEX CONCURRENTLY. Remove transaction wrappers: PostgreSQL does not allow CREATE INDEX CONCURRENTLY inside a transaction block. Delete the leading BEGIN; and trailing COMMIT; lines that Prisma adds. Apply the edited migration: npx prisma migrate deploy Considerations Concurrent builds take longer and use more I/O because they scan the table twice. If the build fails, an invalid index remains; drop it before retrying. This approach is PostgreSQL‑specific; other engines use their own online DDL syntax (e.g., MySQL ALGORITHM=INPLACE).

Managing index locking during zero-downtime migrations

1 answer

Prisma does not emit CREATE INDEX CONCURRENTLY

Manual workflow for zero‑downtime index creation

Considerations

Your perspective belongs here.

0 question comments