When a query begins taking several seconds instead of a few milliseconds, one of the first things an engineer examines is whether the database can find the requested rows efficiently. A database index is an auxiliary data structure that helps narrow a search. The familiar analogy is a book’s index: an entry points the reader toward particular pages rather than requiring a search through every sentence.
The analogy has a limit. A printed index is created once, while a database index often has to be maintained whenever records are inserted, changed, or deleted. That maintenance uses processing time and storage. Index design therefore involves a negotiation between faster reads and the cost of keeping the index current.
How an index changes a search
Imagine an online store with millions of orders. A query asks for every order associated with a particular customer identifier. Without an appropriate index, the database may need to scan many rows to find matches. An index on the customer identifier can help it locate those rows more directly. Whether it uses the index depends on factors such as the query, the distribution of values, and the estimated cost of alternative plans.
Many relational databases support B-tree indexes for equality and range comparisons. PostgreSQL also documents other index types, including GIN and GiST, suited to different classes of operations. The index that helps locate an order by number is not automatically the same index that helps search within a document or compare geographic locations.
The table still holds the underlying records. Depending on the query and index design, an engine may use an index to locate rows or sometimes answer a query directly from indexed data. The database optimizer decides whether using an index is worthwhile. For a query that needs most rows, scanning a table may be faster.
Why more indexes are not always better
Suppose the store adds indexes on customer ID, shipping status, order date, payment status, and combinations of those fields. Some queries become faster. But every new order or status change now requires extra index work. Storage grows, and maintenance can become more demanding. Creating indexes on large production tables can itself require careful operational planning.
Indexes are valuable when their benefits exceed those costs. An index on a field that almost every query ignores may do little good. An index on a field where nearly every row has the same value may not offer the selectivity needed for a particular filter. The database query planner can prefer a table scan when reading much of the table is cheaper than following index entries.
How to decide what to index
Begin with a real slow query, not a hypothetical one. Use the database’s query-plan tools to inspect how it executes that query. In PostgreSQL, EXPLAIN and related facilities can show whether indexes are used and where execution time is spent. Check representative data volumes and the effect on writes. Revisit the results after the data grows, because a strategy that helped a small table may be inappropriate later.
A multi-column index can help some combinations of filters and sort orders, but column order matters for many access patterns. There is no universal index configuration for “all searches.” Database indexing is closer to designing a useful library catalog than choosing the largest catalog possible.
How ordinary indexes differ from vector indexes
A conventional index often helps identify records that match exact values or ordered ranges. A vector index helps find nearby points in a numerical representation space; some strategies trade a measure of exactness for faster retrieval. Neither form automatically solves the other’s task. A company search service may need both exact filters for customer permissions and similarity ranking for document passages.
We explain the numerical representations behind that second approach in vector embeddings, then examine the operational choices in choosing a vector database. An index is a way to organize the work of finding data; judging its usefulness still requires watching real queries run.