Full-Text Search with Ranking

-- Add search vector column
ALTER TABLE articles ADD COLUMN search_vector tsvector;

-- Populate and keep in sync
UPDATE articles SET search_vector =
  setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
  setweight(to_tsvector('english', coalesce(body, '')), 'B');

-- Create GIN index for fast lookups
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

-- Auto-update trigger
CREATE FUNCTION articles_search_trigger() RETURNS trigger AS $$
BEGIN
  NEW.search_vector :=
    setweight(to_tsvector('english', coalesce(NEW.title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(NEW.body, '')), 'B');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_articles_search
  BEFORE INSERT OR UPDATE ON articles
  FOR EACH ROW EXECUTE FUNCTION articles_search_trigger();

-- Search with ranking
SELECT id, title,
  ts_rank(search_vector, query) AS rank
FROM articles,
  plainto_tsquery('english', 'react hooks tutorial') AS query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;