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:

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

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

// 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

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

// 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

// 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

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

// 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

// 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}`
        );
      }
    }
  };
}

// __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');
  });
});

// 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.