Beyond Primary Keys: Secondary Indexes, Full-Text Search, Geospatial Queries, and Vector Embeddings

Data Systems

Many engineering discussions about databases are still framed as if indexing means one thing: add a B-tree on a column and move on.

That is not how real products work.

A login flow needs lookup by email. A CRM needs search by company name. A logistics app needs map bounds queries. A knowledge base needs full-text search. A support assistant needs semantic retrieval over documents that do not contain the exact same words as the query.

Those are all indexing problems. They are just not the same indexing problem.

This article walks through that progression: secondary indexes, clustered versus heap storage, covering indexes, multicolumn and multidimensional indexes, full-text search, and vector embeddings with approximate nearest-neighbor indexes. The common theme is simple: the right index depends on the shape of the question.

For frontend and JavaScript engineers, this matters because search UX, filter latency, autocomplete quality, map interactions, and semantic retrieval all inherit the constraints of the index underneath them.

A Primary Key Solves Only One Kind of Lookup

The key in a key-value store or primary-key index answers one specific question efficiently: find the record for this exact key.

That is essential, but it is rarely enough.

Products quickly accumulate queries such as:

  • find the user by username,
  • list all orders for this customer,
  • return all posts tagged with this topic,
  • search for all documents containing these words,
  • show every restaurant inside the visible map region,
  • retrieve articles semantically similar to this prompt.

Each new access pattern is a request for an additional structure derived from the primary data.

That is what an index really is: a separate representation built to make one family of reads cheap.

Secondary Indexes Duplicate Access Paths on Purpose

A secondary index lets the engine find records by some attribute other than the primary key.

This is conceptually simple but architecturally important. The database is no longer storing the data once. It is storing the data plus extra lookup structures derived from it.

That creates a recurring tradeoff:

  • reads become faster and easier,
  • writes become more expensive because every index must also be maintained.

This is one reason indexing decisions matter so much in product systems. Adding an index often feels like a read-side improvement, but it also changes write cost, disk usage, and recovery behavior.

The main question is not whether indexes are good. It is whether a particular query pattern is important enough to deserve its own maintained structure.

Where the Value Lives Matters: Clustered, Heap, and Covering Designs

Indexes do not all store the same thing.

Sometimes the actual row data is stored directly within the primary index structure. That is usually called a clustered index. The physical layout of the data follows the primary key ordering.

Sometimes the index stores a key plus a reference to the row's location elsewhere. That separate storage area is often called a heap file.

There is also a middle ground: a covering index or an index with included columns. In that design, the index stores enough extra columns to answer some queries without going back to the main row storage.

This is more than a storage detail.

If the index already contains all fields needed for a list page or admin table, the query can be answered from the index alone. If not, the engine may need a second lookup for each result row.

That is directly relevant to product endpoints that need to return small summaries quickly. A well-chosen covering index can turn a page of tiny random lookups into a single efficient structure scan.

Multicolumn Indexes Help, but Column Order Still Matters

Real queries often filter on more than one attribute.

One common answer is a multicolumn or concatenated index, where multiple fields are combined into one sorted index key.

This works well when the query patterns respect the index order.

For example, an index on (last_name, first_name) is useful if you often search by last name, or by full last name plus first name combination. It is much less useful if you mostly search by first name alone.

This is one of the most common sources of confusion in index design. Engineers add the right columns but in the wrong order, then wonder why the query planner still has to work hard.

The general rule is straightforward: concatenated indexes are strongest when the query matches the leading part of the key.

That is powerful, but it still assumes the world can be projected onto one ordered dimension cleanly.

Some Queries Are Genuinely Multidimensional

Not every question fits one ordering.

Suppose a restaurant app needs to find all places within the current map view:

SELECT *
FROM restaurants
WHERE latitude > 51.4946 AND latitude < 51.5079
  AND longitude > -0.1162 AND longitude < -0.1004

That query is not really about one value. It is about a rectangle in two-dimensional space.

A standard concatenated index on (latitude, longitude) cannot answer that especially well because it groups by one dimension first and only partially helps with the other.

This is why multidimensional index structures exist. Systems use structures such as R-trees or related spatial partitioning schemes to group nearby points together and narrow the search space in more than one dimension at once.

The same idea applies beyond maps.

Any query that filters simultaneously on several dimensions, such as time plus temperature or color ranges across RGB channels, may need a structure that respects multi-axis locality rather than one simple sorted order.

Full-Text Search Uses an Inverted Index, Not Row Scans

Text search is another place where ordinary secondary indexes stop being enough.

If a user types a word and expects documents containing that word to appear instantly, scanning all documents is not an option. Instead, search engines use an inverted index.

An inverted index maps:

  • from a term,
  • to the list of documents that contain that term.

This is the reverse of how the documents are stored naturally. A document normally contains many words. The index reorganizes the corpus around each word instead.

That makes queries like "find documents containing both x and y" efficient because the engine can intersect the postings lists for those terms.

The connection to the earlier chapter material is elegant: this is still the same indexing move. The engine builds a separate structure that makes one query shape cheap.

Text Search Gets Complicated Fast: Tokens, Typos, and Substrings

Once you move beyond exact word matching, search gets trickier.

Several complications show up immediately:

  • some languages do not use spaces cleanly between words,
  • users make typos,
  • you may need substring matching,
  • morphology and synonyms matter,
  • ranking matters, not only retrieval.

One fallback technique is to index n-grams, such as trigrams. That supports substring-style matching because the query can be broken into overlapping chunks and matched against documents that contain those chunks.

Another technique is to support approximate string matching through structures that can search terms within a given edit distance.

The important architectural point is not memorizing every search algorithm. It is recognizing that full-text search is not an ordinary B-tree lookup with longer strings. It is a different retrieval problem with different data structures.

Vector Embeddings Change the Meaning of Match

Full-text search is great when the right keywords appear in the text.

Semantic search wants something harder.

Sometimes users search with different words than the document contains. A page about canceling a subscription might be relevant to a query about terminating a contract even if the wording does not overlap cleanly.

This is where vector embeddings enter the picture.

An embedding model converts text, images, audio, or other content into a high-dimensional vector of floating-point numbers. Semantically similar items tend to land near one another in that vector space.

A semantic search engine then:

  1. embeds the query,
  2. compares it with stored document embeddings,
  3. returns the nearest vectors.

That changes the retrieval problem completely. The system is no longer answering "which documents contain this token?" It is answering "which stored vectors are closest to this query vector under some distance metric?"

Exact Vector Search Is Simple and Slow

The most straightforward vector index is a flat index.

Every document vector is stored directly, and the query computes distance against all of them.

This is exact and conceptually simple. It is also expensive once the collection grows because every query compares against every vector.

For small datasets or offline analysis, that may be acceptable. For large interactive systems, it often is not.

That is why vector search usually introduces approximate indexes.

IVF and HNSW Trade Exactness for Speed

Two of the most important approximate vector indexing approaches are IVF and HNSW.

An inverted file (IVF) index clusters vectors into partitions. At query time, the engine searches only a subset of those partitions instead of the entire collection. More probes usually improve recall but cost more time.

An HNSW index organizes vectors into a layered graph. The query navigates that graph to move progressively closer to the target vector. It is approximate too, but often extremely effective for interactive nearest-neighbor search.

These structures matter because vector search is not a conventional database index bolted onto AI terminology. It is its own retrieval space with its own recall-latency tradeoffs.

This is also one place where product expectations need careful handling. Approximate search is fast because it is not guaranteed to examine every candidate. That is often the right trade, but teams should be honest about it.

Search Quality Is Also a Data Governance Problem

Indexes are not only performance features. They shape what a product can find and surface.

That has governance implications.

If personal or sensitive data is indexed for text or vector retrieval, it may become discoverable in ways the original row-oriented view did not make obvious. Deletion workflows may also need to update several derived structures, not just one source table.

This is especially important in systems that combine:

  • raw source documents,
  • extracted keywords,
  • search indexes,
  • vector embeddings,
  • cached retrieval layers.

From a compliance perspective, derived access paths are still access paths.

What Frontend and JavaScript Engineers Should Look For

You do not need to implement HNSW by hand to benefit from understanding it.

These indexing choices show up in user-facing behavior constantly:

  • autocomplete relevance,
  • map-pan responsiveness,
  • filter combinations that suddenly become slow,
  • search results that feel exact versus fuzzy,
  • semantic retrieval that feels helpful versus confusing,
  • admin tables that page quickly or painfully.

They also affect API design. If the product needs rich multi-filter search, proximity ranking, or semantic retrieval, the backend may need separate systems or projections rather than one database table doing everything.

That is not overengineering. It is the natural consequence of supporting several incompatible query shapes well.

Conclusion

A primary key solves only one retrieval problem. Real products need many more.

Secondary indexes create alternate lookup paths. Clustered, heap, and covering designs change how much data the engine can answer directly from the index. Concatenated indexes help when query order matches key order. Multidimensional indexes support map-like and range-in-several-dimensions queries. Inverted indexes power full-text retrieval. Vector embeddings and approximate indexes make semantic search possible when exact keywords are not enough.

The deeper lesson is consistent across all of them: query shape drives index shape. Once you accept that, the explosion of different indexing techniques stops feeling messy. It starts looking like a series of specialized answers to different questions users keep asking.