Building Full-Text Search

Inverted indexes, analysis and ranking, syncing with CDC, and sharding a search cluster.

"Find posts containing *distributed systems*", "search products by name", "find messages mentioning Alice": full-text search comes up in a huge number of designs. A LIKE '%term%' query scans every row and can't rank results, so at any real scale you need an inverted index — usually in a dedicated search system kept in sync with your primary database.

The inverted index

A normal index maps a row to its values. An inverted index maps each term to the list of documents that contain it (the *posting list*):

"distributed" → [doc 4, doc 9, doc 27, doc 31]
"systems"     → [doc 4, doc 12, doc 27]
"kafka"       → [doc 9, doc 31]

query: distributed AND systems → intersect lists → [doc 4, doc 27]

Posting lists are sorted by document ID, so intersections are fast merges. They also store term frequencies and positions, which power ranking and phrase queries ("distributed systems" as an exact phrase).

Analysis: turning text into terms

The same pipeline runs on documents at index time and on queries at search time, so they match:

Tokenize — split "Kafka's partitions" into kafka's, partitions.
Normalize — lowercase, strip accents and punctuation.
Remove stop words (optional) — the, a, of.
Stem — partitions → partition, running → run, so related forms match.

Prefix search ("dist…") and typo tolerance need extra structures: edge n-grams at index time, or a fuzzy match by edit distance at query time. For search-as-you-type, see Design Typeahead.

Ranking

Matching documents must be sorted by relevance. The standard text score is BM25: a term counts more when it appears often in this document, and more when it's *rare* across all documents ("kafka" is more telling than "system"). Real products combine it with other signals:

Recency — decay the score of older posts
Popularity — likes, clicks, sales
Personalization — the searcher's own network or history

A common approach is two stages: retrieve the top few hundred candidates cheaply with BM25 and filters, then re-rank only those with a heavier model.

Keeping the index in sync

The database is the source of truth; the search index is a derived view that's allowed to lag by a second or two.

[App] -> (Postgres)
(Postgres) -> [CDC: Debezium] -> {Kafka} -> [Indexer] -> (Elasticsearch)
[Search API] -> (Elasticsearch)

Make indexing idempotent — index by document ID with a version number, so replays and out-of-order updates can't leave an older version on top. And keep the ability to rebuild the whole index from the database: mapping changes, new analyzers and corruption all require it. Build the new index alongside the old, then switch an alias.

[!NOTE] Near real-time, not real-time Engines like Elasticsearch make new documents searchable on each *refresh* — by default about once a second. That's fine for nearly every product; if a user must see their own post in search immediately, show it from the primary database on their screen instead.

Scaling the index

Split the index into shards and replicate each one.

Partition by document (the usual choice): each shard holds a full inverted index for a subset of documents. A query goes to every shard (scatter-gather) and the top results are merged. Writes touch one shard.
Partition by term: each shard owns a range of terms. A one-word query hits one shard, but multi-term queries must combine lists across shards and popular terms create hot shards. Rarely worth it.

Replicas add read throughput and availability. For time-ordered content (tweets, logs), use time-based indexes — one per day or week — so recent, hot indexes get more resources and old ones can be shrunk, frozen or deleted. See Design Twitter Search for this at scale.

When you don't need a search cluster

Postgres full-text search (tsvector + a GIN index) handles millions of rows with decent ranking and keeps everything in one transactional store.
A trigram index speeds up LIKE '%term%' on short fields.
Reach for Elasticsearch or OpenSearch when you need relevance tuning, facets, fuzzy matching or more scale than one database gives you.

[!INTERVIEW] What a strong answer sounds like "Postgres stays the source of truth for listings. A Debezium connector streams changes into Kafka, and an indexer writes them into Elasticsearch using the listing ID and version, so updates are idempotent and search lags by about a second. The index is sharded by listing ID; queries scatter to all shards and merge the top 50. We rank with BM25 plus a recency and popularity boost, and we can rebuild the index from Postgres behind an alias when the mapping changes."