Optimizing PostgreSQL: Indexing Beyond the Basics and Query Tuning

When database bottlenecks surface, the instinct is often to throw bigger hardware at the problem or sprinkle basic indexes across every column in a WHERE clause. In practice, thoughtful schema design paired with targeted indexing usually resolves the problem with minimal overhead. Here is how I approach index design and query tuning in PostgreSQL when default strategies hit their limits.

Understanding Index Ordering in Multi-Column Indexes

One of the most frequent performance traps I encounter is improper column ordering in composite B-Tree indexes. In PostgreSQL, a B-Tree index on (status, created_at) is fundamentally different from one on (created_at, status).

The rule of thumb is straightforward: place equality columns first, followed by range or sort columns. Consider a table tracking background tasks where you frequently query for pending items ordered by date:

CREATE TABLE background_jobs (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payload JSONB NOT NULL,
    status VARCHAR(32) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

If your workload runs queries filtering by status = 'pending' and ordering by created_at ASC, the proper index places status first:

CREATE INDEX idx_jobs_status_created 
ON background_jobs (status, created_at);

This allows the query planner to jump directly to the pending subset and read the rows in already-sorted order, eliminating an expensive in-memory sort operation.

Slicing Footprint with Partial Indexes

A multi-column index on the entire table can become massive, increasing disk usage and slowing down write operations. If 95% of your records sit in a terminal state like completed or archived, indexing them for active queries is wasteful.

This is where partial indexes shine. By attaching a WHERE clause to the index definition, you keep the index tiny, fast, and cache-friendly:

CREATE INDEX idx_jobs_pending_active 
ON background_jobs (created_at)
WHERE status IN ('pending', 'processing');

When a query filters specifically within that predicate, Postgres uses the partial index. Not only does this save memory, but INSERT and UPDATE operations on completed rows bypass this index entirely, keeping write amplification low.

Index-Only Scans with Covering Indexes

Sometimes you need values from columns that are not part of the search criteria. Normally, Postgres visits the index, retrieves a tuple pointer, and visits the actual table heap to fetch remaining data. If your table is large, those random heap reads add up.

With the INCLUDE clause, you can append payload columns to leaf pages of the index without including them in the B-Tree search structure:

CREATE INDEX idx_jobs_covering 
ON background_jobs (status, created_at) 
INCLUDE (id);

If the table pages are marked clean in the visibility map, Postgres performs an Index Only Scan, bypassing table heap lookups entirely.

Diagnosing Queries with EXPLAIN (ANALYZE, BUFFERS)

Never tune blindly. When inspecting slow queries, running a basic EXPLAIN gives you the planner's estimates, but EXPLAIN (ANALYZE, BUFFERS) tells you the real execution story:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at 
FROM background_jobs 
WHERE status = 'pending' 
ORDER BY created_at ASC 
LIMIT 50;

When reading the output, focus on two critical metrics:

• Shared Hit vs. Shared Read: In the buffers section, hit indicates data read from PostgreSQL's shared memory buffer, while read indicates reads from disk or OS page cache. A high number of read blocks points to cache misses or excessive scans.
• Row Estimates vs. Actual: Compare rows=N in the plan with actual rows=N. A wide divergence usually means statistics are stale, causing the planner to choose nested loops over hash joins or sequential scans over index scans. Running ANALYZE background_jobs; updates those statistics.

Avoiding Expression Invalidation

Another common mistake is wrapping indexed columns in functions within queries:

-- This forces a sequential scan, ignoring standard indexes:
SELECT * FROM background_jobs WHERE date_trunc('day', created_at) = '2026-10-25';

-- Instead, write sargable range predicates:
SELECT * FROM background_jobs 
WHERE created_at >= '2026-10-25 00:00:00+00' 
  AND created_at < '2026-10-26 00:00:00+00';

Keeping the indexed column bare preserves the ability to walk the B-Tree efficiently without requiring an explicit expression index.