A database index is an extra data structure that helps a database find rows without scanning an entire table. The tradeoff is important: an index can dramatically reduce read work, but it consumes memory and storage and adds work to inserts, updates, and deletes.
Table scan vs indexed lookup
Imagine a users table with ten million rows and a query filtering by email. Without an appropriate index, the database may inspect row after row. An index lets it navigate a smaller structure to locate matching keys and then fetch the required table rows.
Why B-trees are so common
B-trees keep keys ordered while keeping the tree relatively shallow. Each node can contain many keys, which reduces the number of levels that must be visited. Database pages map naturally onto this structure, making B-trees a practical choice for equality, range, and ordered queries.
Equality and range queries
An index on created_at can help answer queries such as “show orders created after Tuesday” because the keys are ordered. This is one reason indexes are useful for sorting, range predicates, and prefix-compatible access patterns.
Composite indexes
A composite index stores multiple columns in a defined order. For a query likeWHERE tenant_id = ? AND created_at > ?, an index beginning withtenant_id, created_at can be a strong fit. Column order matters because the database navigates the index from the leading columns.
Covering indexes
Sometimes the index contains every column needed by a query. The database can then answer the request using the index alone, avoiding additional table lookups. This can reduce random I/O, but wider indexes cost more storage and make writes more expensive.
When an index hurts
- Every additional index adds write and maintenance cost.
- Low-selectivity columns may not benefit much from a traditional index.
- Large or unused indexes consume storage and memory.
- An index that does not match the query shape can be effectively ignored.
Use the query planner
Do not choose indexes by intuition alone. PostgreSQL, MySQL, and other systems expose query plans. In PostgreSQL, EXPLAIN (ANALYZE, BUFFERS) can reveal whether an index was used and where the query spent time.
CREATE INDEX idx_orders_tenant_created
ON orders (tenant_id, created_at);
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total
FROM orders
WHERE tenant_id = 42
AND created_at >= '2026-09-01';A useful mental model
Think of an index as a library catalog. The catalog costs space and has to be updated when books move, but it saves you from walking every shelf. Good database design balances lookup speed, write cost, memory, and the workload's real query patterns.
For the next layer of performance, connect indexes withcaching strategiesanddistributed consistency.