What is Database Indexing?

What database indexing means

Database indexing is the practice of building auxiliary data structures that make reads fast. Without an index, finding the rows that match a query means a full table scan — the database reads every row and checks each one. On a small table that is fine; on a table with millions of rows it is painfully slow. An index sidesteps the scan by keeping the relevant values in a sorted, quickly searchable structure that points to where each matching row lives.

The analogy is exactly the index of a book. To find every mention of a topic, you do not read all 400 pages — you flip to the alphabetized index and jump to the listed pages. A database index does the same job: it answers "where are the rows where this column equals that value?" in a handful of steps rather than a linear march through the data.

Where it came from

Indexing is as old as databases themselves. As relational databases took hold in the 1970s and 1980s, the B-tree — a balanced, sorted tree structure — became the workhorse index because it handles both exact matches and range lookups efficiently while staying balanced as data changes. It remains the default index type in most relational databases today.

Since then the toolbox has grown. Hash indexes optimize pure equality lookups, specialized indexes accelerate full-text search and geographic data, and partial or expression indexes target specific query shapes. But the central trade has never changed: an index spends storage and a little write overhead to buy dramatically faster reads.

How it works

A typical B-tree index keeps the indexed column's values sorted in a tree whose depth stays shallow even as the table grows. To find a value, the database walks a few nodes from the top of the tree to a leaf and follows a pointer to the actual row. Because the keys are sorted, the same structure also answers range queries — everything between two dates, say — and can satisfy ORDER BY without a separate sort step.

The cost is paid on writes. Every insert, update, or delete that touches an indexed column must also update the index to keep it correct, so each additional index makes writes a little slower and uses more disk. That is why you do not index everything: you index the columns your queries filter on, join on, or sort by. Composite indexes cover several columns at once, and their column order determines which queries they can accelerate. To decide what to add, engineers read the database's query plan — for example via EXPLAIN — to see whether a query is scanning a table it should be looking up.

When it matters

Indexing matters the moment a table grows large enough that scanning it becomes noticeable — which happens sooner than most teams expect. A missing index on a frequently filtered column is the single most common cause of a query that was instant in development crawling in production. Indexing also matters in the other direction: a table peppered with unused indexes pays a write penalty for no read benefit. The discipline is to index for the queries you actually run, measure with the query planner, and revisit as access patterns change.

At QUANT LAB

We build on PostgreSQL, and getting the indexes right is part of how we keep the applications we ship fast as they grow. During development we read query plans on the hot paths — the dashboard queries, the search endpoints, the foreign-key joins — and add exactly the indexes those queries need, no more. For the multi-tenant SaaS products we build, indexing the tenant identifier alongside the columns each query filters on is often the difference between a snappy app and one that bogs down as accounts pile up.

Indexing is also a recurring theme in our API development work and a frequent quick win when we are brought in to fix a slow custom business application. More often than not, the worst performance complaint traces back to a single large table being scanned where one well-chosen index makes the query instant.