What is PostgreSQL full text search

· Category: SQL & Databases

Short answer

PostgreSQL full-text search converts documents into tsvector structures and queries into tsquery structures, enabling fast linguistic searches with relevance ranking.

Steps

  1. Create a tsvector column: ALTER TABLE articles ADD COLUMN tsv TSVECTOR;
  2. Populate it: UPDATE articles SET tsv = to_tsvector('english', title || ' ' || content);
  3. Search: SELECT * FROM articles WHERE tsv @@ to_tsquery('english', 'database & optimization');
  4. Create a GIN index: CREATE INDEX idx_fts ON articles USING GIN (tsv);
  5. Rank results: SELECT ts_rank(tsv, query) FROM articles, to_tsquery('database') query;

Tips

  • Use triggers to keep tsvector columns synchronized with source text.
  • Customize text search configurations for different languages and stopword lists.

Common issues

  • Full-text search is language-dependent; choosing the wrong dictionary misses relevant stems.
  • Large documents may need chunking or separate highlighting strategies.r