Backend6 min read

Database Indexing

Master database indexing - B-tree indexes, composite index column ordering, covering indexes, partial indexes, and index maintenance.

DatabaseIndexingPerformanceQuery OptimizationB-tree

skill: Database Indexing category: Backend tags: [Database, Indexing, Performance, Query Optimization, B-tree] readingTime: 6

Understanding indexes is the single most impactful database performance skill. Indexes trade write speed (maintain index on insert/update) for read speed. The golden rule: index columns you filter, sort, or join on - in that order. Over-indexing hurts write performance and wastes disk.

Workflow

Understand B-tree structureB-tree indexes store values in sorted order, enabling range queries and equality lookups in O(log n). Understanding the structure explains why column order in composite indexes matters.
Design composite indexes correctlyComposite index column order is critical. The leftmost prefix rule: a composite index (a, b, c) can serve queries on (a), (a, b), (a, b, c) - but not (b) or (c) alone.
Use covering indexesA covering index includes all columns needed by a query - the database reads the index without touching the table (heap). This is the fastest possible read path.
Use partial indexes for sparse conditionsPartial indexes (PostgreSQL) index only rows matching a WHERE clause - smaller, faster, and more selective than full-column indexes for filtered queries.
Monitor and maintain indexesUnused indexes waste write performance and disk space. Bloated indexes (from many updates/deletes) slow reads. Monitor and clean up regularly.

Output Format

Indexing: B-tree covering equality + range + sort, composite left-prefix ordering, covering INCLUDE indexes, partial WHERE indexes, and index usage monitoring.

Composite: (equality_col, range_col, sort_col)
Covering: INCLUDE (selected_cols)
Partial: WHERE status = 'active'
Monitor: pg_stat_user_indexes idx_scan < 50
Maintain: REINDEX CONCURRENTLY + ANALYZE

Examples

Input

Output

Edge Cases & Gotchas

Create index CONCURRENTLY - CREATE INDEX (without CONCURRENTLY) locks the table for writes during build. Always use CREATE INDEX CONCURRENTLY in PostgreSQL production - it takes longer but doesn't block.
NULL values in indexes - PostgreSQL stores NULLs in B-tree indexes; MySQL may not use an index for IS NULL queries. Test with EXPLAIN if querying NULL values.
Index cardinality - Low-cardinality columns (boolean, status with 3 values) make poor standalone indexes. The optimizer often prefers a full table scan for < 20% selectivity.
Foreign key indexes - MySQL requires an index on foreign key columns. PostgreSQL does not - but you should add them manually since FK lookups (ON DELETE CASCADE) scan without them.

When to Break These Rules

Use a full-text search engine (Elasticsearch, Typesense) instead of database indexes for complex text search - databases are poor at relevance ranking and fuzzy matching.
Use columnar storage (Redshift, BigQuery, DuckDB) instead of row-store indexes for analytical queries over billions of rows - different data layout, different optimization rules.

Reference Files

https://use-the-index-luke.com/
https://www.postgresql.org/docs/current/indexes.html