Choose an index family
A query filters by some key: an ID, a date range, words in a description, a location, similarity to an embedding. Which kind of index can help is decided by the shape of the predicate, not by preference, and some shapes cannot be indexed exactly at all. Which family does your predicate need?
You will end with the index family the predicate needs, or the decision that none is needed, plus the checks that keep it correct.
Before you choose
Write down the predicate's shape first
Before naming any structure, write what the query asks: equals a value; falls in a range or starts with a prefix; contains these words (or tags); lies within or overlaps an area; is nearest to a point. The shape decides the family.
Decide
Does the physical order of rows already follow the key?
Usually time: an append-only table whose rows arrive in timestamp order.
Why
If the key is correlated with where rows sit, per-block min/max statistics already skip most blocks, at a tiny fraction of an index's size and with no write cost. Do not buy an order you already own.
What shape is the predicate?
EqualsHash or ordered index
Range or prefixOrdered index (B-tree)
Contains termsInverted index
Area, overlapSpatial index
Nearest, many dimsApproximate nearest-neighbour index
Why
Each shape needs a different property preserved: equality needs only a stable hash; ranges need order; "contains" needs a list per term; areas need proximity in two or three dimensions; similarity in hundreds of dimensions cannot be pruned exactly at all. A shape the index does not preserve cannot be answered by it at any price: a hash can never serve a range.
Where you can land
No index: let block statistics skip data
Use it when the key is correlated with physical row order.
What it costs: nothing, as long as the correlation holds. A batch of late-arriving rows breaks it, and the blocks' ranges start to overlap; re-sort or load late data separately.
Hash or ordered index
Use it when the query asks for exact values.
A hash gives one lookup; an ordered index also serves equality, and ranges later. What it costs: a hash gives up order and ranges for good.
Ordered index (B-tree)
Use it when the query asks for ranges, prefixes or the top N in order.
One contiguous run per query. What it costs: one order per index.
Before you build it
Make the key encoding preserve order
Ranges need keys whose byte order matches value order, and computed keys need functions that are deterministic and stable across versions. This is where silent wrong answers enter.
Inverted index
Use it when the query asks which records contain given words, tags or values.
A list of matching records per term; a query intersects the lists.
Before you build it
Plan the intersection, not the single lookups
Start from the shortest list. Each term alone may match too much to be useful; together they can be very selective. A query made only of very common words gets no pruning.
What it costs: lists cannot be updated in place, so they live in immutable segments that must be merged.
Spatial index
Use it when the query asks for points within an area, overlapping shapes, or nearest points in two or three dimensions.
Either map space onto cells along a space-filling curve and index the cell IDs, or partition space into regions (R-trees).
What it costs: a region query becomes many ranges, growing with the perimeter of the searched area, plus false positives to check exactly. Cell size is the dial: smaller cells mean more seeks, larger ones more false positives.
Approximate nearest-neighbour index
Use it when the query asks for the nearest items in high dimension (embeddings).
Exact search must look at almost everything, so every option gives up exactness: approximate the traversal (graph methods such as HNSW), the partitions searched (inverted-file methods), or the vectors themselves (quantization).
Before you build it
Check the memory cliff first
100 million 768-dimension vectors at 4 bytes each is about 300 GB; quantized to 96 bytes each, about 10 GB. Quantization is usually what makes it fit at all, not a latency tweak. Measure recall against exact results on a sample before accepting it.
Whichever you land on
Count the runs a query produces
One run: an ordered index works well. A few: fine. Many, growing with the searched area: you are in spatial territory, tuning cell size. Approaching the number of rows: stop. An index will not help; you need approximation or a different access strategy.
Price the write path
Each family has a different maintenance cost on every insert, update and delete. Price it before building: write cost is certain, read benefit only estimated.