Database Index

A database index is a structure that the database maintains alongside a table to accelerate lookups. Instead of scanning every row, the engine consults the index to jump directly to matching rows, much like the index at the back of a book.

How It Works

The most common index is the B-tree (or B+tree), which keeps keys sorted and supports equality and range queries in logarithmic time. Other types serve specialized needs:

• Hash indexes for fast exact-match lookups.
• Bitmap indexes for low-cardinality columns in analytics.
• Inverted indexes for full-text search.
• GiST, GIN, and spatial indexes for geometric and document data.
• Vector indexes (HNSW, IVF) for similarity search in vector databases.

Indexes can be single-column, composite (multi-column), unique, partial, or covering (containing all columns a query needs). The query planner decides whether using an index is cheaper than a full scan based on statistics.

Why It Matters

Indexes are the single most important lever for query performance. A well-chosen index can turn a multi-second scan into a sub-millisecond lookup.

They are not free. Every index consumes storage and must be updated on every insert, update, or delete, slowing writes. Too many indexes waste resources; too few cause slow queries. Effective indexing matches indexes to actual query patterns and removes unused ones.

Related Terms

Primary keys are automatically indexed; indexes complement partitioning and are influenced by normalization choices.