PostgreSQL Indexing Strategies

Index Types in PostgreSQL

PostgreSQL supports multiple index types, each optimized for different access patterns. Choosing the right index type is one of the highest-leverage decisions you can make for query performance.

B-tree: The Default Workhorse

B-tree indexes handle equality and range queries. They support multi-column (composite) indexes where column order matters — the leftmost prefix rule applies.

-- Single-column B-tree (default type)
CREATE INDEX idx_users_email ON users (email);

-- Composite index: queries on (status) or (status, created_at) use this
CREATE INDEX idx_orders_status_date ON orders (status, created_at DESC);

-- Verify the index is used
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 20;

Partial & Expression Indexes

Partial indexes only cover rows matching a WHERE condition, making them smaller and faster. Expression indexes index the result of a function or expression.

-- Partial index: only active users (much smaller than a full index)
CREATE INDEX idx_active_users ON users (email)
WHERE deleted_at IS NULL;

-- Expression index: case-insensitive email lookup
CREATE INDEX idx_users_lower_email ON users (LOWER(email));

-- Query that uses the expression index
SELECT * FROM users WHERE LOWER(email) = 'ada@example.com';

GIN & GiST Indexes

GIN indexes are ideal for values that contain multiple elements — arrays, JSONB documents, and full-text search vectors. GiST indexes shine for geometric and range-type data.

-- GIN index on a JSONB column
CREATE INDEX idx_events_payload ON events USING GIN (payload);

-- GIN on an array column
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);

-- GiST for range queries (e.g., overlapping date ranges)
CREATE INDEX idx_bookings_range ON bookings
USING GiST (daterange(check_in, check_out));

Covering Indexes with INCLUDE

Covering indexes add non-key columns so PostgreSQL can satisfy queries entirely from the index (index-only scans) without visiting the heap table.

-- Covering index: INCLUDE columns are stored but not part of the search key
CREATE INDEX idx_orders_covering ON orders (customer_id)
INCLUDE (total, status);

-- This query can now be an index-only scan
EXPLAIN ANALYZE
SELECT customer_id, total, status
FROM orders
WHERE customer_id = 42;

BRIN Indexes for Large Tables

BRIN indexes store min/max values per block range. They are extremely small and efficient for large tables where data is physically ordered (e.g., time-series with sequential inserts).

-- BRIN on a timestamp column (table has millions of rows)
CREATE INDEX idx_logs_created_brin ON logs
USING BRIN (created_at);

-- Very small index for range scans on ordered data
EXPLAIN ANALYZE
SELECT * FROM logs
WHERE created_at >= '2025-01-01' AND created_at < '2025-02-01';