Quick Guide: Optimize backend performance through database query optimization (indexes, prepared statements, avoiding N+1), caching strategies (cache-aside, write-through), connection pooling, and non-blocking async patterns. Always measure before optimizing -- run EXPLAIN ANALYZE, check event loop lag, and track cache hit rates before adding complexity.

All code must follow project conventions in CLAUDE.md (kebab-case, named exports, import ordering,

import type

, named constants)

finally

• examples/core.md - Database patterns: connection pooling, N+1 prevention, indexing, prepared statements, pagination
• examples/caching.md - Cache-aside, write-through, invalidation, key strategies, TTL guidance
• examples/async.md - Event loop optimization, worker threads, chunked processing, concurrency control
• reference.md - Decision frameworks, performance monitoring

• Database queries taking > 100ms
• High-traffic endpoints with repeated data fetches
• API responses with multiple related entities (N+1 risk)
• CPU-intensive operations blocking request handling
• Need to reduce database load via caching

• Premature optimization without measuring first
• Simple CRUD with low traffic (adds complexity without benefit)
• Data that changes frequently and must always be fresh (caching adds staleness)
• Development/debugging (caching obscures issues)

• Database indexing strategies (composite, partial, covering)
• Connection pooling with guaranteed release
• N+1 query prevention (eager loading, DataLoader)
• Caching strategies (cache-aside, write-through, invalidation)
• Event loop optimization (async I/O, setImmediate chunking)
• Worker threads for CPU-bound operations
• Keyset pagination for large datasets

1. Database Optimization - Indexes, query planning, N+1 prevention, pagination
2. Caching - Reduce repeated expensive operations with TTL-bounded cache
3. Async Efficiency - Never block the event loop

• Response times exceed SLA thresholds
• Database CPU/memory approaching limits
• Metrics show specific bottlenecks (EXPLAIN ANALYZE, event loop lag)
• Load testing reveals scaling issues

• "It might be slow someday" (premature)
• Optimizing cold paths (rarely executed code)
• Before profiling identifies the actual bottleneck

• Use

  pool.query()

  for simple queries (auto-manages connection lifecycle)
• For transactions, manually checkout with

  pool.connect()

  and always release in

  finally

• Listen for pool errors (idle clients can still emit errors)

// Transaction with guaranteed connection release
async function createUserWithProfile(
  userData: UserData,
  profileData: ProfileData,
) {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    const userResult = await client.query(
      "INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id",
      [userData.name, userData.email],
    );
    await client.query("INSERT INTO profiles (user_id, bio) VALUES ($1, $2)", [
      userResult.rows[0].id,
      profileData.bio,
    ]);
    await client.query("COMMIT");
    return userResult.rows[0];
  } catch (error) {
    await client.query("ROLLBACK");
    throw error;
  } finally {
    client.release(); // CRITICAL: Always release back to pool
  }
}

finally

1. Eager loading (ORM

   .with()

   ) -- single query with JOINs for known relationships
2. DataLoader -- batches

   .load()

   calls into single query per tick, ideal for GraphQL

// Eager loading: single query fetches jobs + companies + skills
const jobs = await db.query.jobs.findMany({
  where: and(eq(jobs.isActive, true), isNull(jobs.deletedAt)),
  with: {
    company: { with: { locations: true } },
    jobSkills: { with: { skill: true } },
  },
});

// BAD: N+1 anti-pattern -- one query per job
for (const job of jobs) {
  job.company = await db.query.companies.findFirst({
    where: eq(companies.id, job.companyId),
  });
}

• Missing connection release -- connections never returned to pool cause pool exhaustion and application hangs
• N+1 queries in loops -- fetching related data one-by-one instead of eager loading destroys performance
• Blocking event loop -- synchronous I/O or CPU-intensive work blocks all concurrent requests
• Cache without TTL -- unbounded cache grows until memory exhaustion or serves infinitely stale data
• Full table scans on large tables -- missing indexes on WHERE/JOIN columns

• No index on foreign keys -- JOINs and ON DELETE CASCADE become slow
• Over-indexing -- every index slows writes; remove unused indexes with

  pg_stat_user_indexes

• Cache key collisions -- generic keys cause wrong data returned to wrong users
• Offset pagination on large tables -- OFFSET scans all previous rows; use keyset pagination
• Unbounded parallelism --

  Promise.all

  on 10,000 items overwhelms downstream services

• EXPLAIN shows estimates; EXPLAIN ANALYZE shows actual -- always use ANALYZE for real performance data
• Composite index (a, b) does NOT help queries filtering only on b -- column order matters
• Applying functions to indexed columns (e.g.,

  YEAR(created_at)

  ) prevents index use -- rewrite as range conditions
• DataLoader caches within request -- don't reuse across requests or you get stale data
• Worker threads have ~30ms startup overhead -- don't use for fast operations
• Connection pool

  idleTimeoutMillis

  can cause "connection terminated unexpectedly" if set too short

• SELECT *

  fetches unnecessary data and prevents covering index optimization -- select specific columns
• Cache SET with EX option replaces existing TTL -- calling SET again resets expiration timer

All code must follow project conventions in CLAUDE.md

finally