Full-Text Search and Trigram Matching

Cartwheel's product search is name ILIKE '%straw%', fired on every keystroke against 200,000 products. A leading wildcard cannot use a B-tree, so each keystroke is a sequential scan of the whole catalogue, and the search box is the single most expensive read path in the application by execution count.

Postgres has two proper answers and they are not interchangeable. Full-text search understands words — tokenizing, stemming, dropping stop words, ranking results. Trigram matching understands character sequences, which makes arbitrary substring and fuzzy matching indexable. "Strawberries" finding "strawberry" is the first one's job. "straw" finding "Strawberry Punnet 400g" mid-word, and "strwaberry" finding it anyway, is the second's.

tsvector and tsquery

A text search configuration, english in Cartwheel's case, parses a document into tokens, discards stop words, and reduces the rest to lexemes. The result is a tsvector: a sorted list of lexemes with the positions where each occurred. A tsquery is the parsed search expression, and @@ is the operator that asks whether one matches the other.

SELECT to_tsvector('english', 'A punnet of the ripest strawberries');
--  'punnet':2 'ripest':5 'strawberri':6

SELECT to_tsvector('english', 'ripe strawberry')
    @@ to_tsquery('english', 'strawberries');   -- true

Half the words vanished: a, of and the are stop words and carry no search value. strawberries became the lexeme strawberri, and so does strawberry, so a search for one finds the other without any synonym table. The numbers are token positions, and they are what makes phrase search possible later. Both sides of @@ go through the same configuration, so the match is between normalized forms rather than between the strings a human typed.

Indexing It Properly

The value has to be stored, not computed at query time. Computing to_tsvector inside the WHERE clause means parsing and stemming every product on every keystroke — the same sequential scan as ILIKE, with more CPU. A stored generated column computes it once per write and makes it an ordinary indexable column.

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

CREATE INDEX products_search_idx ON products USING gin (search);

Naming the configuration is not optional decoration. Postgres only accepts text-search functions that specify a configuration name in an index expression, and the reason is stated in the manual: index contents must be unaffected by default_text_search_config, or different entries could hold vectors built under different rules with no way to tell which is which, and the index could not be dumped and restored correctly. The built-in default of that setting is pg_catalog.simple, which does no stemming and drops no stop words, and initdb overrides it based on the server's locale, so leaving it implicit means staging and production can disagree about what a word is.

The coalesce calls exist because concatenating a null makes the whole expression null, which would un-index every product without a description and raise no error doing it. Combining the name with the description in one vector is a deliberate simplification: Postgres can weight fields A through D so a match in the name outranks a match in the body, and that is worth adding when relevance complaints start arriving, not before.

Building Queries Humans Type

Users do not type tsquery syntax. Three functions translate what they do type. plainto_tsquery takes a bag of words and joins them with AND. phraseto_tsquery keeps word order and requires adjacency. websearch_to_tsquery accepts the syntax people already know from search engines: quoted text becomes a phrase, OR becomes alternation, and a leading minus sign becomes negation.

SELECT id, name, ts_rank(p.search, q) AS rank
  FROM products p,
       websearch_to_tsquery('english', $1) AS q
 WHERE p.search @@ q
 ORDER BY rank DESC
 LIMIT 20;

-- $1 = 'strawberry -frozen'  →  'strawberri' & !frozen
-- $1 = '"oat milk" or soy'   →  'oat' <-> 'milk' | 'soy'

The query is parsed once and joined against the indexed column, so the GIN index does the filtering and only surviving rows are ranked. That ordering matters: the manual warns that ranking is expensive because it has to consult the tsvector of every matching document, which is I/O bound and slow, and practical queries match a lot of documents. Narrow the match before you rank it, and treat ranking as a cost you have chosen rather than one you inherited. A broad query on a 200,000-row catalogue can match tens of thousands of rows, all of which get ranked before the LIMIT discards them.

Trigram Matching with pg_trgm

Full-text search deliberately cannot answer "contains these five characters anywhere". pg_trgm can. The extension breaks each string into overlapping three-character sequences, padding each word with two leading spaces and one trailing space so that cat yields four trigrams, and indexes those. Two strings sharing many trigrams are similar; a search pattern's trigrams can be looked up like any other index key.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX products_sku_trgm_idx
    ON products USING gin (sku gin_trgm_ops);

SELECT id, sku FROM products WHERE sku ILIKE '%88-4471%';
SELECT id, name FROM products WHERE name % 'strwaberry';

The first query is the one that could not be indexed at all before the extension existed, and now is. Trigram index searches have supported LIKE and ILIKE since 9.1 and regular-expression matches since 9.3, which covers essentially every "contains" search an application issues. The second uses the similarity operator %, true when two strings' similarity exceeds pg_trgm.similarity_threshold, which defaults to 0.3 — that is the typo tolerance, and the threshold is the dial you actually tune. There is also a distance operator, <->, and it comes with an index choice: GiST can answer ORDER BY name <-> $1 LIMIT 10 efficiently as a nearest-neighbour scan and GIN cannot, so a "closest matches" endpoint wants gist_trgm_ops while a plain contains-filter wants gin_trgm_ops.

Choosing Between Them

Full-text search is for natural language: descriptions, articles, reviews — content where stemming, stop words and ranking are the point. Trigrams are for short strings where the user's input is an approximation of the stored value: names, SKUs, autocomplete, anything that has to survive a typo. Applying either one to the other's job produces a search that is technically working and practically useless.

Cartwheel ends up with both. A tsvector over product name and description handles "gluten free oat milk". A trigram index on sku handles a warehouse operator typing four characters off a label. The two are queried separately and the results merged in the application, because they answer different questions and their scores are not comparable. Two honest result sets are easier to debug than one blended relevance number.

Where Postgres Stops

The boundary is real and worth naming before you hit it. There is no distributed index: search capacity is the capacity of one primary and its replicas. Relevance tuning is four weight labels and a ranking function, not a pluggable scoring pipeline. There is no learned ranking, no query understanding, no per-field analyzer chain, and no synonym or spell-correction machinery beyond what you assemble from dictionaries and trigrams yourself.

What that buys is one system instead of two. No search cluster to run, secure and upgrade, no synchronization pipeline, and no class of bug where the index and the database disagree about what exists. The decision to move to a dedicated engine should come from relevance requirements or scale, not from an assumption that databases cannot search, and the last chapter takes that decision up properly alongside the extensions worth adding before it. For a 200,000-row catalogue with a search box, Postgres is the right answer by a wide margin.