postgres-syntax-full-text-search
postgres-syntax-full-text-search
Quick Reference :
PostgreSQL full-text search (FTS) matches a tsvector (a normalized document : sorted lexemes plus positions) against a tsquery (a normalized query : lexemes joined by boolean operators) via the @@ match operator. The pipeline is always : raw text -> to_tsvector(config, text) -> tsvector ; raw query -> a query constructor -> tsquery ; then tsvector @@ tsquery. Normalization (stemming, stop-word removal, lowercasing) happens on BOTH sides, which is why FTS finds friendly when you search friend and LIKE cannot.
Four query constructors exist and the choice is the single highest-leverage decision. to_tsquery parses operator syntax (& | ! <->) and RAISES a syntax error on malformed input : NEVER feed it raw end-user text. plainto_tsquery ANDs every word. phraseto_tsquery requires the words in order (<->). websearch_to_tsquery accepts Google-style syntax (quotes, or, leading -) and NEVER raises an error : it is the ONLY safe constructor for untrusted user input.
Performance comes from a GIN index. to_tsvector on a column is computed per row at query time unless indexed : without a GIN index every search is a seqscan. ALWAYS index either an expression to_tsvector('english', body) (must use the 2-argument form) or a generated tsvector column (v12+, the preferred modern pattern). The query MUST repeat the exact same config used in the index expression or the index is skipped. Rank with ts_rank / ts_rank_cd, snippet with ts_headline, prioritize fields with setweight. For typo-tolerant or substring matching, FTS is the wrong tool : use pg_trgm instead.