A database index on a text column can find rows that start with a string, but not rows that contain the words "cheap flights goa". Full-text search needs a different structure, the inverted index, which maps each word to the documents containing it. It is the core of Elasticsearch/OpenSearch, Lucene, Solr and Postgres full-text search.
In simple words
It's the index at the back of a textbook: "photosynthesis — pages 12, 45, 87". Instead of reading every page to find a word, you look up the word and jump to the pages. Search engines build that index for billions of pages.
The database stays the source of truth; the search index is a derived copy updated from change events.
Deep Dive: The Inverted Index
docs: 1 "Cheap flights to Goa"
2 "Goa beach hotels"
3 "Cheap hotels in Delhi"
analysis: lowercase, tokenize, remove stop words ("to", "in"), stem ("hotels" -> "hotel")
term -> postings list (doc ids, often with positions and frequencies)
cheap -> [1, 3]
flight -> [1]
goa -> [1, 2]
beach -> [2]
hotel -> [2, 3]
delhi -> [3]
query "cheap goa" -> AND: [1,3] ∩ [1,2] = [1]
OR: [1,2,3], ranked by relevance
Postings lists are sorted by doc id, so intersections are fast merges, and they compress well (store gaps between ids).
Deep Dive: Ranking
TF-IDF / BM25: a document scores higher if the term appears often in it (term frequency) and the term is rare across all documents (inverse document frequency), normalised for document length. BM25 is the default in Lucene-based engines.
Signals: freshness, popularity, click-through, personalisation are blended in (often in a second re-ranking stage).
Semantic search: embeddings + vector similarity catch meaning ("inexpensive" ≈ "cheap"); hybrid search combines BM25 and vectors.
Deep Dive: Search as a System
source DB --(CDC / events)--> indexer --> search cluster
index split into shards,
each shard replicated
query -> coordinator -> scatter to all shards -> each returns top K
-> gather, merge, re-rank -> top K to the user
The database stays the source of truth; the search index is a derived, eventually consistent copy updated from change events.
Indexing is batched into segments (Lucene writes immutable segments and merges them), so new documents appear after a short refresh interval (around a second by default in Elasticsearch).
Sharding by document means every query touches every shard; replicas add query throughput and availability.
Key Pointers
Inverted index: term → sorted list of documents. Built with the same text analysis applied at index and query time.
BM25 for lexical relevance; add vectors for semantic matching.
Search indexes are derived data: rebuildable from the source of truth.
Near-real-time, not instant: expect a small delay before new docs are searchable.
How It Works
Capture changes from the primary database (CDC or domain events) into a queue.
Indexers analyse text (tokenize, normalise, stem) and write documents to the right shard.
At query time, analyse the query the same way, look up postings, intersect/union, score with BM25 and signals.
Merge per-shard top results, re-rank, hydrate display data, return with pagination.
Follow-up Questions
Why not LIKE '%goa%' in SQL?
A leading wildcard can't use a B-tree index, so it scans the whole table, and it has no relevance ranking, stemming or typo tolerance.
How do you support typos ("goaa")?
Fuzzy matching by edit distance on terms, or n-gram indexing; spelling suggestions from the query log.
How do you reindex with zero downtime after changing the analyzer?
Build a new index in the background from the source of truth, dual-write new changes to both, then switch an alias to the new index and drop the old one.
Trade-offs
A search cluster gives relevance and speed on text at the cost of another system to operate and keep in sync, with eventual consistency between the database and the index. For small datasets, database full-text search (e.g. Postgres tsvector) is often enough.