Indexes are the single highest-leverage performance tool in a relational database, and they're also the most commonly misused. Add too few and queries crawl. Add too many and writes stall, storage balloons, and the query planner makes bad choices. The 2026 guide to getting this right follows.
What changed in 2026
- Postgres 16's parallel index builds cut the time to add a large index on live tables significantly —
CREATE INDEX CONCURRENTLY is now even less disruptive.
- Vector indexes (HNSW via pgvector 0.7+) became a mainstream concern as teams added semantic search to relational workloads. The principles of index selectivity still apply.
- DuckDB's ART (Adaptive Radix Tree) indexes are now the reference for analytical query engines, distinct from OLTP B-tree assumptions.
- MySQL 8.4 improved invisible indexes — you can mark an index invisible and watch query plans change before actually dropping it.
How a B-tree index works
A B-tree index is a balanced tree where every leaf node holds a sorted list of column values plus pointers to heap rows. A full table scan touches every page (O(n) I/O); an index scan walks the tree height (typically 3–4 levels for millions of rows), finds the matching leaf, and follows pointers — O(log n).
The catch: those heap pointer follows are random I/O. For queries returning >5–15% of rows, the planner often chooses a sequential scan anyway. That's correct behaviour.
Index types and when to use each
| Type |
Best for |
Notes |
| B-tree |
Equality, range, ORDER BY |
Default; use unless you have a reason not to |
| GIN |
Arrays, full-text search, JSONB keys |
Slower writes, big win for containment queries |
| GiST |
Geometric, range types, nearest-neighbour |
Lossy or exact depending on operator class |
| BRIN |
Huge append-only tables (logs, events) |
Tiny index, only useful when data is physically ordered |
| Hash |
Pure equality (=), no range |
Rarely worth it over B-tree in Postgres |
| HNSW (pgvector) |
Vector similarity search |
Tune m and ef_construction for recall vs speed |
Composite indexes: column order rules
-- Query: WHERE status = 'active' AND created_at > '2026-01-01'
-- Correct order: equality column first, range column second
CREATE INDEX idx_orders_status_created
ON orders (status, created_at);
The rule: equality predicates first, then range predicates. The index can scan a contiguous range only for columns up to and including the first range predicate. Columns after a range predicate in the WHERE clause won't benefit from the composite index.
-- Wrong order for the query above:
CREATE INDEX idx_wrong ON orders (created_at, status);
-- This forces a range scan on created_at and can't use status from the index directly.
Covering indexes
A covering (or "index-only") index includes all columns the query needs, eliminating the heap fetch entirely.
-- Query: SELECT email FROM users WHERE tenant_id = 42 ORDER BY created_at
CREATE INDEX idx_users_tenant_email ON users (tenant_id, created_at) INCLUDE (email);
The INCLUDE clause (Postgres 11+, SQL Server) adds columns to the leaf level without being part of the sort key. This avoids a heap fetch but keeps the index smaller than putting email in the key.
How to find missing indexes
-- Postgres: queries doing sequential scans on large tables
SELECT schemaname, tablename, seq_scan, seq_tup_read,
idx_scan, idx_tup_fetch
FROM pg_stat_user_tables
WHERE seq_scan > 100
ORDER BY seq_tup_read DESC;
Then run EXPLAIN (ANALYZE, BUFFERS) on the slow queries. Look for Seq Scan on tables with many rows; look for Buffers: shared hit=...read= — reads are I/O, hits are cache.
How to pick
- Start from slow queries, not schema. Profile first (
pg_stat_statements, slow query log).
- Index columns in WHERE and JOIN ON. Foreign key columns are almost always worth indexing.
- Use composite indexes to cover common query patterns — don't create three single-column indexes when one composite handles all three predicates.
- Add INCLUDE columns for high-frequency read paths to go index-only.
- Set up
pg_stat_user_indexes monitoring to find unused indexes and drop them.
Common mistakes
Indexing low-cardinality columns alone. A B-tree on a boolean is_deleted column with 99% false rows is nearly useless — the planner skips it. Combine with a high-cardinality column or use a partial index.
-- Partial index: only index active rows
CREATE INDEX idx_orders_active ON orders (customer_id) WHERE status = 'active';
Not accounting for write amplification. Every INSERT, UPDATE on an indexed column, and DELETE must update all relevant indexes. A table with 12 indexes has 12× the write overhead.
Index bloat. Dead tuples from UPDATEs leave bloat in indexes. Run REINDEX CONCURRENTLY or use pg_repack periodically on high-churn tables.
Indexing JSONB columns entirely. A GIN index on a full JSONB column is large and slow to update. Index specific paths using a computed/generated column instead.
What to skip
- Indexes on every column "just in case" — unused indexes consume storage and slow every write.
- Hash indexes for range queries — they can't do it.
- Manual index hints in Postgres — the planner is good; if it's making bad choices, fix statistics or table bloat first.
FAQ
How do I know if my index is being used?
SELECT * FROM pg_stat_user_indexes WHERE relname = 'your_table' — check idx_scan. Zero or very low after significant traffic means the index is unused.
Does adding an index lock the table?
CREATE INDEX CONCURRENTLY (Postgres) does not take a full table lock and is safe in production, though it takes longer and can fail if a concurrent transaction aborts — check for invalids with \d tablename afterward.
Should I index foreign keys?
Yes, almost always. Without an index on the FK column, a DELETE on the parent table triggers a sequential scan of the child table to enforce the constraint.
What is index selectivity?
Selectivity is the fraction of rows an index predicate eliminates. High selectivity (few matching rows, like a UUID) makes an index very useful. Low selectivity (like a gender column) makes it nearly worthless for reads.
Where to go next
See SQL window functions in 2026 for writing queries that get the most from your indexes, and Caching strategies in 2026 for when to move hot query results out of the database entirely.