# Prisma N+1 in Production:Real Query Plans and Fixes


## The silent killer hiding in your ORM

It was a Tuesday morning when our on-call alert fired. P95 latency on `/api/dashboard` had crossed 4 seconds. Nothing had been deployed. No traffic spike. The database CPU was sitting at 78% on a `db.r6g.2xlarge` we'd just scaled up to "fix" the same problem two weeks earlier.

We opened DataDog, found the trace, and stared at it for a moment.

**1,847 database queries. For a single request.**

The offending code had been in production for four months. It passed code review. It passed QA. It looked completely normal:

```typescript
const tenants = await prisma.tenant.findMany({
  where: { status: 'active' }
});

const enriched = await Promise.all(
  tenants.map(t =>
    prisma.subscription.findFirst({
      where: { tenantId: t.id }
    })
  )
);
```

At 12 tenants in staging, this ran in 40ms. At 1,847 active tenants in production, Prisma fired 1,848 queries — one to fetch the tenants, then one per tenant to fetch its subscription. The ORM hid every single one behind a clean `await`. No warnings. No errors. Just a silent, compounding tax that scaled linearly with your growth.

This is the N+1 problem. It doesn't crash your app. It just makes it slower every time you succeed.

The fix took 11 minutes. The diagnosis took three hours. This post is about closing that gap.

* * *

## Section 01 — Detecting it

### Step 1: enable Prisma query logging

```typescript
const prisma = new PrismaClient({
  log: [
    { emit: 'event', level: 'query' },
    { emit: 'stdout', level: 'error' },
  ],
});

prisma.$on('query', (e) => {
  console.log(`[QUERY] ${e.query} | ${e.duration}ms`);
});
```

A route that emits 200+ log lines for a single request is your N+1. But logging alone doesn't tell you why it's slow at the database level.

### Step 2: read the query plan

```typescript
// Raw output from EXPLAIN ANALYZE on the child query:
// Seq Scan on "Subscription"
//   actual time=4.831..4.831 rows=1 loops=1847
//   Filter: tenantId = $1
//   Rows Removed by Filter: 49999
// Execution Time: 8,921.4 ms

// What this means:
// loops=1847   → this query plan ran 1,847 times
// Seq Scan     → full table read every loop (50k rows × 1,847 = 92M row reads)
// No index     → tenantId column is unindexed, every loop scans the whole table
```

### Step 3: find repeat queries in production

```typescript
const hotQueries = await prisma.$queryRaw<HotQuery[]>`
  SELECT query, calls, mean_exec_time, total_exec_time
  FROM pg_stat_statements
  ORDER BY total_exec_time DESC
  LIMIT 10
`;
```

* * *

## Section 02 — Fix #1: `include` and `select`

```typescript
// Before: 1 + N queries (1,848 total at 1,847 tenants)
const tenants = await prisma.tenant.findMany({
  where: { status: 'active' }
});
const enriched = await Promise.all(
  tenants.map(t => prisma.subscription.findFirst({
    where: { tenantId: t.id }
  }))
);

// After: 2 queries flat — regardless of tenant count
const tenants = await prisma.tenant.findMany({
  where: { status: 'active' },
  include: {
    subscription: {
      select: { id: true, plan: true, status: true }
    }
  }
});

// Results:
// Queries  → 1,848  down to 2
// Latency  → 8.9s   down to 38ms
// DB CPU   → 78%    down to 4%
```

| Scenario | Before (N+1) | After (fixed) |
| --- | --- | --- |
| Basic N+1 | 1,848 queries / 8.9s | 2 queries / 38ms |
| Under load (100 RPS) | 184,800 queries/s | 200 queries/s |
| DB CPU (db.r6g.2xlarge) | 78% | 4% |

> **Gotcha:** Deep `include` chains (3+ levels) can produce enormous JOINs that are slower than the N+1 they replace. Benchmark anything beyond 2 levels.

* * *

## Section 03 — Fix #2: the dataloader pattern

```typescript
// lib/dataloader.ts — no external library needed
type BatchFn<T> = (ids: string[]) => Promise<(T | null)[]>;

function createLoader<T>(batchFn: BatchFn<T>) {
  const queue: { id: string; resolve: (v: T | null) => void }[] = [];
  let scheduled = false;

  return async function load(id: string): Promise<T | null> {
    return new Promise((resolve) => {
      queue.push({ id, resolve });
      if (!scheduled) {
        scheduled = true;
        process.nextTick(async () => {
          const ids = queue.map(q => q.id);
          const results = await batchFn([...new Set(ids)]);
          const map = new Map(ids.map((id, i) => [id, results[i]]));
          queue.forEach(q => q.resolve(map.get(q.id) ?? null));
          queue.length = 0;
          scheduled = false;
        });
      }
    });
  };
}

const userLoader = createLoader<User>(async (ids) => {
  const users = await prisma.user.findMany({
    where: { id: { in: ids } },
    select: { id: true, name: true, email: true }
  });
  return ids.map(id => users.find(u => u.id === id) ?? null);
});

const user = await userLoader.load(post.authorId);
```

* * *

## Section 04 — Fix #3: `$queryRaw` for complex cases

```typescript
const result = await prisma.$queryRaw<Post[]>`
  SELECT p.*
  FROM "User" u
  CROSS JOIN LATERAL (
    SELECT *
    FROM "Post"
    WHERE "authorId" = u.id
    ORDER BY "createdAt" DESC
    LIMIT 5
  ) p
  WHERE u."tenantId" = ${tenantId}
`;

// Benchmark vs include chain:
// 10 authors  →  11 queries / 42ms   vs  1 query / 6ms
// 100 authors → 101 queries / 390ms  vs  1 query / 11ms
// 1k authors  → 1,001 queries / 4.1s vs  1 query / 68ms
```

* * *

## Section 05 — The part everyone skips: indexes

```typescript
// schema.prisma

model Subscription {
  id       String @id @default(cuid())
  tenantId String
  plan     String
  status   String

  @@index([tenantId])
  @@index([tenantId, status])
}

model Post {
  id       String @id @default(cuid())
  authorId String
  tenantId String

  @@index([authorId])
  @@index([tenantId, authorId])
}

// Rule: every FK column in a Prisma include, where, or orderBy needs an index.
// Confirm with EXPLAIN ANALYZE: "Index Scan" not "Seq Scan"
```

* * *

## Section 06 — Preventing regression with CI query budgets

```typescript
// lib/queryCounter.ts
export function createQueryCounter(prisma: PrismaClient) {
  let count = 0;
  prisma.$on('query', () => { count++; });
  return {
    reset: () => { count = 0; },
    get: () => count,
    assertMax: (max: number, label?: string) => {
      if (count > max) {
        throw new Error(
          `Query budget exceeded on ${label ?? 'unknown'}: expected ≤${max}, got ${count}`
        );
      }
    }
  };
}
```

```typescript
// __tests__/dashboard.test.ts
describe('GET /api/dashboard', () => {
  it('stays within query budget', async () => {
    const counter = createQueryCounter(prisma);
    await request(app)
      .get('/api/dashboard')
      .set('Authorization', `Bearer ${token}`);
    counter.assertMax(3, 'GET /api/dashboard');
  });
});
```

```typescript
// Express middleware — emit query count to APM
app.use(async (req, res, next) => {
  const counter = createQueryCounter(prisma);
  res.on('finish', () => {
    datadog.gauge('prisma.query_count', counter.get(), {
      route: req.route?.path ?? req.path,
      status: res.statusCode,
    });
  });
  next();
});
```

* * *

## Results

| Metric | Before | After |
| --- | --- | --- |
| P95 latency `/dashboard` | 4.2s | 210ms |
| DB queries per request | 1,847 | 3 |
| DB CPU (db.r6g.2xlarge) | 78% | 4% |
| Monthly RDS bill | $1,840 | $420 |

No infra changes. No cache layer. No schema redesign.