Indexes are the single highest-leverage performance tool in a relational database. A well-placed index turns a 30-second query into 3 milliseconds. A poorly thought-out index collection silently tanks write throughput and eats disk. This is the 2026 practical guide — no theory, just what to do and why.
What changed in 2026
- BRIN and bloom indexes saw wider adoption for append-only time-series tables in Postgres 17+.
- pgvector HNSW indexes are now the standard path for vector similarity search within Postgres, replacing IVFFlat for most use cases.
pg_stat_statements and auto_explain are enabled by default on major managed providers (Neon, Supabase, RDS), making index diagnosis easier.- Invisible indexes (MySQL 8.0+, MariaDB) let you test index removal without dropping, reducing risk in production.
Index types at a glance
| Index type |
Best for |
Not good for |
| B-tree (default) |
Equality, range, ORDER BY |
Full-text, JSONB containment |
| Composite B-tree |
Multi-column filtering |
Queries skipping the leftmost column |
| Partial B-tree |
Filtered subsets (active rows) |
Full-table scans |
| Covering (INCLUDE) |
Index-only scans |
Wide rows with frequent updates |
| GIN |
Full-text search, JSONB, arrays |
Numerical range queries |
| GiST |
Geometry, range types |
Simple equality |
| HNSW (pgvector) |
ANN vector search |
Exact nearest-neighbor at huge scale |
B-tree: the workhorse
-- Single-column index for equality / range
CREATE INDEX idx_orders_user_id ON orders (user_id);
-- Composite: leftmost first by selectivity
CREATE INDEX idx_orders_status_created
ON orders (status, created_at DESC);
-- Supports: WHERE status = 'pending' ORDER BY created_at DESC
-- Does NOT use: WHERE created_at > '2026-01-01' (misses left prefix)
The left-prefix rule is the most violated index principle. If you have (a, b, c), queries filtering only on b or c cannot use this index.
Partial indexes: surgical precision
-- Only index rows that are 'pending' — the set you actually query
CREATE INDEX idx_orders_pending
ON orders (created_at)
WHERE status = 'pending';
A partial index is often 10–100× smaller than a full index. Smaller index = fits in cache = faster scans. Use whenever you have a high-cardinality column you always filter alongside.
GIN for JSONB and full-text
-- JSONB containment queries
CREATE INDEX idx_events_meta ON events USING GIN (metadata);
-- Now this is fast:
SELECT * FROM events WHERE metadata @> '{"type": "click"}';
-- Full-text search
CREATE INDEX idx_posts_search ON posts
USING GIN (to_tsvector('english', title || ' ' || body));
Without a GIN index, every JSONB @> operator triggers a sequential scan of every row.
Covering indexes (INCLUDE)
CREATE INDEX idx_orders_user_covering
ON orders (user_id)
INCLUDE (total, created_at);
-- Allows index-only scan for:
SELECT user_id, total, created_at FROM orders WHERE user_id = 42;
The INCLUDE columns are stored in the leaf pages but not in the B-tree key. Queries that touch only those columns never have to visit the heap.
How to pick the right index
- Run
EXPLAIN (ANALYZE, BUFFERS) on the slow query. Look for Seq Scan on large tables — that is where you need an index.
- Check cardinality. Low-cardinality columns (boolean, small enum) may not benefit from an index; the planner may prefer a seq scan under 5–10% selectivity.
- Start with the WHERE clause columns, then cover ORDER BY, then add INCLUDE for SELECT columns.
- Use partial indexes when you always filter on a specific value alongside the indexed column.
- Use
pg_stat_user_indexes to find indexes with zero scans — those are candidates for removal.
Common mistakes
Over-indexing. Every index slows down INSERT/UPDATE/DELETE because all indexes must be maintained. A table with 20 indexes on every column will write at a fraction of its potential speed.
Wrong column order in composite indexes. Always put equality-filter columns first, range-filter columns last, and ORDER BY columns at the end.
Indexing a nullable column without accounting for NULLs. Postgres B-tree stores NULLs but they cannot satisfy IS NOT NULL bounds scans efficiently. Use a partial index WHERE col IS NOT NULL if you never query NULLs.
Not monitoring index bloat. Dead tuples accumulate in MVCC databases. Run VACUUM or check pgstattuple regularly; bloated indexes are slow even if they exist.
Ignoring write amplification. On write-heavy tables (>10k inserts/s) every extra index costs real throughput. Profile writes with and without the index before adding.
What to skip
- Indexing every foreign key blindly. Foreign keys on tiny lookup tables (10 rows) need no index; the planner will prefer a seq scan.
- GiST for non-spatial data. Unless you have geometry or range types, B-tree is faster and simpler.
- Index hints in ORMs. If you need to force an index hint, the query or schema is wrong. Fix the root cause.
FAQ
How do I know if an index is being used?
Run EXPLAIN (ANALYZE) and look for Index Scan or Index Only Scan nodes. Also query pg_stat_user_indexes for idx_scan counts in production.
Can I add an index without locking the table?
Yes: CREATE INDEX CONCURRENTLY in Postgres builds the index without an exclusive lock, though it takes longer. Always use this in production.
How many indexes is too many?
There is no universal number. A read-heavy reporting table can support 15 indexes; a high-throughput event log should have 2–3. Measure write latency under load.
Should I index UUID primary keys?
Yes, but use UUIDv7 (time-ordered) instead of UUIDv4. Random UUIDs cause index fragmentation and page splits because inserts land in random B-tree positions.
Where to go next