Indexes Are Tradeoffs, Not Free Lunches
Every index speeds up reads and slows down writes. Every INSERT, UPDATE, and DELETE on an indexed column requires updating the index too. A table with 10 indexes means 10 additional write operations per row change. That's why "just add an index" isn't always the answer — it's a tradeoff, and understanding the mechanics helps you make the right call.
B-Tree: The Default Workhorse
B-tree indexes are the default in PostgreSQL, MySQL, and SQLite. They store data in a balanced tree structure where each node contains multiple keys and pointers. This lets them efficiently handle:
- Equality:
WHERE id = 42 - Range:
WHERE price BETWEEN 10 AND 50 - Sorting:
ORDER BY created_at DESC - Prefix matching:
WHERE name LIKE 'John%'
A B-tree on a table with 10 million rows typically has a depth of 3-4 levels. That means finding any single row requires 3-4 page reads. With the top levels cached in memory (they almost always are), it's effectively 1-2 disk reads. That's why indexed lookups are fast.
Composite Index Column Order
This is where most people go wrong. A composite index on (a, b, c) can be used for:
WHERE a = 1 ✓ (leftmost prefix)
WHERE a = 1 AND b = 2 ✓
WHERE a = 1 AND b = 2 AND c = 3 ✓ (full index)
WHERE a = 1 AND c = 3 △ (uses a, skips b, scans for c)
WHERE b = 2 ✗ (can't skip leftmost column)
WHERE b = 2 AND c = 3 ✗The rule: a composite index is usable starting from the leftmost column. Put the most selective (highest cardinality) equality column first, followed by other equality columns, then range/sort columns last.
-- Query: WHERE status = 'active' AND created_at > '2026-01-01' ORDER BY created_at
-- Good: status first (equality), then created_at (range + sort)
CREATE INDEX idx_status_created ON orders(status, created_at);
-- Bad: created_at first means PostgreSQL can't use the index
-- for the status filter efficiently
CREATE INDEX idx_created_status ON orders(created_at, status);Hash Indexes
Hash indexes compute a hash of the indexed value and store it in a hash table. Lookup is O(1) on average. But they only support equality (=) — no ranges, no sorting, no prefix matching.
In PostgreSQL, hash indexes were crash-unsafe before version 10 and are now WAL-logged and safe. But honestly, B-trees are so well optimized that the performance difference is negligible for most workloads. I'd reach for a hash index only if benchmarking proves it helps on a specific equality-heavy query pattern.
GIN: The JSONB and Full-Text Index
GIN (Generalized Inverted Index) is designed for values that contain multiple elements — arrays, JSONB documents, tsvector (full-text search). It works by creating an index entry for each element within the value.
-- Full-text search
ALTER TABLE articles ADD COLUMN search_vector tsvector;
UPDATE articles SET search_vector =
to_tsvector('english', title || ' ' || body);
CREATE INDEX idx_search ON articles USING gin(search_vector);
-- Now this is fast:
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('postgresql & performance');GIN indexes are larger than B-trees and slower to update (inserts are batched into a pending list and merged later). But for containment queries on composite types, they're the only option that performs well.
The gin_trgm_ops operator class deserves special mention — it enables efficient LIKE '%substring%' queries, which B-trees can't handle:
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_name_trgm ON users USING gin(name gin_trgm_ops);
-- Now this uses the index:
SELECT * FROM users WHERE name LIKE '%smith%';Partial Indexes
A partial index only indexes rows matching a WHERE clause. This is incredibly useful and underused:
-- Only 2% of orders are unshipped, but that's what you query
CREATE INDEX idx_unshipped ON orders(created_at)
WHERE shipped_at IS NULL;
-- Only index active users (ignore 5 million deleted accounts)
CREATE INDEX idx_active_email ON users(email)
WHERE deleted_at IS NULL;Benefits: smaller index size, faster updates (fewer rows to maintain), better cache utilization. If you're indexing a column where 95% of rows have the same value and you only query the other 5%, a partial index is the right call.
Expression Indexes
You can index the result of a function or expression:
-- Case-insensitive email lookup
CREATE INDEX idx_email_lower ON users(lower(email));
-- Query must match: WHERE lower(email) = 'john@example.com'
-- Date extraction
CREATE INDEX idx_order_month ON orders(date_trunc('month', created_at));
-- Query: WHERE date_trunc('month', created_at) = '2026-09-01'
-- JSONB field extraction
CREATE INDEX idx_config_plan ON accounts((config->>'plan'));
-- Query: WHERE config->>'plan' = 'enterprise'Index Maintenance
Indexes bloat over time as rows are deleted and updated. PostgreSQL marks old index entries as dead but doesn't immediately reclaim the space. VACUUM handles this, but heavily updated tables can accumulate bloat faster than VACUUM cleans it up.
Monitor index bloat with pgstattuple:
SELECT * FROM pgstatindex('idx_orders_status');
-- Check avg_leaf_density — below 50% suggests significant bloatFor badly bloated indexes, REINDEX CONCURRENTLY (PostgreSQL 12+) rebuilds the index without blocking writes.