Database Indexes: How They Work and What Types Exist

You've seen that indexes make queries faster — the more useful question is why some indexes are used and others are ignored, and why adding too many actually hurts performance.

B-tree — the default for almost everything

• A B-tree index keeps values in sorted order across balanced tree nodes, which means it handles equality (=), ranges (BETWEEN, >, <), prefix matching (LIKE 'foo%'), and ORDER BY all from one structure — O(log n) traversal instead of a full table scan
• This is what PostgreSQL and MySQL InnoDB create when you run CREATE INDEX without specifying a type

Composite indexes — column order is the critical decision

• An index on (customer_id, status) sorts first by customer_id, then by status within each customer — which means it serves WHERE customer_id = ? or WHERE customer_id = ? AND status = ?, but not WHERE status = ? alone, because status has no starting sort position without customer_id
• Put the most selective equality column first; range columns at the end

Covering indexes — zero heap fetches

• When the index contains every column a query needs, the database answers it entirely from the index without touching the table — called an index-only scan, which eliminates the most expensive I/O step for hot read queries
• Add non-key columns with INCLUDE (col) in PostgreSQL to make any index covering without affecting the sort key

Partial indexes — index only the rows you actually query

• A partial index carries a WHERE clause — CREATE INDEX ON orders(user_id) WHERE deleted_at IS NULL — which means it only indexes active rows, so it stays small and precisely aligned with the query pattern instead of growing with every soft-deleted record

Every index is a write tax — inserts and updates must maintain every index on the table. Run EXPLAIN ANALYZE before and after; never add an index without confirming it changes the plan.