Optimizing PostgreSQL for High-Throughput Reads: Indexes, EXPLAIN, and Autovacuum

When read latency starts climbing in a relational application, the instinctive reaction is often to throw a Redis cache in front of every slow endpoint. While caching has its place, it introduces cache invalidation bugs and distributed synchronization complexity.

In the majority of applications, PostgreSQL itself can easily handle tens of thousands of queries per second on modest hardware when queries and indexes are designed in harmony with PostgreSQL's execution engine.

Reading EXPLAIN (ANALYZE, BUFFERS) Correctly

Running EXPLAIN tells you what the PostgreSQL query planner estimates it will do. Running EXPLAIN (ANALYZE, BUFFERS) executes the query, recording actual timing and disk I/O metrics.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, total_amount, created_at
FROM orders
WHERE user_id = 'e7b163a7-0e6e-473d-9b5a-73e4b7c6c491'
  AND status = 'COMPLETED'
ORDER BY created_at DESC
LIMIT 20;

When inspecting the output, prioritize these two metrics above total execution time:

1. Shared Hit vs Read Blocks: Shared Hit Blocks represent data pages retrieved directly from memory (PostgreSQL's buffer cache). Shared Read Blocks represent pages fetched from disk or the operating system page cache. If a query requires thousands of read blocks for a simple 20-row result, an index is missing or improperly structured.
2. Rows Removed by Filter: If an index scan locates 50,000 index tuples only for the engine to filter out 49,980 rows in a secondary filter step, your index is not selective enough.

The Composite Index Column Ordering Rule

Composite indexes are powerful, but their column order must strictly mirror how the query planner processes predicates:

Rule of thumb: Place equality columns first, followed by range/ordering columns.

Consider this query:

-- Queries orders by tenant, status, and sorted by date
SELECT * FROM orders
WHERE tenant_id = 42 AND status = 'PAID'
ORDER BY created_at DESC;

If you create the index as:

-- SUBOPTIMAL: Range/order column in the middle
CREATE INDEX idx_orders_bad ON orders (tenant_id, created_at, status);

PostgreSQL can use tenant_id to jump to the tenant's records, but because created_at is in the second position, it cannot use the index to directly isolate status = 'PAID' without scanning across different dates.

The optimal index:

-- OPTIMAL: Equality columns first, sort column last
CREATE INDEX idx_orders_optimal ON orders (tenant_id, status, created_at DESC);

With this structure, the engine jumps directly to the matching (tenant_id, status) slice in the B-Tree and traverses it in exact created_at DESC order, eliminating the need for an in-memory Sort node entirely.

Covering Indexes with INCLUDE

If you have high-frequency queries fetching a small set of columns, an Index-Only Scan avoids reading the heap table pages altogether:

CREATE INDEX idx_users_active_lookup
ON users (email)
INCLUDE (id, is_active, last_login_at);

By placing payload columns in the INCLUDE clause, they are stored in the leaf nodes of the B-Tree without contributing to the B-Tree sorting key. This keeps the index compact and enables index-only reads.

Autovacuum and Bloat Management

Even the best index becomes sluggish if the underlying table suffers from bloat. In PostgreSQL's MVCC (Multi-Version Concurrency Control) model, an UPDATE or DELETE does not overwrite the existing row on disk; it writes a new version and marks the old row as dead.

Autovacuum is responsible for reclaiming these dead tuple spaces for future inserts. On high-throughput write tables, default autovacuum settings are often too conservative:

-- Tune autovacuum for a high-write table
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.05,  -- Trigger vacuum when 5% of rows are dead (default is 20%)
  autovacuum_vacuum_cost_limit = 1000     -- Allow vacuum to do more work per cycle
);

Tuning scale factors prevents tables from accumulating gigabytes of dead space, keeping index and heap scans tightly contained within RAM buffer cache.