api-performance-api-performance
Query optimization, caching, indexing, connection pooling, async patterns
Install
npx skills add https://github.com/agents-inc/skills/tree/main/dist/plugins/api-performance-api-performance/skills/api-performance-api-performance
claude plugin marketplace add https://llmmart.ai/marketplace.json && claude plugin install agents-inc-skills@llmmart
git clone https://github.com/agents-inc/skills.git
The skills CLI installs just this skill, for any of its supported agents. Claude Code installs the whole agents-inc/skills collection as a plugin from our marketplace. Git is the plain clone.
Skill manifest
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 - 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
<red_flags>
RED FLAGS
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.allon 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
idleTimeoutMilliscan 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
(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)
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>
Files (skills)
-
examples
-
async.md 10.2 KB
Loading… -
caching.md 8.7 KB
Loading… -
core.md 7.7 KB
Loading…
-
-
reference.md 3.6 KB
Loading… -
SKILL.md 13.4 KB
--- 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) ```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 } } ``` **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 ```typescript // 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 } }, }, }); ``` ```typescript // 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](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. ```typescript // 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:** | 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> --- <red_flags> ## RED FLAGS **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 **(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)** **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>
Comments (0)
Sign in to join the conversation.
Reviews (0)
No reviews yet.
No comments yet.