/api-performance-api-performance

name: api-performance-api-performance
description: Query optimization, caching, indexing, connection pooling, async patterns

Backend Performance Optimization

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

---

<critical_requirements>

CRITICAL: Before Using This Skill

**(You MUST always release database connections back to the pool using `finally` blocks)**

**(You MUST use eager loading or batching (DataLoader) to prevent N+1 queries -- never lazy load in loops)**

**(You MUST set TTL on all cached data to prevent stale data and memory exhaustion)**

**(You MUST offload CPU-intensive work to Worker Threads -- blocking the event loop degrades all requests)**

</critical_requirements>

---

**Detailed Resources:**

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

---

**Auto-detection:** connection pool, query optimization, database index, N+1, caching, cache invalidation, prepared statement, worker threads, event loop, CPU-bound, latency, throughput, performance tuning, EXPLAIN ANALYZE, keyset pagination, cache-aside, write-through

**When to use:**

• 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

**When NOT to use:**

• 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)

**Key patterns covered:**

• 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

---

<philosophy>

Philosophy

Backend performance optimization follows one core principle: **measure first, optimize second**. Premature optimization wastes development time and adds complexity without evidence of benefit.

**The Three Pillars of Backend Performance:**

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

**When to optimize:**

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

**When NOT to optimize:**

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

</philosophy>

---

<patterns>

Core Patterns

Pattern 1: Connection Pooling with Guaranteed Release

Connection pooling reuses database connections instead of creating new ones per request. A PostgreSQL handshake takes 20-30ms -- pooling eliminates this overhead.

**Key rules:**

• 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
  }
}

**Why good:** `finally` guarantees connection release even on error, preventing pool exhaustion

See [examples/core.md](examples/core.md) for full pool configuration, sizing formula, and external pooler guidance.

---

Pattern 2: N+1 Query Prevention

The N+1 problem occurs when fetching N records triggers N additional queries for related data. With 100 records, that's 101 database round-trips.

**Two solutions:**

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),
  });
}

**Why bad:** 1 query for jobs + N queries