Full-Text Search Databases

One question shape is still missing from the map, and it's hiding in the most ordinary pixel of the marketplace: the search bar. A hungry customer types "spicy noodles" and hits enter.

Try answering with what you own so far. The relational reflex is WHERE description LIKE '%spicy noodles%' — and it fails three ways at once. The leading % means no B-tree can help (an index sorts whole values; it can't find middles), so every row gets scanned. The scan is byte-blind, so "Spice-Noodle Soup" doesn't match — same words to a human, different bytes to a machine. And even the rows it does find come back in arrival order, because SQL has no opinion about which match is better.

That third failure is the deep one. Every question this container has asked — by key, by aggregate, by sorted range, by hops, by window — returned a set. This question returns a ranking: which documents best match, best first, top ten only. It's the only question on the map where the answer has an order that the data itself doesn't contain. That's not a feature request; it's a different question shape — and it gets its own family.

In this lesson: why byte-matching can never be search, the one flipped image the whole family stands on, the pipeline that lets machines see words instead of bytes, and the family's unusual systems posture (it refuses, by design, to be your source of truth).

Scope — and a promise: this is deliberately the teaser. How posting lists are built and compressed, and the actual ranking math (TF-IDF, BM25), get their full treatment in Inverted Index + TF-IDF/BM25 — where you'll construct the index yourself. Elasticsearch internals and interview ammo live in Elasticsearch (Interview Mastery). Today is the shape of the machine, not its gears.

Every index you've met so far points the same direction: from a record to its values. Row 17 → its title. Key → its document. Partition → its sorted messages. Useful — and useless for our question, because the question starts from the words.

Now picture the index at the back of a textbook. It points the other way: from a word to every page containing it. "Consistency — pages 14, 88, 203." Nobody answers "where is consistency discussed?" by reading the book front to back; you flip to the back and jump.

That's the entire founding idea of this family, and it's called an inverted index: for every term, keep a posting list — the set of documents containing it (plus counts and positions). Answering "spicy noodles" stops being a scan of everything and becomes: look up two posting lists, combine them, rank the result. You touch the entries for your terms — never the corpus. The asymmetry should feel familiar by now: it's the key-value hash and the graph pointer all over again — work proportional to the question, not to the data — wearing its third costume this container.

Building these lists well (sorting, compressing, merging them) and ranking their results (why a rare word counts more than a common one) is genuinely beautiful engineering — and it's exactly what Inverted Index + TF-IDF/BM25 holds for you. Here, the flip itself is the lesson.

There's a quiet step between raw text and that flipped index, and it's the most underrated component in search: the analyzer. Its job sounds mundane — turn text into terms — and it's where "search understands language" actually lives.

A minimal analyzer pipeline: tokenize (split on whitespace, punctuation, hyphens — this alone makes Spice-Noodle visible as spice + noodle), lowercase, drop stop-words (the, in, with carry no signal), and stem — collapse word forms to a shared root, so running, runner, runs all become run, and spicy, spiced, spices collapse together.

Now the move that's easy to miss and impossible to unsee: the same pipeline runs twice — over every document at write time, and over every query at read time. Matching never happens between raw strings; it happens between analyzed terms. "Running" finds "runner's" because both were reduced to run before they ever met. That's the whole trick — not intelligence, just a shared normal form.

Two consequences worth carrying out of this section. First, the analyzer is configuration, per language and per field — real engines ship dozens (German compounds, CJK segmentation, synonyms, typo tolerance), and choosing them is search-quality engineering. Second, analyzed terms are baked into the index at write time — change your analyzer, and you must reindex everything. Hold that thought; it explains the family's deployment posture two sections from now.

Below is the marketplace's dish catalog behind a real search box — and both machines running live on every keystroke. The right side is a genuine analyzer (tokenizer + a crude 5-rule stemmer) and a genuine inverted index built over the corpus; the ranking is deliberately naive term-frequency, because the real scoring math is §8's treasure.

The arc: "spicy noodles" — watch LIKE scan all 24 rows to find nothing while the flipped index ranks Spice-Noodle Soup first. "running" — stemming finds the Runner's Ramen that bytes never could. Then flip to the ×100 catalog and watch which counter grows. Then type anything you want — it's your search bar.

Here's where this family breaks the pattern of every database before it — and does so on purpose.

It's near-real-time, not real-time. Under the hood (Lucene — the 25-year-old library inside Elasticsearch, Solr, and OpenSearch), documents are written into immutable segments, and new documents only become searchable when a refresh creates a new segment — every 1 second by default. Index a dish, search for it instantly, and you may miss it. No other store in this section would call that acceptable; here it's a deliberate trade for indexing throughput. The practical rule falls out immediately: never serve read-your-own-write flows from the search index — the cart is not a search problem.

It's a derived index, not a system of record. This isn't a critic's opinion — it's the vendor's own guidance: your source of truth should live somewhere else, with the search engine as a secondary index. No multi-document transactions, and none are coming, because the design goal is different. The standard deployment is what the 2024 Stonebraker–Pavlo retrospective calls a polystore: the primary store (usually Relational Databases' family) owns the truth; the search engine holds a rebuildable projection of it, shaped for the relevance question. And that rebuildability is a feature — remember, changing an analyzer means reindexing; you can only do that fearlessly when the index is cattle and the primary is the pet.

One honest thread this opens: if truth lives in one store and its searchable projection in another, something must keep them in sync — and doing that reliably is a famous problem with a famous solution, waiting in Outbox + CDC: Reliable Events from Your Database (§6). The landscape lesson's polyglot bill, arriving right on schedule.

The decision framing, honest both ways.

Default first, as always. Postgres full-text search (tsvector + a GIN index) does real tokenization, stemming, and ranking — no second system, no sync pipeline. For moderate catalogs and internal search, it's the right call, and you already watched the crossover point: in the very first widget of this container, the Postgres-with-extensions engine held six workloads green at launch scale — and its search lane was the first to fall at 10×. That wasn't scriptwriting; that's the actual production pattern: pg FTS until relevance-tuned, language-aware, high-volume search becomes a product surface.

The family earns its runbook when ranked language search is a primary workload: per-language analyzers, typo tolerance, faceting, "did you mean", relevance tuning as an ongoing discipline, petabyte log search. That's Elasticsearch territory — powering search at Wikipedia, GitHub, Netflix, and Uber — and it arrives with the full polyglot bill: a JVM cluster to operate, shard sizing, reindex ceremonies, and the sync pipeline above.

And know what it's NOT for: exact lookups, status = 'delivered' filters, prefix-only autocomplete at small scale, or anything needing transactional truth. A search engine bought for non-relevance questions is the landscape lesson's oldest mistake — a specialist hired for a question you aren't asking.

Four requests hit the marketplace's platform team. For each: search engine, pg FTS, or neither — one sentence why. Commit first.

1. Dish search across 40,000 restaurant menus — multilingual, typo-tolerant, merchandising-tuned; the top result decides dinner.
2. "Find order #58291" in the support console.
3. Search within the company's 900 internal wiki pages.
4. Ops wants to grep 2 TB/day of service logs for error strings and correlate by request-id.

Answers. (1) Search engine — ranked, multilingual, product-surface relevance is the family's exact question; this is Wikipedia/Uber-shaped. (2) Neither — that's a key lookup wearing a search costume; the relational primary answers it in a millisecond (the coat-check ticket, not the catalog). (3) pg FTS — real search, modest scale, no relevance-tuning team; the default-first rule in action. (4) Search engine, log-flavored — full-text over huge append-heavy text IS the other thing this family dominates (petabyte log clusters at Netflix/Uber); notice it's also time-shaped, which is why log stacks pair the inverted index with time-based retention — two families you now know, composed.

• "Search is WHERE text LIKE '%q%'." A leading wildcard defeats every B-tree → full scan; bytes can't see that Spice-Noodle and spicy noodles are the same words; and there's no ranking. You watched 24 rows scanned for zero matches while the index returned five, ordered.
• "Elasticsearch can be our database." Its own vendor disagrees: truth lives elsewhere; the search engine is a derived, rebuildable secondary index in a polystore. No multi-document transactions — and that's a design choice, not a gap.
• "Search results are instantly consistent." Near-real-time means the refresh interval (default 1 s). A write followed by an immediate search can miss. Never serve read-your-own-write flows from the search index.
• "The ranking algorithm is the magic." Half the magic is the humble analyzer — tokenization and stemming decide what can match; scoring only orders what the analyzer allowed. (The scoring half gets its due in §8.)
• "Any text field needs a search engine." pg FTS carries moderate search well — you saw its lane hold at 1× and fall at 10×. The family earns its runbook when ranked language search is a primary product surface.
• "A search engine replaces my other indexes." It joins them: primary store + search projection + cache, each holding the same truth in the shape of its own question — the landscape lesson's thesis as a deployment diagram.

• The relevance question is different in kind: predicate over language, answer as a ranked top-K — the only question whose answer has an order the data doesn't. LIKE '%…%' fails it three ways (no index, no morphology, no ranking).
• The founding image: the book, flipped — an inverted index maps each term to a posting list of documents; queries touch only their terms' lists. Work ∝ the question, not the data — the container's favorite asymmetry, third costume.
• The analyzer is the underrated star: tokenize → lowercase → stop-words → stem, run identically on documents and queries — matching happens in analyzed-term space, which is why running finds runner. Analyzers are config; changing one means reindexing.
• Systems posture: near-real-time (Lucene segments + ~1 s refresh — never serve read-your-write from it) and derived-index polystore (truth lives elsewhere, the index is rebuildable cattle; sync ⟶ Outbox + CDC).
• Default-first: pg FTS until ranked language search is a product surface — then Elasticsearch (Lucene inside), the engine behind Wikipedia, GitHub, Netflix, Uber, and petabyte log search.
• The mechanics and the ranking math are waiting in Inverted Index + TF-IDF/BM25, where you build this machine yourself.
• Next: we just matched by words — but "cozy winter soup" and "hearty ramen for cold nights" share zero words and one meaning. Matching by MEANING is the map's final stop: Vector Databases.