Diff v1 → v2

  # Postgres Full-Text & Fuzzy Search
  
  > **When to use it:** building search over text in Postgres — a search box, autocomplete,
  > typo tolerance, "did you mean", relevance ranking, or hybrid keyword+semantic search —
  > and deciding whether native FTS is enough or you need a dedicated engine. Companion to
- > [[Push Compute to the Datastore]] (§4 names FTS; this is the depth).
+ > [[push-compute-to-datastore|Push Compute to the Datastore]] (§4 names FTS; this is the depth).
  
  The thesis: **an indexed search inside your transactional database beats shipping rows to
  the app to substring-match, and beats standing up Elasticsearch — until it structurally
  can't.** Know where that wall is.
  
  ## 1. tsvector / tsquery + the dictionary pipeline
  
  `to_tsvector('english', text)` runs parser → dictionary chain (lowercase, stem, drop stop
  words). `::tsvector` casting does **not** normalize — always use `to_tsvector`.[^pg-fts]
  
  A text-search **configuration** = a parser + an ordered dictionary chain per token type. The
  first dictionary to emit a lexeme wins, so **Snowball stemmers must be last** (they match
  everything). Chain order matters:
  
  - **Stemming**: `snowball` (default `english`) — fast, no compound-word splitting.
  - **Ispell/Hunspell**: real morphology + compound splitting (German/Norwegian/Dutch). Put
    *before* the stemmer.
  - **Synonym/thesaurus**: normalize domain vocab (`postgres → pgsql`); before the stemmer.
  - **`unaccent`**: a *filtering* dictionary — passes output on instead of terminating, so
    chain it **before** the stemmer. Gotcha: `unaccent()` is `STABLE`, not `IMMUTABLE`, so it
    can't go directly in a functional index — wrap it in your own `IMMUTABLE` SQL function.[^pg-fts]
  
  ## 2. Choose the query function by trust level — this is the one that bites
  
  | Function | Input grammar | On garbage input |
  |---|---|---|
  | `to_tsquery` | full `& | ! <-> :* ()` | **raises a syntax error** |
  | `plainto_tsquery` | ignored, ANDs all terms | never errors |
  | `phraseto_tsquery` | inserts `<->` between terms (phrase) | never errors |
  | `websearch_to_tsquery` (PG11+) | `"phrase"`, `OR`, leading `-` = NOT | **never errors, degrades gracefully** |
  
  **Do `websearch_to_tsquery` for untrusted search-box input, not `to_tsquery`** — the latter
  throws your endpoint a 500 on a bare `&`, while websearch gives Google-style power (phrases,
  OR, exclusion) and silently discards junk. (Stop words are stripped even inside quotes.)[^pg-fts]
  
  Operators: `<->` = followed-by, `<N>` = distance ≤16384, `:*` = prefix. Precedence tightest→
  loosest: `!` > `<->` > `&` > `|`.
  
  ## 3. Ranking: ts_rank_cd + setweight
  
  - **`ts_rank_cd`** (cover density — rewards matched terms appearing close together) over
    **`ts_rank`** (frequency only) for multi-term boxes. `ts_rank_cd` **needs positions** — if
    you `strip()` the tsvector it silently returns 0.[^pg-fts]
  - **`setweight(vec,'A')`** to boost title over body. Default weight multipliers are
    `{D:0.1, C:0.4, B:0.4, A:1.0}` — wait, exactly `{0.1,0.2,0.4,1.0}` for D,C,B,A.
  - **Normalization bitmask** (3rd arg): `2` = divide by doc length (penalize long docs,
    *changes ordering*); `32` = squash into `[0,1)` for display only (*cosmetic*, no reorder).
  
  ```sql
  UPDATE docs SET tsv =
    setweight(to_tsvector('english', title), 'A') ||
    setweight(to_tsvector('english', body ), 'B');
  SELECT id, ts_rank_cd(tsv, q) AS rank
  FROM docs, websearch_to_tsquery('english', $1) q
  WHERE tsv @@ q ORDER BY rank DESC LIMIT 20;
  ```
  
  ## 4. Indexing: GIN, then GiST, then RUM
  
  - **GIN is the preferred tsvector index** (docs say so): inverted, non-lossy for plain
    matches. Weight/position-qualified queries still need a heap recheck (GIN stores neither).
  - **GiST**: lossy signature index; needs a recheck always. Pick it only when build speed/size
    is the binding constraint.
  - **RUM** (3rd-party, `postgrespro/rum` — **not** in contrib, **not** on RDS/Cloud SQL):
    stores positions in the index, so phrase queries and `ORDER BY tsv <=> tsquery` ranked
    retrieval need no heap recheck/sort. Cost: ~2.5× GIN size, ~4× write amplification. Reach
    for it only when ranked/phrase latency is a *proven* hot-path bottleneck.[^pg-fts]
  - **Generated column vs expression index**: same write cost (both compute `to_tsvector` per
    write). Prefer a `GENERATED ... STORED` column (PG12+) over the legacy
    `tsvector_update_trigger`. GIN `fastupdate` (on by default) batches writes into a pending
    list — turn it **off** when consistent search latency matters more than write throughput.
  
  ```sql
  ALTER TABLE docs ADD COLUMN tsv tsvector
    GENERATED ALWAYS AS (to_tsvector('english', coalesce(title,'')||' '||coalesce(body,''))) STORED;
  CREATE INDEX docs_tsv ON docs USING GIN (tsv);
  ```
  
  ## 5. pg_trgm — making LIKE '%x%', ILIKE, and regex indexable
  
  Trigram indexes extract 3-char grams regardless of position, so **unlike a B-tree the pattern
  need not be left-anchored** — this is *the* fix for "`LIKE '%foo%'` won't use an index".[^pg-trgm]
  
  - `gin_trgm_ops` (default, faster reads) vs `gist_trgm_ops`. **GiST is required for
    `ORDER BY col <-> 'term' LIMIT n`** (KNN similarity) — GIN structurally can't do it.
  - `similarity()` (whole-string) / `word_similarity()` (best substring extent) /
    `strict_word_similarity()` (whole-word boundary). Operators `%` / `<%` / `<<%` with GUC
    thresholds `0.3` / `0.6` / `0.5`.
  - Trigrams pad short words with spaces, so 1–2 char queries produce unstable gram sets — fall
    back to `ILIKE 'prefix%'` (B-tree, left-anchored) below ~3 chars.
  
  ```sql
  CREATE INDEX articles_title_trgm ON articles USING GIN (title gin_trgm_ops);
  SELECT * FROM articles WHERE title ILIKE '%postgre%';   -- now a Bitmap Index Scan
  ```
  
  ## 6. Fuzzy / phonetic: fuzzystrmatch
  
  `levenshtein(a,b[,ins,del,sub])` and `levenshtein_less_equal(a,b,max)` (early-exit) for edit
  distance/typo budgets on short strings, any script. `metaphone`/`dmetaphone` for "sounds like"
  name matching (`dmetaphone` when pronunciation is ambiguous); `soundex` is legacy English-only.
  These **don't work well with UTF-8 multibyte** — use `daitch_mokotoff()` there.[^pg-fzz] Combine
  with `unaccent(lower(...))` (via an IMMUTABLE wrapper) so diacritics don't break matches.
  
  ## 7. Hybrid search (keyword FTS + pgvector) with RRF
  
  Keyword search nails exact terms/IDs/rare tokens; vector search captures paraphrase/synonymy.
  Hybrid beats either alone. Combine with **Reciprocal Rank Fusion** — it consumes rank *order*,
  not raw scores (which are on incomparable scales), so no normalization needed:[^rrf]
  
  ```
  RRF_score(d) = Σ_lists  1 / (k + rank_in_list(d))          -- k = 60 (paper) or 50 (Supabase); implementations diverge
  ```
  
  Each leg ranks independently (`row_number() OVER (ORDER BY ts_rank_cd(...) DESC)` and
  `... ORDER BY embedding <#> $q`), `FULL OUTER JOIN` on id, sum `coalesce(1.0/(k+rank),0)` per
  leg (optionally weighted). Prefer RRF over weighted-score fusion by default (weighted fusion is
  outlier-sensitive and needs normalization); use weighting only when eval data says one signal
  should dominate.
  
  ## 8. When to leave Postgres — the structural wall
  
  Native FTS (`tsvector`+GIN, `ts_rank_cd`, `pg_trgm`) is enough through roughly **tens of
  thousands to low millions of rows**, moderate QPS, one/few Snowball languages, no hard faceting.
  It breaks specifically at:[^leave]
  
  1. **Top-N relevance-ranked queries at multi-million rows** — `ts_rank`/`ts_rank_cd` **are not
     indexable**, so top-N-by-relevance must score *every* matching row. Benchmark (Neon, 10M
     rows): native ranked query **38.8s** vs pg_search **81ms**. This wall breaks first, not raw
     row count.
  2. **Faceted/aggregation UIs** — no BM25 corpus stats, no doc-values store for fast buckets.
  3. **CJK / non-Snowball languages** — no word-boundary-free tokenization.
  4. **Typo tolerance at scale with zero tuning** (Elasticsearch `fuzziness:AUTO`, Typesense
     default-on) vs hand-wired `pg_trgm` thresholds.
  
  **Intermediate step before a new cluster:** **ParadeDB `pg_search`** (BM25 via Tantivy, a native
  index AM — writes immediately visible, no ES refresh lag). AGPL-3.0; self-host only (managed is
  waitlist); PG15+. It fixes the "ts_rank isn't indexable" wall inside Postgres.[^paradedb]
  
  If you do leave: **Typesense/Meilisearch** for instant typo-tolerant product search with low ops
  (single binary, no JVM); **Elasticsearch/OpenSearch** for deep aggregation/log-analytics at
  scale (3-node minimum, JVM tuning — a real specialty). **Always run the engine as a secondary,
  always-rederivable-from-Postgres index, never the system of record** (GitLab's pattern), with a
  `pg_trgm` fallback if the index breaks.
  
  ## See also
- - [[Push Compute to the Datastore]] — the hub and the ladder.
- - [[Postgres Query Planning and EXPLAIN]] — verify FTS indexes are actually used.
+ - [[push-compute-to-datastore|Push Compute to the Datastore]] — the hub and the ladder.
+ - [[postgres-query-planning|Postgres Query Planning & EXPLAIN]] — verify FTS indexes are actually used.
  
  [^pg-fts]: PostgreSQL full text search — <https://www.postgresql.org/docs/current/textsearch.html>
      (configs, `websearch_to_tsquery` PG11+, ranking, GIN/GiST). `unaccent` IMMUTABLE-wrapper is
      a widely-repeated community gotcha, not verbatim in the docs.
  [^pg-trgm]: `pg_trgm` — <https://www.postgresql.org/docs/current/pgtrgm.html>. GiST-only KNN
      `<->`; thresholds `%`=0.3, `<%`=0.6, `<<%`=0.5.
  [^pg-fzz]: `fuzzystrmatch` — <https://www.postgresql.org/docs/current/fuzzystrmatch.html>.
  [^rrf]: RRF — Cormack, Clarke & Büttcher, SIGIR 2009 (k=60). Supabase hybrid-search ships k=50.
  [^leave]: Neon FTS-vs-pg_search benchmark (June 2025, 10M rows); GitLab & Xata "leaving Postgres
      FTS" writeups. Corpus/QPS thresholds are team self-reports, treat as heuristics.
  [^paradedb]: ParadeDB pg_search — <https://github.com/paradedb/paradedb> (BM25/Tantivy, AGPL-3.0,
      PG15+; first-party managed not yet GA). Vendor perf claims (4–20×) are unaudited.