Benjamin Adams

Case study · PlusWellbeing.ai

9s → 0.25s: killing an N+1 in a healthcare API

The schema and query plans below are recreated on a toy dataset out of respect for employer confidentiality. The shapes, techniques, and numbers in the headline are the real ones.

A core endpoint — the one that lists a user’s chat sessions — was taking about 9 seconds at the median for active users. Tracing showed the API firing roughly 75 queries per request, adding up to ~98 seconds of cumulative database time across a session list. Nothing was slow individually; there was just an absurd number of round trips.

The shape was the classic N+1, twice over:

const sessions = await db.query(
  "SELECT * FROM chat_sessions WHERE user_id = $1", [userId]);

for (const session of sessions) {          // ~25 sessions
  // 1 query: the latest message for the preview line
  session.lastMessage = await db.query(
    "SELECT * FROM messages WHERE session_id = $1
     ORDER BY created_at DESC LIMIT 1", [session.id]);

  // 1 query: unread count
  session.unread = await db.query(
    "SELECT COUNT(*) FROM messages
     WHERE session_id = $1 AND read_at IS NULL", [session.id]);

  // 1 query: participant profile
  session.participant = await db.query(...);
}                                          // ≈ 1 + 25 × 3 queries

The rewrite: think in sets, not rows

Three per-row lookups became two set-based queries. DISTINCT ON — Postgres’s “first row per group” idiom — fetches every session’s latest message in one pass, and a single GROUP BY computes all unread counts at once:

-- latest message per session, one query for all sessions
SELECT DISTINCT ON (m.session_id) m.*
FROM messages m
WHERE m.session_id = ANY($1)
ORDER BY m.session_id, m.created_at DESC;

-- unread counts for all sessions, one query
SELECT session_id, COUNT(*) AS unread
FROM messages
WHERE session_id = ANY($1) AND read_at IS NULL
GROUP BY session_id;

On the toy dataset (25 sessions, ~200 messages each), the before/after plans tell the story — 76 plan executions collapse into two:

-- BEFORE (per session, × 75):
Limit (cost=0.42..4.10 rows=1)  actual time=1.2..1.3
  -> Index Scan Backward using messages_session_created_idx
     Filter: (session_id = '…')
-- × 75 round trips ≈ tens of seconds of cumulative DB time

-- AFTER (once):
Unique (cost=310..390 rows=25)  actual time=3.8..4.1
  -> Index Scan using messages_session_created_idx
HashAggregate (cost=180..205 rows=25)  actual time=2.9..3.1
-- 3 queries total, single-digit milliseconds each

The sneaky half: decryption

Fixing the queries only got the endpoint to ~1s. The rest was application-side: message fields are encrypted at rest, and the old code decrypted every field on every row it touched — including fields the response never used. Restricting decryption to the two fields the session list actually renders (preview text, sender name) cut the remaining time to ~250ms.

Final result: ~75 queries → 3 · ~98s cumulative DB time → ~20ms · 9s → 0.25s endpoint latency.

What I took from it