PostgreSQL Full-Text Search: tsvector, GIN Indexes, and Rankings

PostgreSQL FTS: Often Beats Elasticsearch for Small-Medium Apps

Setup (Weighted Search)

ALTER TABLE posts ADD COLUMN search_vector tsvector;

UPDATE posts SET search_vector =
  setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
  setweight(to_tsvector('english', coalesce(body, '')), 'B');

CREATE INDEX posts_search_idx ON posts USING GIN(search_vector);

-- Auto-update trigger
CREATE FUNCTION update_fts() 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 fts_update BEFORE INSERT OR UPDATE ON posts
  FOR EACH ROW EXECUTE FUNCTION update_fts();

Search with Ranking and Snippets

SELECT title,
  ts_rank(search_vector, query) AS rank,
  ts_headline('english', body, query, 'MaxWords=30, StartSel=<mark>, StopSel=</mark>') AS snippet
FROM posts, plainto_tsquery('english', 'postgresql performance') query
WHERE search_vector @@ query
ORDER BY rank DESC LIMIT 20;

Trigram Similarity

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX name_trgm ON users USING GIN(name gin_trgm_ops);

SELECT name, similarity(name, 'Jhon Doe') sim
FROM users WHERE name % 'Jhon Doe' ORDER BY sim DESC;

-> Format search results with the JSON Viewer.