The inverted index

For a small table, searching with LIKE '%cache%' is a reasonable start. As documents and traffic grow, repeatedly inspecting text becomes expensive, and substring matching still doesn't tell you which result is most useful. A search index changes the direction of the lookup: start with a word, then find the documents containing it.

Why a scan stops working

A normal B-tree orders whole field values. It can often help an equality or suitably indexed prefix lookup, but a leading wildcard doesn't tell it where to start. A match could be anywhere inside any value, so the engine usually scans many rows or index entries.

At 10 million documents averaging 2 KB, a naive full pass considers roughly 20 GB of text per query. That's an illustration of the work, not a latency prediction; caching the text doesn't remove the repeated comparisons. PostgreSQL full-text search or a specialized trigram index can be a good next step without adding a separate search cluster.

Turn text into terms

An analyzer converts a field's text into searchable terms. A simple English pipeline might:

These are product choices, not universal improvements. Stemming can merge meanings you wanted to keep separate, and English tokenization won't suit every language. Use exact keyword fields for ids, tags and case-sensitive codes rather than running every field through a prose analyzer.

A tiny worked index

Suppose the corpus is three short documents. For this example, only lowercase and split words; don't stem or remove words.

Reverse those rows to build posting lists:

An AND query for fast cache intersects the two lists and returns D1. An OR query takes their union and returns D1, D2 and D3; ranking then decides their order. Sorted document ids let the engine merge lists efficiently, starting with selective terms.

More than document ids

Posting lists can also keep term frequency and token positions. Frequency helps ranking. Positions let a phrase query distinguish "cache reads" from two words appearing far apart. Storing offsets supports highlighting the matched text in results.

That extra information costs storage and indexing work. Index only the fields you need, choose analyzers per field, and keep the original record in the source database. An index is a different representation of that record, built to answer a particular kind of query.