Add pg_trgm for what full-text cannot do — typo tolerance, substring matching, and matching short non-word strings like SKUs:

Where the ceiling actually is:

The honest summary: Postgres full-text is the right answer for most applications up to a few million documents with moderate query volume and modest relevance requirements, and its unbeatable advantage is transactional consistency. It stops being the right answer when relevance itself becomes a product you iterate on.

---

When a Dedicated Engine Is Earned

Reach for Elasticsearch/OpenSearch, Typesense, Meilisearch, Vespa or Algolia when you can point at items on this list — not because search feels important:

• Relevance is a product surface that non-engineers need to tune: boosts, synonyms, pinned results, business rules, A/B tests.
• Faceted navigation over high-cardinality fields, with counts, computed per query.
• Autocomplete under ~30 ms across millions of documents.
• Typo tolerance as a default, not an opt-in fuzzy query.
• Per-language analysis across a genuinely multilingual corpus.
• Combined text, geo, filter and numeric scoring in one ranked query.
• Aggregations over search results — histograms, top-N by facet.
• Search volume large enough that it must not share the OLTP instance.

What you take on is worth stating plainly, because it is the part the evaluation skips. A Lucene-based engine is memory-bound: performance depends on the filesystem cache holding hot segments, so capacity planning is in RAM, not CPU. Segments are immutable, written once and merged in the background — the LSM-tree structure from Module P-2, with the same consequence that compaction (here, merging) is background I/O you must plan for. Because segments are immutable, a "partial update" is a delete plus a reinsert of the whole document, which makes a frequently-updated field on a large document surprisingly expensive. Indexing is near-real-time, not real-time: a document becomes searchable when a refresh occurs, one second by default, which is the direct cause of the read-your-own-writes complaint below. And a cluster is a distributed system with its own failure modes, its own upgrade path, and mapping changes that require a full reindex.

Two rules that save teams from the worst outcomes:

The search engine is not your source of truth. It must be rebuildable from the database at any time. Teams that store data only in Elasticsearch discover the reasons the hard way: no transactions, no foreign keys, no constraints, a mapping change that requires reindexing data you can no longer reconstruct, and a recovery story that depends on snapshots of a system that was never designed to be authoritative.

Do not evaluate on a fifty-document test index. Relevance behaviour is dominated by corpus statistics — IDF, average document length, the per-shard effect above — so a small index tells you almost nothing about how ranking will behave in production.

---

The Hard Part Is Not Search, It Is Staying in Sync

Once the index lives outside your database, you own a data consistency problem, and it is the same one Module P-16 posed: two systems, one non-transactional boundary.

The obvious implementation is a dual write, and it is broken:

Every failure mode you would expect is real: the process dies between them and the index is permanently stale; Elasticsearch is briefly unavailable and the write is lost with the database already committed; the database transaction rolls back after the index write succeeds and you have indexed a document that does not exist; and two concurrent updates can reach the index in the opposite order to the database, so the index settles on the older value. That last one is the nastiest, because it is intermittent and leaves no trace.

The three sound designs, and their costs:

1. Outbox plus indexing worker. In the same transaction as the business write, insert a row saying "article 42 changed." A worker (Module P-19) reads pending rows, fetches the current state of article 42 from the database, and indexes it. Nothing is lost because nothing leaves the transaction, and the ordering problem disappears because the worker indexes current state rather than a captured diff — a late-arriving job simply re-indexes the same current value idempotently. Cost: a worker to operate, and a lag of seconds.

2. Change data capture. Read the database's write-ahead log and stream changes to the index (Module P-23). No application code touches the index, ordering follows the log, and it captures writes from migrations and manual UPDATEs that application-level hooks miss entirely. Cost: real infrastructure (Debezium or equivalent), and schema changes that ripple downstream.

3. Periodic full reindex as a backstop — not as the primary mechanism. Whatever you choose above, drift happens: a bug, an incident, a manual data fix, a mapping change. A scheduled job that reconciles or rebuilds is what turns drift from a permanent silent error into a bounded one. Do it with an alias swap, which is the standard zero-downtime pattern:

Application code queries the alias and never a concrete index name, which makes both reindexing and rollback a single metadata operation.

The complaint you will get on day one: read-your-own-writes

A user edits their document, clicks save, immediately searches for it, and it is not there. Nothing is broken — the refresh interval has not elapsed, and the outbox worker may be a second behind on top of that. But this is the exact user-visible symptom Module P-11 named, and "search is eventually consistent" is not an acceptable answer to the person who just typed the words.

The workable answers, in order of preference:

• Do not use search for the view that must be fresh. "My documents" is a database query with a filter, not a search query. This resolves most of the complaint for free, and the mistake is routing every list through the search engine because it is convenient.
• Return the just-written document from the database and merge it into the result set for that user, for a few seconds after their write.
• Wait for a refresh on that one write (refresh: "wait_for"), which makes only the writing request slower rather than raising the global refresh rate. Forcing a global refresh per write is the tempting version and it destroys indexing throughput, because refreshes create segments and segments must then be merged.

---

Query-Side Concerns That Bite at Scale

Deep pagination is quadratic in a distributed engine. from: 100000, size: 10 requires every shard to produce its top 100,010 hits and the coordinator to merge and discard 100,000 of them. With ten shards that is a million documents scored and sorted to return ten. Engines cap it (index.max_result_window, 10,000 by default) precisely because it is a cluster-wide memory hazard rather than a slow query. Use a cursor — search_after on a stable sort key — which is the same design as keyset pagination in SQL (Module F-8) and the same reason: an offset must be counted, a cursor can be seeked. If a user genuinely needs page 4,000, the honest read is that they need an export (Module P-19), not a search result.

Autocomplete is a latency problem, not a search problem. The budget is roughly 30–50 ms end to end because it fires per keystroke, which also means the query volume is 5–10× your search volume. Three implementations: prefix queries (simple, slow on large corpora), edge n-grams (index p, po, pos, post… — fast queries at a large index-size cost), and an FST-backed suggester (fastest, a separate structure to build). Two things make it cheap regardless of choice: debounce on the client, and cache aggressively — the distribution of two- and three-character prefixes is small and enormously skewed, so a Redis cache absorbs most of the traffic (Module F-12's working-set argument at its most favourable).

Typo tolerance costs more than it looks. Fuzzy matching expands one term into every term within an edit distance, so the posting lists to union grow fast, and short terms become almost meaningless at distance 2. The standard compromise is distance scaled to term length — exact for ≤3 characters, distance 1 up to ~6, distance 2 beyond — and never applying fuzziness to a phrase query.

Multi-tenancy needs the Module P-8 decision made explicitly. Index-per-tenant gives clean isolation, per-tenant deletion, and per-tenant mappings — but every shard carries fixed memory and file-handle overhead, so a few thousand small tenants each with their own index will exhaust a cluster on overhead alone before it holds any real data. The standard answer is the bridge model from Module P-8: pool small tenants into a shared index with a mandatory tenant filter, and silo the whales into dedicated indices. Whatever you choose, the tenant filter must be applied by a server-side layer that no query can omit — a forgotten filter in a search query is a cross-tenant data leak, and unlike a SQL WHERE clause omission it will not error, it will simply return other customers' documents.

Everything user-facing needs a timeout and a fallback. A search cluster degrading is common, and search is usually not the operation that must succeed. A short timeout, allow_partial_search_results where degraded results beat none, and a fallback to a simple database query on the primary filter is the difference between "search is a bit worse right now" and a broken page. Module A-7's degradation argument, applied to the component most likely to need it.

---

Measuring Relevance, Because Otherwise You Are Guessing

Search is the one subsystem where "it works" is not observable from the outside — it returns results either way. The instrumentation that makes it improvable:

• Zero-result rate, segmented by query. It is the most actionable number you have: the top zero-result queries are a direct list of missing synonyms, missing content, and analysis bugs.
• Click-through rate and click position. If users routinely click result seven, your ranking is wrong in a measurable way.
• Query reformulation rate. A user searching again within a few seconds did not find what they wanted.
• Offline relevance metrics (NDCG, MRR) against a labelled judgement set, so a ranking change can be evaluated before it ships rather than after complaints.
• Latency at p99, separated by query type — autocomplete, filtered search, faceted search — because their budgets differ by an order of magnitude and one average hides all three.

Why this matters in production: relevance regressions are invisible to every alert you have. Adding an aggressive stemmer, changing a boost, or reindexing with a slightly different mapping can make results materially worse while every dashboard stays green, latency improves, and error rate is zero. Zero-result rate and CTR are the only alarms that fire, which is why they are worth instrumenting on the day search ships rather than the day someone complains.

---

Where This Shows Up in Your Stack

• PostgreSQL: tsvector in a GENERATED ALWAYS AS … STORED column plus a GIN index is the whole implementation, and generating it rather than maintaining it in application code removes the possibility of drift. Use websearch_to_tsquery for user input — to_tsquery throws a syntax error on unbalanced quotes, which becomes a 500 the first time someone types an apostrophe. Add pg_trgm for typos, substrings, and SKU-like strings. Know that GIN has a pending-insert list, so fastupdate trades write latency for occasional larger cleanups, and that ts_rank scores every matching row, so broad queries are expensive even with a perfect index.
• Node.js: the analysis-mismatch bug is usually a client-side default — an analyzer set at index time with no matching search_analyzer. Never build query strings from user input by concatenation; use the engine's structured query DSL, since a query-string parser is an injection surface for both syntax errors and expensive query shapes (a leading wildcard, a distance-2 fuzzy on a common term). Always set a request timeout; the default in most clients is none.
• Redis: the natural cache for autocomplete prefixes and for the top-N popular queries, where the hit ratio is extremely high (Module F-12). RediSearch exists and is a credible small-to-mid-scale engine with the same caveat as any Redis-resident data: memory is the capacity limit and eviction policy must not be allowed to touch it (Module P-12).
• Elasticsearch / OpenSearch: plan capacity in RAM for the filesystem cache, not CPU. Query an alias, never a concrete index, so reindexing and rollback are metadata operations. Tune refresh_interval up (30s) for bulk indexing and back down for interactive use, and use refresh: "wait_for" on individual writes rather than forcing global refreshes. Keep shard counts low — a shard is a Lucene index with fixed overhead, and over-sharding is the most common self-inflicted cluster problem.
• Kafka / CDC: the sound sync mechanisms are the outbox relay (Module A-3) or WAL-based CDC (Module P-23), never a dual write. If you already run Kafka, an indexing consumer gives you replayability for free — a mapping change becomes "reset the consumer offset and let it rebuild," which is the cheapest reindex you will ever run.

---

Summary

• LIKE '%term%' fails twice over: a B-tree cannot serve a leading wildcard so every query is a sequential scan, and substring matching has no concept of relevance, stemming, or language. Making it fast with a trigram index gives you fast substring matching, not search.
• An inverted index maps terms to documents, built by an analysis pipeline — tokenise, lowercase, drop stopwords, stem, expand synonyms. Each stage is a lossy trade: stemming raises recall and lowers precision, stopwords destroy phrase queries, and synonyms belong at query time so a non-engineer can edit them without a reindex.
• Index-time and query-time analysis must agree. A mismatch returns zero results with no error and nothing unhealthy anywhere — the most common self-inflicted search bug there is.
• BM25 replaced TF-IDF because it saturates term frequency (k₁) and normalises document length (b), which stops keyword stuffing paying and stops long documents winning by accumulation. Scores are meaningful only as an ordering within one result set, never as an absolute threshold — and IDF is computed per shard, which is why relevance can look wrong on a small staging index and fine in production.
• Postgres full-text goes further than most teams assume: tsvector in a generated column, GIN index, setweight for field boosting, websearch_to_tsquery for safe user input, pg_trgm for typos and substrings. Its decisive advantage is that the index is inside the transaction, so the entire consistency problem below does not exist. Its real ceilings are ranking cost on broad queries, one language per column, four weight classes of tuning, no FST autocomplete, sharing the OLTP instance — and synonym dictionaries living as files on the server's filesystem, which managed hosting usually will not let you write.
• A dedicated engine is earned by specific requirements — tunable relevance as a product surface, facets with counts, sub-30 ms autocomplete, default typo tolerance, multi-language analysis, aggregations — not by search feeling important. What you take on: a memory-bound cluster whose capacity is planned in RAM, immutable segments merged in the background (Module P-2's LSM shape again), partial updates that are really full document rewrites, near-real-time rather than real-time visibility, and mapping changes that require a reindex.
• The search engine must never be the source of truth. It must be rebuildable from the database at any time; teams that store data only there discover why when a mapping change requires reindexing data they can no longer reconstruct.
• Dual writes are broken — the process can die between them, the index write can be lost after the database commits, and concurrent updates can land in the index in the wrong order and settle on the older value. Use an outbox plus an indexing worker that reads current state (which makes late jobs idempotent), or CDC from the WAL, and keep a periodic reindex with an alias swap as the backstop that bounds drift and gives you rollback.
• Expect the read-your-own-writes complaint on day one (Module P-11): the refresh interval plus indexing lag means a user's own save is briefly unfindable. Serve "my documents" from the database rather than from search, merge the just-written row for that user, or use a per-write wait_for — never a global refresh per write, which destroys indexing throughput by creating segments that must then be merged.
• Deep pagination is a cluster-wide memory hazard, since from: 100000 makes every shard produce and discard 100,010 hits; use a search_after cursor, the same keyset argument as Module F-8. Autocomplete is a latency problem with a 30–50 ms budget and 5–10× the query volume, so debounce and cache the short prefixes where hit ratios are extremely high. Scale fuzziness to term length.
• Multi-tenant search is Module P-8's decision: index-per-tenant isolates but each shard carries fixed overhead, so pool small tenants behind a mandatory server-side tenant filter and silo the whales. A forgotten tenant filter does not error — it returns another customer's documents.
• Instrument zero-result rate, CTR and click position, reformulation rate, and offline NDCG, because relevance regressions are invisible to every other alert: latency can improve and errors stay at zero while results get materially worse.

The next module, Vector Search and Embeddings, is the other half of retrieval. BM25 matches the words the user typed; embeddings match what they meant, at the cost of an index that is approximate by construction and a recall/latency knob you have to choose deliberately. The two are complements rather than replacements, and hybrid ranking — which the module ends on — is what production retrieval actually looks like.

---