Database Performance Optimization

The database is almost always the bottleneck. Not because databases are slow — modern PostgreSQL, MySQL, and MongoDB are extraordinary pieces of engineering — but because it's easy to write application code that uses them badly.

Here's a practical guide to the techniques that make the biggest difference.

EXPLAIN ANALYZE First

Before optimising anything, measure. Run EXPLAIN ANALYZE (PostgreSQL) or EXPLAIN (MySQL) before the query you want to improve. It shows you the query plan — how the database actually executes your SQL.

EXPLAIN ANALYZE
SELECT u.id, u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id, u.name
ORDER BY order_count DESC
LIMIT 20;

Look for:

• Seq Scan — reading every row in a table. Usually means a missing index.
• Nested Loop on large sets — O(n²) in the worst case. May need an index or a different join strategy.
• Sort with large rows — expensive. An index with the right sort order can eliminate it.
• Actual rows vs estimated rows — large discrepancies mean stale statistics. Run ANALYZE tablename.

Indexing Strategy

An index is a sorted copy of a column (or columns) that lets the database find rows without scanning the entire table. Adding the right index can turn a 10-second query into a 10ms query.

B-tree indexes (the default)

Good for: equality (=), range (>, <, BETWEEN), sorting (ORDER BY), and prefix matching (LIKE 'prefix%').

-- Index for filtering by status and sorting by created_at
CREATE INDEX idx_orders_status_created ON orders (status, created_at DESC);

-- This query now uses the index for both the WHERE and ORDER BY
SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 50;

Composite index column order matters

The index is useful for queries that match a prefix of the index columns. An index on (status, created_at) helps queries that filter on status alone, or on status + created_at together. It does NOT help queries that filter on created_at alone.

-- Uses the index
SELECT * FROM orders WHERE status = 'shipped' AND created_at > '2024-01-01';

-- Uses the index (partial — status prefix)
SELECT * FROM orders WHERE status = 'shipped';

-- Does NOT use the index
SELECT * FROM orders WHERE created_at > '2024-01-01';

Partial indexes

Index only the rows you actually query frequently. A partial index is smaller, faster to maintain, and fits better in memory.

-- Only index pending orders — the ones you actually query
CREATE INDEX idx_orders_pending ON orders (created_at DESC)
WHERE status = 'pending';

Index on expressions

Index the result of a function, not the raw column.

-- Case-insensitive email lookup without LOWER() on every read
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

SELECT * FROM users WHERE LOWER(email) = LOWER('User@Example.com');

N+1 Query Problem

The N+1 problem is the most common performance issue in ORM-based code. You load a list of records, then issue a separate query for each one's association.

// N+1: 1 query for orders + N queries for users
const orders = await Order.findAll({ limit: 100 });
for (const order of orders) {
  const user = await User.findByPk(order.userId); // N queries
  console.log(order.id, user.name);
}

Fix with eager loading (JOIN or IN clause):

// 1 query (or 2 at most)
const orders = await Order.findAll({
  limit: 100,
  include: [{ model: User, attributes: ['id', 'name'] }],
});

In raw SQL:

SELECT o.id, o.total, u.name
FROM orders o
JOIN users u ON u.id = o.user_id
ORDER BY o.created_at DESC
LIMIT 100;

Connection Pooling

Opening a new database connection for every request is expensive (TCP handshake, auth, memory allocation). Use a connection pool — a set of pre-opened connections that requests borrow and return.

// PostgreSQL with node-postgres (pg)
import { Pool } from 'pg';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,           // max connections in the pool
  idleTimeoutMillis: 30_000,
  connectionTimeoutMillis: 2_000,
});

// Each request borrows a connection
const { rows } = await pool.query('SELECT * FROM users WHERE id = $1', [userId]);

Rule of thumb: pool size ≈ (number of CPU cores × 2) + effective spindle count. For most web apps, 10–20 connections per application server instance is a good starting point.

Pagination: OFFSET vs Keyset

OFFSET pagination is simple but gets slower as the offset grows. To get page 1000 of 20 results, the database must scan and discard 19,980 rows.

-- Slow for large offsets
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 19980;

Keyset pagination (cursor-based) stays fast regardless of position. The cursor is the last value seen; the next page starts after it.

-- Fast regardless of position — uses the index directly
SELECT * FROM posts
WHERE created_at < :last_seen_created_at
ORDER BY created_at DESC
LIMIT 20;

The tradeoff: you can't jump to page 500. For infinite scroll and "load more" UX (the norm in most modern apps), keyset is strictly better.

Read Replicas for Read-Heavy Workloads

Write to the primary, read from replicas. Most web applications are 80–90% reads. Routing reads to replicas:

• Reduces primary load, improving write latency
• Lets you scale reads horizontally by adding replicas
• Adds resilience — primary failure doesn't take down reads

const writablePool = new Pool({ connectionString: process.env.DATABASE_PRIMARY_URL });
const readPool = new Pool({ connectionString: process.env.DATABASE_REPLICA_URL });

// Writes go to primary
await writablePool.query('INSERT INTO events ...');

// Reads go to replica
const { rows } = await readPool.query('SELECT * FROM events WHERE ...');

Caveat: replicas have replication lag (typically <100ms on a well-configured setup, but up to seconds under load). Don't read from a replica immediately after a write you need to see.

Caching

Not everything needs to hit the database on every request. Cache aggressively where data is:

• Read frequently — product catalog, user profiles, configuration
• Expensive to compute — aggregations, reports
• Tolerant of staleness — show counts, trending lists

Redis is the standard for server-side caching:

async function getProductById(id: string) {
  const cached = await redis.get(`product:${id}`);
  if (cached) return JSON.parse(cached);

  const product = await db.query('SELECT * FROM products WHERE id = $1', [id]);
  await redis.setex(`product:${id}`, 300, JSON.stringify(product)); // 5 min TTL
  return product;
}

Invalidate cache on write. Never cache data longer than its useful freshness window.

Key Takeaways

1. Always EXPLAIN ANALYZE before and after changes — measure, don't guess.
2. Index the columns you filter and sort on. Order composite index columns from highest-cardinality to lowest.
3. Eliminate N+1 queries with eager loading.
4. Use connection pooling — never open connections per-request.
5. Switch from OFFSET to keyset pagination for any list that can grow large.
6. Route reads to replicas; write to primary.
7. Cache aggressively; invalidate on write.

Database performance is mostly about not asking the database to do unnecessary work. Good indexes and query structure take you 90% of the way.