Backend Performance Optimization

Quick Guide: Optimize backend performance through database query optimization (indexes, prepared statements, avoiding N+1), caching strategies (Redis cache-aside, write-through), connection pooling, and non-blocking async patterns. Always measure before optimizing.

<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 .with() or eager loading 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:

• •For code examples, see examples/ folder:
  • •caching.md - Redis caching patterns and strategies
  • •database.md - Query optimization, indexing, connection pooling
  • •async.md - Event loop, worker threads, CPU-bound tasks
• •For decision frameworks and anti-patterns, see reference.md

Auto-detection: Redis, connection pool, query optimization, database index, N+1, caching, cache invalidation, prepared statement, worker threads, event loop, CPU-bound, latency, throughput, performance tuning

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 or API costs

When NOT to use:

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

Key patterns covered:

• •Database indexing strategies (composite, partial, covering)
• •Connection pooling (node-postgres, external poolers)
• •N+1 query prevention (eager loading, DataLoader)
• •Redis caching (cache-aside, write-through, invalidation)
• •Event loop optimization (async patterns, setImmediate)
• •Worker threads for CPU-bound operations
• •Prepared statements for repeated queries

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
2. •Caching - Reduce repeated expensive operations
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
• •Load testing reveals scaling issues

When NOT to optimize:

• •"It might be slow someday" (premature)
• •Following blog post advice without context
• •Optimizing cold paths (rarely executed code)
• •Before profiling identifies the actual bottleneck

Core Patterns

Pattern 1: Database Connection Pooling

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

Configuration

// Good Example - Proper connection pooling with node-postgres
import { Pool } from "pg";

const POOL_MAX_CONNECTIONS = 20;
const POOL_IDLE_TIMEOUT_MS = 30000;
const POOL_CONNECTION_TIMEOUT_MS = 5000;

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: POOL_MAX_CONNECTIONS,
  idleTimeoutMillis: POOL_IDLE_TIMEOUT_MS,
  connectionTimeoutMillis: POOL_CONNECTION_TIMEOUT_MS,
});

// Listen for pool errors (idle clients can still emit errors)
pool.on("error", (err) => {
  console.error("Unexpected pool error:", err);
});

// For simple queries - auto-manages connection lifecycle
async function getUsers() {
  const result = await pool.query("SELECT * FROM users WHERE active = $1", [
    true,
  ]);
  return result.rows;
}

// For transactions - manual checkout with guaranteed 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
  }
}

// Graceful shutdown
async function shutdown() {
  await pool.end();
}

export { pool, getUsers, createUserWithProfile, shutdown };

Why good: Named constants document configuration, pool.query() auto-manages simple queries, finally block guarantees connection release preventing pool exhaustion, error listener catches backend failures, graceful shutdown prevents connection leaks

// Bad Example - Connection leak and missing error handling
import { Pool } from "pg";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

async function createUser(data) {
  const client = await pool.connect();
  await client.query("INSERT INTO users (name) VALUES ($1)", [data.name]);
  // Missing client.release() - connection leaked!
  // Missing error handling - transaction left open on failure
}

Why bad: Missing client.release() causes connection pool exhaustion, no try/catch means failed queries leave connections checked out forever, no transaction handling means partial writes possible

Pattern 2: N+1 Query Prevention

The N+1 problem occurs when fetching N records triggers N additional queries for related data. Use eager loading or batching instead.

Eager Loading with ORM

// Good Example - Single query with eager loading (Drizzle)
import { db } from "./database";
import { and, eq, isNull } from "drizzle-orm";

async function getJobsWithCompanies() {
  // Single SQL query fetches jobs + companies + locations
  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,
        },
      },
    },
  });

  return jobs;
}

Why good: .with() generates a single SQL query with JOINs, eliminates N+1 problem entirely, fully typed result prevents runtime errors

// Bad Example - N+1 query anti-pattern
async function getJobsWithCompanies() {
  const jobs = await db.query.jobs.findMany({
    where: eq(jobs.isActive, true),
  });

  // N+1: One query per job to get company!
  for (const job of jobs) {
    job.company = await db.query.companies.findFirst({
      where: eq(companies.id, job.companyId),
    });
  }

  return jobs;
}

Why bad: 1 query for jobs + N queries for companies = N+1 total queries, latency grows linearly with data size, database gets hammered with many small queries

DataLoader Pattern (GraphQL/REST)

// Good Example - DataLoader for batching
import DataLoader from "dataloader";

// Create loader once per request (caches within request lifecycle)
function createCompanyLoader() {
  return new DataLoader<string, Company>(async (companyIds) => {
    // Single query for all requested companies
    const companies = await db.query.companies.findMany({
      where: inArray(companies.id, [...companyIds]),
    });

    // Return in same order as requested IDs
    const companyMap = new Map(companies.map((c) => [c.id, c]));
    return companyIds.map((id) => companyMap.get(id) ?? null);
  });
}

// Usage in resolver or handler
async function resolveJob(job: Job, context: Context) {
  // DataLoader batches all .load() calls in same tick
  const company = await context.loaders.company.load(job.companyId);
  return { ...job, company };
}

Why good: DataLoader batches multiple .load() calls into single query, caches results within request preventing duplicate fetches, works with any data source (DB, API, etc.)

Pattern 3: Database Indexing

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

Index Strategies

// Good Example - Strategic indexes (Drizzle schema)
import {
  pgTable,
  uuid,
  varchar,
  timestamp,
  boolean,
  index,
} from "drizzle-orm/pg-core";
import { sql } from "drizzle-orm";

export const jobs = pgTable(
  "jobs",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    companyId: uuid("company_id").notNull(),
    title: varchar("title", { length: 255 }).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
    countryEmploymentIdx: index("jobs_country_employment_idx").on(
      table.country,
      table.employmentType,
    ),

    // Partial index - only indexes active non-deleted jobs
    activeJobsIdx: index("jobs_active_idx")
      .on(table.isActive, table.createdAt)
      .where(sql`${table.deletedAt} IS NULL`),

    // Foreign key index for JOIN performance
    companyIdIdx: index("jobs_company_id_idx").on(table.companyId),

    // Text search index
    titleIdx: index("jobs_title_idx").on(table.title),
  }),
);

Why good: Composite index matches common query patterns (country + employmentType), partial index reduces index size by excluding deleted records, foreign key index speeds up JOINs, column order in composite index matches query filter order

Index Decision Framework:

Additional Patterns

The following patterns are documented with full examples in examples/:

• •Redis Caching - Cache-aside, write-through, invalidation, TTL - see caching.md
• •Prepared Statements - Reuse query plans for repeated queries - see database.md
• •Event Loop Optimization - Async patterns, setImmediate chunking - see async.md
• •Worker Threads - CPU-bound operations without blocking - see async.md

<critical_reminders>

CRITICAL REMINDERS

All code must follow project conventions in CLAUDE.md

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

(You MUST use .with() or eager loading 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)

Failure to follow these rules will cause connection pool exhaustion, N+1 performance degradation, memory leaks from unbounded caches, and blocked event loops affecting all concurrent requests.

</critical_reminders>