High-Throughput Database Optimization: Compound Indexing, Caching Patterns, and Query Tuning
High‑throughput systems struggle when database queries misalign with storage structures. By applying ESR‑based compound indexes, covering indexes, and stampede‑resistant caching, teams can reduce disk I/O, avoid buffer pool pollution, and keep latency predictable.
Modern distributed applications routinely ingest telemetry, transactions, and user state at tens of thousands of requests per second. While compute nodes and edge workers can scale in milliseconds, relational engines such as PostgreSQL still choke on disk I/O, lock contention, and write amplification. The result is a cascade of slow queries, connection pool exhaustion, and exponential retry storms that can bring an entire service down.
Why Indexes Matter in a High‑Throughput World
In PostgreSQL, every query must fetch pages from shared buffers or disk blocks. A missing or poorly ordered index forces the planner to perform a sequential scan, streaming gigabytes of raw data into memory. This not only stalls the offending query but also evicts hot pages from the buffer pool, causing unrelated transactions to wait for disk reads. The cost is twofold: slower reads and slower writes, because every INSERT, UPDATE, or DELETE must update every relevant B‑Tree index and write to the WAL.
The key to high‑throughput is aligning the index structure with the query shape. The ESR (Equality‑Sort‑Range) rule provides a simple heuristic:
- Equality: Place columns used with = first.
- Sort: Place columns used in ORDER BY next.
- Range: Place columns used with BETWEEN, <, >, etc., last.
When a query matches this ordering, the planner can perform an index scan that returns results in the desired order, eliminating an explicit sort step.
Covering Indexes and Index‑Only Scans
PostgreSQL’s INCLUDE clause lets you attach non‑search columns to the leaf nodes of a B‑Tree without affecting the routing keys. This means the planner can satisfy a query entirely from the index, performing an index‑only scan and bypassing the heap entirely. The trade‑off is a slightly larger index, but the performance win is often worth it.
For example, consider an order‑tracking table:
CREATE TABLE orders( id BIGSERIAL PRIMARY KEY, tenant_id UUID NOT NULL, customer_id UUID NOT NULL, status VARCHAR(32) NOT NULL, total_amount NUMERIC(12,2) NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), metadata JSONB );
A typical analytical query might look for the most recent delivered orders for a tenant:
SELECT id, customer_id, total_amount, created_at FROM orders WHERE tenant_id = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11' AND status = 'DELIVERED' AND created_at >= '2026-01-01 00:00:00Z' ORDER BY created_at DESC LIMIT 50;
Without an index, PostgreSQL performs a Seq Scan and a top‑N heap sort. Adding a compound covering index resolves this:
CREATE INDEX CONCURRENTLY idx_orders_tenant_status_created_covering ON orders(tenant_id, status, created_at DESC) INCLUDE(customer_id, total_amount);
The planner now uses an index‑only scan, eliminating both the sequential scan and the sort.
Preventing Cache Stampedes with XFetch
Even with perfect indexes, an analytical endpoint can suffer a cache stampede when a key expires under load. The XFetch algorithm mitigates this by probabilistically refreshing a key before it hard‑expires. Below is a concise TypeScript implementation that works with Node.js and Redis:
import { createClient } from 'redis';
class ResilientCache {
private redis = createClient({ url: process.env.REDIS_URL || 'redis://localhost:6379' });
constructor() {
this.redis.connect().catch(err => console.error('Redis error:', err));
}
async getOrCompute(key, ttlSeconds, beta = 1.0, computeFn) {
const raw = await this.redis.get(key);
const now = Date.now();
if (raw) {
const record = JSON.parse(raw);
const early = now - (record.delta * beta * Math.log(Math.random())) > record.expiry;
if (!early) return record.value;
}
const start = Date.now();
const fresh = await computeFn();
const delta = Date.now() - start;
const payload = { value: fresh, delta, expiry: now + ttlSeconds * 1000 };
const margin = ttlSeconds + Math.ceil(delta / 1000) + 10;
await this.redis.set(key, JSON.stringify(payload), { EX: margin });
return fresh;
}
}
This pattern ensures that only a single thread recomputes the value while others continue to serve stale data until the new value is ready.
Additional Best Practices
- Partial Indexes: For status‑skewed tables, index only active rows to keep the index small.
- Keep Queries Sargable: Avoid applying functions to indexed columns; use range predicates instead.
- Tune Buffer Settings: Set shared_buffers to 25% of RAM, work_mem to 4–64 MB per query, and lower random_page_cost to 1.1 for NVMe disks.
- Monitor for Bloat: Regularly vacuum and analyze to prevent index bloat from frequent updates.
When to avoid these techniques? In write‑heavy, read‑light workloads, compound indexes add unnecessary write overhead. In scenarios with high cache churn and low key reuse, a Redis cluster may waste RAM. Always weigh the trade‑offs against your workload profile.
By combining ESR‑based compound indexing, covering indexes, and XFetch caching, teams can transform a database that once throttled their entire stack into a resilient, high‑throughput backbone that scales with their application.
Why it matters
Efficient indexing and caching turn a bottlenecked database into a scalable foundation, reducing infrastructure costs and preventing outages that could cost millions in lost revenue.
Key points
- ESR rule aligns indexes with query shape for fast scans
- Covering indexes enable index‑only scans, cutting heap access
- XFetch prevents cache stampedes by early probabilistic refresh
- Partial indexes keep index size manageable for skewed data
- Tuning buffer and random_page_cost settings boosts planner decisions
Frequently asked questions
What is the ESR rule?
ESR stands for Equality‑Sort‑Range, a heuristic that orders compound index columns to match query predicates: equality first, then sort columns, then range columns.
How does XFetch differ from a simple TTL?
XFetch adds a probabilistic early refresh before the TTL expires, reducing the chance that many clients hit the database simultaneously when a key expires.
When should I avoid compound indexes?
In write‑heavy, read‑light systems, compound indexes can degrade performance because each write updates every index; consider partitioning or a different storage engine.
