Implement Full-Text Search with Relevance Ranking

ALTER TABLE products
ADD COLUMN tsv_search_content TSVECTOR GENERATED ALWAYS AS (to_tsvector('english', coalesce(name, '') || ' ' || coalesce(description, '')))
STORED;

CREATE INDEX tsv_search_idx ON products USING GIN (tsv_search_content);

SELECT
  id,
  name,
  description,
  ts_rank_cd(tsv_search_content, query) AS rank
FROM products,
  websearch_to_tsquery('english', 'search terms') AS query
WHERE query @@ tsv_search_content
ORDER BY rank DESC
LIMIT 10;

How it works: This PostgreSQL example demonstrates how to implement efficient full-text search with relevance ranking. First, a `TSVECTOR` column `tsv_search_content` is added to the `products` table, automatically populated with processed text from `name` and `description`. A GIN index is then created on this column for fast lookups. Finally, a `SELECT` query uses `websearch_to_tsquery` to parse search terms and `ts_rank_cd` to calculate a relevance score, ordering results by rank to show the most relevant items first.