Deep Dive: PostgreSQL B-Tree, GIN, and BRIN Indexing Strategies for Terabyte Datasets

1. The Cost of Sequential Table Scans

When relational database tables reach tens of millions of rows, executing unindexed queries (e.g. WHERE status = 'PENDING' AND created_at > NOW() - INTERVAL '7 days') forces PostgreSQL to perform sequential scans (Seq Scan), reading every single disk block into RAM. This spikes CPU utilization to 100% and stalls all concurrent user connections.

2. Index Types & Optimal Use Cases

• B-Tree Index: Default index type ideal for equality (=) and range queries (>, <, BETWEEN) on unique identifiers, timestamps, and numeric columns.
• GIN (Generalized Inverted Index): Critical for JSONB columns, array fields, and full-text search indexing.
• BRIN (Block Range Index): High-efficiency index for naturally ordered time-series datasets, occupying 99% less disk space than B-Tree indexes.
• Partial Indexes: Indexing a subset of rows (e.g. WHERE status = 'UNPROCESSED') to minimize index write overhead.

3. Code Example: Creating High-Performance Partial & Composite Indexes

-- Composite B-Tree Index for Time-Range Queries
CREATE INDEX CONCURRENTLY idx_orders_customer_created 
ON orders (customer_id, created_at DESC);

-- Partial Index targeting only active unfulfilled orders
CREATE INDEX CONCURRENTLY idx_orders_unfulfilled 
ON orders (id, created_at) 
WHERE status = 'UNFULFILLED';

-- GIN Index for JSONB Metadata searching
CREATE INDEX CONCURRENTLY idx_orders_payload_gin 
ON orders USING GIN (metadata jsonb_path_ops);