Prisma + Postgres Performance

---
name: prisma-postgres-performance
description: Audit Prisma + PostgreSQL usage for performance. Use when API latency is creeping up, the database is the suspected bottleneck, or before a traffic-scaling event — finds N+1 queries, missing indexes, over-fetching, and transaction misuse.
---

# Prisma + PostgreSQL Performance Audit

You are auditing a codebase that uses Prisma ORM with PostgreSQL. Find the queries that will hurt at 10× current traffic. Evidence first: every finding needs the file:line of the query and the schema model it touches.

## Step 1 — N+1 queries

- Search for Prisma calls inside loops, `.map` with await, and resolver/serializer patterns that fetch relations per item.
- Check relation access patterns: a `findMany` followed by per-row `findUnique` on a relation is the classic miss — should be `include`/`in`-batched or a single join via `include`.
- In GraphQL/tRPC routers, check whether list endpoints fetch relations per element (needs dataloader-style batching).

## Step 2 — Over-fetching

- `findMany` without `select` on wide models (especially models with text/json blob columns) used in list views.
- `include` chains pulling relations the response never uses.
- Unbounded queries: `findMany` with no `take` on user-generated tables. Every list query should have a hard cap and pagination (prefer cursor pagination on monotonic keys over offset for large tables).

## Step 3 — Indexes vs. actual query shapes

- Build the list of real query shapes from the code: every `where`, `orderBy`, and join key combination.
- Compare against `@@index`/`@unique` in schema.prisma. Flag: filtered columns with no index, composite filters where only a single-column index exists (order matters — equality columns before range columns), `orderBy` columns not covered, and foreign keys without indexes (Postgres does NOT auto-index FK columns).
- Flag redundant indexes too (covered by a composite prefix) — they slow writes for nothing.

## Step 4 — Transactions and connections

- Long transactions doing external work (HTTP calls, LLM calls!) inside `$transaction` — these hold connections and locks; flag every one.
- Interactive transactions where a batch (`$transaction([])`) would do.
- Connection pool sizing vs. deployment shape: serverless/edge needs a pooler (pgbouncer/accelerate); flag a raw connection string in serverless contexts.
- Hot upsert patterns that should be `ON CONFLICT` (Prisma `upsert` is fine; read-then-write race patterns are not).

## Step 5 — The data layer around the database

- Repeated identical reads within a request (no per-request memoization) or across requests (no Redis layer for hot, slow queries).
- Counts: `count()` on big tables per page load; consider cached or approximate counts.
- JSON columns being filtered/sorted in app code after fetching everything.

## Output format

1. **Top issues ranked by expected impact** — each with file:line, the query shape, why it degrades (with row-count assumptions stated), and the exact fix (Prisma code or schema.prisma index DDL).
2. **Schema changes** — consolidated migration-ready list of index additions/removals.
3. **Safe-to-ship-now vs. needs-measurement** — split recommendations; for the latter, give the `EXPLAIN ANALYZE` command to run.