Full-text search

PostgreSQL full-text search (FTS) tokenizes text into `tsvector`, matches with `tsquery`, and ranks with `ts_rank`. It fits product search up to moderate scale before Elasticsearch complexity is justified.

					ALTER TABLE articles ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'B')
  ) STORED;

CREATE INDEX idx_articles_fts ON articles USING GIN (search_vector);

SELECT id, title, ts_rank(search_vector, query) AS rank
FROM articles, plainto_tsquery('english', 'postgres indexing') AS query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;
				

Dictionaries control stemming and stop words. Prefix search needs `:*` in tsquery or trigram (`pg_trgm`) for fuzzy matching. Highlighting uses `ts_headline`.

On interviews: describe tsvector + GIN pipeline, when FTS is enough versus a dedicated search cluster, and how ranking weights affect relevance.

Common pitfalls: searching raw text without an index; wrong language config; expecting Google-grade typo tolerance from basic FTS; not updating vectors on write (use generated columns or triggers).

The trade-off is operational simplicity inside PostgreSQL versus advanced relevance, analyzers, and horizontal scale from a search engine.

Checklist:

• Store or generate tsvector on write.
• Index with GIN (or GiST for different trade-offs).
• Pick dictionary/language per content type.
• Define fallback when FTS recall or scale is insufficient.