PostgreSQL Indexing Strategy: B-Tree, GIN, BRIN, and Partial Indexing for High-Throughput Databases

When a database table crosses the threshold of ten million rows, naive SQL queries that previously completed in 15 milliseconds suddenly trigger catastrophic sequential table scans taking 12 seconds. In high-concurrency environments, these slow queries lock up connection pools, saturate CPU cores, and bring production APIs to a standstill.

The knee-jerk reaction is often to indiscriminately add indexes to every column mentioned in a WHERE clause. However, indexes are not free: every additional index imposes a severe write penalty on INSERT, UPDATE, and DELETE operations, consumes expensive RAM in shared_buffers, and bloats disk storage.

In this guide, we break down the exact indexing strategies we use at Codedway to keep high-throughput PostgreSQL databases executing queries in under 5 milliseconds while maintaining lean write amplification.

1. Index Types: Selecting the Correct Data Structure

PostgreSQL provides multiple distinct indexing engine types. Selecting the wrong index type for your access pattern guarantees suboptimal performance.

┌─────────────────┬───────────────────────────────┬────────────────────────────────┐
│ Index Type      │ Optimal Workload              │ Storage / Write Cost           │
├─────────────────┼───────────────────────────────┼────────────────────────────────┤
│ B-Tree          │ Equality (=), Ranges (<, >),  │ Moderate storage, moderate     │
│                 │ Sorting (ORDER BY)            │ write overhead per update      │
├─────────────────┼───────────────────────────────┼────────────────────────────────┤
│ GIN (Inverted)  │ JSONB queries, Full-text,     │ High disk consumption, heavy   │
│                 │ Array containment (@>)        │ write amplification            │
├─────────────────┼───────────────────────────────┼────────────────────────────────┤
│ BRIN (Range)    │ Append-only time-series logs, │ Negligible disk footprint      │
│                 │ naturally ordered sequences   │ (<1% of B-Tree), fast writes   │
├─────────────────┼───────────────────────────────┼────────────────────────────────┤
│ Hash            │ Exact equality matching only  │ Similar to B-Tree, lacks range │
│                 │ (no sorting support)          │ or sort capabilities           │
└─────────────────┴───────────────────────────────┴────────────────────────────────┘

2. Deep Dive: B-Tree Index Optimization & Covering Indexes

The B-Tree (balanced tree) is the default and most versatile index structure. It keeps key-value pairs sorted in hierarchical 8KB memory pages, guaranteeing $O(\log N)$ search time.

The Problem with Multi-Column Index Ordering

Consider a multi-column B-Tree index defined as:

CREATE INDEX idx_orders_tenant_status_created 
ON orders (tenant_id, status, created_at);

Due to the hierarchical sorting of B-Trees:

• A query filtering by WHERE tenant_id = 'org_123' can utilize the index.
• A query filtering by WHERE tenant_id = 'org_123' AND status = 'PAID' can utilize the index.
• A query filtering only by WHERE status = 'PAID' cannot utilize the index efficiently because the leftmost prefix rule is violated.

The Power of Covering Indexes with INCLUDE

In PostgreSQL 11+, you can create Covering Indexes using the INCLUDE clause. This allows you to append non-key payload columns to leaf pages of the index without including them in the B-Tree search structure:

-- Covering index: allows Index-Only Scans without table heap lookups
CREATE INDEX idx_deliveries_lookup_covering
ON deliveries (tenant_id, driver_id)
INCLUDE (status, completed_at, total_amount);

Why this matters: The Index-Only Scan

When a query executes:

SELECT status, completed_at, total_amount
FROM deliveries
WHERE tenant_id = 'tenant_98' AND driver_id = 'drv_412';

PostgreSQL does not need to visit the main table heap on disk at all. It satisfies the query entirely from the B-Tree leaf pages in RAM, turning a 45ms random disk I/O operation into a 0.8ms in-memory scan.

3. BRIN Indexes: The Secret Weapon for Massive Time-Series Data

When storing immutable event streams, audit logs, or IoT telemetry (e.g. 500 million rows), standard B-Trees consume gigabytes of RAM. A B-Tree on a created_at timestamp column across 100M rows can easily exceed 2.8 gigabytes in index size.

Enter BRIN (Block Range Index)

BRIN exploits physical correlation. If data is written sequentially over time, records located in adjacent disk blocks have monotonically increasing timestamps. Instead of indexing every individual row, a BRIN index stores only the minimum and maximum values for physical ranges of pages (default: 128 pages = 1MB of disk):

-- BRIN index on physical timestamp sequence
CREATE INDEX idx_telemetry_created_brin
ON telemetry_events
USING BRIN (created_at)
WITH (pages_per_range = 64);

The Performance Comparison:

• B-Tree Size on 100M rows: ~2,400 MB (Frequently exceeds buffer pool)
• BRIN Size on 100M rows: ~3.2 MB (99.8% smaller!)
• Query execution time: Virtually indistinguishable from B-Tree when querying wide time windows (WHERE created_at >= NOW() - INTERVAL '7 days').

4. Partial Indexing: Eliminating Wasted Index Bloat

Most applications have skewed data distributions. For example, 96% of background jobs in a table are status = 'COMPLETED', and only 4% are status = 'FAILED' or status = 'QUEUED'.

A naive index on status:

-- BAD: Indexes millions of completed rows you will never query in real-time
CREATE INDEX idx_jobs_status ON background_jobs (status);

Indexes all 50 million completed jobs, consuming 1.2GB of RAM.

The Optimization: The Partial Index

Instead, index only the rows your application actively queries:

-- GOOD: Partial index containing only active work items
CREATE INDEX idx_active_jobs_priority
ON background_jobs (priority, created_at)
WHERE status IN ('QUEUED', 'PROCESSING');

This partial index:

1. Is 96% smaller (~45MB instead of 1.2GB).
2. Fits permanently inside PostgreSQL's in-memory shared_buffers.
3. Incurs zero write overhead when rows transition into the un-indexed COMPLETED state.

5. Production Zero-Downtime Rule: Always Use CONCURRENTLY

Never run CREATE INDEX on a live production table without the CONCURRENTLY keyword:

-- DANGEROUS: Takes exclusive SHARE lock, blocking all concurrent INSERT/UPDATE/DELETE
CREATE INDEX idx_customers_email ON customers (email);

-- SAFE: Builds index in background across multiple passes without blocking writes
CREATE INDEX CONCURRENTLY idx_customers_email ON customers (email);

Failure Mode with CONCURRENTLY

If a concurrent index build fails (e.g. due to a unique constraint violation or deadlock), PostgreSQL leaves an INVALID index behind. Check for invalid indexes using:

SELECT indrelid::regclass, indexrelid::regclass, indisvalid
FROM pg_index
WHERE NOT indisvalid;

If an invalid index is discovered, simply drop it (DROP INDEX CONCURRENTLY idx_name;) and resolve the underlying conflict before re-running.

6. Checklist for Production Indexing Audits

Before deploying schema migrations to production, verify:

1. Analyze with EXPLAIN ANALYZE: Verify that Seq Scan is replaced by Index Scan or Index Only Scan on tables larger than 50,000 rows.
2. Leftmost Prefix Compliance: Ensure multi-column indexes match query predicate sequences.
3. Monitor Index Usage: Periodically query pg_stat_user_indexes to identify dead indexes that accumulate write penalties without ever serving read queries (idx_scan = 0).
4. Tune Fillfactor for High-Churn Tables: For tables experiencing frequent UPDATE operations, set WITH (fillfactor = 85) to allow HOT (Heap-Only Tuple) optimization, preventing index updates entirely when non-indexed columns change.

Disciplined indexing is the single highest-leverage optimization you can perform on a relational database — turning sluggish applications into lightning-fast platforms without spending an extra dollar on cloud hardware.