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

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

(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 - 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

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

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

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 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 for companies, latency grows linearly with data size

See examples/core.md for DataLoader batching pattern.

Pattern 3: Database Indexing

Indexes speed up queries by avoiding full table scans. Index columns used in WHERE, JOIN, and ORDER BY clauses.

// Strategic indexes on a table definition
export const jobs = pgTable(
  "jobs",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    companyId: uuid("company_id").notNull(),
    country: varchar("country", { length: 100 }),
    employmentType: varchar("employment_type", { length: 50 }),
    isActive: boolean("is_active").default(true),
    createdAt: timestamp("created_at").defaultNow(),
    deletedAt: timestamp("deleted_at"),
  },
  (table) => [
    // Composite index for common filter combination
    index("jobs_country_employment_idx").on(
      table.country,
      table.employmentType,
    ),
    // Partial index -- only indexes active non-deleted jobs
    index("jobs_active_idx")
      .on(table.isActive, table.createdAt)
      .where(sql`${table.deletedAt} IS NULL`),
    // Foreign key index for JOIN performance
    index("jobs_company_id_idx").on(table.companyId),
  ],
);

Index Decision Framework: