# Prisma performance-optimization guide: eliminating N+1, select/omit, cursor paging, connection pools, cacheStrategy, and TypedSQL > An implementation guide to raising Prisma (v7) performance to production quality. Faithful to the official docs, it explains, with real code and a measurement-first optimization order: N+1 avoidance with include/select and the in operator, preventing over-fetching with select and omit (GA), offset vs. cursor paging, aggregation with aggregate/groupBy/_count, connection-pool settings on the driver adapter, Accelerate's cacheStrategy (ttl/swr), and TypedSQL (Preview) for hot paths. - Published: 2026-06-26 - Author: 友田 陽大 - Tags: Prisma, TypeScript, PostgreSQL, パフォーマンス, 型安全 - URL: https://tomodahinata.com/en/blog/prisma-performance-optimization-n-plus-1-connection-pool-guide - Category: Prisma ORM - Pillar guide: https://tomodahinata.com/en/blog/prisma-orm-production-guide-type-safe-database-v7-driver-adapters ## Key points - Optimization starts from measurement. First identify slow queries and fix in order of effectiveness (N+1 → index → over-fetch → connection → cache). Don't rewrite by guessing. - Eliminate N+1 with include/select. A list + relations completes in 1–2 queries instead of ballooning by row count. For batch fetching, the where-id in operator is also effective. - Stop over-fetching. Only the needed columns with select, exclude confidential/huge columns from the default with omit (GA). With a global omit, never structurally leak password. - Deep pages degrade with offset. Move large data to cursor paging. Lean aggregation to the DB side with aggregate/groupBy, and relation counts with _count. - In v7, the connection pool is set on the driver-adapter side. For serverless, mitigate exhaustion and latency at once with Accelerate's cacheStrategy (ttl/swr). Hot paths with TypedSQL (Preview). --- 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)](/blog/prisma-orm-production-guide-type-safe-database-v7-driver-adapters), and schema-side index design is in the [schema-design guide](/blog/prisma-schema-data-modeling-relations-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](https://www.prisma.io/docs/orm/prisma-client/queries/query-optimization-performance) 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. ```ts // ❌ 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. ```ts // 別の手：関連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 ```ts 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). ```ts // 単発：このクエリだけ 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.** ```ts // オフセット：実装は単純だが、深いページほど遅くなる 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.** ```ts // 集計：平均・合計などは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.** ```ts 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](/blog/nextjs-prisma-app-router-server-actions-production-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. ```ts 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). ```sql -- 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; ``` ```ts 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](/blog/prisma-orm-production-guide-type-safe-database-v7-driver-adapters)). > **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](/blog/prisma-schema-data-modeling-relations-design-guide)) - [ ] **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.