Prisma Migrate and CREATE INDEX CONCURRENTLY: edit the migration before deploy

Prisma Migrate generates a regular PostgreSQL index build:

CREATE INDEX "User_email_idx" ON "User"("email");

On a live table, that statement blocks INSERT, UPDATE, and DELETE until the index build finishes. Prisma’s official pgfence integration guide recommends editing the generated SQL to add CONCURRENTLY and ensuring the migration runs outside a transaction block.

The reviewable workflow is:

npx prisma migrate dev --create-only --name add_user_email_index

Open the new prisma/migrations/<timestamp>_add_user_email_index/migration.sql file and change the index statement:

SET lock_timeout = '2s';
SET statement_timeout = '30min';

CREATE INDEX CONCURRENTLY "User_email_idx" ON "User"("email");

Then check the migration before deployment:

npx @flvmnt/[email protected] analyze \
  --format prisma \
  --ci \
  --max-risk medium \
  prisma/migrations/**/migration.sql

Only after review and staging rehearsal should the deployment job run:

npx prisma migrate deploy

Why CONCURRENTLY changes the deployment contract

PostgreSQL documents two very different index builds.

A regular CREATE INDEX takes a SHARE lock on the table. Plain reads continue. Writes wait. A large table can therefore turn a harmless-looking generated migration into a long write outage.

CREATE INDEX CONCURRENTLY performs more work and usually takes longer, but it does not hold a lock that blocks normal inserts, updates, or deletes. It uses several internal transactions and two table scans. That design is why PostgreSQL refuses to run it inside a transaction block. The full behavior and failure modes are in the PostgreSQL CREATE INDEX reference.

This is not a formatting detail. Adding CONCURRENTLY changes how the migration must be executed.

What “outside a transaction” means

The migration SQL must not wrap the concurrent build in BEGIN and COMMIT:

BEGIN;
CREATE INDEX CONCURRENTLY "User_email_idx" ON "User"("email");
COMMIT;

PostgreSQL rejects that every time.

The migration runner also matters. A file with no visible BEGIN can still fail if the tool applying it wraps the file in a transaction. Prisma’s own integration guide tells you to ensure the migration runs outside a transaction block. Verify that behavior against the exact Prisma version and deployment command in your pipeline, then rehearse it against staging before production.

pgfence can prove whether the SQL file contains an explicit transaction block. Static file analysis cannot prove what every external runner adds around that file at runtime. Treat runner behavior as a separate deployment check.

Keep concurrent indexes in focused migrations

Do not mix a concurrent index with unrelated DDL in the same migration file. A focused file is easier to rehearse, retry, and reason about if the build fails.

This also matters because a failed concurrent build can leave an invalid index behind. IF NOT EXISTS is not a repair mechanism. PostgreSQL only checks whether a relation with that name exists, not whether the existing index is valid. Before retrying, inspect pg_index.indisvalid, drop the invalid index concurrently, and run the build again.

SELECT
  n.nspname AS schema_name,
  c.relname AS index_name,
  i.indisvalid,
  i.indisready
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname = 'User_email_idx';

If it is invalid:

DROP INDEX CONCURRENTLY IF EXISTS "User_email_idx";

Then retry the original concurrent build outside a transaction.

Put the safety check before deploy

Prisma’s generated SQL is reviewable code. Check it in the pull request, not in the production job after the migration has already started.

- name: Check Prisma migration safety
  run: >-
    npx --yes @flvmnt/[email protected] analyze
    --format prisma
    --ci
    --max-risk medium
    prisma/migrations/**/migration.sql

- name: Apply migrations
  run: npx prisma migrate deploy

This catches the regular CREATE INDEX, explicit transaction wrappers, missing timeouts, and other lock-heavy statements before migrate deploy gets production credentials.

For the failure and retry path, see CREATE INDEX CONCURRENTLY IF NOT EXISTS does not fix a broken index. For the broader Prisma setup, see Prisma now documents pgfence for pre-deploy migration checks.