Why should you care?

PostgresVerse blog and product description search beats ILIKE '%phone%' that cannot use indexes.

See it live — copy this example

Run in pgAdmin or psql.

ALTER TABLE products ADD COLUMN search_doc tsvector
  GENERATED ALWAYS AS (
    to_tsvector('english', coalesce(name,'') || ' ' || coalesce(description,''))
  ) STORED;

CREATE INDEX idx_products_fts ON products USING GIN (search_doc);

SELECT name, ts_rank(search_doc, query) AS rank
FROM products, plainto_tsquery('english', 'wireless keyboard') query
WHERE search_doc @@ query
ORDER BY rank DESC
LIMIT 10;

What happened?

• Generated tsvector column combines name and description.
• GIN index supports @@ match.
• plainto_tsquery parses user phrase; ts_rank orders relevance.

Practice next

1. Add description text column with sample prose.
2. Create generated search_doc and GIN index.
3. Search varied phrases; try typo — note no fuzzy by default.
4. Weight fields with setweight on name vs description.
5. Add unaccent extension for café vs cafe matching.

Remember

tsvector + tsquery + GIN is PostgreSQL FTS stack. Generated columns keep index in sync. pg_trgm adds fuzzy/substring complement.

PostgresVerse site search

Marketplace search box uses FTS; ranks in-stock items higher in app layer.

Outcome: Search P95 60ms vs 4s ILIKE on catalog table.