How do you build full-text search over 100M+ records?

Question

IT Interview Practice · Accepted Answer

Use a dedicated search engine — **Elasticsearch/OpenSearch built on an inverted index** — fed from your source DB via **CDC**, and shard the index for scale. Your primary database (Postgres/MySQL) can't do relevance-ranked text search over hundreds of millions of rows; a `LIKE '%term%'` scans every row. A search engine inverts the problem.

```text
Source DB ─▶ CDC (Debezium) ─▶ Kafka ─▶ Indexer ─▶ Elasticsearch/OpenSearch
(Postgres)   (change stream)                        ├─ Shard 0 (inverted index) + replicas
                                                     ├─ Shard 1
Query ─▶ Coordinator ─▶ scatter to shards ─▶ gather ─┴─ Shard N ── ranked by BM25
```

## Inverted index — why it's fast

A normal index maps row → values. An **inverted index** maps *term → list of documents containing it*. Searching "database scaling" looks up two short posting lists and intersects them — O(matching docs), not O(all docs). This is what makes sub-100ms search over 100M+ documents possible. Text is **analyzed** first: tokenized, lowercased, stemmed ("running" → "run"), stopwords dropped — so queries match variants.

## Ingestion via CDC

Don't dual-write (app writes DB *and* ES) — a failure leaves them inconsistent. Instead, stream changes with **Change Data Capture**: **Debezium** tails the DB's write-ahead log, publishes row changes to **Kafka**, and an indexer applies them to Elasticsearch. This keeps search **eventually consistent** with the source and survives ES downtime (events replay from Kafka).

## Sharding & relevance

The index is split into **shards** (each a self-contained Lucene index), spread across nodes; **replicas** add read throughput and HA. A query scatters to all shards, each returns its top-k, and the coordinator merges. Relevance uses **BM25** by default — term frequency × inverse document frequency — tunable with boosts, synonyms, and field weights.

## Interview perspective

Interviewers check whether you (1) reach for the right tool instead of `LIKE`, (2) know *how* the inverted index works, and (3) solve the **sync problem** without dual-writes. Good: "use Elasticsearch." Great: you name CDC/Debezium for ingestion, BM25 for ranking, and accept eventual consistency as the trade for scale.

**Common trap:** treating Elasticsearch as your source of truth. It's a derived index — not durable/transactional like your DB. Rebuild it from the source when needed.

## Trade-offs & failure modes

- **Eventual consistency:** search lags writes by the CDC pipeline latency (usually sub-second).
- **Reindex pain:** mapping/analyzer changes need a full reindex — use aliases for zero-downtime swaps.
- **Deep pagination:** page 10,000 is expensive (scatter-gather) — use `search_after`, not `from/size`.
- **Hot shard / skew:** uneven doc distribution; pick a good routing key and enough shards up front (they're fixed at creation).
- **Relevance tuning is ongoing:** BM25 defaults rarely match business intent — expect iteration.

Inverted index — why it's fast

Ingestion via CDC

Sharding & relevance

Interview perspective

Trade-offs & failure modes

Related questions