The slow query I should have caught in staging

The ticket said “dashboard feels sluggish.” Not broken, just slow enough that coordinators stopped refreshing volunteer lists during busy shifts. That is often harder to fix than a hard outage, because everything technically works.

This post is a full walkthrough: the endpoint, what the ORM hid, what EXPLAIN ANALYZE revealed, every fix we applied, and the database habits I wish I had before production traffic arrived. If you build list endpoints, admin dashboards, or multi-tenant SaaS on Postgres, most of this will apply directly.

---

The endpoint

GET /orgs/:orgId/volunteers, a paginated list with:

• Volunteers for one organization
• Assignment count for the current month
• Program memberships
• Sorted by created_at DESC
• 50 rows per page

Fine with 200 volunteers in development. Unusable at 40,000.

---

What the ORM code looked like

const volunteers = await db.query.volunteers.findMany({
  where: eq(volunteers.organizationId, orgId),
  with: {
    assignments: {
      where: gte(assignments.startsAt, startOfMonth),
    },
    programMemberships: true,
  },
  limit: 50,
  offset: page * 50,
});

Fifty rows. Innocent.

What actually hit the database

Depending on Drizzle/Prisma relation loading:

1. SELECT * FROM volunteers WHERE organization_id = $1 ORDER BY created_at DESC LIMIT 50 OFFSET $2
2. For each of 50 volunteers: SELECT * FROM assignments WHERE volunteer_id = $n AND starts_at >= $month
3. For each of 50 volunteers: SELECT * FROM program_memberships WHERE volunteer_id = $n

Up to 101 queries per page load. At 0.5ms each, that is 50ms minimum, before serialization, before concurrent users, before pool contention.

This is the classic N+1 problem. ORMs make it easy to write and hard to see until production scale.

---

How to catch it before production

1. Log query count per request in staging:

let queryCount = 0;
const client = db.$client;
const originalQuery = client.query.bind(client);
client.query = (...args) => {
  queryCount++;
  return originalQuery(...args);
};

// after handler
logger.info("request_queries", { path: req.path, queryCount });

If a list endpoint runs 50+ queries, investigate.

2. Seed staging with realistic volume, not 10 rows. Generate 50,000 volunteers for one org.

3. EXPLAIN (ANALYZE, BUFFERS) on every distinct query shape.

---

Reading EXPLAIN output (practical, not academic)

Single volunteer assignment fetch

EXPLAIN (ANALYZE, BUFFERS)
SELECT a.*
FROM assignments a
WHERE a.volunteer_id = '7c9e6679-7425-40de-944b-e07fc1f90ae7'
  AND a.starts_at >= '2026-03-01';

Index Scan using assignments_volunteer_id_idx on assignments a
  (cost=0.42..124.55 rows=48 width=96)
  (actual time=0.038..0.412 rows=52 loops=1)
  Index Cond: (volunteer_id = '...')
  Filter: (starts_at >= '2026-03-01')

Read this as:

• Index Scan means it used an index, good
• actual time=0.412 is ~0.4ms for this volunteer
• Filter: (starts_at >= ...) means the index found by volunteer_id, then filtered by date. A composite index can do better.
• loops=1 means it ran once. In N+1, loops=50.

Deep pagination disaster

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM volunteers
WHERE organization_id = '...'
ORDER BY created_at DESC
LIMIT 50 OFFSET 10000;

Limit  (actual time=842..845 rows=50)
  ->  Index Scan using volunteers_org_created_idx on volunteers
        (actual time=0.02..841 rows=10050)

Postgres walked 10,050 rows to skip 10,000 and return 50. Offset pagination scales linearly with page depth. Page 200 is not like page 1.

---

Fix 1: eliminate N+1 with batching

Two-query approach:

const rows = await db
  .select()
  .from(volunteers)
  .where(eq(volunteers.organizationId, orgId))
  .orderBy(desc(volunteers.createdAt))
  .limit(50);

const ids = rows.map((r) => r.id);

const [assignmentCounts, memberships] = await Promise.all([
  db
    .select({
      volunteerId: assignments.volunteerId,
      count: sql<number>`count(*)::int`,
    })
    .from(assignments)
    .where(
      and(
        inArray(assignments.volunteerId, ids),
        gte(assignments.startsAt, startOfMonth),
      ),
    )
    .groupBy(assignments.volunteerId),

  db
    .select()
    .from(programMemberships)
    .where(inArray(programMemberships.volunteerId, ids)),
]);

const countByVolunteer = new Map(assignmentCounts.map((r) => [r.volunteerId, r.count]));
const membershipsByVolunteer = groupBy(memberships, (m) => m.volunteerId);

return rows.map((v) => ({
  ...v,
  assignmentCount: countByVolunteer.get(v.id) ?? 0,
  programs: membershipsByVolunteer.get(v.id) ?? [],
}));

3 queries total, fixed cost per page.

When you need one SQL query

Sort by assignment count in the database:

SELECT
  v.id,
  v.name,
  v.email,
  v.created_at,
  COUNT(a.id) FILTER (WHERE a.starts_at >= $2) AS assignment_count
FROM volunteers v
LEFT JOIN assignments a ON a.volunteer_id = v.id
WHERE v.organization_id = $1
GROUP BY v.id
ORDER BY assignment_count DESC, v.created_at DESC
LIMIT 50;

One round trip. Planner can optimize joins with proper indexes.

---

Fix 2: cursor pagination

Replace OFFSET with keyset pagination on (created_at, id):

type Cursor = { createdAt: Date; id: string };

function decodeCursor(raw: string | undefined): Cursor | null {
  if (!raw) return null;
  const [iso, id] = Buffer.from(raw, "base64url").toString().split("|");
  return { createdAt: new Date(iso), id };
}

function encodeCursor(row: { createdAt: Date; id: string }) {
  return Buffer.from(`${row.createdAt.toISOString()}|${row.id}`).toString("base64url");
}

const cursor = decodeCursor(req.query.cursor);

const rows = await db
  .select()
  .from(volunteers)
  .where(
    and(
      eq(volunteers.organizationId, orgId),
      cursor
        ? or(
            lt(volunteers.createdAt, cursor.createdAt),
            and(
              eq(volunteers.createdAt, cursor.createdAt),
              lt(volunteers.id, cursor.id),
            ),
          )
        : undefined,
    ),
  )
  .orderBy(desc(volunteers.createdAt), desc(volunteers.id))
  .limit(50);

const nextCursor = rows.length === 50 ? encodeCursor(rows[rows.length - 1]) : null;

Each page: index seek + 50 rows. Page 1 and page 500 cost the same.

Trade-off: no “jump to page 47” without storing cursors or accepting expensive offset for rare jumps.

---

Fix 3: indexes that match access patterns

Volunteers list

CREATE INDEX volunteers_org_created_id_idx
  ON volunteers (organization_id, created_at DESC, id DESC);

Covers WHERE organization_id = ? ORDER BY created_at DESC, id DESC for cursor pagination.

Assignments by volunteer + month

CREATE INDEX assignments_volunteer_starts_idx
  ON assignments (volunteer_id, starts_at DESC);

Equality on volunteer_id first, range on starts_at second. Column order matters.

Index that looks useful but is not

CREATE INDEX assignments_starts_at_idx ON assignments (starts_at);

Useless for WHERE volunteer_id = ? AND starts_at >= ?. Leading column must match equality filters in your query.

Measuring index value

SELECT
  schemaname,
  relname AS table,
  indexrelname AS index,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

Indexes with idx_scan = 0 for months are candidates to drop. Every index slows writes.

Enable pg_stat_statements:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT
  calls,
  mean_exec_time,
  total_exec_time,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Optimize what actually runs hot, not what you imagine runs hot.

---

Fix 4: connection pool exhaustion

While fixing queries, p99 latency still spiked. CPU was fine. Pool was empty.

avg queries per request: 101 → 3 after N+1 fix
concurrent dashboard users: 40
pool size: 20

Each request held a connection across many sequential queries. Under load, requests queued for pool slots → timeouts → retries → worse.

Sizing rule of thumb:

pool_per_instance ≈ (expected concurrent requests × avg queries per request) / queries_parallelized

But stay below Postgres max_connections divided by instance count. Leave headroom for migrations and admin consoles.

Use PgBouncer in transaction mode for many small app instances. Watch waiting count on pool metrics.

---

Schema design lessons from the same product

Normalize first, denormalize when measured

We considered assignment_count_month column on volunteers, updated by trigger.

Pros: instant reads
Cons: trigger complexity, write amplification, drift risk

For 50-row pages with batched aggregation, live COUNT was fast enough after indexes. For org-wide analytics across millions of rows, we added a nightly rollup table:

CREATE TABLE volunteer_stats_daily (
  organization_id uuid NOT NULL,
  volunteer_id uuid NOT NULL,
  stat_date date NOT NULL,
  assignment_count int NOT NULL,
  PRIMARY KEY (organization_id, volunteer_id, stat_date)
);

Populated by cron. Dashboard “this month” sums daily rows, which is cheap at scale.

Foreign keys and cascades

Always FK volunteer_id → volunteers.id with explicit ON DELETE behavior. RESTRICT vs CASCADE is a product decision, so document it.

Soft deletes

deleted_at on volunteers complicates every query:

WHERE organization_id = $1 AND deleted_at IS NULL

Partial index:

CREATE INDEX volunteers_active_org_created_idx
  ON volunteers (organization_id, created_at DESC)
  WHERE deleted_at IS NULL;

---

Locks, deadlocks, and migrations

Row-level locks during assignment

BEGIN;
SELECT * FROM shifts WHERE id = $1 FOR UPDATE;
-- check status, insert assignment
COMMIT;

FOR UPDATE prevents two coordinators assigning the last seat simultaneously.

Deadlock story

Transaction A: lock shift 1, then shift 2.
Transaction B: lock shift 2, then shift 1.
Postgres kills one. Application must retry.

Fix: lock resources in consistent order (always lower UUID first), or lock at coarser granularity.

Safe migrations on large tables

Add index without blocking writes:

CREATE INDEX CONCURRENTLY assignments_volunteer_starts_idx
  ON assignments (volunteer_id, starts_at DESC);

CONCURRENTLY avoids long ACCESS EXCLUSIVE lock. Takes longer. Can fail, so retry failed index builds.

Add NOT NULL column to millions of rows:

1. ADD COLUMN new_col type NULL
2. Backfill in batches: UPDATE ... WHERE id > $cursor LIMIT 1000
3. ALTER COLUMN SET DEFAULT
4. ALTER COLUMN SET NOT NULL when no nulls remain

Never jump to step 4 on day one during peak traffic.

---

Second scenario: inventory reservation

Commerce platform, reserving stock at checkout.

Bad:

SELECT quantity FROM inventory WHERE sku = $1;
-- app checks quantity > 0
UPDATE inventory SET quantity = quantity - 1 WHERE sku = $1;

Race: two checkouts read quantity = 1, both decrement, stock goes negative.

Good:

UPDATE inventory
SET quantity = quantity - 1
WHERE sku = $1 AND quantity > 0
RETURNING quantity;

If zero rows returned, out of stock. Single atomic statement.

For high contention SKUs, queue reservations or use advisory locks per SKU.

---

Read replicas and staleness

Reporting dashboards can read from replica:

const reportingDb = process.env.REPORTING_DATABASE_URL ?? db;

Accept replication lag of 100ms to seconds. Never read-your-writes from replica for user-facing actions immediately after a write.

---

Checklist before shipping database-heavy endpoints

1. Seed staging with production-scale row counts
2. Count queries per request, targeting under 5 for list endpoints
3. EXPLAIN (ANALYZE, BUFFERS) on each query shape
4. Index leading columns match equality filters, then ranges
5. Cursor pagination for deep lists
6. Pool size tested under concurrent load
7. Migrations use CONCURRENTLY and expand/contract for column changes
8. pg_stat_statements enabled in staging and production

---

Results

Metric	Before	After
Queries per page	~101	3
p95 latency (page 1)	800ms	90ms
p95 latency (deep list)	12s	110ms
Indexes added	0	2
User-visible changes	none	none

The lesson is not a clever trick. The database does exactly what you ask. If you ask 101 times per page, it answers 101 times. Slow queries are honest about your access patterns, so learn to read them in ORM code and in EXPLAIN output. That skill pays off on every product you touch.