Full Text Search in VitaDiet

Introduciton

Full Text Search (FTS) is a technique for searching natural language text in a database. Unlike a simple LIKE search which matches exact character patterns, FTS understands language — it normalizes words to their root form, ignores common words like "the" and "a", and ranks results by relevance.

For example a LIKE search for '%chicken%' will: *Scan every row in the table sequentially *Only match the exact string "chicken" *Return results in no particular order *Become slower as the table grows

A FTS search for 'chicken' will: *Use a GIN index to find matches instantly *Match "chicken", "chickens", "chickened" *Rank results by relevance *Perform consistently regardless of table size

For VitaDiet, FTS is used to power the food search feature, allowing users to search 350,000+ foods quickly and accurately.

How PostgreSQL FTS Works

PostgreSQL FTS is built around two core data types:

tsvector — a preprocessed representation of a text document. It stores the normalized lexemes (root forms of words) of a text column. For example: SELECT to_tsvector('english', 'Grilled Chicken Breast'); Result: 'breast':3 'chicken':2 'grill':1 *"Grilled" becomes "grill" (stemmed to root form) *Numbers indicate the position of each word *Stop words like "and", "the" are removed

tsquery — a processed search query. It converts user input into a format that can be matched against a tsvector: SELECT plainto_tsquery('english', 'grilled chicken'); Result: 'grill' & 'chicken' The @@ operator checks if a tsquery matches a tsvector: SELECT to_tsvector('english', 'Grilled Chicken Breast')

@@ plainto_tsquery('english', 'grilled chicken');

Result: true