Real incident: a feed endpoint with a hidden N+1 took down Postgres at 2am IST during a marketing campaign spike. 12-minute outage. Mesrai catches the shape on every PR now.

The vulnerable diff

tsapi/feed-resolvers.ts

// api/feed-resolvers.ts (the buggy resolver)
export async function getFeed(userId: string) {
  const posts = await db.posts.findMany({ where: { feedUserId: userId }, take: 50 });
  // BUG: N+1 — one query per post for items
  const result = await Promise.all(
    posts.map(async (p) => ({
      ...p,
      items: await db.feedItems.findMany({ where: { postId: p.id } }),
    }))
  );
  return result;
}

What is wrong

Same N+1 pattern as cluster 4 post 31, real-incident version. A feed endpoint with 50 posts issues 51 queries per call. Under steady-state traffic (5 RPS) the database handles it. During a marketing campaign at 2am IST (200 RPS), 10K queries per second hit Postgres simultaneously, exhausted the connection pool, the pool overflowed onto disk swap, memory pressure crashed the instance.

The attack

Post-mortem timeline:

textexploit

t+0:00 — Marketing campaign push at 2am IST.
t+0:01 — Traffic spike. Feed endpoint at 200 RPS.
t+0:02 — Postgres connection pool exhausted (max 200, all in use).
t+0:03 — New queries queue. Latency p99 jumps to 8s.
t+0:05 — Postgres OOMs. Instance crashes.
t+0:05 — RDS auto-failover. 90s downtime during failover.
t+0:07 — Failover complete. Same load → same crash on new instance.
t+0:08 — Pager. Engineer pauses feed endpoint at LB.
t+0:12 — Marketing aware. Hotfix: rewrite resolver with IN-clause.
t+0:18 — Hotfix deployed. Feed re-enabled. Traffic absorbed.

Cost: 12 minutes downtime, missed peak conversion window.

Familiar shape.

Mesrai's review comment

textreview-comment.txt

mesraipilot · Bot · reviewed 45 sec ago

[mesrai] [code-review] [Performance] [N+1] [high]

`posts.map(async p => db.feedItems.findMany(...))` is N+1. For 50
posts: 51 queries per request. Under load this exhausts the
connection pool.

Batch with IN clause:

  const posts = await db.posts.findMany({ where: { feedUserId: userId }, take: 50 });
  const postIds = posts.map(p => p.id);
  const items = await db.feedItems.findMany({
    where: { postId: { in: postIds } },
  });
  const byPost = new Map<string, FeedItem[]>();
  for (const item of items) {
    if (!byPost.has(item.postId)) byPost.set(item.postId, []);
    byPost.get(item.postId)!.push(item);
  }
  return posts.map(p => ({ ...p, items: byPost.get(p.id) ?? [] }));

For deeper joins, Prisma `include` does this for you.

The fix

tsapi/feed-resolvers.ts (fixed)

// api/feed-resolvers.ts — fixed
export async function getFeed(userId: string) {
  const posts = await db.posts.findMany({ where: { feedUserId: userId }, take: 50 });
  const postIds = posts.map(p => p.id);
  const items = await db.feedItems.findMany({
    where: { postId: { in: postIds } },
  });

  const byPost = new Map<string, FeedItem[]>();
  for (const item of items) {
    (byPost.get(item.postId) ?? byPost.set(item.postId, []).get(item.postId)!).push(item);
  }

  return posts.map(p => ({ ...p, items: byPost.get(p.id) ?? [] }));
}

Two queries total instead of 51. Connection pool stays healthy under any traffic. Memory pressure on Postgres gone.

Why human review missed it

N+1 is the most common reason for traffic-spike outages in CRUD services. Mesrai catches every loop with a DB call on every PR.

Related rules + further reading

Mesrai rule pack: performance/no-n-plus-one (cluster 4 ref).

PostgreSQL Wiki: Slow Query Optimization.

Same shape as cluster 4 post 31 — different surface.

Takeaway

N+1 + traffic spike = outage. Two queries total instead of 51. Mesrai catches the pattern; cluster 4 covered it; cluster 10 shows the incident cost.

A Hidden N+1 That Took Down Postgres at 2AM IST