Performance

Prisma Intermediate/Advanced

Chapter 7 · Performance

Prisma makes queries easy to write, which also makes slow ones easy to write. Almost every Prisma performance problem is one of a few familiar mistakes: too many queries, too much data, a missing index, or too few (or too many) database connections. This chapter shows how to spot each one and what to do about it. The rule throughout: measure first, then fix what the measurements show.

Step 1: See the SQL

const base = new PrismaClient({
  adapter,
  log: [{ emit: "event", level: "query" }, "warn", "error"],
});

base.$on("query", (e) => {
  console.log(`${e.duration} ms  ${e.query}`);
});

Every SQL statement Prisma sends is now printed with its duration. Chapter 6's slow-query extension times whole Prisma operations; this log shows the individual statements underneath, which is what you need to spot the problems below. Keep it on in development and off (or sampled) in production, because it's noisy and the params field can contain personal data.

Problem 1: Too Many Queries (N+1)

// SLOW: 1 query for posts + 1 query per post for its author
const posts = await prisma.post.findMany({ take: 50 });
for (const p of posts) {
  const author = await prisma.user.findUnique({ where: { id: p.authorId } });
  console.log(p.title, author?.name);
}

// FAST: 2 queries in total, however many posts there are
const posts2 = await prisma.post.findMany({
  take: 50,
  include: { author: { select: { name: true } } },
});

The first version sends 51 queries for 50 posts. In the query log it's unmistakable: the same statement repeated over and over with a different ID. The fix is to load related data with include or a nested select, as in Prisma Fundamentals, Chapter 9.

Joins instead of extra queries

By default, include fetches each level of relations with a separate query using WHERE id IN (...). On PostgreSQL and MySQL, the relationJoins preview feature can load them in a single query with database joins instead:

// schema.prisma: previewFeatures = ["relationJoins"]
await prisma.post.findMany({
  relationLoadStrategy: "join",   // or "query" for the default behaviour
  include: { author: true, tags: true },
});

Neither strategy is always faster — one round trip versus several simpler queries. If a page is slow and loads several levels of relations, try both and compare the timings in your log.

Problem 2: Too Much Data

Habit	Cost	Better
findMany() with no select	Every column, including long `content` text	`select` only the fields the page shows
include: { posts: true }	Every related row, unbounded	Add `take`, `where` and `orderBy` inside the include
No take on a list endpoint	The whole table as it grows	Always paginate
Large skip values	The database still reads and discards skipped rows	Cursor pagination for deep pages
Counting by fetching	Rows sent just to call `.length`	`count()` or relation `_count` (Chapter 2)

// A list page needs a summary, not the article bodies
const page = await prisma.post.findMany({
  where:   { published: true },
  orderBy: { id: "desc" },
  take:    20,
  ...(cursor ? { cursor: { id: cursor }, skip: 1 } : {}),
  select:  { id: true, title: true, slug: true, author: { select: { name: true } } },
});

Problem 3: Missing Indexes

An index lets the database find matching rows without reading the whole table. Prisma creates indexes for @id and @unique fields. For other columns you filter or sort on often, add one:

model Post {
  // ...
  @@index([authorId])                    // already in the blog schema
  @@index([published, createdAt(sort: Desc)])  // "latest published posts"
}

Then run prisma migrate dev to create it. To check whether a query actually uses an index, ask the database directly with EXPLAIN ANALYZE (PostgreSQL), using Chapter 3's raw SQL:

const plan = await prisma.$queryRaw`
  EXPLAIN ANALYZE
  SELECT "id", "title" FROM "Post"
  WHERE "published" = true ORDER BY "createdAt" DESC LIMIT 20
`;
console.log(plan);
// "Seq Scan on Post"  -> reading every row: consider an index
// "Index Scan using ..." -> the index is being used

Indexes aren't free

Every index is updated on every insert and update, and takes disk space. Add indexes for queries you've measured as slow and that run often, not for every column "just in case". On a small table, a full scan is often faster anyway, so test with realistic data volumes — Chapter 4's seed scripts help here.

Problem 4: Connections

Each PrismaClient keeps a pool of open database connections and reuses them. In Prisma 7, the pool belongs to the database driver behind your adapter, and so do its settings:

import { PrismaPg } from "@prisma/adapter-pg";

const adapter = new PrismaPg({
  connectionString: process.env.DATABASE_URL,
  max: 10,                        // pool size (pg's default is 10)
  connectionTimeoutMillis: 5_000,  // pg's default is 0: wait forever
  idleTimeoutMillis: 300_000,
});

Prisma 6 URL settings no longer apply

Tutorials that add ?connection_limit=5&pool_timeout=10 to the database URL describe Prisma 6's built-in pool. With driver adapters, set pool options on the adapter instead. Prisma's upgrade guide also notes that pg has no connection timeout by default, where Prisma 6 used 5 seconds; the values above restore Prisma 6's timeouts.

The most common connection mistake

// WRONG: a new client, and a new pool, on every request
app.get("/posts", async (req, res) => {
  const prisma = new PrismaClient({ adapter: new PrismaPg({ connectionString }) });
  res.json(await prisma.post.findMany());
});

This opens fresh connections for every request and quickly exhausts the database's connection limit. Create one client when the app starts and share it — exactly what lib/prisma.ts from Prisma Fundamentals does. Pool size is a balance: too small and requests queue waiting for a connection; too large, multiplied by every running copy of your app, and you exceed what the database allows. Serverless platforms make this harder, which Chapter 9 covers.

Problem 5: Doing Work One Row at a Time

Instead of	Use
A loop of `create` calls	`createMany` (Chapter 4)
A loop of `update` calls with the same change	`updateMany` with a `where`
Read a value, add 1, write it back	`{ increment: 1 }`, one atomic statement
Independent queries awaited one after another	`Promise.all` or `$transaction([...])`
Complex bulk changes Prisma can't express	One raw SQL statement (Chapter 3)

// Three independent reads: run them together, not one after another
const [latest, popular, tagCount] = await Promise.all([
  prisma.post.findMany({ orderBy: { createdAt: "desc" }, take: 5, select: { title: true } }),
  prisma.post.findMany({ orderBy: { viewCount: "desc" }, take: 5, select: { title: true } }),
  prisma.tag.count(),
]);

A Performance Checklist

Turn on the query log and reproduce the slow request.
Many near-identical statements? Fix the N+1 with include/select.
Large rows or unbounded lists? Add select, take and pagination.
One slow statement? EXPLAIN ANALYZE it and consider an index.
Fast queries but slow requests under load? Look at the pool size and timeouts.
Measure again to confirm the fix actually helped.

Hands-On Exercises

Exercise 1

An endpoint returns each tag with its three most-viewed published posts, but it loops over tags and runs a query per tag. Use the query log to count its queries, then rewrite it to use a fixed number of queries.

📄 View solution

Exercise 2

Seed 100,000 posts, then use EXPLAIN ANALYZE to compare "latest 20 published posts" before and after adding @@index([published, createdAt(sort: Desc)]). Describe what changed in the plan.

📄 View solution

Exercise 3

Your API runs as 4 copies, each with a pool of 10, and the database allows 50 connections. Is that safe? What changes when you add a migration job, an admin script and a fifth copy? Configure the adapter accordingly.

📄 View solution

Chapter 7 Quick Reference

Measure first: log: [{ emit: "event", level: "query" }] and $on("query", e => e.duration)
N+1: repeated identical statements; fix with include/nested select
relationLoadStrategy: "join" (preview relationJoins) vs. the default "query" — measure both
Fetch less: select, bounded includes, take, cursor pagination, count()
@@index for frequent filters/sorts; confirm with EXPLAIN ANALYZE
Prisma 7 pools live in the driver: set max and timeouts on the adapter; one shared client per app
Batch: createMany, updateMany, increment, Promise.all