Database Indexing Strategies

Indexes are the single most impactful tool for improving query performance. An unindexed table scan reads every row; an indexed lookup jumps directly to the matching rows. Understanding the trade-offs helps you index smarter, not more.

B-Tree Indexes

The default index type in most relational databases is the B-tree. It stores keys in a balanced tree structure, enabling equality lookups, range scans, and ordered retrieval. B-tree indexes work well for columns with high cardinality.

When to Avoid B-Trees

B-tree indexes are not ideal for columns with very low cardinality (e.g., a boolean flag) because the index may be ignored in favour of a full table scan by the query planner.

Composite Indexes

A composite index covers multiple columns. The order of columns matters: the index can be used for queries that filter on the leftmost columns first. Choose column order based on the most common query patterns.

Partial Indexes

Partial indexes only include rows matching a condition, making them smaller and faster. For example, an index on WHERE deleted_at IS NULL covers only active records and is far more efficient for soft-delete patterns.