api-performance-api-performance ยท diff
git:20260202.b236384 to git:20260316.5c62783
176 added, 180 removed. Audit A to A.
---
name: api-performance-api-performance
- description: Query optimization, caching, indexing
+ 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 (Redis cache-aside, write-through), connection pooling, and non-blocking async patterns. Always measure before optimizing.
+ > **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 `.with()` or eager loading to prevent N+1 queries - never lazy load in loops)**
+ **(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)**
+ **(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/](examples/) folder:
- - [caching.md](examples/caching.md) - Redis caching patterns and strategies
- - [database.md](examples/database.md) - Query optimization, indexing, connection pooling
- - [async.md](examples/async.md) - Event loop, worker threads, CPU-bound tasks
- - For decision frameworks and anti-patterns, see [reference.md](reference.md)
+ - [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, monitoring, anti-patterns
---
- **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
+ **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 or API costs
+ - 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 be fresh (caching adds staleness)
+ - 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 (node-postgres, external poolers)
+ - Connection pooling with guaranteed release
- N+1 query prevention (eager loading, DataLoader)
- - Redis caching (cache-aside, write-through, invalidation)
- - Event loop optimization (async patterns, setImmediate)
+ - Caching strategies (cache-aside, write-through, invalidation)
+ - Event loop optimization (async I/O, setImmediate chunking)
- Worker threads for CPU-bound operations
- - Prepared statements for repeated queries
+ - 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
- 2. **Caching** - Reduce repeated expensive operations
+ 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
+ - Metrics show specific bottlenecks (EXPLAIN ANALYZE, event loop lag)
- 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
</philosophy>
---
<patterns>
## 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
-
- ```typescript
- // 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;
+ ### Pattern 1: Connection Pooling with Guaranteed Release
- const pool = new Pool({
- connectionString: process.env.DATABASE_URL,
- max: POOL_MAX_CONNECTIONS,
- idleTimeoutMillis: POOL_IDLE_TIMEOUT_MS,
- connectionTimeoutMillis: POOL_CONNECTION_TIMEOUT_MS,
- });
+ Connection pooling reuses database connections instead of creating new ones per request. A PostgreSQL handshake takes 20-30ms -- pooling eliminates this overhead.
- // Listen for pool errors (idle clients can still emit errors)
- pool.on("error", (err) => {
- console.error("Unexpected pool error:", err);
- });
+ **Key rules:**
- // 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;
- }
+ - 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)
- // For transactions - manual checkout with guaranteed release
+ ```typescript
+ // 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
}
}
-
- // 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
-
- ```typescript
- // 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 good:** `finally` guarantees connection release even on error, preventing pool exhaustion
- **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
+ 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. Use eager loading or batching instead.
-
- #### Eager Loading with ORM
-
- ```typescript
- // 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,
- },
- },
- },
- });
+ 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.
- return jobs;
- }
- ```
+ **Two solutions:**
- **Why good:** `.with()` generates a single SQL query with JOINs, eliminates N+1 problem entirely, fully typed result prevents runtime errors
+ 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
```typescript
- // 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;
- }
+ // 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 } },
+ },
+ });
```
- **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)
-
```typescript
- // 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);
+ // 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),
});
}
-
- // 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.)
+ **Why bad:** 1 query for jobs + N queries for companies, latency grows linearly with data size
+ See [examples/core.md](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.
- #### Index Strategies
-
```typescript
- // Good Example - Strategic indexes (Drizzle schema)
- import {
- pgTable,
- uuid,
- varchar,
- timestamp,
- boolean,
- index,
- } from "drizzle-orm/pg-core";
- import { sql } from "drizzle-orm";
-
+ // Strategic indexes -- array return format (Drizzle v0.36+)
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) => ({
+ (table) => [
// Composite index for common filter combination
- countryEmploymentIdx: index("jobs_country_employment_idx").on(
+ index("jobs_country_employment_idx").on(
table.country,
table.employmentType,
),
-
- // Partial index - only indexes active non-deleted jobs
- activeJobsIdx: index("jobs_active_idx")
+ // 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
- companyIdIdx: index("jobs_company_id_idx").on(table.companyId),
-
- // Text search index
- titleIdx: index("jobs_title_idx").on(table.title),
- }),
+ index("jobs_company_id_idx").on(table.companyId),
+ ],
);
```
- **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:**
| Column Usage | Index Type | When to Use |
| --------------------------- | ---------------- | ---------------------------------------------- |
| WHERE equality | B-tree (default) | High-selectivity columns |
| WHERE range (>, <, BETWEEN) | B-tree | Date ranges, numeric ranges |
| WHERE multiple columns | Composite | Queries always filter by same columns together |
| WHERE on subset | Partial | Most queries filter on active/non-deleted |
| Full-text search | GIN/GiST | Text search with LIKE, tsvector |
| JSON field access | GIN | JSONB column queries |
+ **Composite index column order:** Equality conditions first, range conditions last, high selectivity first.
+
+ See [examples/core.md](examples/core.md) for EXPLAIN ANALYZE examples, index monitoring queries, and unused index detection.
+
+ ---
+
+ ### Pattern 4: Cache-Aside with TTL
+
+ The most common caching pattern. Check cache first, fetch from database on miss, store with TTL.
+
+ ```typescript
+ const CACHE_TTL_SECONDS = 300;
+ const CACHE_PREFIX = "app:user";
+
+ async function getUserById(userId: string): Promise<User | null> {
+ const cacheKey = `${CACHE_PREFIX}:${userId}`;
+ const cached = await cacheClient.get(cacheKey);
+ if (cached) return JSON.parse(cached) as User;
+
+ const user = await db.query.users.findFirst({ where: eq(users.id, userId) });
+ if (!user) return null;
+
+ await cacheClient.set(cacheKey, JSON.stringify(user), {
+ EX: CACHE_TTL_SECONDS,
+ });
+ return user;
+ }
+ ```
+
+ **Why good:** TTL prevents stale data accumulation, namespaced keys prevent collisions, early return on cache hit
+
+ See [examples/caching.md](examples/caching.md) for write-through, tag-based invalidation, key strategies, and TTL guidance.
+
+ ---
+
+ ### Pattern 5: Worker Threads for CPU-Bound Operations
+
+ Node.js uses a single thread for JavaScript. CPU-intensive work blocks ALL concurrent requests.
+
+ **Rule of thumb:**
+
+ | CPU Duration | Solution | Rationale |
+ | ------------ | --------------------- | ------------------------------------- |
+ | < 50ms | Keep on main thread | Worker overhead not worth it |
+ | 50-500ms | setImmediate chunking | Yields to event loop between chunks |
+ | > 500ms | Worker Threads | Offload completely to separate thread |
+
+ ```typescript
+ // Chunked processing with setImmediate -- yields to event loop between batches
+ const CHUNK_SIZE = 100;
+
+ async function processLargeArray(items: Item[]): Promise<ProcessedItem[]> {
+ const results: ProcessedItem[] = [];
+ for (let i = 0; i < items.length; i += CHUNK_SIZE) {
+ const chunk = items.slice(i, i + CHUNK_SIZE);
+ for (const item of chunk) {
+ results.push(expensiveTransform(item));
+ }
+ if (i + CHUNK_SIZE < items.length) {
+ await new Promise((resolve) => setImmediate(resolve));
+ }
+ }
+ return results;
+ }
+ ```
+
+ See [examples/async.md](examples/async.md) for worker pool implementation, concurrency control with p-limit, and event loop lag monitoring.
+
+ ---
+
+ ### Pattern 6: Keyset Pagination for Large Datasets
+
+ OFFSET pagination scans all previous rows -- at OFFSET 100,000 the database reads and discards 100,000 rows. Keyset pagination uses a cursor for constant-time performance.
+
+ ```sql
+ -- BAD: OFFSET scans all previous rows
+ SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 100000;
+
+ -- GOOD: Keyset pagination -- constant time regardless of position
+ SELECT * FROM products
+ WHERE id > :last_seen_id
+ ORDER BY id LIMIT 20;
+ ```
+
+ **When to use offset:** Small datasets (< 100k rows), need total count, random page access required.
+
+ **When to use keyset:** Large datasets, infinite scroll, real-time data where inserts shouldn't cause duplicates.
+
+ See [examples/core.md](examples/core.md) for full TypeScript implementations of both patterns.
+
</patterns>
---
- ## Additional Patterns
+ <red_flags>
- The following patterns are documented with full examples in [examples/](examples/):
+ ## RED FLAGS
- - **Redis Caching** - Cache-aside, write-through, invalidation, TTL - see [caching.md](examples/caching.md)
- - **Prepared Statements** - Reuse query plans for repeated queries - see [database.md](examples/database.md)
- - **Event Loop Optimization** - Async patterns, setImmediate chunking - see [async.md](examples/async.md)
- - **Worker Threads** - CPU-bound operations without blocking - see [async.md](examples/async.md)
+ **High Priority Issues:**
+ - 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
+
+ **Medium Priority Issues:**
+
+ - 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
+
+ **Gotchas & Edge Cases:**
+
+ - 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
+
+ </red_flags>
+
---
<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 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)**
+ **(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>