PostgreSQL Full-Text Search Setup and Usage

Need search functionality but adding Elasticsearch feels like overkill. PostgreSQL's built-in full-text search is powerful but the setup isn't obvious.

PostgreSQL FTS from zero to production:

-- 1. Add a tsvector column
ALTER TABLE articles ADD COLUMN search_vector tsvector;

-- 2. Populate it (combining title with higher weight + body)
UPDATE articles SET search_vector =
  setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
  setweight(to_tsvector('english', coalesce(body, '')), 'B');

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

-- 4. Auto-update on INSERT/UPDATE
CREATE OR REPLACE 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 update_search_vector
  BEFORE INSERT OR UPDATE OF title, body ON articles
  FOR EACH ROW EXECUTE FUNCTION articles_search_trigger();

-- 5. Search with ranking
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, plainto_tsquery('english', 'database performance') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;

-- 6. Highlight matching terms
SELECT ts_headline('english', body, plainto_tsquery('database performance'),
  'StartSel=<b>, StopSel=</b>, MaxFragments=3')
FROM articles
WHERE search_vector @@ plainto_tsquery('database performance');

PostgreSQL FTS handles 90% of search use cases without adding another service. It supports stemming, ranking, phrase search, and prefix matching out of the box.

Adding search functionality without external services