PostgreSQL full-text search setup

Need text search functionality without adding Elasticsearch or another external search service.

PostgreSQL has built-in full-text search:

-- 1. Add tsvector column with GIN index
ALTER TABLE articles ADD COLUMN search_vector tsvector;

CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);

-- 2. Populate the vector
UPDATE articles SET search_vector = 
  setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
  setweight(to_tsvector('english', coalesce(body, '')), 'B');

-- 3. Keep it updated with a trigger
CREATE FUNCTION update_search_vector() 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 articles_search_update
  BEFORE INSERT OR UPDATE ON articles
  FOR EACH ROW EXECUTE FUNCTION update_search_vector();

-- 4. Query with ranking
SELECT title, 
       ts_rank(search_vector, query) as rank,
       ts_headline('english', body, query, 'MaxWords=30') as snippet
FROM articles, 
     to_tsquery('english', 'python & machine & learning') as query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;

-- 5. Search with prefix matching
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'mach:*');

-- 6. Phrase search (PostgreSQL 13+)
SELECT * FROM articles
WHERE search_vector @@ phraseto_tsquery('english', 'machine learning');

Weights: A > B > C > D (default). Use setweight to prioritize title matches over body matches.

PostgreSQL FTS is good enough for most applications (up to millions of documents). It avoids the operational complexity of maintaining a separate search service.

Applications needing search without external search infrastructure