Chapter 14 — Performance & Scaling

The why

Before you start thinking about load balancers or caching strategies — 90% of performance problems are caused by a missing database index, loading data you don't need, the N+1 problem, or running reports on the same database your live API uses.

Fix those four things and most performance problems disappear.

The missing index — most common cause of slowness

Without an index, PostgreSQL reads every row in the table to find matches. With an index, it jumps directly to matching rows.

How to find missing indexes

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 'usr-123' AND status = 'pending'
ORDER BY created_at DESC
LIMIT 50;

Look for Seq Scan — that means PostgreSQL is reading every row:

Seq Scan on orders  (cost=0..18450 rows=12)  ← BAD: scanning all rows

vs.

Index Scan using idx_orders_agent_status ON orders  ← GOOD: using the index

Adding the right index

-- Composite index: covers WHERE user_id = ? AND status = ? ORDER BY created_at
CREATE INDEX idx_orders_user_status_created
ON orders (user_id, status, created_at DESC);

Column order matters:

• Put the most-filtered column first (user_id)
• Then the next most-filtered (status)
• Then the sort column last (created_at)

An index on (user_id, status, created_at) covers:

• WHERE user_id = ? ✅
• WHERE user_id = ? AND status = ? ✅
• WHERE user_id = ? AND status = ? ORDER BY created_at ✅
• WHERE status = ? ❌ (can’t use this index — user_id must come first)

SELECT * — stop doing this

// ❌ Loads ALL columns — maybe 50+ columns, including ones you don't use
const orders = await this.orderModel.findAll({ where: { user_id } });
 
// ✅ Load only what this endpoint actually needs
const orders = await this.orderModel.findAll(

On a table with 50 columns and 1 million rows, SELECT * transfers 50x more data than necessary.

The N+1 problem — the silent killer

// ❌ N+1: 1 query to get 100 orders, then 100 more queries to get each user
const orders = await this.orderModel.findAll({ limit: 100 });
 
for (const order of orders) {

Caching — avoid repeating expensive work

Some data is read constantly but changes rarely (currency codes, transaction types). These are ideal for caching.

In-memory cache (per pod)

private currencyCodes: CurrencyCode[] | null = null;
 
async getCurrencyCodes(): Promise<CurrencyCode[]> {
  if (this.currencyCodes) return this.currencyCodes;  // cache hit
  this.currencyCodes = await this.currencyModel.

Downside: Each pod has its own copy. Updates require restarting all pods.

Redis cache (shared across pods)

async getCurrencyCodes(): Promise<CurrencyCode[]> {
  const cached = await this.redis.get('currency_codes');
  if (cached) return JSON.parse(cached);  // cache hit
 

Stateless app — the key to horizontal scaling

A stateless app stores nothing in memory that other pods need to see:

• No in-memory session storage (use Redis)
• No local file writes that other pods need to read (use S3)
• No global variables that accumulate state (use the database)

If your app is stateless, adding more pods is trivial — each one is identical, and the load balancer distributes traffic between them.

The correct order when the database is slow

Before jumping to expensive solutions, go through these steps in order:

Step 1: Is there a missing index? → Add it. 5-minute fix.

Step 2: Is the query plan stupid? → Use EXPLAIN ANALYZE, rewrite the query.

Step 3: Are reads dominating? → Add a read replica. Route SELECT queries to it.

Step 4: Are writes too hot on one table? → Look for "hot rows" (e.g. a wallet balance updated 10,000 times per minute).

Step 5: Are analytical queries killing the live DB? → Move reporting to a data warehouse (BigQuery, Snowflake). Never run SUM(total_amount) WHERE created_at > '2026-01-01' on the same database as your live API.

The connection pool — a common scaling trap

PostgreSQL creates a new OS process for each connection. On a large server, you might have max_connections = 100.

If you run 10 pods and each pod has pool_max = 50, that's 500 connections — 5× over the limit. New connections are refused.

The formula:

pool_max × pods ≤ postgres.max_connections × 0.8

Example:

• max_connections = 100
• pods = 10
• pool_max per pod = floor((100 × 0.8) / 10) = 8

Set pool.max = 8 in your Sequelize config. Leave 20% for migrations, CLI sessions, and monitoring tools.

Quick wins checklist

Before you ask "why is this API slow?", check these in order:

• EXPLAIN ANALYZE shows Seq Scan on a large table → Add an index
• Response time scales with result size → You have N+1 → Use include
• SELECT * on a wide table → Use attributes: ['col1', 'col2']
• Same reference data fetched on every request → Cache in Redis with TTL
• Analytical query running on production DB → Move to a warehouse or schedule off-peak
• Connection pool errors → Recalculate pool_max

One thing to remember

Fix: load the related data in the original query with include: