May 9, 2026 · 8 min read

PostgreSQL Indexing Patterns I Actually Use

Most index advice stops at "add an index on the column you filter on." That's fine for toy datasets. At 50M rows with 200k daily inserts, the wrong index choice costs you more than no index at all.

Sequential scan vs index scan cost comparison

EXPLAIN ANALYZE Is the Only Truth

Before touching indexes, run EXPLAIN (ANALYZE, BUFFERS) on your slow query. The BUFFERS output shows actual I/O — blocks hit from cache vs blocks read from disk. That number is what matters, not the estimated row count.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM events
WHERE user_id = 42
  AND created_at > NOW() - INTERVAL '30 days';

If you see Seq Scan with Buffers: shared hit=0 read=180000, you're reading 1.4 GB off disk on every query. That's where you start.

BRIN for Time-Series Data

On a 50M-row events table with a created_at column, a standard BTREE index weighed 480 MB and required constant maintenance. Switching to BRIN:

CREATE INDEX events_created_brin
    ON events USING BRIN (created_at)
    WITH (pages_per_range = 128);

Index size: 4.2 MB — 99.1% smaller. For append-only time-series data where rows are inserted in timestamp order, BRIN stores min/max values per block range instead of indexing every row. Range queries on recent data are fast because the planner skips entire block ranges.

BRIN degrades on wide date ranges (30+ days) and non-sequential inserts. Know your access pattern before switching.

Scan type cost comparison — Fig 2. Index scan cost relative to table size and selectivity.

Index size comparison: BTREE vs BRIN vs Partial — Fig 3. Index sizes for the same 50M-row table. Partial indexes win when your queries are selective.

BRIN query performance vs date range — Fig 4. BRIN query time by date range. Stays fast for recent data, degrades past 30 days.

Partial Indexes

Only index the rows you actually query. If 95% of your queries filter on status = 'pending':

CREATE INDEX jobs_pending_idx
    ON jobs (created_at DESC)
    WHERE status = 'pending';

Our pending jobs index is 90 MB vs 620 MB for a full composite. The planner uses it only when the WHERE clause matches the index predicate — and it does, always, for our job worker queries.

Covering Indexes

If a query only needs columns that are all in the index, PostgreSQL never touches the heap (the actual table rows). Use INCLUDE to add non-key columns:

CREATE INDEX users_email_covering
    ON users (email)
    INCLUDE (id, name, created_at);

A lookup by email returns id, name, and created_at without a heap fetch. On a hot table with high read traffic, eliminating heap fetches cuts I/O in half.

Composite Index Column Order

For a composite index on (user_id, created_at):

Put the equality filter column first (user_id = ?)
Put the range filter column second (created_at > ?)

The inverse order forces a full scan of all matching created_at ranges across every user. The planner can't use the range component effectively if it comes before the equality component.

Always validate with EXPLAIN (ANALYZE, BUFFERS). The index existing doesn't mean the planner uses it.