PostgreSQL Indexing Mastery: B-Trees, BRIN, GIN, and Query Planner Internals

#Beyond Standard Indexing: How the PostgreSQL Planner Thinks

Every software engineer knows how to run CREATE INDEX ON table (column). However, in high-volume OLTP systems processing millions of writes per hour, blindly creating indexes degrades write throughput, inflates table bloat, and creates heavy VACUUM lock contention.

To truly master database performance, we must inspect how the planner evaluates index costs:

EXPLAIN (ANALYZE, BUFFERS, COSTS)
SELECT id, title, published_at
FROM articles
WHERE status = 'PUBLISHED'
  AND published_at >= '2026-01-01'
ORDER BY published_at DESC
LIMIT 20;

When evaluating paths, the cost is calculated by summing disk page fetches with CPU operator execution:

$$\text{Cost} = (\text{pages} \times \text{seq_page_cost}) + (\text{tuples} \times \text{cpu_tuple_cost})$$

#The Secret of Leftmost Prefix Matching

Consider the composite index:

CREATE INDEX idx_articles_status_date ON articles (status, published_at DESC);

This B-Tree sorts first by status, and within identical statuses, by published_at descending.

Root Page
   ├── ["ARCHIVED", 2026-03-01]
   ├── ["DRAFT",    2026-08-15]
   └── ["PUBLISHED",2026-09-28]  ──► Matches directly to leaf page!

If your query filters by WHERE published_at >= '2026-01-01' without filtering by status, the database engine cannot perform a binary tree descent and must scan all pages or fall back to a full table scan.

#When BRIN (Block Range Index) Beats B-Tree

For massive append-only tables (like application logs, analytics events, telemetry, or immutable ledger lines), a B-Tree index can consume up to 40% of the entire table disk footprint.

A BRIN (Block Range Index) stores only the minimum and maximum value for a contiguous block of disk pages (default: 128 pages = 1MB).

-- B-Tree size on 100 million rows: ~2.4 GB
-- BRIN index size on 100 million rows: ~380 KB (99.98% storage savings!)
CREATE INDEX idx_telemetry_brin ON telemetry_events USING BRIN (created_at);

BRIN indexes are extraordinarily fast when rows are physically inserted in chronological order on disk.

#Index Only Scans: The Holy Grail

An Index-Only Scan completely bypasses reading the heap table pages because all columns requested in the SELECT clause exist inside the index tree itself.

CREATE INDEX idx_articles_covering 
ON articles (status, published_at DESC) 
INCLUDE (title, view_count);

When this runs, PostgreSQL verifies visibility map status in memory and fulfills the entire query without a single random I/O read from the main disk blocks.