# Backend Performance - Async Examples

> Event loop optimization, worker threads, and CPU-bound task handling. See [core.md](core.md) for database optimization and [caching.md](caching.md) for cache strategies.

---

## Event Loop Fundamentals

Node.js uses a single thread for JavaScript execution. Blocking this thread blocks ALL concurrent requests.

```typescript
// Bad Example - Blocking the event loop
import { readFileSync } from "fs";

app.get("/report", (req, res) => {
  // BAD: Synchronous file read blocks all other requests
  const data = readFileSync("large-file.csv");

  // BAD: CPU-intensive processing blocks event loop
  const result = processLargeDataset(data);

  res.json(result);
});
```

**Why bad:** While this request is processing, no other requests can be handled. 1000 concurrent users all wait for this one operation.

```typescript
// Good Example - Non-blocking async operations
import { readFile } from "fs/promises";

app.get("/report", async (req, res) => {
  // GOOD: Async file read doesn't block
  const data = await readFile("large-file.csv");

  // But CPU-intensive work still blocks - see Worker Threads below
  const result = processLargeDataset(data);

  res.json(result);
});
```

**Better but not complete:** Async I/O is non-blocking, but CPU-intensive `processLargeDataset` still blocks. Use Worker Threads for CPU work.

---

## Worker Threads for CPU-Bound Tasks

Offload CPU-intensive operations to separate threads.

### Worker Definition

```typescript
// workers/data-processor.worker.ts
import { parentPort, workerData } from "worker_threads";

interface WorkerInput {
  data: string;
  options: ProcessingOptions;
}

interface ProcessingOptions {
  format: "csv" | "json";
  aggregate: boolean;
}

// Heavy computation runs in separate thread
function processData(input: WorkerInput) {
  const { data, options } = input;

  // CPU-intensive work here
  const rows = data.split("\n");
  const processed = rows.map((row) => {
    // Complex processing...
    return transformRow(row, options);
  });

  if (options.aggregate) {
    return aggregateResults(processed);
  }

  return processed;
}

// Receive data from main thread
const result = processData(workerData as WorkerInput);

// Send result back to main thread
parentPort?.postMessage(result);
```

### Worker Manager

```typescript
// lib/worker-pool.ts
import { Worker } from "worker_threads";
import { cpus } from "os";

const WORKER_SCRIPT = "./workers/data-processor.worker.js";
const MAX_WORKERS = cpus().length;
const WORKER_TIMEOUT_MS = 30000;

interface WorkerTask<T> {
  resolve: (value: T) => void;
  reject: (error: Error) => void;
  timeout: NodeJS.Timeout;
}

class WorkerPool {
  private workers: Worker[] = [];
  private queue: Array<{ data: unknown; task: WorkerTask<unknown> }> = [];
  private activeWorkers = 0;

  async runTask<T>(data: unknown): Promise<T> {
    return new Promise((resolve, reject) => {
      const timeout = setTimeout(() => {
        reject(new Error("Worker timeout"));
      }, WORKER_TIMEOUT_MS);

      const task: WorkerTask<T> = { resolve, reject, timeout };

      if (this.activeWorkers < MAX_WORKERS) {
        this.executeTask(data, task);
      } else {
        this.queue.push({ data, task: task as WorkerTask<unknown> });
      }
    });
  }

  private executeTask<T>(data: unknown, task: WorkerTask<T>) {
    this.activeWorkers++;

    const worker = new Worker(WORKER_SCRIPT, { workerData: data });

    worker.on("message", (result: T) => {
      clearTimeout(task.timeout);
      task.resolve(result);
      this.cleanup(worker);
    });

    worker.on("error", (error) => {
      clearTimeout(task.timeout);
      task.reject(error);
      this.cleanup(worker);
    });

    worker.on("exit", (code) => {
      if (code !== 0) {
        clearTimeout(task.timeout);
        task.reject(new Error(`Worker exited with code ${code}`));
      }
      this.cleanup(worker);
    });
  }

  private cleanup(worker: Worker) {
    this.activeWorkers--;
    worker.terminate();

    // Process next queued task
    if (this.queue.length > 0) {
      const next = this.queue.shift()!;
      this.executeTask(next.data, next.task);
    }
  }
}

export const workerPool = new WorkerPool();
```

### Usage in Request Handler

```typescript
// Good Example - CPU work offloaded to worker
import { workerPool } from "./lib/worker-pool";

app.get("/report", async (req, res) => {
  // I/O is async - doesn't block
  const data = await readFile("large-file.csv", "utf-8");

  // CPU work offloaded to worker thread - doesn't block event loop
  const result = await workerPool.runTask({
    data,
    options: { format: "csv", aggregate: true },
  });

  res.json(result);
});
```

**Why good:** Main thread remains free to handle other requests while worker processes data, worker pool limits concurrency preventing resource exhaustion, timeout prevents hung workers

---

## setImmediate for Long-Running Loops

For CPU-bound operations that can't be moved to workers, break them into chunks.

```typescript
// Bad Example - Long loop blocks event loop
async function processLargeArray(items: Item[]) {
  const results: ProcessedItem[] = [];

  for (const item of items) {
    results.push(expensiveTransform(item)); // Blocks until complete
  }

  return results;
}
```

```typescript
// Good Example - Chunked processing with setImmediate
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);

    // Process chunk
    for (const item of chunk) {
      results.push(expensiveTransform(item));
    }

    // Yield to event loop between chunks
    if (i + CHUNK_SIZE < items.length) {
      await new Promise((resolve) => setImmediate(resolve));
    }
  }

  return results;
}
```

**Why good:** `setImmediate` yields control back to event loop between chunks, allows other requests to be processed during long operations, total throughput same but latency for other requests improved

**Trade-off:** Adds slight overhead per chunk, total processing time slightly longer, but system remains responsive

---

## Async Best Practices

### Parallel vs Sequential

```typescript
// Bad Example - Sequential when parallel possible
async function getUserData(userId: string) {
  const user = await getUser(userId);
  const posts = await getPosts(userId);
  const friends = await getFriends(userId);
  // Total time: getUser + getPosts + getFriends

  return { user, posts, friends };
}

// Good Example - Parallel independent operations
async function getUserData(userId: string) {
  const [user, posts, friends] = await Promise.all([
    getUser(userId),
    getPosts(userId),
    getFriends(userId),
  ]);
  // Total time: max(getUser, getPosts, getFriends)

  return { user, posts, friends };
}
```

**Why good:** Independent operations run in parallel, total time is the slowest operation, not the sum

### Promise.allSettled for Partial Failures

```typescript
// Good Example - Handle partial failures gracefully
async function getUserDataResilient(userId: string) {
  const results = await Promise.allSettled([
    getUser(userId),
    getPosts(userId),
    getFriends(userId),
  ]);

  return {
    user: results[0].status === "fulfilled" ? results[0].value : null,
    posts: results[1].status === "fulfilled" ? results[1].value : [],
    friends: results[2].status === "fulfilled" ? results[2].value : [],
  };
}
```

**Why good:** One failure doesn't fail entire request, graceful degradation, can still return partial data

### Avoid Unbounded Parallelism

```typescript
// Bad Example - Unbounded parallelism can overwhelm resources
async function processAllUsers(userIds: string[]) {
  // If userIds has 10,000 items, this creates 10,000 concurrent operations!
  await Promise.all(userIds.map((id) => processUser(id)));
}

// Good Example - Bounded concurrency
import pLimit from "p-limit";

const MAX_CONCURRENT = 10;
const limit = pLimit(MAX_CONCURRENT);

async function processAllUsers(userIds: string[]) {
  await Promise.all(userIds.map((id) => limit(() => processUser(id))));
}
```

**Why good:** Limits concurrent operations to prevent resource exhaustion, predictable memory usage, prevents overwhelming downstream services

---

## Event Loop Lag Monitoring

```typescript
// Monitor event loop lag in production
const CHECK_INTERVAL_MS = 1000;
const LAG_WARNING_THRESHOLD_MS = 50;
const LAG_CRITICAL_THRESHOLD_MS = 200;

let lastCheck = process.hrtime.bigint();

setInterval(() => {
  const now = process.hrtime.bigint();
  const expectedNs = BigInt(CHECK_INTERVAL_MS * 1_000_000);
  const actualNs = now - lastCheck;
  const lagMs = Number(actualNs - expectedNs) / 1_000_000;

  if (lagMs > LAG_CRITICAL_THRESHOLD_MS) {
    console.error(`CRITICAL: Event loop lag ${lagMs.toFixed(2)}ms`);
  } else if (lagMs > LAG_WARNING_THRESHOLD_MS) {
    console.warn(`WARNING: Event loop lag ${lagMs.toFixed(2)}ms`);
  }

  lastCheck = now;
}, CHECK_INTERVAL_MS);

// Or use dedicated library
import { monitorEventLoopDelay } from "perf_hooks";

const histogram = monitorEventLoopDelay({ resolution: 20 });
histogram.enable();

// Periodically report
setInterval(() => {
  console.log({
    min: histogram.min / 1e6,
    max: histogram.max / 1e6,
    mean: histogram.mean / 1e6,
    p99: histogram.percentile(99) / 1e6,
  });
  histogram.reset();
}, 60000);
```

---

## When to Use Each Pattern

| Scenario             | Solution              | Notes                        |
| -------------------- | --------------------- | ---------------------------- |
| File I/O             | `fs/promises`         | Always use async versions    |
| CPU < 50ms           | Keep on main thread   | Worker overhead not worth it |
| CPU 50-500ms         | setImmediate chunking | Simple, no worker complexity |
| CPU > 500ms          | Worker Threads        | Offload completely           |
| Many small async ops | p-limit               | Bound concurrency            |
| Independent I/O      | Promise.all           | Parallel execution           |
| Partial failure OK   | Promise.allSettled    | Graceful degradation         |

---

## See Also

- [core.md](core.md) - Query optimization, indexing, connection pooling
- [caching.md](caching.md) - Cache strategies and invalidation

# Backend Performance - Caching Examples

> Caching strategies and invalidation patterns. See [core.md](core.md) for database optimization and [async.md](async.md) for event loop patterns.

---

## Cache-Aside Pattern

The most common caching pattern. Check cache first, fetch from database on miss, store with TTL.

```typescript
// Good Example - Cache-aside with TTL
const CACHE_TTL_SECONDS = 300; // 5 minutes
const CACHE_PREFIX = "app:user";

interface User {
  id: string;
  email: string;
  name: string;
}

async function getUserById(userId: string): Promise<User | null> {
  const cacheKey = `${CACHE_PREFIX}:${userId}`;

  // Check cache first
  const cached = await cacheClient.get(cacheKey);
  if (cached) {
    return JSON.parse(cached) as User;
  }

  // Cache miss - fetch from database
  const user = await db.query.users.findFirst({
    where: eq(users.id, userId),
  });

  if (!user) return null;

  // Store in cache with TTL
  await cacheClient.set(cacheKey, JSON.stringify(user), {
    EX: CACHE_TTL_SECONDS,
  });

  return user;
}

export { getUserById };
```

**Why good:** TTL prevents stale data accumulation, namespaced cache keys prevent collisions, early return on cache hit for performance

```typescript
// Bad Example - No TTL, generic keys
async function getUser(id: string) {
  const cached = await cacheClient.get(id); // Generic key -- no prefix
  if (cached) return JSON.parse(cached);

  const user = await db.query.users.findFirst({ where: eq(users.id, id) });
  await cacheClient.set(id, JSON.stringify(user)); // No TTL!
  return user;
}
```

**Why bad:** No TTL means data never expires (memory exhaustion + infinite staleness), generic key could collide with other data types

---

## Write-Through Pattern

Update cache immediately when data changes to maintain consistency.

```typescript
// Good Example - Write-through caching
const CACHE_TTL_SECONDS = 300;

async function updateUserProfile(
  userId: string,
  updates: Partial<User>,
): Promise<User> {
  // Update database first
  const [updatedUser] = await db
    .update(users)
    .set({ ...updates, updatedAt: new Date() })
    .where(eq(users.id, userId))
    .returning();

  // Immediately update cache (write-through)
  const cacheKey = `${CACHE_PREFIX}:${userId}`;
  await cacheClient.set(cacheKey, JSON.stringify(updatedUser), {
    EX: CACHE_TTL_SECONDS,
  });

  return updatedUser;
}

async function deleteUser(userId: string): Promise<void> {
  // Delete from database
  await db.delete(users).where(eq(users.id, userId));

  // Invalidate cache
  const cacheKey = `${CACHE_PREFIX}:${userId}`;
  await cacheClient.del(cacheKey);
}
```

**Why good:** Cache always reflects database state, no stale reads after updates, explicit invalidation on delete

---

## Cache Key Strategies

```typescript
// Good Example - Structured cache keys
const CACHE_PREFIX = "myapp";

// Simple entity caching
const userKey = (id: string) => `${CACHE_PREFIX}:user:${id}`;
const productKey = (id: string) => `${CACHE_PREFIX}:product:${id}`;

// Query result caching (include query params in key)
const productListKey = (filters: ProductFilters) => {
  const normalized = JSON.stringify({
    category: filters.category || "all",
    minPrice: filters.minPrice || 0,
    maxPrice: filters.maxPrice || Infinity,
    page: filters.page || 1,
  });
  // Hash for shorter keys
  const hash = crypto.createHash("md5").update(normalized).digest("hex");
  return `${CACHE_PREFIX}:products:list:${hash}`;
};

// User-specific caching
const userCartKey = (userId: string) => `${CACHE_PREFIX}:cart:${userId}`;

// Pattern for bulk invalidation
const userPattern = (userId: string) => `${CACHE_PREFIX}:user:${userId}:*`;
```

**Why good:** Consistent prefix prevents collisions with other apps, hierarchical structure enables pattern-based invalidation, hashing complex queries keeps keys manageable

---

## Cache Invalidation Patterns

### 1. Direct Invalidation

```typescript
// Delete specific key on update
async function updateProduct(id: string, data: ProductUpdate) {
  await db.update(products).set(data).where(eq(products.id, id));
  await cacheClient.del(productKey(id));

  // Also invalidate list caches that might contain this product
  const listPattern = `${CACHE_PREFIX}:products:list:*`;
  const keys = await cacheClient.keys(listPattern);
  if (keys.length > 0) {
    await cacheClient.del(keys);
  }
}
```

### 2. TTL-Based Expiration

```typescript
// Let TTL handle expiration - simpler but allows staleness
const SHORT_TTL = 60; // 1 minute for frequently changing data
const MEDIUM_TTL = 300; // 5 minutes for user data
const LONG_TTL = 3600; // 1 hour for static content
```

### 3. Tag-Based Invalidation

Uses cache set operations to group keys by tag for bulk invalidation.

```typescript
// Good Example - Tag-based cache invalidation using set data structures
async function setWithTags(
  key: string,
  value: string,
  ttl: number,
  tags: string[],
) {
  // Store the value
  await cacheClient.set(key, value, { EX: ttl });

  // Add key to each tag set (uses set-add operation)
  for (const tag of tags) {
    await cacheClient.sAdd(`tag:${tag}`, key);
    await cacheClient.expire(`tag:${tag}`, ttl);
  }
}

async function invalidateByTag(tag: string) {
  const keys = await cacheClient.sMembers(`tag:${tag}`);
  if (keys.length > 0) {
    await cacheClient.del(keys);
    await cacheClient.del(`tag:${tag}`);
  }
}

// Usage
await setWithTags(productKey("123"), JSON.stringify(product), MEDIUM_TTL, [
  "category:electronics",
  "brand:apple",
]);

// Invalidate all electronics products
await invalidateByTag("category:electronics");
```

**Why good:** Enables invalidating related cached items without knowing exact keys, useful for category/relationship-based invalidation

---

## Caching Middleware

Reusable middleware pattern for route-level caching. Adapt to your framework's middleware signature.

```typescript
// Good Example - Caching middleware (adapt types to your framework)
interface CacheOptions {
  ttlSeconds: number;
  keyPrefix: string;
}

const DEFAULT_CACHE_TTL = 300;

function createCacheMiddleware(options: CacheOptions) {
  return async (req: Request, res: Response, next: NextFunction) => {
    const cacheKey = `${options.keyPrefix}:${req.url}`;

    // Check cache
    const cached = await cacheClient.get(cacheKey);
    if (cached) {
      const { body, headers } = JSON.parse(cached);
      Object.entries(headers).forEach(([key, value]) => {
        res.setHeader(key, value as string);
      });
      res.setHeader("X-Cache", "HIT");
      return res.json(body);
    }

    // Cache miss - continue to handler, cache response after
    const originalJson = res.json.bind(res);
    res.json = (body: unknown) => {
      // Only cache successful responses
      cacheClient.set(cacheKey, JSON.stringify({ body }), {
        EX: options.ttlSeconds || DEFAULT_CACHE_TTL,
      });
      res.setHeader("X-Cache", "MISS");
      return originalJson(body);
    };

    next();
  };
}
```

**Why good:** Reusable across routes, only caches successful responses, X-Cache header for debugging, configurable TTL per route

---

## TTL Best Practices

| Data Type       | Recommended TTL | Rationale                        |
| --------------- | --------------- | -------------------------------- |
| User session    | 86400s (24h)    | Balance security vs convenience  |
| User profile    | 300s (5m)       | Changes infrequently             |
| Product catalog | 3600s (1h)      | Infrequent updates               |
| Search results  | 60s (1m)        | Balance freshness vs performance |
| Real-time data  | 10-30s          | Need fresh data                  |
| Static config   | 86400s+         | Rarely changes                   |

**TTL Anti-Patterns:**

- No TTL (infinite cache) - Memory exhaustion, infinite staleness
- Very short TTL everywhere (< 10s) - Defeats caching purpose
- Same TTL for all data - Different data has different freshness needs

---

## Cache Hit/Miss Monitoring

```typescript
// Track cache hit/miss rates
const CACHE_HITS = new Map<string, number>();
const CACHE_MISSES = new Map<string, number>();

async function getCached<T>(
  key: string,
  fetchFn: () => Promise<T>,
  ttl: number,
): Promise<T> {
  const cached = await cacheClient.get(key);
  if (cached) {
    CACHE_HITS.set(key, (CACHE_HITS.get(key) || 0) + 1);
    return JSON.parse(cached);
  }

  CACHE_MISSES.set(key, (CACHE_MISSES.get(key) || 0) + 1);
  const data = await fetchFn();
  await cacheClient.set(key, JSON.stringify(data), { EX: ttl });
  return data;
}
```

**Why good:** Separates cache concerns from business logic, tracks per-key hit rates for targeted optimization, generic wrapper works with any data type

---

## See Also

- [core.md](core.md) - Query optimization, indexing, connection pooling
- [async.md](async.md) - Event loop and worker threads

# Backend Performance - Core Examples

> Core database performance patterns. See [caching.md](caching.md) for cache strategies and [async.md](async.md) for event loop optimization.

---

## Prepared Statements

Prepare query plans once, execute many times with different parameters.

```typescript
// Good Example - Prepared statement for repeated queries
const DEFAULT_LIMIT = 50;

// Define prepared statement once at module level (ORM syntax varies)
const getActiveJobsByCountry = db
  .select()
  .from(jobs)
  .where(
    and(
      eq(jobs.country, sql.placeholder("country")),
      eq(jobs.isActive, true),
      isNull(jobs.deletedAt),
    ),
  )
  .limit(DEFAULT_LIMIT)
  .prepare("get_active_jobs_by_country");

// Execute with parameters - reuses query plan
async function findJobsByCountry(country: string) {
  return await getActiveJobsByCountry.execute({ country });
}

export { findJobsByCountry };
```

**Why good:** Query plan compiled once and reused, faster than building query each execution, parameterized queries prevent SQL injection

**Caveat:** Prepared statements created outside a transaction cannot be used inside transactions. Create them inside the transaction callback if needed.

---

## Query Optimization Techniques

### Select Only Needed Columns

```typescript
// Good Example - Select specific columns
const jobs = await db
  .select({
    id: jobs.id,
    title: jobs.title,
    company: companies.name,
  })
  .from(jobs)
  .innerJoin(companies, eq(jobs.companyId, companies.id));

// Bad Example - SELECT * fetches unnecessary data
const jobs = await db.select().from(jobs);
```

**Why good:** Reduces data transfer, uses less memory, can enable covering indexes (index-only scans)

### Avoid Functions on Indexed Columns

```typescript
// Bad Example - Function prevents index use
const orders = await db
  .select()
  .from(orders)
  .where(sql`YEAR(${orders.createdAt}) = 2024`);

// Good Example - Range condition uses index
const START_OF_2024 = new Date("2024-01-01");
const START_OF_2025 = new Date("2025-01-01");

const orders = await db
  .select()
  .from(orders)
  .where(
    and(
      gte(orders.createdAt, START_OF_2024),
      lt(orders.createdAt, START_OF_2025),
    ),
  );
```

**Why good:** Range conditions allow index seeks, function-based queries require full table scan

### Batch Inserts

```typescript
// Good Example - Batch insert to avoid long transactions
const BATCH_SIZE = 1000;

async function bulkInsertProducts(products: NewProduct[]) {
  for (let i = 0; i < products.length; i += BATCH_SIZE) {
    const batch = products.slice(i, i + BATCH_SIZE);
    await db.insert(productsTable).values(batch);
  }
}
```

**Why good:** Batched inserts prevent transaction timeout, memory-efficient processing of large datasets

---

## Pagination Patterns

### Offset Pagination (Simple but Limited)

```typescript
// Good Example - Offset pagination with total count
const DEFAULT_PAGE_SIZE = 20;
const MAX_PAGE_SIZE = 100;

interface PaginationParams {
  page: number;
  pageSize: number;
}

async function getJobsPaginated(params: PaginationParams) {
  const pageSize = Math.min(
    params.pageSize || DEFAULT_PAGE_SIZE,
    MAX_PAGE_SIZE,
  );
  const offset = (params.page - 1) * pageSize;

  const [jobsResult, countResult] = await Promise.all([
    db
      .select()
      .from(jobs)
      .where(isNull(jobs.deletedAt))
      .orderBy(desc(jobs.createdAt))
      .limit(pageSize)
      .offset(offset),

    db
      .select({ count: sql<number>`count(*)::int` })
      .from(jobs)
      .where(isNull(jobs.deletedAt)),
  ]);

  return {
    data: jobsResult,
    pagination: {
      page: params.page,
      pageSize,
      total: countResult[0].count,
      totalPages: Math.ceil(countResult[0].count / pageSize),
    },
  };
}
```

**When to use:** Small to medium datasets (< 100k rows), need total count, random page access required

**Limitations:** OFFSET scans all previous rows - performance degrades at high offsets

### Keyset (Cursor) Pagination

```typescript
// Good Example - Keyset pagination for large datasets
interface CursorParams {
  cursor?: string; // Last seen ID
  limit: number;
}

async function getJobsCursor(params: CursorParams) {
  const limit = Math.min(params.limit || DEFAULT_PAGE_SIZE, MAX_PAGE_SIZE);

  let query = db
    .select()
    .from(jobs)
    .where(isNull(jobs.deletedAt))
    .orderBy(desc(jobs.createdAt), desc(jobs.id))
    .limit(limit + 1); // Fetch one extra to check for more

  // If cursor provided, filter to items after cursor
  if (params.cursor) {
    const cursorJob = await db.query.jobs.findFirst({
      where: eq(jobs.id, params.cursor),
    });
    if (cursorJob) {
      query = query.where(
        or(
          lt(jobs.createdAt, cursorJob.createdAt),
          and(
            eq(jobs.createdAt, cursorJob.createdAt),
            lt(jobs.id, cursorJob.id),
          ),
        ),
      );
    }
  }

  const results = await query;
  const hasMore = results.length > limit;
  const data = hasMore ? results.slice(0, limit) : results;
  const nextCursor = hasMore ? data[data.length - 1].id : null;

  return {
    data,
    pagination: {
      nextCursor,
      hasMore,
    },
  };
}
```

**Why good:** Constant time regardless of offset, efficient for large datasets, handles real-time data well (no duplicates from inserts)

**Trade-offs:** Can't jump to arbitrary pages, no total count without separate query

---

## Index Monitoring

### Check Index Usage (PostgreSQL)

```sql
-- Find unused indexes
SELECT
  schemaname,
  relname AS table_name,
  indexrelname AS index_name,
  idx_scan AS times_used,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- Find missing indexes (sequential scans on large tables)
SELECT
  schemaname,
  relname AS table_name,
  seq_scan,
  seq_tup_read,
  idx_scan,
  n_live_tup AS row_count
FROM pg_stat_user_tables
WHERE seq_scan > 0
  AND n_live_tup > 10000
ORDER BY seq_tup_read DESC
LIMIT 10;
```

### EXPLAIN ANALYZE Examples

```sql
-- Check if index is being used
EXPLAIN ANALYZE
SELECT * FROM jobs
WHERE country = 'germany' AND employment_type = 'full_time';

-- Good output:
-- Index Scan using jobs_country_employment_idx on jobs
-- Index Cond: (country = 'germany' AND employment_type = 'full_time')
-- Execution Time: 0.123 ms

-- Bad output (needs index):
-- Seq Scan on jobs
-- Filter: (country = 'germany' AND employment_type = 'full_time')
-- Rows Removed by Filter: 50000
-- Execution Time: 45.678 ms
```

---

## Connection Pool Sizing

### Calculation Formula

```typescript
// Pool size = (core_count * 2) + effective_spindle_count
// For SSDs, effective_spindle_count ~ 1-2

const CPU_CORES = 4;
const EFFECTIVE_SPINDLES = 2; // SSD
const CALCULATED_POOL_SIZE = CPU_CORES * 2 + EFFECTIVE_SPINDLES; // = 10

// Also consider:
// - PostgreSQL max_connections (default 100)
// - Number of application instances
// - Leave headroom for admin connections

const POOL_MAX_CONNECTIONS = Math.min(CALCULATED_POOL_SIZE, 20);
```

### External Poolers (PgBouncer)

For high-scale deployments with many application instances:

```
Application Instances (10) x Pool Size (20) = 200 connections
PostgreSQL max_connections = 100

Solution: PgBouncer in transaction mode
- All app instances connect to PgBouncer
- PgBouncer maintains smaller pool to PostgreSQL
- Each transaction gets a real connection, returned immediately after
```

**When to use an external pooler:**

- More than 5 application instances
- Serverless functions (many short-lived connections)
- Hitting PostgreSQL connection limits
- Connection storms during traffic spikes

---

## See Also

- [caching.md](caching.md) - Cache strategies and invalidation
- [async.md](async.md) - Event loop and worker threads

# Database Performance Examples

Query optimization, indexing, connection pooling, and prepared statements.

---

## Prepared Statements

Prepare query plans once, execute many times with different parameters.

```typescript
// Good Example - Prepared statement for repeated queries
import { sql } from "drizzle-orm";

const DEFAULT_LIMIT = 50;

// Define prepared statement once at module level
const getActiveJobsByCountry = db
  .select()
  .from(jobs)
  .where(
    and(
      eq(jobs.country, sql.placeholder("country")),
      eq(jobs.isActive, true),
      isNull(jobs.deletedAt),
    ),
  )
  .limit(DEFAULT_LIMIT)
  .prepare("get_active_jobs_by_country");

// Execute with parameters - reuses query plan
async function findJobsByCountry(country: string) {
  return await getActiveJobsByCountry.execute({ country });
}

// Multiple different queries
const getJobsByCompany = db
  .select()
  .from(jobs)
  .where(eq(jobs.companyId, sql.placeholder("companyId")))
  .prepare("get_jobs_by_company");

const getJobById = db
  .select()
  .from(jobs)
  .where(eq(jobs.id, sql.placeholder("id")))
  .prepare("get_job_by_id");

export { findJobsByCountry, getJobsByCompany, getJobById };
```

**Why good:** Query plan compiled once and reused, faster than building query each execution, parameterized queries prevent SQL injection

**Caveat:** Prepared statements created outside a transaction cannot be used inside transactions. Create them inside the transaction callback if needed.

---

## Query Optimization Techniques

### Select Only Needed Columns

```typescript
// Good Example - Select specific columns
const jobs = await db
  .select({
    id: jobs.id,
    title: jobs.title,
    company: companies.name,
  })
  .from(jobs)
  .innerJoin(companies, eq(jobs.companyId, companies.id));

// Bad Example - SELECT * fetches unnecessary data
const jobs = await db.select().from(jobs);
```

**Why good:** Reduces data transfer, uses less memory, can enable covering indexes (index-only scans)

### Avoid Functions on Indexed Columns

```typescript
// Bad Example - Function prevents index use
const orders = await db
  .select()
  .from(orders)
  .where(sql`YEAR(${orders.createdAt}) = 2024`);

// Good Example - Range condition uses index
const START_OF_2024 = new Date("2024-01-01");
const START_OF_2025 = new Date("2025-01-01");

const orders = await db
  .select()
  .from(orders)
  .where(
    and(
      gte(orders.createdAt, START_OF_2024),
      lt(orders.createdAt, START_OF_2025),
    ),
  );
```

**Why good:** Range conditions allow index seeks, function-based queries require full table scan

### Batch Operations

```typescript
// Good Example - Batch insert
const BATCH_SIZE = 1000;

async function bulkInsertProducts(products: NewProduct[]) {
  // Insert in batches to avoid memory issues and long transactions
  for (let i = 0; i < products.length; i += BATCH_SIZE) {
    const batch = products.slice(i, i + BATCH_SIZE);
    await db.insert(productsTable).values(batch);
  }
}

// With Neon Batch API - single network round-trip
const batchResults = await db.batch([
  db.insert(companies).values({ name: "Acme" }).returning(),
  db.insert(jobs).values({ title: "Engineer", companyId: "..." }),
  db.query.jobs.findMany({ where: eq(jobs.isActive, true) }),
]);
```

**Why good:** Neon batch reduces network round-trips, batched inserts prevent transaction timeout, memory-efficient processing

---

## Pagination Patterns

### Offset Pagination (Simple but Limited)

```typescript
// Good Example - Offset pagination with total count
const DEFAULT_PAGE_SIZE = 20;
const MAX_PAGE_SIZE = 100;

interface PaginationParams {
  page: number;
  pageSize: number;
}

async function getJobsPaginated(params: PaginationParams) {
  const pageSize = Math.min(
    params.pageSize || DEFAULT_PAGE_SIZE,
    MAX_PAGE_SIZE,
  );
  const offset = (params.page - 1) * pageSize;

  const [jobsResult, countResult] = await Promise.all([
    db
      .select()
      .from(jobs)
      .where(isNull(jobs.deletedAt))
      .orderBy(desc(jobs.createdAt))
      .limit(pageSize)
      .offset(offset),

    db
      .select({ count: sql<number>`count(*)::int` })
      .from(jobs)
      .where(isNull(jobs.deletedAt)),
  ]);

  return {
    data: jobsResult,
    pagination: {
      page: params.page,
      pageSize,
      total: countResult[0].count,
      totalPages: Math.ceil(countResult[0].count / pageSize),
    },
  };
}
```

**When to use:** Small to medium datasets (< 100k rows), need total count, random page access required

**Limitations:** OFFSET scans all previous rows - performance degrades at high offsets

### Keyset (Cursor) Pagination

```typescript
// Good Example - Keyset pagination for large datasets
interface CursorParams {
  cursor?: string; // Last seen ID
  limit: number;
}

async function getJobsCursor(params: CursorParams) {
  const limit = Math.min(params.limit || DEFAULT_PAGE_SIZE, MAX_PAGE_SIZE);

  let query = db
    .select()
    .from(jobs)
    .where(isNull(jobs.deletedAt))
    .orderBy(desc(jobs.createdAt), desc(jobs.id))
    .limit(limit + 1); // Fetch one extra to check for more

  // If cursor provided, filter to items after cursor
  if (params.cursor) {
    const cursorJob = await db.query.jobs.findFirst({
      where: eq(jobs.id, params.cursor),
    });
    if (cursorJob) {
      query = query.where(
        or(
          lt(jobs.createdAt, cursorJob.createdAt),
          and(
            eq(jobs.createdAt, cursorJob.createdAt),
            lt(jobs.id, cursorJob.id),
          ),
        ),
      );
    }
  }

  const results = await query;
  const hasMore = results.length > limit;
  const data = hasMore ? results.slice(0, limit) : results;
  const nextCursor = hasMore ? data[data.length - 1].id : null;

  return {
    data,
    pagination: {
      nextCursor,
      hasMore,
    },
  };
}
```

**Why good:** Constant time regardless of offset, efficient for large datasets, handles real-time data well (no duplicates from inserts)

**Trade-offs:** Can't jump to arbitrary pages, no total count without separate query

---

## Index Monitoring

### Check Index Usage (PostgreSQL)

```sql
-- Find unused indexes
SELECT
  schemaname,
  relname AS table_name,
  indexrelname AS index_name,
  idx_scan AS times_used,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- Find missing indexes (sequential scans on large tables)
SELECT
  schemaname,
  relname AS table_name,
  seq_scan,
  seq_tup_read,
  idx_scan,
  n_live_tup AS row_count
FROM pg_stat_user_tables
WHERE seq_scan > 0
  AND n_live_tup > 10000
ORDER BY seq_tup_read DESC
LIMIT 10;
```

### EXPLAIN ANALYZE Examples

```sql
-- Check if index is being used
EXPLAIN ANALYZE
SELECT * FROM jobs
WHERE country = 'germany' AND employment_type = 'full_time';

-- Good output:
-- Index Scan using jobs_country_employment_idx on jobs
-- Index Cond: (country = 'germany' AND employment_type = 'full_time')
-- Execution Time: 0.123 ms

-- Bad output (needs index):
-- Seq Scan on jobs
-- Filter: (country = 'germany' AND employment_type = 'full_time')
-- Rows Removed by Filter: 50000
-- Execution Time: 45.678 ms
```

---

## Connection Pool Sizing

### Calculation Formula

```typescript
// Pool size = (core_count * 2) + effective_spindle_count
// For SSDs, effective_spindle_count ≈ 1-2

const CPU_CORES = 4;
const EFFECTIVE_SPINDLES = 2; // SSD
const CALCULATED_POOL_SIZE = CPU_CORES * 2 + EFFECTIVE_SPINDLES; // = 10

// But also consider:
// - PostgreSQL max_connections (default 100)
// - Number of application instances
// - Leave headroom for admin connections

const POOL_MAX_CONNECTIONS = Math.min(CALCULATED_POOL_SIZE, 20);
```

### External Poolers (PgBouncer)

For high-scale deployments with many application instances:

```
Application Instances (10) × Pool Size (20) = 200 connections
PostgreSQL max_connections = 100

Solution: PgBouncer in transaction mode
- All app instances connect to PgBouncer
- PgBouncer maintains smaller pool to PostgreSQL
- Each transaction gets a real connection, returned immediately after
```

**When to use PgBouncer:**

- More than 5 application instances
- Serverless functions (many short-lived connections)
- Hitting PostgreSQL connection limits
- Connection storms during traffic spikes

---

## See Also

- [caching.md](caching.md) - Redis caching patterns
- [async.md](async.md) - Event loop and worker threads