Full-text search introduction

Postgres includes built-in full-text search (FTS)—tsvector documents and tsquery patterns—with GIN indexes for speed. Simpler than Elastic for many apps.

Basic FTS

ALTER TABLE articles ADD COLUMN search_vector tsvector;

UPDATE articles SET search_vector =
  to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''));

SELECT title FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgres & index');

Practice: Seed customers/orders tables from earlier lessons, then run queries in psql or DB Fiddle (PostgreSQL).

Ranking

SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'database') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

Triggers and pg_trgm

Keep search_vector updated via triggers on INSERT/UPDATE. For typo-tolerant LIKE patterns, pg_trgm extension complements FTS.

Important interview questions and answers

1. Q: tsvector vs storing plain text?
   A: tsvector stores normalized lexemes for fast matching.
2. Q: @@ operator?
   A: Matches document against query.

Self-check

1. What type stores preprocessed lexemes?
2. How do you combine title and body into one search document?

Tip: Keep tsvector updated with triggers when title/body change.