Prisma performance-optimization guide: eliminating N+1, select/omit, cursor paging, connection pools, cacheStrategy, and TypedSQL

An ORM being "slow" usually isn't the ORM's fault. N+1, over-fetching, missing indexes, how connections are used — most of the cause is in how it's used. And the good news is they can be fixed declaratively, while keeping type safety. Prisma v7 made the client itself light and fast by going Rust-free (the official benchmark shows up to 3.4× faster, about 90% smaller bundle), but what really works is app-side optimization.

This article is an implementation guide to raising Prisma (v7) performance to production quality. Prisma's overall operation is in the Prisma ORM production-operations guide (v7), and schema-side index design is in the schema-design guide. This article lists "implementation techniques to make it fast" in order of effectiveness.

Rules for this article: APIs and behavior are based on the Prisma official documentation (as of June 2026, the v7 family). Since some "Preview" features (relationLoadStrategy / TypedSQL) are included, I note them. Detailed connection-pool defaults change by version, so always confirm in the official documentation before production.

0. Mental model: measure, then fix in order of effectiveness

The iron rule of performance improvement is "don't guess, measure." In PostgreSQL production tuning too, I first identify heavy queries with pg_stat_statements and EXPLAIN ANALYZE, then work in order of effectiveness. Same with Prisma: before rewriting queries at random, grasp which query runs how many times and how many ms (Prisma's logs, the APM's DB spans, PostgreSQL-side statistics).

The order to fix is mostly decided too.

① Eliminate N+1 → ② index → ③ over-fetching (select/omit) → ④ connection pool → ⑤ cache → ⑥ raw SQL on hot paths.

The higher up, the "bigger effect and smaller cost." Crushing in this order is the shortest path.

1. Eliminate N+1 (the biggest effect)

The N+1 problem — fetching a list with one query, then fetching each row's relation one at a time so queries balloon by row count — is the leading ORM-performance accident. In Prisma, declaratively fetching relations with include / select means no additional query per row.

// ❌ N+1：ユーザー取得後、ループで投稿を引く（1 + N クエリ）
const users = await prisma.user.findMany();
for (const u of users) {
  u.posts = await prisma.post.findMany({ where: { authorId: u.id } }); // 行数分
}

// ✅ include：投稿をまとめて取得（公式いわく「2クエリ」で済む）
const usersWithPosts = await prisma.user.findMany({
  include: { posts: true },
});

The official documentation states plainly that using include completes in "2 queries: fetching users and fetching posts." It doesn't increase proportionally to row count like a manual loop. For situations where you want to batch-fetch manually, the in operator is also effective.

// 別の手：関連IDをまとめて1クエリで取る
const userIds = users.map((u) => u.id);
const posts = await prisma.post.findMany({
  where: { authorId: { in: userIds } },
});

Advanced (Preview): there's relationLoadStrategy: "join" | "query" to choose the relation-load strategy at fetch, and "join" makes it a single query with the DB's LATERAL JOIN, etc. But it's Preview at writing time (needs the relationJoins preview flag), and join is planned to become the default in the future. Adopt in production on the premise of the flag and verification. Either way, straightforwardly using include/select avoids N+1.

2. Stop over-fetching: select and omit

The cost of network transfer, memory, and serialization is proportional to the columns and record count fetched. Fetching even the body and huge JSON in a list slows it by that much.

2.1 select: fetch only the needed columns

const list = await prisma.post.findMany({
  select: {
    id: true,
    title: true,
    author: { select: { name: true } }, // ネストでも必要分だけ
  },
});

2.2 omit (GA): exclude confidential/huge columns from the default

Whereas select is "addition (list what you need)," omit is "subtraction (remove what you don't need from the default)." omit is GA in Prisma 6.2.0 (no flag needed in v7).

// 単発：このクエリだけ password を除外
const user = await prisma.user.findUnique({
  where: { id: 1 },
  omit: { password: true },
});

// グローバル：全クエリで常に password を除外（最も安全）
const prisma = new PrismaClient({
  adapter,
  omit: {
    user: { password: true }, // 既定で漏れなくする
  },
});

Design tip (security): confidential columns like passwordHash are on the safe side excluded from the default with a global omit. Make a state of "it doesn't come out unless explicitly fetched" instead of "it leaks if you forget to select." Preventing over-fetching satisfies both performance and confidentiality minimization at once.

3. Paging: deep pages to cursor

Paging has two methods, and you choose by data scale.

// オフセット：実装は単純だが、深いページほど遅くなる
const page = await prisma.post.findMany({ skip: 20, take: 10 });

// カーソル：大規模データで安定・高速
const firstPage = await prisma.post.findMany({
  take: 10,
  orderBy: { id: "asc" },
});
const last = firstPage.at(-1);
const nextPage = last
  ? await prisma.post.findMany({
      take: 10,
      skip: 1, // カーソル行自身を除外
      cursor: { id: last.id },
      orderBy: { id: "asc" }, // 安定した並び順が必須
    })
  : [];

Per the official guidance, offset becomes "more costly the larger the offset." Use offset for "an admin screen wanting to jump by page number," and cursor for "infinite scroll, feeds, huge data." Cursor presumes a stable orderBy (a unique key).

4. Lean aggregation to the DB: aggregate / groupBy / _count

"Fetch everything in the app, then count/sum" is the worst. Let the DB do the aggregation.

// 集計：平均・合計などはDB側で
const stats = await prisma.user.aggregate({
  _avg: { age: true },
  _count: true,
  where: { active: true },
});

// グループ集計：having で集計値にフィルタ
const byCountry = await prisma.user.groupBy({
  by: ["country"],
  _sum: { profileViews: true },
  having: { profileViews: { _avg: { gt: 100 } } },
});

// 関連の件数：_count で「投稿数」をDB側で数える（投稿本体は取らない）
const usersWithCounts = await prisma.user.findMany({
  select: {
    name: true,
    _count: { select: { posts: true } }, // フィルタ付きも可
  },
});

_count satisfies the frequent requirement of "show only the post count in a user list" without fetching the post bodies. It avoids the antipattern of include'ing the whole relation in a list and .length'ing it. With distinct, deduplication is also possible on the DB side.

5. Connection pool: in v7, set it on the driver adapter

Connection management sways performance and stability, especially in serverless. This changed greatly from v6. In v6 you set it with connection-string query parameters (connection_limit, etc.), but in v7 you set it in the driver adapter's constructor.

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

const adapter = new PrismaPg({
  connectionString: process.env.DATABASE_URL,
  // プールサイズ・タイムアウトは adapter（＝ドライバ）側のオプションで
  // 例（pg ドライバ）：max（プール上限）, connectionTimeoutMillis, idleTimeoutMillis
});

export const prisma = new PrismaClient({ adapter });

Key points.

• Pool size and timeouts are on the adapter side (for pg, max / connectionTimeoutMillis / idleTimeoutMillis). The old DATABASE_URL?connection_limit=... isn't used in v7.
• Work back the pool size from the DB's limit: design so that the number of instances that can run × the pool size doesn't exceed max_connections. In a configuration where many instances stand up in serverless, this is the main cause of connection exhaustion.
• Since the concrete defaults move by version/driver, it's safe to make the adapter options explicit in your own environment and dial them in with a load test.

The serverless standard: if many functions hit the DB directly, put a pooler (PgBouncer), or the pool-equipped Prisma Postgres / globally-pooled Prisma Accelerate, in front (→ §6). For details, the Next.js × Prisma implementation guide.

6. Cache: Accelerate's cacheStrategy (ttl/swr)

For places where reads are heavy or the same result is returned repeatedly, not hitting the DB itself with a cache is the most effective. Using Prisma Accelerate, you can specify cacheStrategy per query.

import { withAccelerate } from "@prisma/extension-accelerate";

const prisma = basePrisma.$extends(withAccelerate());

const posts = await prisma.post.findMany({
  where: { published: true },
  cacheStrategy: {
    ttl: 60, // 60秒は無条件にキャッシュを返す（DBを叩かない）
    swr: 60, // ttl切れ後60秒は古い値を返しつつ背後で再取得
    tags: ["published_posts"], // タグで対象を絞って無効化
  },
});

• ttl (time-to-live): a hit within this many seconds returns the cache with no DB query.
• swr (stale-while-revalidate): within this many seconds after ttl expires, return the stale value immediately while updating behind. It restores freshness without sacrificing latency.
• tags: you can invalidate related caches together.

Drawing the consistency line (important): attach cacheStrategy only to reads that may be cached. Don't attach it to reads where "always-latest is needed" like balance, inventory, or permissions. The ttl/swr values are the business judgment of "how stale is acceptable."

7. Hot paths: type-safe raw SQL with TypedSQL (Preview)

For hot paths needing complex aggregation or DB-specific optimization hard to express in the ORM, use raw SQL. Prisma's TypedSQL generates, from a .sql file, a function with types on both the parameters and the result rows (a Preview feature, needs the typedSql flag).

-- prisma/sql/topAuthors.sql
-- @param {Int} $1:minPosts
SELECT u.id, u.name, COUNT(p.id) AS post_count
FROM "User" u
JOIN "Post" p ON p."authorId" = u.id
GROUP BY u.id
HAVING COUNT(p.id) >= $1
ORDER BY post_count DESC;

import { topAuthors } from "./generated/prisma/sql";

// 戻り値も引数も型付き。手書きの $queryRaw より安全
const rows = await prisma.$queryRawTyped(topAuthors(5));

While TypedSQL is Preview, you can write safely with the ordinary $queryRaw (always parameterized with a tagged template). Don't pass user input to $queryRawUnsafe — this line is unchanged even in raw SQL (→ Prisma production-operations guide §8).

Observability: Prisma Postgres comes with Query Insights that visualizes slow queries (the former Prisma Optimize was replaced by Query Insights). Identify production slow queries not by "intuition" but with such measurement, and crush them in the §0 order.

8. Performance-optimization checklist

The pre-production check items, listed in order of effectiveness.

• First measure: identify slow queries (Prisma logs / APM / PostgreSQL statistics / Query Insights)
• Eliminate N+1: replace in-loop queries with include/select, or batch in
• Index: @@index on search-condition, sort, and FK columns (→ schema design)
• Prevent over-fetching: only needed columns with select, confidential/huge columns with a global omit
• Paging: deep pages from offset to cursor
• Aggregation on the DB side: use aggregate/groupBy/_count/distinct, don't fetch everything and count
• Connection pool: in v7, set on the adapter side. Prevent exhaustion by working back from max_connections
• Cache: cacheStrategy(ttl/swr) on cacheable reads. Don't attach to always-latest-required data
• Hot paths: TypedSQL (Preview) if needed. Always parameterize raw SQL
• Replace loop writes with bulk operations (createMany/updateMany/deleteMany)

Conclusion

The path to making Prisma fast is not ORM magic but discipline. Measure, then crush in the order N+1 → index → over-fetch → connection → cache → raw SQL. Erase N+1 with include/select, stop over-fetching and confidentiality leaks with omit, save deep pages with cursor, lean aggregation to the DB, manage connections with the adapter, and use cache with consistency in mind — all can be done while keeping type safety.

"I want to raise the DB layer of an app that keeps being called slow to production quality, measurement-first" — from identifying slow queries through optimizing schema, queries, connections, and cache, I can help work in order of effectiveness. Combined with PostgreSQL core tuning, the perceived performance changes greatly.