Backend Performance

Backend Performance

Measure, diagnose, and fix server-side performance: database queries, API latency, memory leaks.

Core principle: Measure the specific path. "The API is slow" is not a diagnosis. "The GET /tasks endpoint at p95 is 2.3s due to an N+1 query on task.assignee" is.

When to Use

• API endpoint latency is unacceptably high
• Database queries are slow or timing out
• Memory usage grows without bound
• Suspecting N+1 queries or missing indexes
• Setting latency budgets and regression detection

When NOT to Use

• Frontend/browser performance (use frontend-performance skill)
• Initial development without any measured problem
• Premature optimization (measure first)

Measurement First

Establish Baseline

Before any change, record:

• Metric value at p50, p95, p99
• Measurement method (tool, sample size, date, commit hash)

After optimization, compare with the same method.

Diagnosis Decision Tree

API is slow
  |
  ├── WHERE is the time spent?
  │     ├── Database queries → EXPLAIN plan, slow query log
  │     ├── External API calls → tracing, timeout analysis
  │     ├── CPU computation → profiling tool
  │     └── Memory pressure / GC → heap profiling
  |
  ├── WHAT is the specific number? (p95 = 2.3s)
  |
  └── FIX the specific root cause → measure again

Profiling Tools

Database Query Optimization

Reading EXPLAIN Plans (PostgreSQL)

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT t.*, u.name FROM tasks t JOIN users u ON t.user_id = u.id
WHERE t.org_id = $1 AND t.status = 'pending';

Finding Slow Queries

-- PostgreSQL: top slow queries
SELECT query, mean_exec_time, calls, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

-- Enable slow query log (MySQL)
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;  -- log queries > 500ms

N+1 Query Problem

Detection

// N+1 pattern: 1 query + N queries
// Log: SELECT * FROM tasks (1 query)
//      SELECT * FROM users WHERE id = 1
//      SELECT * FROM users WHERE id = 2
//      ... (N more queries)

Fix: Eager Loading

// N+1: separate query per task
const tasks = await Task.findAll();
for (const task of tasks) {
  task.user = await User.findById(task.userId);  // N queries!
}

// Fix: single JOIN query
const tasks = await Task.findAll({
  include: [{ model: User }],  // 1 query with JOIN
});

Fix: DataLoader (Batching)

import DataLoader from 'dataloader';

const userLoader = new DataLoader(async (ids: readonly string[]) => {
  const users = await User.findAll({ where: { id: [...ids] } });
  return ids.map(id => users.find(u => u.id === id) ?? null);
});

// Now batched automatically: 1 query for N users
const user = await userLoader.load(task.userId);

Index Strategy

When to Add an Index

Add an index on any column that appears in:

• WHERE clause on tables with >10k rows
• JOIN ON (always index foreign keys)
• ORDER BY (for avoiding filesort)
• GROUP BY (for covering the group)

-- Foreign key index (add if missing)
CREATE INDEX idx_tasks_user_id ON tasks(user_id);

-- Composite index for multi-column WHERE
CREATE INDEX idx_tasks_org_status ON tasks(org_id, status);

-- Partial index for soft-delete pattern
CREATE INDEX idx_tasks_active ON tasks(id) WHERE deleted_at IS NULL;

Index Anti-Patterns

• Over-indexing: >15 indexes on one table (slow writes)
• Indexing low-cardinality columns (boolean, status with 2 values — check selectivity)
• Non-sargable predicates: WHERE YEAR(created_at) = 2025 (can't use index)

Unbounded Data Fetches

// DANGEROUS: fetches entire table
const tasks = await Task.findAll();

// SAFE: always paginate
const tasks = await Task.findAll({
  where: { orgId },
  limit: Math.min(Number(req.query.limit) || 20, 100),
  offset: (Number(req.query.page) || 0) * limit,
  order: [['createdAt', 'DESC']],
});

Rule: Every list query needs a LIMIT. Max limit should be enforced server-side (not just relied on from client).

Memory Leak Detection

Indicators

• Heap grows monotonically over hours (not just during traffic spikes)
• GC runs more frequently but reclaims less over time
• RSS keeps growing after traffic drops

Profiling

// Node.js: track heap over time
setInterval(() => {
  const { heapUsed, heapTotal, rss } = process.memoryUsage();
  console.log({
    heapUsed: Math.round(heapUsed / 1024 / 1024) + 'MB',
    heapTotal: Math.round(heapTotal / 1024 / 1024) + 'MB',
    rss: Math.round(rss / 1024 / 1024) + 'MB',
  });
}, 30_000);

Common Leak Sources

API Latency Budgets

Regression Detection

Caching Strategy

// Redis caching with TTL
async function getUser(id: string): Promise<User> {
  const cached = await redis.get(`user:${id}`);
  if (cached) return JSON.parse(cached);
  
  const user = await db.findUser(id);
  await redis.setex(`user:${id}`, 300, JSON.stringify(user));  // 5min TTL
  return user;
}

Cache invalidation rules:

• Invalidate on write, not on read
• TTL-based expiry as safety net
• Cache computed results, not raw DB results
• Never cache: user-specific auth state, sensitive PII without encryption

Common Mistakes

Verification Checklist

• p50/p95/p99 baselines recorded before optimization
• EXPLAIN plans reviewed for all slow queries (no Seq Scan on large tables)
• N+1 queries eliminated (DataLoader or eager loading)
• All list endpoints paginated with enforced max limit
• Foreign key columns indexed
• Memory usage stable over time (no monotonic growth)
• Latency budgets defined with alerting
• Cache implemented for expensive, frequently-read data
• Regression thresholds set (+50% p95 → alert)