PostgreSQL Query Optimization for High-Traffic Applications

The e-commerce platform I worked on at Sprinting Software handled over 1,000 orders per day. For the first few months, the database was fine. Then we hit a scaling inflection point — checkout latency spiked from 200ms to 4 seconds, and our APM traces pointed straight at the database.

These are the techniques that brought it back down. Not theory — specific queries, specific indexes, specific decisions we made and why.

Start With EXPLAIN ANALYZE

Before you touch an index or rewrite a query, run EXPLAIN ANALYZE on the slow query and read the output. Everything else follows from understanding what PostgreSQL is actually doing.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.*, c.email, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
  AND o.created_at > NOW() - INTERVAL '24 hours'
ORDER BY o.created_at DESC;

Key things to look for in the output:

• Seq Scan on a large table — missing index
• Hash Join vs Index Scan — sometimes a hash join is faster, but on a filtered subset an index scan wins
• Rows Removed by Filter — if this number is large, your index isn't selective enough
• Buffers: hit vs read — cache misses indicate the working set doesn't fit in shared_buffers

The Indexes That Actually Matter

Partial Indexes for Status Columns

Status columns are a classic example of a low-cardinality column where a standard index helps less than you'd expect. If 95% of your orders are completed, a query for status = 'pending' still scans most of the index.

Use a partial index instead:

-- Index only the rows you actually query
CREATE INDEX idx_orders_pending
  ON orders(created_at DESC)
  WHERE status = 'pending';
 
CREATE INDEX idx_orders_processing
  ON orders(created_at DESC)
  WHERE status IN ('pending', 'processing');

This index is tiny (only pending orders), always in memory, and blazing fast for the dashboard query that shows "orders to action today."

Composite Index Order Matters

The order of columns in a composite index determines which queries can use it.

-- Supports: WHERE tenant_id = ? AND status = ?
-- Supports: WHERE tenant_id = ? (prefix)
-- Does NOT support: WHERE status = ? alone
CREATE INDEX idx_orders_tenant_status
  ON orders(tenant_id, status, created_at DESC);

Rule: put the most selective column first, equality conditions before range conditions.

Covering Indexes to Avoid Table Lookups

If your query only needs certain columns, a covering index lets PostgreSQL answer it entirely from the index without touching the table (called an "index-only scan"):

-- Query: SELECT id, status, total FROM orders WHERE customer_id = ?
CREATE INDEX idx_orders_customer_covering
  ON orders(customer_id)
  INCLUDE (status, total, created_at);

The N+1 Problem Is a Database Problem

ORM-generated N+1 queries kill performance faster than any missing index. In TypeORM:

// ❌ N+1: runs 1 query for orders, then 1 per order for customer
const orders = await orderRepo.find();
for (const order of orders) {
  console.log(order.customer.email); // lazy-loads each customer
}
 
// ✅ Single query with JOIN
const orders = await orderRepo.find({
  relations: { customer: true },
  where: { status: 'pending' },
});

Always check your ORM's query log in development. TypeORM: set logging: true in your DataSource config. Every query your API makes should be visible and reviewable.

Connection Pooling with PgBouncer

Each PostgreSQL connection costs ~5–10MB of RAM and a forked process. A Node.js API under load can open hundreds of connections if you're not careful.

Use PgBouncer in transaction mode between your app and Postgres:

[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp
 
[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
server_idle_timeout = 600

With this config, your app can have 1,000 concurrent connections to PgBouncer, but only 25 actual connections to PostgreSQL. In transaction mode, a connection is only held for the duration of a transaction — idle connections are returned to the pool immediately.

Pagination That Doesn't Hurt

OFFSET-based pagination is a performance trap. OFFSET 1000 means PostgreSQL reads 1,000 rows and throws them away:

-- ❌ Gets slower the deeper you paginate
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 1000;
 
-- ✅ Cursor-based: always fast regardless of position
SELECT * FROM orders
WHERE created_at < :cursor
ORDER BY created_at DESC
LIMIT 20;

For admin dashboards where true offset pagination is needed and tables are large, create a covering index on the sort column and accept the limitation.

Caching With Redis

Not every database query needs to hit PostgreSQL. Read-heavy data that changes infrequently — product catalogues, pricing, tenant configuration — belongs in Redis:

async findAll(tenantId: string): Promise<Product[]> {
  const cacheKey = `products:${tenantId}`;
  const cached = await this.redis.get(cacheKey);
  if (cached) return JSON.parse(cached);
 
  const products = await this.productRepo.find({ where: { tenantId } });
  await this.redis.setex(cacheKey, 300, JSON.stringify(products)); // 5min TTL
  return products;
}

Invalidate on write:

async updateProduct(id: string, dto: UpdateProductDto): Promise<Product> {
  const product = await this.productRepo.save({ id, ...dto });
  await this.redis.del(`products:${product.tenantId}`);
  return product;
}

The Query That Actually Hurt Us

The specific query that caused our latency spike was an order search with multiple optional filters:

// Dynamic filter builder — every combination creates a different query plan
const qb = orderRepo.createQueryBuilder('order');
if (status)     qb.andWhere('order.status = :status', { status });
if (customerId) qb.andWhere('order.customer_id = :customerId', { customerId });
if (dateFrom)   qb.andWhere('order.created_at >= :dateFrom', { dateFrom });
if (dateTo)     qb.andWhere('order.created_at <= :dateTo', { dateTo });
qb.orderBy('order.created_at', 'DESC').limit(50);

The fix was two-part:

1. Created a composite index on (status, created_at DESC) — the most common filter combination
2. Added LIMIT enforcement server-side regardless of what the client requests — a missing LIMIT on an admin search was doing full table scans

The result: 4s → 180ms on the order search endpoint.

The pattern that matters most: measure before you optimize. EXPLAIN ANALYZE on your slowest queries, then index the columns in those specific WHERE clauses. Generic advice about indexing everything or nothing is less useful than reading your actual query plans.