skip to content

When does PostgreSQL's built-in full-text and trigram search stop being enough, so that you would move a help-centre search to a dedicated search engine?

level: seniorimportance: should knowfreq 38%

answer

  1. no corpus statistics in ranking
  2. every match gets ranked
  3. typos need a second mechanism
  4. transactional for free
  5. a sync pipeline is the price

basics

~20 s

Stay in PostgreSQL while keyword matching, field weights and modest corpus size are enough. Move when you need corpus-aware relevance (ts_rank has no IDF or BM25), facets, typo tolerance and language analysis at scale, or search load isolated from the database.

solid answer

~40 s

Built-in search gives a lot: matches that are transactionally consistent with the rows, joins to permissions and product tables, one backup and no sync pipeline. Its limits are concrete: `ts_rank` and `ts_rank_cd` use only the document and the query, never corpus statistics, so rare terms are not boosted the way BM25 does; ranking reads and sorts every match, so common terms get slow; typo tolerance means adding `pg_trgm` and blending scores by hand; analysis is one configuration per `tsvector`, with synonym files on the server's filesystem; and a `tsvector` must stay under 1 MB, with positions capped at 16,383. Move when relevance tuning, faceting, multilingual analysis or search traffic outgrow that. The cost is a second system and eventual consistency: new and deleted articles show up only after the sync catches up.

go deeper

for a junior

Know that PostgreSQL has built-in full-text search good enough for many applications, and that large or demanding search products often use a separate search engine.

for a middle

Name the concrete limits: no corpus statistics in ts_rank, ranking every match, typo tolerance only via pg_trgm, and the tsvector size and position caps.

for a senior

Argue from production signals: relevance complaints, latency on common terms, faceting needs, load on the primary. Exhaust in-database mitigations first, and price the sync pipeline and eventual consistency.

for a principal

Own the build-versus-integrate call: compare the cost of operating and syncing a search cluster with the product value of better relevance, and set the evidence that will trigger the move.

## What staying inside PostgreSQL buys For a software vendor's help centre, PostgreSQL's full-text search plus `pg_trgm` is often the right first answer, because it inherits the database's properties: - **Transactional consistency**: an article committed a millisecond ago is searchable, and a deleted one is gone, with no sync lag. - **Joins and security**: the search query can filter by product, language or plan entitlement, and row-level rules apply as they do to any query. - **One system**: one backup, one failover story, one set of credentials, and no pipeline to keep alive. The interview question is really *which requirements break that bargain*. ## Where the built-in search runs out | Requirement | PostgreSQL mechanism | Where it strains | |---|---|---| | Relevance that knows the corpus | `ts_rank`, `ts_rank_cd` | they use no global information: no inverse document frequency, no BM25, so a rare term is not boosted over a common one | | Fast top-N on common terms | index plus rank and sort | ranking reads every matching row's `tsvector` and sorts all matches before `LIMIT` | | Typo tolerance | `pg_trgm` | a separate mechanism; combining trigram and lexeme scores is hand-written SQL | | Facets and counts per category | `GROUP BY` over the matches | fine for a few categories, costly when every query needs many counts over large match sets | | Many languages, synonyms | one configuration per `tsvector`, or a `regconfig` column | synonym and thesaurus files live in `$SHAREDIR/tsearch_data`, which needs filesystem access to the server | | Very long documents | `tsvector` limits | under 1 MB per `tsvector`; positions cap at 16,383; at most 256 positions per lexeme | The 16,383 cap deserves a sentence: words after that point all receive the same final position, so phrase and proximity matching stop working reliably deep inside very long articles. A help centre rarely hits it; a document archive might. Why BM25 and inverse document frequency help is ranking theory; the PostgreSQL fact is simply that its built-in ranking does not use them. ## Signals that the limit has arrived Moving is justified by evidence, not by corpus size alone. Watch for: 1. **Relevance complaints that weights cannot fix**: users say the right article is on page two, and tuning `setweight` labels and normalization flags has stopped helping. 2. **Latency on common terms**: page-one queries for broad words grow with the number of matches, because every match is ranked. 3. **Feature requests outside the model**: faceted navigation with live counts, highlighting across many fields, autocomplete with typo tolerance, per-language analysis chosen at query time, learning-to-rank. 4. **Search load hurting the primary**: ranking-heavy queries competing with transactional traffic. Routing search to a read-only standby is a step before a new system. ## What moving costs A dedicated search engine is a second system with its own copy of the data: - **Eventual consistency**: new, edited and deleted articles appear in results only after the sync catches up, and deletes that are missed leave ghosts in results. - **A pipeline to own**: dual writes from the application, or change data capture from the database, plus a way to rebuild the index from scratch. - **Security duplicated**: permission filters must be copied into the index and kept in step. - **Operations**: another cluster to size, upgrade, monitor and back up. How such an engine works internally is a subject of its own. ## What to try before moving Inside PostgreSQL, several steps buy headroom: - filter before ranking: product, language and published flags in the `WHERE` clause shrink the set that must be ranked; - require all words (`websearch_to_tsquery` ANDs them) instead of building OR queries; - keep a stored, weighted `tsvector` so nothing is re-parsed at query time, and call `ts_headline` only for the returned page; - add `pg_trgm` for titles and a `ts_stat` word list for *did you mean* suggestions; - move search reads to a standby. ## How to answer in an interview State the bargain, name two or three concrete limits with their PostgreSQL identifiers, and tie the decision to observed signals. A good closing line: **stay while consistency and simplicity matter more than relevance sophistication; move when relevance, facets or search traffic become product features in their own right, and budget for the sync pipeline when you do.**

  • What would you try inside PostgreSQL before adding a search cluster?
    Shrink what must be ranked: filter by product and language first, AND the user's words with `websearch_to_tsquery`, keep a stored weighted `tsvector`, and run `ts_headline` only on the returned page. Add `pg_trgm` for typo tolerance on titles, and route search reads to a standby if they compete with writes.
  • How does an external search index change the guarantees the application can give?
    Results become eventually consistent: a just-published article may be missing, and a just-deleted one may still appear until the sync applies the change. The application must tolerate that, re-check permissions or existence against PostgreSQL when it matters, and be able to rebuild the index from the database.
  • Which tsvector limits matter for very long articles?
    A `tsvector` must stay under 1 MB, so oversized inputs fail with a `string is too long for tsvector` error. Positions are capped at 16,383 and each lexeme keeps at most 256 positions, so phrase and proximity matching degrade deep inside very long documents. Splitting long documents into sections avoids both.

  • ts_rank boosts rare terms the way a search engine's IDF does.
  • A GIN index makes ORDER BY ts_rank fast however many rows match.
  • Full-text search tolerates typos without pg_trgm.
  • A dedicated search engine stays transactionally consistent with PostgreSQL.
  • PostgreSQL cannot do phrase search, so phrases require a search engine.