Skip to content

LearnIndex & store

PostgreSQL full-text search

tsvector · ts_rank_cd

PostgreSQL’s built-in keyword search: text becomes a tsvector of normalized lexemes, queries become a tsquery, a GIN index finds matches and ts_rank or ts_rank_cd orders them.

In DocSlurpGlossary entry

On this page

PostgreSQL has full-text search built in. Three pieces do the work:

  • A tsvector is a document reduced to normalized lexemes with their positions. to_tsvector('english', 'a fat cat sat on a mat - it ate a fat rats') gives 'ate':9 'cat':3 'fat':2,11 'mat':7 'rat':12 'sat':4: stop words gone, “rats” stemmed to “rat”, positions kept.
  • A tsquery is a parsed query. websearch_to_tsquery accepts search-box syntax: "operating margin" -draft means the phrase “operating margin” and not “draft”. plainto_tsquery treats everything as plain words.
  • A GIN index on the tsvector column is an inverted index, so tsvector @@ tsquery matches without scanning the table.

Matches are ordered with ts_rank (term frequency) or ts_rank_cd (cover density: higher when the query terms appear close together).

The gotcha

Neither ranking function is BM25. PostgreSQL’s ranking uses only the matching document itself, with no global statistics, so a rare word like “EBITDA” counts no more than a common one like “revenue”. By default, length is not normalized either. Ranking also needs every match’s tsvector, so a broad query that matches most rows can be slow; cap the number of matches you rank, or use an extension that provides BM25.

In DocSlurp

Every chunk row has a generated ts_content column, to_tsvector('english', text), with a GIN index. This path handles queries with quoted phrases or -negation, documents not yet written to LanceDB, and requests the native index can’t serve. To keep broad queries bounded, it ranks at most 1,000 matching chunks (SEARCH_LEXICAL_CANDIDATES) with ts_rank_cd. If websearch_to_tsquery finds nothing, it retries with an OR of the terms plus three-word phrase windows from the question.

In DocSlurp

In DocSlurp

Every chunk has a generated ts_content column, to_tsvector('english', text), with a GIN index. This path serves quoted phrases, negation and documents not yet in LanceDB, ranking at most 1,000 matches (SEARCH_LEXICAL_CANDIDATES) by ts_rank_cd (core/server/src/services/search.ts).

Sources

Jump to