A database index is a data structure the database maintains alongside a table so it can find rows matching a condition without scanning the whole table. It works like a book’s index: rather than reading every page to find one topic, you look up the topic in a sorted list that points you to the right pages.
B-trees and what they’re good at
The workhorse structure is the B-tree: a shallow, sorted tree that lets the database jump to a value in a handful of steps, whether the table has ten thousand rows or a billion. Because a B-tree keeps values in order, it accelerates more than exact matches — range conditions (WHERE order_date >= '2026-01-01'), sorting, and joins on the indexed column all benefit. Creating one is a single statement:
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
Now WHERE customer_id = 4589 reads a few index pages instead of every order ever placed. Primary keys get an index automatically, since the database needs one to enforce uniqueness; foreign key columns usually deserve one too, because they’re what joins run on, though most databases don’t add it for you. Most databases also offer specialized index types beyond B-trees — for text search, geospatial data, or JSON — but B-tree is the default and covers the large majority of cases.
When indexes help, and when they hurt
An index pays off when queries are selective — when the condition narrows a big table down to a small fraction of its rows. That’s the everyday shape of transactional workloads: fetch this customer, this order, this session. The same index does little for a query that touches most of the table anyway; the query planner will rightly ignore it and scan.
Indexes aren’t free. Every INSERT, UPDATE, and DELETE must also update every index on the table, so each index taxes write throughput and takes disk space. A table with a dozen speculative indexes is slower to write to, and the unused ones are pure overhead — which is why database monitoring usually includes watching for indexes that never get used.
It’s also why analytical databases mostly skip them. Columnar warehouses like BigQuery and Snowflake don’t offer traditional B-tree indexes at all; their queries scan large fractions of a column by design, and they lean on columnar storage, compression, and partition pruning instead. Indexing strategy is chiefly a concern when you run analytics against a row-oriented database like PostgreSQL or MySQL.
Indexes and Metabase
When a Metabase dashboard is slow, the queries behind its cards are the place to look, and missing indexes are a frequent culprit — especially on columns that dashboard filters and joins hit repeatedly. If every card filters orders by created_at and joins on customer_id, indexing those two columns can turn a ten-second load into a sub-second one. Metabase can’t add indexes for you (it only reads), so the fix happens in the database — but the query patterns your dashboards generate tell you exactly which columns deserve one.
Related terms
Put it to work
- PostgreSQL monitoring dashboard — Dashboard
- Cache hit rate — Metric
- Queue time — Metric
