613 lines
22 KiB
Markdown
613 lines
22 KiB
Markdown
# Prisma Performance Antipatterns
|
||
|
||
Prisma is a cross-domain performance concern. A single Prisma misuse can simultaneously cause CPU waste, memory bloat, async bottlenecks, bundle inflation, and structural coupling. This reference catalogs the most common antipatterns by domain, with before/after fixes.
|
||
|
||
## CPU
|
||
|
||
### N+1 queries
|
||
|
||
The most common Prisma perf issue. Happens when fetching related records in a loop instead of using `include` or a single `findMany`.
|
||
|
||
```typescript
|
||
// BAD: N+1 — one query per user
|
||
const users = await prisma.user.findMany();
|
||
for (const user of users) {
|
||
const posts = await prisma.post.findMany({ where: { authorId: user.id } });
|
||
user.posts = posts;
|
||
}
|
||
|
||
// GOOD: single query with include
|
||
const users = await prisma.user.findMany({
|
||
include: { posts: true },
|
||
});
|
||
```
|
||
|
||
**Signal in profiling:** CPU hotspot in Prisma query engine; many small queries in `--trace-gc` output; sequential await pattern visible in async profile.
|
||
|
||
### Over-fetching fields (no `select`)
|
||
|
||
`findMany` returns all columns by default. On wide tables (30+ columns), this wastes CPU on serialization/deserialization of unused fields.
|
||
|
||
```typescript
|
||
// BAD: fetches all 30 columns when you need 2
|
||
const users = await prisma.user.findMany();
|
||
const names = users.map(u => u.name);
|
||
|
||
// GOOD: fetch only what you need
|
||
const users = await prisma.user.findMany({
|
||
select: { id: true, name: true },
|
||
});
|
||
```
|
||
|
||
**Signal:** CPU time in Prisma result deserialization; heap profile shows large object arrays with mostly-null fields.
|
||
|
||
### Raw queries for hot paths
|
||
|
||
Prisma's query builder adds overhead per query (AST construction, validation, serialization). For hot paths called thousands of times, `$queryRaw` eliminates this.
|
||
|
||
```typescript
|
||
// Prisma query builder overhead (~2-5ms per query)
|
||
const result = await prisma.user.findFirst({ where: { email } });
|
||
|
||
// Raw SQL (~0.5-1ms per query)
|
||
const [result] = await prisma.$queryRaw`
|
||
SELECT id, name, email FROM "User" WHERE email = ${email} LIMIT 1
|
||
`;
|
||
```
|
||
|
||
**When to use:** Only when profiling shows Prisma query construction is a measurable fraction of the query's total time. Not worth it for queries that run once.
|
||
|
||
## Memory
|
||
|
||
### Unbounded `findMany` without pagination
|
||
|
||
Loading an entire table into memory is the #1 Prisma memory issue.
|
||
|
||
```typescript
|
||
// BAD: loads entire table into memory
|
||
const allOrders = await prisma.order.findMany();
|
||
|
||
// GOOD: cursor-based pagination
|
||
let cursor: string | undefined;
|
||
do {
|
||
const batch = await prisma.order.findMany({
|
||
take: 1000,
|
||
...(cursor ? { skip: 1, cursor: { id: cursor } } : {}),
|
||
orderBy: { id: 'asc' },
|
||
});
|
||
await processBatch(batch);
|
||
cursor = batch[batch.length - 1]?.id;
|
||
} while (cursor);
|
||
```
|
||
|
||
**Signal:** Heap snapshot shows large arrays of Prisma model objects; RSS spikes during query execution.
|
||
|
||
### Eager-loading deep relations
|
||
|
||
Nested `include` can explode result size exponentially.
|
||
|
||
```typescript
|
||
// BAD: 3-level deep include — if User has 10 posts, each post has 50 comments,
|
||
// each comment has 5 reactions → 2,500 reaction objects per user
|
||
const users = await prisma.user.findMany({
|
||
include: {
|
||
posts: {
|
||
include: {
|
||
comments: {
|
||
include: { reactions: true },
|
||
},
|
||
},
|
||
},
|
||
},
|
||
});
|
||
|
||
// GOOD: flatten with separate queries, load only what's needed
|
||
const users = await prisma.user.findMany({ include: { posts: true } });
|
||
const postIds = users.flatMap(u => u.posts.map(p => p.id));
|
||
const comments = await prisma.comment.findMany({
|
||
where: { postId: { in: postIds } },
|
||
select: { id: true, content: true, postId: true },
|
||
});
|
||
```
|
||
|
||
**Signal:** Heap profile shows deeply nested objects; GC pressure from short-lived intermediate objects during result construction.
|
||
|
||
### Forgotten `$disconnect` in scripts/CLIs
|
||
|
||
In long-running scripts or CLI tools, not disconnecting leaves the connection pool alive, preventing graceful shutdown and leaking connections.
|
||
|
||
```typescript
|
||
// BAD: connection pool stays open
|
||
async function main() {
|
||
const data = await prisma.user.findMany();
|
||
process.exit(0); // connections not properly closed
|
||
}
|
||
|
||
// GOOD: explicit disconnect
|
||
async function main() {
|
||
try {
|
||
const data = await prisma.user.findMany();
|
||
} finally {
|
||
await prisma.$disconnect();
|
||
}
|
||
}
|
||
```
|
||
|
||
## Async
|
||
|
||
### Sequential queries that could be parallel
|
||
|
||
Independent Prisma queries awaited sequentially when they have no data dependency.
|
||
|
||
```typescript
|
||
// BAD: sequential — total time = sum of all queries
|
||
const users = await prisma.user.count();
|
||
const posts = await prisma.post.count();
|
||
const comments = await prisma.comment.count();
|
||
|
||
// GOOD: parallel — total time = max of all queries
|
||
const [users, posts, comments] = await Promise.all([
|
||
prisma.user.count(),
|
||
prisma.post.count(),
|
||
prisma.comment.count(),
|
||
]);
|
||
```
|
||
|
||
**Signal:** Async profile shows sequential await chain; wall-clock time much higher than sum of individual query times would suggest for parallel execution.
|
||
|
||
### Missing `$transaction` for batch writes
|
||
|
||
Multiple related writes without a transaction are both slower (individual round-trips) and unsafe (partial failure).
|
||
|
||
```typescript
|
||
// BAD: 100 individual writes, 100 round-trips
|
||
for (const item of items) {
|
||
await prisma.order.create({ data: item });
|
||
}
|
||
|
||
// GOOD: batched transaction, 1 round-trip
|
||
await prisma.$transaction(
|
||
items.map(item => prisma.order.create({ data: item }))
|
||
);
|
||
|
||
// ALSO GOOD: createMany for simple inserts (even faster, single query)
|
||
await prisma.order.createMany({ data: items });
|
||
```
|
||
|
||
### Connection pool exhaustion
|
||
|
||
Default pool size is 5 connections (`connection_limit` in connection string). Under concurrency, queries queue up waiting for a connection.
|
||
|
||
```
|
||
// In DATABASE_URL or programmatic config:
|
||
// BAD: default pool (5 connections)
|
||
DATABASE_URL="postgresql://user:pass@host/db"
|
||
|
||
// GOOD: sized for workload (2-3x expected concurrent queries)
|
||
DATABASE_URL="postgresql://user:pass@host/db?connection_limit=20"
|
||
```
|
||
|
||
**Signal:** Query latency spikes under load; Prisma logs show `Timed out fetching a new connection from the connection pool`.
|
||
|
||
### Interactive transactions holding connections too long
|
||
|
||
`$transaction` with an interactive callback holds a connection for the entire callback duration. Long-running logic inside the callback starves the pool.
|
||
|
||
```typescript
|
||
// BAD: holds connection while doing CPU-heavy work
|
||
await prisma.$transaction(async (tx) => {
|
||
const data = await tx.record.findMany();
|
||
const processed = heavyComputation(data); // 500ms CPU work holding a connection
|
||
await tx.record.updateMany({ data: processed });
|
||
});
|
||
|
||
// GOOD: fetch, process outside transaction, then write
|
||
const data = await prisma.record.findMany();
|
||
const processed = heavyComputation(data);
|
||
await prisma.$transaction(async (tx) => {
|
||
await tx.record.updateMany({ data: processed });
|
||
});
|
||
```
|
||
|
||
## Bundle
|
||
|
||
### Generated client size
|
||
|
||
`@prisma/client` generates a client tailored to your schema. For large schemas (50+ models), the generated client can be 500 KiB+, impacting bundle size in serverless or edge deployments.
|
||
|
||
**Mitigations:**
|
||
- Use `prisma generate --no-engine` for edge runtimes (Prisma Accelerate / Data Proxy)
|
||
- Evaluate if all models are needed — split schemas for microservices
|
||
- For serverless: the Prisma engine binary (~15 MiB) is the real cost; use Prisma Accelerate to offload query execution
|
||
|
||
### Barrel re-export pulling full client
|
||
|
||
```typescript
|
||
// BAD: barrel re-exports everything — bundler can't tree-shake Prisma enums/types
|
||
// lib/index.ts
|
||
export * from '@prisma/client';
|
||
|
||
// GOOD: import directly
|
||
import { PrismaClient } from '@prisma/client';
|
||
```
|
||
|
||
## Structure
|
||
|
||
### Circular model references
|
||
|
||
Prisma schema supports circular relations (User → Post → Comment → User). Code that mirrors these relations with eager loading creates circular data structures that break JSON serialization and cause stack overflows.
|
||
|
||
```typescript
|
||
// BAD: circular eager loading
|
||
const user = await prisma.user.findFirst({
|
||
include: { posts: { include: { author: true } } }, // author → back to user
|
||
});
|
||
JSON.stringify(user); // circular reference error or infinite recursion
|
||
|
||
// GOOD: break the cycle with select
|
||
const user = await prisma.user.findFirst({
|
||
include: {
|
||
posts: {
|
||
select: { id: true, title: true }, // don't include author back-ref
|
||
},
|
||
},
|
||
});
|
||
```
|
||
|
||
### Prisma Client as a singleton
|
||
|
||
Instantiating `PrismaClient` in multiple modules wastes connections and memory. Use a singleton pattern.
|
||
|
||
```typescript
|
||
// BAD: new client per import
|
||
// utils/db.ts
|
||
export const prisma = new PrismaClient();
|
||
// services/user.ts
|
||
import { PrismaClient } from '@prisma/client';
|
||
const prisma = new PrismaClient(); // second connection pool!
|
||
|
||
// GOOD: singleton
|
||
// lib/prisma.ts
|
||
import { PrismaClient } from '@prisma/client';
|
||
const globalForPrisma = globalThis as unknown as { prisma: PrismaClient };
|
||
export const prisma = globalForPrisma.prisma || new PrismaClient();
|
||
if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma;
|
||
```
|
||
|
||
**Signal:** Multiple `PrismaClient` instances visible in heap snapshot; connection count exceeds pool size × expected clients.
|
||
|
||
## Schema & Database
|
||
|
||
These are optimizations at the Prisma schema and database level — often the root cause behind application-level symptoms. A slow `findMany` might not be an application problem at all; it might be a missing index turning a 2ms lookup into a 500ms full table scan.
|
||
|
||
### Missing indexes
|
||
|
||
The single most impactful schema optimization. Prisma auto-creates indexes for `@id` and `@unique`, but not for fields used in `where`, `orderBy`, or relation joins.
|
||
|
||
```prisma
|
||
// BAD: no index on fields used in queries
|
||
model Order {
|
||
id String @id @default(cuid())
|
||
userId String
|
||
status String
|
||
createdAt DateTime @default(now())
|
||
user User @relation(fields: [userId], references: [id])
|
||
}
|
||
|
||
// Query: prisma.order.findMany({ where: { userId, status: 'active' }, orderBy: { createdAt: 'desc' } })
|
||
// Without indexes: full table scan + filesort
|
||
|
||
// GOOD: indexes matching query patterns
|
||
model Order {
|
||
id String @id @default(cuid())
|
||
userId String
|
||
status String
|
||
createdAt DateTime @default(now())
|
||
user User @relation(fields: [userId], references: [id])
|
||
|
||
@@index([userId, status]) // composite index for the common filter
|
||
@@index([userId, createdAt]) // covers orderBy queries
|
||
}
|
||
```
|
||
|
||
**How to detect:** Run `EXPLAIN ANALYZE` on slow queries via `$queryRaw`:
|
||
|
||
```typescript
|
||
const plan = await prisma.$queryRaw`
|
||
EXPLAIN ANALYZE
|
||
SELECT * FROM "Order" WHERE "userId" = ${userId} AND "status" = 'active'
|
||
ORDER BY "createdAt" DESC
|
||
LIMIT 50
|
||
`;
|
||
console.log(plan);
|
||
// Look for: "Seq Scan" (bad) vs "Index Scan" (good)
|
||
// Look for: "Sort" with high cost (missing index on orderBy column)
|
||
```
|
||
|
||
**Index design rules:**
|
||
- Column order in composite indexes matters: put equality filters first, range/sort last (`@@index([status, createdAt])` not the reverse)
|
||
- Covering indexes avoid table lookups: if you always `select: { id, name }`, an index on `[userId, id, name]` satisfies the query from the index alone
|
||
- Don't over-index: each index slows writes and consumes memory. Index the patterns you actually query.
|
||
|
||
### Relation-level indexes (foreign keys)
|
||
|
||
Prisma creates foreign key constraints but does **not** always create indexes on FK columns (depends on the database). PostgreSQL only auto-indexes the referenced side (`@id`), not the FK side.
|
||
|
||
```prisma
|
||
model Comment {
|
||
id String @id @default(cuid())
|
||
postId String
|
||
post Post @relation(fields: [postId], references: [id])
|
||
|
||
// Without this, JOIN and WHERE on postId is a seq scan:
|
||
@@index([postId])
|
||
}
|
||
```
|
||
|
||
**Rule of thumb:** Every `@relation(fields: [...])` should have a corresponding `@@index` unless the field is already `@unique`.
|
||
|
||
### Query plan analysis workflow
|
||
|
||
When profiling shows a Prisma query as a hotspot:
|
||
|
||
1. **Extract the SQL.** Enable Prisma query logging to see the generated SQL:
|
||
```typescript
|
||
const prisma = new PrismaClient({ log: ['query'] });
|
||
```
|
||
Or set `DATABASE_URL` env var with `?log=query`.
|
||
|
||
2. **Run EXPLAIN ANALYZE.** Copy the SQL and run it with `EXPLAIN ANALYZE` via `$queryRaw` or directly in a database client.
|
||
|
||
3. **Read the plan.** Key things to look for:
|
||
|
||
| Plan node | Meaning | Action |
|
||
|-----------|---------|--------|
|
||
| `Seq Scan` | Full table scan | Add index on filter columns |
|
||
| `Sort` (not `Index Scan`) | In-memory sort | Add index on `orderBy` column |
|
||
| `Nested Loop` with `Seq Scan` inner | N+1 at DB level | Add index on join column |
|
||
| `Hash Join` with large `Buckets` | Large hash table built in memory | Consider narrowing the join with `WHERE` |
|
||
| `Bitmap Heap Scan` | Index used but many rows | Index is helping, but query returns too many rows — add `take`/`LIMIT` |
|
||
|
||
4. **Add the index.** Update `schema.prisma`, run `prisma migrate dev`, re-run `EXPLAIN ANALYZE` to confirm the plan changed.
|
||
|
||
### Denormalization for read-heavy paths
|
||
|
||
Normalized schemas are correct but can be slow for read-heavy dashboards/aggregations. Prisma makes denormalization easy with computed/stored fields.
|
||
|
||
```prisma
|
||
// Normalized: counting comments requires JOIN + COUNT every time
|
||
model Post {
|
||
id String @id @default(cuid())
|
||
comments Comment[]
|
||
}
|
||
|
||
// Denormalized: precomputed count, updated on write
|
||
model Post {
|
||
id String @id @default(cuid())
|
||
commentCount Int @default(0) // maintained by application logic
|
||
comments Comment[]
|
||
}
|
||
```
|
||
|
||
```typescript
|
||
// Update the count atomically when adding a comment
|
||
await prisma.$transaction([
|
||
prisma.comment.create({ data: { postId, content } }),
|
||
prisma.post.update({
|
||
where: { id: postId },
|
||
data: { commentCount: { increment: 1 } },
|
||
}),
|
||
]);
|
||
```
|
||
|
||
**When to denormalize:**
|
||
- Dashboard/list queries that aggregate across relations (COUNT, SUM)
|
||
- Fields displayed on every list item that currently require a JOIN
|
||
- Read:write ratio > 100:1 for the aggregated data
|
||
|
||
**When NOT to denormalize:**
|
||
- Data that changes frequently from multiple sources (hard to keep consistent)
|
||
- Counts that need to be exact in real-time (eventual consistency is acceptable for most UIs)
|
||
|
||
### Field type optimization
|
||
|
||
```prisma
|
||
// BAD: String for enum-like values — no DB-level validation, indexes less efficient
|
||
model Order {
|
||
status String // "pending", "active", "shipped"
|
||
}
|
||
|
||
// GOOD: native enum — smaller storage, faster comparison, DB validates values
|
||
enum OrderStatus {
|
||
PENDING
|
||
ACTIVE
|
||
SHIPPED
|
||
}
|
||
|
||
model Order {
|
||
status OrderStatus @default(PENDING)
|
||
}
|
||
```
|
||
|
||
```prisma
|
||
// BAD: String for UUIDs — 36 bytes as text
|
||
model User {
|
||
id String @id @default(uuid())
|
||
}
|
||
|
||
// GOOD: native UUID type (PostgreSQL) — 16 bytes, faster comparison
|
||
// In the datasource, Prisma uses @db.Uuid for PostgreSQL:
|
||
model User {
|
||
id String @id @default(uuid()) @db.Uuid
|
||
}
|
||
```
|
||
|
||
```prisma
|
||
// BAD: DateTime stored as String
|
||
model Event {
|
||
occurredAt String // "2024-01-15T10:30:00Z"
|
||
}
|
||
|
||
// GOOD: native DateTime — indexable, sortable, smaller storage
|
||
model Event {
|
||
occurredAt DateTime @default(now())
|
||
}
|
||
```
|
||
|
||
### JSON columns: when to use vs. when to normalize
|
||
|
||
```prisma
|
||
// JSON is good for: truly unstructured data, user preferences, metadata blobs
|
||
model User {
|
||
id String @id @default(cuid())
|
||
settings Json // { theme: "dark", notifications: { email: true } }
|
||
}
|
||
|
||
// JSON is bad for: data you filter/sort on — can't index JSON fields efficiently
|
||
// BAD: filtering on JSON field (requires full scan or expression index)
|
||
// prisma.user.findMany({ where: { settings: { path: ['theme'], equals: 'dark' } } })
|
||
|
||
// GOOD: extract frequently-queried fields into columns
|
||
model User {
|
||
id String @id @default(cuid())
|
||
theme String @default("light") // indexable, filterable
|
||
settings Json // remaining unstructured data
|
||
@@index([theme])
|
||
}
|
||
```
|
||
|
||
### Soft deletes and query performance
|
||
|
||
Soft deletes (`deletedAt` field) affect every query if not handled properly.
|
||
|
||
```prisma
|
||
model User {
|
||
id String @id @default(cuid())
|
||
deletedAt DateTime?
|
||
|
||
// CRITICAL: partial index for active records (PostgreSQL)
|
||
// Prisma doesn't support partial indexes natively — use raw SQL migration:
|
||
// CREATE INDEX idx_user_active ON "User" ("id") WHERE "deletedAt" IS NULL;
|
||
|
||
@@index([deletedAt]) // at minimum, index the soft-delete column
|
||
}
|
||
```
|
||
|
||
**Problem:** Without indexing, every `where: { deletedAt: null }` filter on every query scans the entire table including deleted rows. If 90% of rows are soft-deleted, you're scanning 10x more data than needed.
|
||
|
||
**Solution:** Use Prisma middleware to auto-filter, plus a partial index:
|
||
|
||
```typescript
|
||
prisma.$use(async (params, next) => {
|
||
if (params.action === 'findMany' || params.action === 'findFirst') {
|
||
params.args.where = { ...params.args.where, deletedAt: null };
|
||
}
|
||
return next(params);
|
||
});
|
||
```
|
||
|
||
### Connection infrastructure
|
||
|
||
Beyond pool sizing (`connection_limit`), the connection infrastructure matters for production:
|
||
|
||
**PgBouncer / connection poolers:**
|
||
|
||
Prisma opens one connection per pool slot. In serverless (Vercel, AWS Lambda), each cold start creates a new pool — this quickly exhausts DB connections.
|
||
|
||
```
|
||
# Without pooler: 50 Lambda instances × 5 connections = 250 DB connections
|
||
# With PgBouncer in transaction mode: 50 Lambda instances → PgBouncer → 20 DB connections
|
||
|
||
# Connection string through PgBouncer:
|
||
DATABASE_URL="postgresql://user:pass@pgbouncer-host:6432/db?pgbouncer=true"
|
||
```
|
||
|
||
The `?pgbouncer=true` flag tells Prisma to avoid prepared statements (PgBouncer in transaction mode doesn't support them).
|
||
|
||
**Prisma Accelerate (managed connection pooling):**
|
||
|
||
For serverless/edge deployments where you can't run PgBouncer:
|
||
|
||
```typescript
|
||
import { PrismaClient } from '@prisma/client/edge';
|
||
import { withAccelerate } from '@prisma/extension-accelerate';
|
||
|
||
const prisma = new PrismaClient().$extends(withAccelerate());
|
||
|
||
// Queries go through Prisma's managed connection pool
|
||
const users = await prisma.user.findMany({
|
||
cacheStrategy: { ttl: 60 }, // optional: edge cache for 60s
|
||
});
|
||
```
|
||
|
||
### Migration performance
|
||
|
||
Large table migrations can lock tables and cause downtime.
|
||
|
||
**Dangerous migrations:**
|
||
- Adding a `NOT NULL` column without a default to a large table → full table rewrite
|
||
- Creating an index on a large table → `CREATE INDEX` locks writes (use `CREATE INDEX CONCURRENTLY` in PostgreSQL)
|
||
- Renaming a column → Prisma drops and recreates, which can lose data
|
||
|
||
**Safe migration patterns:**
|
||
|
||
```sql
|
||
-- Adding a column: use default to avoid rewrite
|
||
ALTER TABLE "User" ADD COLUMN "role" TEXT DEFAULT 'member';
|
||
|
||
-- Creating an index: use CONCURRENTLY to avoid locks (add to migration SQL manually)
|
||
CREATE INDEX CONCURRENTLY idx_order_status ON "Order" ("status");
|
||
|
||
-- Renaming: two-step deploy (add new column, migrate data, drop old)
|
||
ALTER TABLE "User" ADD COLUMN "full_name" TEXT;
|
||
UPDATE "User" SET "full_name" = "name";
|
||
-- Deploy code that reads from full_name
|
||
-- Then: ALTER TABLE "User" DROP COLUMN "name";
|
||
```
|
||
|
||
**Prisma-specific:** Edit the generated migration SQL before running `prisma migrate deploy` to add `CONCURRENTLY` or split dangerous operations.
|
||
|
||
### Database-level query optimization checklist
|
||
|
||
When a Prisma query is slow and application-level fixes (select, pagination, parallel queries) haven't helped:
|
||
|
||
1. **Enable query logging** — see the actual SQL Prisma generates
|
||
2. **EXPLAIN ANALYZE** — identify seq scans, sorts, nested loops
|
||
3. **Check indexes** — does every `where`/`orderBy`/FK column have an index?
|
||
4. **Check index usage** — `pg_stat_user_indexes` shows which indexes are actually used
|
||
5. **Check table bloat** — `VACUUM ANALYZE` if the table has high dead tuple ratio
|
||
6. **Check connection count** — `SELECT count(*) FROM pg_stat_activity` — are you near `max_connections`?
|
||
7. **Check slow query log** — enable `log_min_duration_statement = 100` in PostgreSQL to catch queries > 100ms
|
||
|
||
## Cross-Domain Patterns
|
||
|
||
These are the interactions that make Prisma a cross-domain concern:
|
||
|
||
| Pattern | Domains | Mechanism |
|
||
|---------|---------|-----------|
|
||
| N+1 in a loop | CPU + Async + Memory | Sequential queries waste CPU on engine overhead, block the event loop, and accumulate intermediate results in memory |
|
||
| Unbounded findMany → GC pressure | Memory + CPU | Large result arrays trigger frequent GC, showing as CPU time |
|
||
| Deep include → large payload → slow serialization | Memory + CPU + Bundle | Deep nested objects consume heap, CPU time in JSON.stringify, and if sent to client, inflate response payload |
|
||
| Connection pool exhaustion → queued queries → timeout | Async + CPU | Queries queue behind the pool, wasting wall-clock time; pool management itself has CPU overhead |
|
||
| Generated client in edge bundle | Bundle + Structure | Large generated client inflates bundle; barrel re-export prevents tree-shaking |
|
||
| Multiple PrismaClient instances | Memory + Structure + Async | Each instance has its own connection pool, multiplying memory and connection usage |
|
||
| Missing index → seq scan → slow query → CPU + Async | CPU + Async + Schema | DB does full table scan, Prisma engine waits for slow response, event loop blocked on the await |
|
||
| No connection pooler in serverless → DB connection exhaustion | Async + Schema | Each Lambda/edge cold start opens a new pool, quickly hitting DB max_connections |
|
||
| Soft deletes without partial index → every query scans dead rows | CPU + Memory + Schema | DB reads 10x more rows than needed, Prisma deserializes all of them into memory |
|
||
|
||
## Detection Checklist
|
||
|
||
When profiling reveals Prisma as a hotspot, check these in order:
|
||
|
||
1. **N+1**: Grep for `prisma.<model>.find` inside `for`/`while`/`.map`/`.forEach` loops
|
||
2. **Missing select**: Look for `findMany`/`findFirst` without `select` on wide tables
|
||
3. **Unbounded queries**: Look for `findMany` without `take`/`cursor`
|
||
4. **Sequential awaits**: Look for consecutive `await prisma.*` that could be `Promise.all`
|
||
5. **Deep includes**: Look for nested `include` > 2 levels deep
|
||
6. **Multiple clients**: Grep for `new PrismaClient()` — should appear exactly once
|
||
7. **Pool sizing**: Check `connection_limit` in DATABASE_URL — default (5) is often too low for servers
|
||
8. **Missing indexes**: Enable query logging, run `EXPLAIN ANALYZE` on slow queries, look for `Seq Scan`
|
||
9. **FK indexes**: Check that every `@relation(fields: [...])` has a corresponding `@@index`
|
||
10. **Soft deletes**: If using `deletedAt`, check for partial index on active records
|
||
11. **Field types**: Look for `String` used where `enum`, `@db.Uuid`, or `DateTime` would be more efficient
|
||
12. **Connection pooler**: In serverless, check for PgBouncer or Prisma Accelerate — raw Prisma pools exhaust DB connections
|