codeflash-agent/plugin/languages/javascript/references/prisma-performance.md

Prisma Performance Antipatterns

Prisma is a cross-domain performance concern. A single Prisma misuse can simultaneously cause CPU waste, memory bloat, async bottlenecks, bundle inflation, and structural coupling. This reference catalogs the most common antipatterns by domain, with before/after fixes.

CPU

N+1 queries

The most common Prisma perf issue. Happens when fetching related records in a loop instead of using include or a single findMany.

// BAD: N+1 — one query per user
const users = await prisma.user.findMany();
for (const user of users) {
  const posts = await prisma.post.findMany({ where: { authorId: user.id } });
  user.posts = posts;
}

// GOOD: single query with include
const users = await prisma.user.findMany({
  include: { posts: true },
});

Signal in profiling: CPU hotspot in Prisma query engine; many small queries in --trace-gc output; sequential await pattern visible in async profile.

Over-fetching fields (no select)

findMany returns all columns by default. On wide tables (30+ columns), this wastes CPU on serialization/deserialization of unused fields.

// BAD: fetches all 30 columns when you need 2
const users = await prisma.user.findMany();
const names = users.map(u => u.name);

// GOOD: fetch only what you need
const users = await prisma.user.findMany({
  select: { id: true, name: true },
});

Signal: CPU time in Prisma result deserialization; heap profile shows large object arrays with mostly-null fields.

Raw queries for hot paths

Prisma's query builder adds overhead per query (AST construction, validation, serialization). For hot paths called thousands of times, $queryRaw eliminates this.

// Prisma query builder overhead (~2-5ms per query)
const result = await prisma.user.findFirst({ where: { email } });

// Raw SQL (~0.5-1ms per query)
const [result] = await prisma.$queryRaw`
  SELECT id, name, email FROM "User" WHERE email = ${email} LIMIT 1
`;

When to use: Only when profiling shows Prisma query construction is a measurable fraction of the query's total time. Not worth it for queries that run once.

Memory

Unbounded findMany without pagination

Loading an entire table into memory is the #1 Prisma memory issue.

// BAD: loads entire table into memory
const allOrders = await prisma.order.findMany();

// GOOD: cursor-based pagination
let cursor: string | undefined;
do {
  const batch = await prisma.order.findMany({
    take: 1000,
    ...(cursor ? { skip: 1, cursor: { id: cursor } } : {}),
    orderBy: { id: 'asc' },
  });
  await processBatch(batch);
  cursor = batch[batch.length - 1]?.id;
} while (cursor);

Signal: Heap snapshot shows large arrays of Prisma model objects; RSS spikes during query execution.

Eager-loading deep relations

Nested include can explode result size exponentially.

// BAD: 3-level deep include — if User has 10 posts, each post has 50 comments,
// each comment has 5 reactions → 2,500 reaction objects per user
const users = await prisma.user.findMany({
  include: {
    posts: {
      include: {
        comments: {
          include: { reactions: true },
        },
      },
    },
  },
});

// GOOD: flatten with separate queries, load only what's needed
const users = await prisma.user.findMany({ include: { posts: true } });
const postIds = users.flatMap(u => u.posts.map(p => p.id));
const comments = await prisma.comment.findMany({
  where: { postId: { in: postIds } },
  select: { id: true, content: true, postId: true },
});

Signal: Heap profile shows deeply nested objects; GC pressure from short-lived intermediate objects during result construction.

Forgotten $disconnect in scripts/CLIs

In long-running scripts or CLI tools, not disconnecting leaves the connection pool alive, preventing graceful shutdown and leaking connections.

// BAD: connection pool stays open
async function main() {
  const data = await prisma.user.findMany();
  process.exit(0); // connections not properly closed
}

// GOOD: explicit disconnect
async function main() {
  try {
    const data = await prisma.user.findMany();
  } finally {
    await prisma.$disconnect();
  }
}

Async

Sequential queries that could be parallel

Independent Prisma queries awaited sequentially when they have no data dependency.

// BAD: sequential — total time = sum of all queries
const users = await prisma.user.count();
const posts = await prisma.post.count();
const comments = await prisma.comment.count();

// GOOD: parallel — total time = max of all queries
const [users, posts, comments] = await Promise.all([
  prisma.user.count(),
  prisma.post.count(),
  prisma.comment.count(),
]);

Signal: Async profile shows sequential await chain; wall-clock time much higher than sum of individual query times would suggest for parallel execution.

Missing $transaction for batch writes

Multiple related writes without a transaction are both slower (individual round-trips) and unsafe (partial failure).

// BAD: 100 individual writes, 100 round-trips
for (const item of items) {
  await prisma.order.create({ data: item });
}

// GOOD: batched transaction, 1 round-trip
await prisma.$transaction(
  items.map(item => prisma.order.create({ data: item }))
);

// ALSO GOOD: createMany for simple inserts (even faster, single query)
await prisma.order.createMany({ data: items });

Connection pool exhaustion

Default pool size is 5 connections (connection_limit in connection string). Under concurrency, queries queue up waiting for a connection.

// In DATABASE_URL or programmatic config:
// BAD: default pool (5 connections)
DATABASE_URL="postgresql://user:pass@host/db"

// GOOD: sized for workload (2-3x expected concurrent queries)
DATABASE_URL="postgresql://user:pass@host/db?connection_limit=20"

Signal: Query latency spikes under load; Prisma logs show Timed out fetching a new connection from the connection pool.

Interactive transactions holding connections too long

$transaction with an interactive callback holds a connection for the entire callback duration. Long-running logic inside the callback starves the pool.

// BAD: holds connection while doing CPU-heavy work
await prisma.$transaction(async (tx) => {
  const data = await tx.record.findMany();
  const processed = heavyComputation(data);  // 500ms CPU work holding a connection
  await tx.record.updateMany({ data: processed });
});

// GOOD: fetch, process outside transaction, then write
const data = await prisma.record.findMany();
const processed = heavyComputation(data);
await prisma.$transaction(async (tx) => {
  await tx.record.updateMany({ data: processed });
});

Bundle

Generated client size

@prisma/client generates a client tailored to your schema. For large schemas (50+ models), the generated client can be 500 KiB+, impacting bundle size in serverless or edge deployments.

Mitigations:

• Use prisma generate --no-engine for edge runtimes (Prisma Accelerate / Data Proxy)
• Evaluate if all models are needed — split schemas for microservices
• For serverless: the Prisma engine binary (~15 MiB) is the real cost; use Prisma Accelerate to offload query execution

Barrel re-export pulling full client

// BAD: barrel re-exports everything — bundler can't tree-shake Prisma enums/types
// lib/index.ts
export * from '@prisma/client';

// GOOD: import directly
import { PrismaClient } from '@prisma/client';

Structure

Circular model references

Prisma schema supports circular relations (User → Post → Comment → User). Code that mirrors these relations with eager loading creates circular data structures that break JSON serialization and cause stack overflows.

// BAD: circular eager loading
const user = await prisma.user.findFirst({
  include: { posts: { include: { author: true } } }, // author → back to user
});
JSON.stringify(user); // circular reference error or infinite recursion

// GOOD: break the cycle with select
const user = await prisma.user.findFirst({
  include: {
    posts: {
      select: { id: true, title: true }, // don't include author back-ref
    },
  },
});

Prisma Client as a singleton

Instantiating PrismaClient in multiple modules wastes connections and memory. Use a singleton pattern.

// BAD: new client per import
// utils/db.ts
export const prisma = new PrismaClient();
// services/user.ts
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient(); // second connection pool!

// GOOD: singleton
// lib/prisma.ts
import { PrismaClient } from '@prisma/client';
const globalForPrisma = globalThis as unknown as { prisma: PrismaClient };
export const prisma = globalForPrisma.prisma || new PrismaClient();
if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma;

Signal: Multiple PrismaClient instances visible in heap snapshot; connection count exceeds pool size × expected clients.

Schema & Database

These are optimizations at the Prisma schema and database level — often the root cause behind application-level symptoms. A slow findMany might not be an application problem at all; it might be a missing index turning a 2ms lookup into a 500ms full table scan.

Missing indexes

The single most impactful schema optimization. Prisma auto-creates indexes for @id and @unique, but not for fields used in where, orderBy, or relation joins.

// BAD: no index on fields used in queries
model Order {
  id        String   @id @default(cuid())
  userId    String
  status    String
  createdAt DateTime @default(now())
  user      User     @relation(fields: [userId], references: [id])
}

// Query: prisma.order.findMany({ where: { userId, status: 'active' }, orderBy: { createdAt: 'desc' } })
// Without indexes: full table scan + filesort

// GOOD: indexes matching query patterns
model Order {
  id        String   @id @default(cuid())
  userId    String
  status    String
  createdAt DateTime @default(now())
  user      User     @relation(fields: [userId], references: [id])

  @@index([userId, status])       // composite index for the common filter
  @@index([userId, createdAt])    // covers orderBy queries
}

How to detect: Run EXPLAIN ANALYZE on slow queries via $queryRaw:

const plan = await prisma.$queryRaw`
  EXPLAIN ANALYZE
  SELECT * FROM "Order" WHERE "userId" = ${userId} AND "status" = 'active'
  ORDER BY "createdAt" DESC
  LIMIT 50
`;
console.log(plan);
// Look for: "Seq Scan" (bad) vs "Index Scan" (good)
// Look for: "Sort" with high cost (missing index on orderBy column)

Index design rules:

• Column order in composite indexes matters: put equality filters first, range/sort last (@@index([status, createdAt]) not the reverse)
• Covering indexes avoid table lookups: if you always select: { id, name }, an index on [userId, id, name] satisfies the query from the index alone
• Don't over-index: each index slows writes and consumes memory. Index the patterns you actually query.

Relation-level indexes (foreign keys)

Prisma creates foreign key constraints but does not always create indexes on FK columns (depends on the database). PostgreSQL only auto-indexes the referenced side (@id), not the FK side.

model Comment {
  id     String @id @default(cuid())
  postId String
  post   Post   @relation(fields: [postId], references: [id])

  // Without this, JOIN and WHERE on postId is a seq scan:
  @@index([postId])
}

Rule of thumb: Every @relation(fields: [...]) should have a corresponding @@index unless the field is already @unique.

Query plan analysis workflow

When profiling shows a Prisma query as a hotspot:

1. Extract the SQL. Enable Prisma query logging to see the generated SQL:

   const prisma = new PrismaClient({ log: ['query'] });
   

   Or set DATABASE_URL env var with ?log=query.

2. Run EXPLAIN ANALYZE. Copy the SQL and run it with EXPLAIN ANALYZE via $queryRaw or directly in a database client.

3. Read the plan. Key things to look for:

   Plan node	Meaning	Action
   Seq Scan	Full table scan	Add index on filter columns
   Sort (not Index Scan)	In-memory sort	Add index on orderBy column
   Nested Loop with Seq Scan inner	N+1 at DB level	Add index on join column
   Hash Join with large Buckets	Large hash table built in memory	Consider narrowing the join with WHERE
   Bitmap Heap Scan	Index used but many rows	Index is helping, but query returns too many rows — add take/LIMIT

4. Add the index. Update schema.prisma, run prisma migrate dev, re-run EXPLAIN ANALYZE to confirm the plan changed.

Denormalization for read-heavy paths

Normalized schemas are correct but can be slow for read-heavy dashboards/aggregations. Prisma makes denormalization easy with computed/stored fields.

// Normalized: counting comments requires JOIN + COUNT every time
model Post {
  id       String    @id @default(cuid())
  comments Comment[]
}

// Denormalized: precomputed count, updated on write
model Post {
  id           String    @id @default(cuid())
  commentCount Int       @default(0)  // maintained by application logic
  comments     Comment[]
}

// Update the count atomically when adding a comment
await prisma.$transaction([
  prisma.comment.create({ data: { postId, content } }),
  prisma.post.update({
    where: { id: postId },
    data: { commentCount: { increment: 1 } },
  }),
]);

When to denormalize:

• Dashboard/list queries that aggregate across relations (COUNT, SUM)
• Fields displayed on every list item that currently require a JOIN
• Read:write ratio > 100:1 for the aggregated data

When NOT to denormalize:

• Data that changes frequently from multiple sources (hard to keep consistent)
• Counts that need to be exact in real-time (eventual consistency is acceptable for most UIs)

Field type optimization

// BAD: String for enum-like values — no DB-level validation, indexes less efficient
model Order {
  status String // "pending", "active", "shipped"
}

// GOOD: native enum — smaller storage, faster comparison, DB validates values
enum OrderStatus {
  PENDING
  ACTIVE
  SHIPPED
}

model Order {
  status OrderStatus @default(PENDING)
}

// BAD: String for UUIDs — 36 bytes as text
model User {
  id String @id @default(uuid())
}

// GOOD: native UUID type (PostgreSQL) — 16 bytes, faster comparison
// In the datasource, Prisma uses @db.Uuid for PostgreSQL:
model User {
  id String @id @default(uuid()) @db.Uuid
}

// BAD: DateTime stored as String
model Event {
  occurredAt String // "2024-01-15T10:30:00Z"
}

// GOOD: native DateTime — indexable, sortable, smaller storage
model Event {
  occurredAt DateTime @default(now())
}

JSON columns: when to use vs. when to normalize

// JSON is good for: truly unstructured data, user preferences, metadata blobs
model User {
  id       String @id @default(cuid())
  settings Json   // { theme: "dark", notifications: { email: true } }
}

// JSON is bad for: data you filter/sort on — can't index JSON fields efficiently
// BAD: filtering on JSON field (requires full scan or expression index)
// prisma.user.findMany({ where: { settings: { path: ['theme'], equals: 'dark' } } })

// GOOD: extract frequently-queried fields into columns
model User {
  id       String  @id @default(cuid())
  theme    String  @default("light")  // indexable, filterable
  settings Json    // remaining unstructured data
  @@index([theme])
}

Soft deletes and query performance

Soft deletes (deletedAt field) affect every query if not handled properly.

model User {
  id        String    @id @default(cuid())
  deletedAt DateTime?

  // CRITICAL: partial index for active records (PostgreSQL)
  // Prisma doesn't support partial indexes natively — use raw SQL migration:
  // CREATE INDEX idx_user_active ON "User" ("id") WHERE "deletedAt" IS NULL;

  @@index([deletedAt])  // at minimum, index the soft-delete column
}

Problem: Without indexing, every where: { deletedAt: null } filter on every query scans the entire table including deleted rows. If 90% of rows are soft-deleted, you're scanning 10x more data than needed.

Solution: Use Prisma middleware to auto-filter, plus a partial index:

prisma.$use(async (params, next) => {
  if (params.action === 'findMany' || params.action === 'findFirst') {
    params.args.where = { ...params.args.where, deletedAt: null };
  }
  return next(params);
});

Connection infrastructure

Beyond pool sizing (connection_limit), the connection infrastructure matters for production:

PgBouncer / connection poolers:

Prisma opens one connection per pool slot. In serverless (Vercel, AWS Lambda), each cold start creates a new pool — this quickly exhausts DB connections.

# Without pooler: 50 Lambda instances × 5 connections = 250 DB connections
# With PgBouncer in transaction mode: 50 Lambda instances → PgBouncer → 20 DB connections

# Connection string through PgBouncer:
DATABASE_URL="postgresql://user:pass@pgbouncer-host:6432/db?pgbouncer=true"

The ?pgbouncer=true flag tells Prisma to avoid prepared statements (PgBouncer in transaction mode doesn't support them).

Prisma Accelerate (managed connection pooling):

For serverless/edge deployments where you can't run PgBouncer:

import { PrismaClient } from '@prisma/client/edge';
import { withAccelerate } from '@prisma/extension-accelerate';

const prisma = new PrismaClient().$extends(withAccelerate());

// Queries go through Prisma's managed connection pool
const users = await prisma.user.findMany({
  cacheStrategy: { ttl: 60 },  // optional: edge cache for 60s
});

Migration performance

Large table migrations can lock tables and cause downtime.

Dangerous migrations:

• Adding a NOT NULL column without a default to a large table → full table rewrite
• Creating an index on a large table → CREATE INDEX locks writes (use CREATE INDEX CONCURRENTLY in PostgreSQL)
• Renaming a column → Prisma drops and recreates, which can lose data

Safe migration patterns:

-- Adding a column: use default to avoid rewrite
ALTER TABLE "User" ADD COLUMN "role" TEXT DEFAULT 'member';

-- Creating an index: use CONCURRENTLY to avoid locks (add to migration SQL manually)
CREATE INDEX CONCURRENTLY idx_order_status ON "Order" ("status");

-- Renaming: two-step deploy (add new column, migrate data, drop old)
ALTER TABLE "User" ADD COLUMN "full_name" TEXT;
UPDATE "User" SET "full_name" = "name";
-- Deploy code that reads from full_name
-- Then: ALTER TABLE "User" DROP COLUMN "name";

Prisma-specific: Edit the generated migration SQL before running prisma migrate deploy to add CONCURRENTLY or split dangerous operations.

Database-level query optimization checklist

When a Prisma query is slow and application-level fixes (select, pagination, parallel queries) haven't helped:

1. Enable query logging — see the actual SQL Prisma generates
2. EXPLAIN ANALYZE — identify seq scans, sorts, nested loops
3. Check indexes — does every where/orderBy/FK column have an index?
4. Check index usage — pg_stat_user_indexes shows which indexes are actually used
5. Check table bloat — VACUUM ANALYZE if the table has high dead tuple ratio
6. Check connection count — SELECT count(*) FROM pg_stat_activity — are you near max_connections?
7. Check slow query log — enable log_min_duration_statement = 100 in PostgreSQL to catch queries > 100ms

Cross-Domain Patterns

These are the interactions that make Prisma a cross-domain concern:

Pattern	Domains	Mechanism
N+1 in a loop	CPU + Async + Memory	Sequential queries waste CPU on engine overhead, block the event loop, and accumulate intermediate results in memory
Unbounded findMany → GC pressure	Memory + CPU	Large result arrays trigger frequent GC, showing as CPU time
Deep include → large payload → slow serialization	Memory + CPU + Bundle	Deep nested objects consume heap, CPU time in JSON.stringify, and if sent to client, inflate response payload
Connection pool exhaustion → queued queries → timeout	Async + CPU	Queries queue behind the pool, wasting wall-clock time; pool management itself has CPU overhead
Generated client in edge bundle	Bundle + Structure	Large generated client inflates bundle; barrel re-export prevents tree-shaking
Multiple PrismaClient instances	Memory + Structure + Async	Each instance has its own connection pool, multiplying memory and connection usage
Missing index → seq scan → slow query → CPU + Async	CPU + Async + Schema	DB does full table scan, Prisma engine waits for slow response, event loop blocked on the await
No connection pooler in serverless → DB connection exhaustion	Async + Schema	Each Lambda/edge cold start opens a new pool, quickly hitting DB max_connections
Soft deletes without partial index → every query scans dead rows	CPU + Memory + Schema	DB reads 10x more rows than needed, Prisma deserializes all of them into memory

Detection Checklist

When profiling reveals Prisma as a hotspot, check these in order:

1. N+1: Grep for prisma.<model>.find inside for/while/.map/.forEach loops
2. Missing select: Look for findMany/findFirst without select on wide tables
3. Unbounded queries: Look for findMany without take/cursor
4. Sequential awaits: Look for consecutive await prisma.* that could be Promise.all
5. Deep includes: Look for nested include > 2 levels deep
6. Multiple clients: Grep for new PrismaClient() — should appear exactly once
7. Pool sizing: Check connection_limit in DATABASE_URL — default (5) is often too low for servers
8. Missing indexes: Enable query logging, run EXPLAIN ANALYZE on slow queries, look for Seq Scan
9. FK indexes: Check that every @relation(fields: [...]) has a corresponding @@index
10. Soft deletes: If using deletedAt, check for partial index on active records
11. Field types: Look for String used where enum, @db.Uuid, or DateTime would be more efficient
12. Connection pooler: In serverless, check for PgBouncer or Prisma Accelerate — raw Prisma pools exhaust DB connections