Prisma Performance Patterns

Optimize Prisma queries with select, findUnique index hits, batching, and avoiding N+1

When to Use

• Diagnosing slow queries or high database load in a Prisma application
• Reducing data transfer by selecting only needed fields
• Eliminating N+1 query patterns in list endpoints
• Optimizing bulk read and write operations

Instructions

1. Select only needed fields —

   select

   reduces data transfer and avoids loading large text/JSON columns:

const users = await prisma.user.findMany({
  select: { id: true, name: true, email: true },
  // NOT: include the entire User with all 20 fields
});

2. Use

   findUnique

   over

   findFirst

   when querying by

   @id

   or

   @unique

   fields. Prisma batches

   findUnique

   calls automatically via internal DataLoader:

// These two calls in the same tick are batched into one SQL query
const [user1, user2] = await Promise.all([
  prisma.user.findUnique({ where: { id: id1 } }),
  prisma.user.findUnique({ where: { id: id2 } }),
]);

3. Avoid N+1 with

   include

   — instead of querying related records in a loop:

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

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

4. Limit nested includes — each

   include

   level adds a database query. For deep nesting, use raw SQL with JOINs instead:

// Avoid: 4 levels of include = 4 queries
const data = await prisma.user.findMany({
  include: { posts: { include: { comments: { include: { author: true } } } } },
});

// Better: raw query with JOINs for the specific data shape you need

5. Use

   createMany

   for bulk inserts — one

   INSERT

   instead of N:

await prisma.post.createMany({
  data: posts,
  skipDuplicates: true,
});

6. Batch writes in transactions — reduces round-trips:

await prisma.$transaction(
  items.map((item) =>
    prisma.inventory.update({
      where: { sku: item.sku },
      data: { quantity: { decrement: item.qty } },
    })
  )
);

7. Add database indexes for filtered and sorted fields:

model Post {
  authorId  String
  createdAt DateTime @default(now())
  published Boolean

  @@index([authorId, createdAt])
  @@index([published, createdAt])
}

8. Enable query logging to find slow queries:

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

prisma.$on('query', (e) => {
  if (e.duration > 100) console.warn(`Slow query (${e.duration}ms):`, e.query);
});

9. Use connection pooling — configure pool size based on your serverless or server environment:

DATABASE_URL="postgresql://user:pass@host:5432/db?connection_limit=10&pool_timeout=30"

10. Paginate with cursors for large datasets — offset pagination degrades with large

    skip

    values:

const page = await prisma.post.findMany({
  cursor: { id: lastId },
  skip: 1,
  take: 20,
  orderBy: { id: 'asc' },
});

Details

Prisma generates SQL queries for every Client operation. Understanding the generated SQL helps diagnose performance issues.

DataLoader batching: Prisma's internal DataLoader batches

findUnique

calls that occur in the same event loop tick into a single

WHERE id IN (...)

query. This only works with

findUnique

—

findFirst

and

findMany

are never batched.

include

query mechanics: Each

include

generates a separate

SELECT

with a

WHERE foreign_key IN (...)

clause using the parent IDs. This is more efficient than N+1 but still adds one query per include level. For read-heavy endpoints with complex data shapes, consider denormalizing or using raw SQL views.

Connection pool sizing: The default pool size is

num_physical_cpus * 2 + 1

. For serverless environments (Vercel, AWS Lambda), set

connection_limit=1

and use an external pooler (PgBouncer, Prisma Data Proxy) to avoid exhausting database connections.

Prisma Accelerate / Data Proxy: For edge deployments and serverless, Prisma Accelerate provides connection pooling and global caching. It adds latency per query but solves the connection exhaustion problem.

Common performance antipatterns:

• Fetching entire tables without pagination — always use

  take

  or

  cursor

• Using

  include

  with

  findMany

  on large collections — fetches all related records for all results
• Not indexing foreign key columns — Prisma does not create indexes for relation fields automatically
• Running

  count

  alongside

  findMany

  — two separate queries. Use a raw query with

  COUNT(*) OVER()

  for total count in one pass

Source

https://prisma.io/docs/orm/prisma-client/queries/query-optimization-performance

Process

1. Read the instructions and examples in this document.
2. Apply the patterns to your implementation, adapting to your specific context.
3. Verify your implementation against the details and edge cases listed above.

Harness Integration

• Type: knowledge — this skill is a reference document, not a procedural workflow.
• No tools or state — consumed as context by other skills and agents.

Success Criteria

• The patterns described in this document are applied correctly in the implementation.
• Edge cases and anti-patterns listed in this document are avoided.