Full-Text Search

LIKE '%latte%' can answer "does this exact substring appear?" It cannot answer the more search-shaped questions: "does this document talk about lattes?", "are roasted and roasting related?", "which match is best?", or "show the matching words in context." PostgreSQL full-text search gives text its own query language: documents become tsvector, questions become tsquery, and @@ tests whether they match.

A tiny cafe document search

Use a cafe docs table: menu blurbs, reviews, and staff notes. The search_vector column is generated from the title plus body so every row carries a preprocessed search document.

CREATE TABLE cafe_docs (
  doc_id int PRIMARY KEY,
  doc_type text NOT NULL,
  title text NOT NULL,
  body text NOT NULL,
  search_vector tsvector GENERATED ALWAYS AS (
    to_tsvector('english', title || ' ' || body)
  ) STORED
);
 
INSERT INTO cafe_docs (doc_id, doc_type, title, body) VALUES
  (1, 'menu', 'Oat Milk Latte', 'A creamy latte made with oat milk and two espresso shots. Popular with morning commuters.'),
  (2, 'menu', 'Citrus Cold Brew', 'Slow-steeped cold brew with orange peel and a light caramel finish.'),
  (3, 'review', 'Quiet patio breakfast', 'The patio was quiet, the croissant was flaky, and the coffee arrived quickly.'),
  (4, 'review', 'Mobile order delay', 'My mobile order for cold brew was delayed, but the staff refunded the drink.'),
  (5, 'note', 'Roasting schedule', 'Roast espresso beans on Monday and filter coffee beans on Wednesday.'),
  (6, 'note', 'Allergy reminder', 'Almond syrup is stored separately from oat milk and dairy milk.');

CREATE TABLE
INSERT 0 6

You could search the raw text with ILIKE '%oat%', and for six rows that is perfectly fine. The structural limits matter when the table grows or the feature becomes search: a leading wildcard gives the index no useful starting point, it matches substrings rather than words, it cannot understand grammar, and it returns matches without a relevance score. Full-text search solves a different problem.

Vectors, queries, stems, and stop words

to_tsvector turns text into sorted lexemes: normalized word forms with positions. Common stop words disappear; related forms collapse to a stem. Watch the raw text become a compact search value:

SELECT to_tsvector('english', 'A creamy latte made with oat milk and two espresso shots.') AS vector;

                                    vector
-------------------------------------------------------------------------------
 'creami':2 'espresso':10 'latt':3 'made':4 'milk':7 'oat':6 'shot':11 'two':9
(1 row)

A, with, and and vanish because they are not useful search terms in the English configuration. creamy becomes creami. That is stemming, not spelling correction: full-text search is word-aware, not magic.

A tsquery is the search question. plainto_tsquery is beginner-friendly: give it ordinary text, and PostgreSQL normalizes it into a query. to_tsquery is the operator-shaped version, useful when you want explicit & for AND, | for OR, ! for NOT, or :* for prefix search.

SELECT plainto_tsquery('english', 'oat milk') AS plain_query,
       to_tsquery('english', 'cold & brew') AS typed_query;

  plain_query   |   typed_query
----------------+-----------------
 'oat' & 'milk' | 'cold' & 'brew'
(1 row)

The match operator is @@: vector matches query.

SELECT d.doc_id,
       d.title,
       d.doc_type
FROM cafe_docs AS d
WHERE d.search_vector @@ plainto_tsquery('english', 'oat milk')
ORDER BY d.doc_id;

 doc_id |      title       | doc_type
--------+------------------+----------
      1 | Oat Milk Latte   | menu
      6 | Allergy reminder | note
(2 rows)

This is already better than substring matching. The query asks for documents containing the normalized search terms, not just the exact characters typed next to each other.

Rank and highlight matches

A search page needs ordering. ts_rank scores how strongly a document matches a query. The exact number is a ranking signal, not a business metric; use it to sort, not to tell a stakeholder "this result is 0.099 better."

SELECT d.doc_id,
       d.title,
       round(ts_rank(d.search_vector, q.query)::numeric, 4) AS rank,
       ts_headline(
         'english',
         d.body,
         q.query,
         'StartSel=<mark>, StopSel=</mark>, MaxWords=12, MinWords=4'
       ) AS snippet
FROM cafe_docs AS d
CROSS JOIN plainto_tsquery('english', 'coffee quiet') AS q(query)
WHERE d.search_vector @@ q.query
ORDER BY rank DESC, d.doc_id;

 doc_id |         title         |  rank  |                                 snippet
--------+-----------------------+--------+--------------------------------------------------------------------------
      3 | Quiet patio breakfast | 0.0907 | <mark>quiet</mark>, the croissant was flaky, and the <mark>coffee</mark>
(1 row)

ts_headline returns display text with matches marked. In a real app, treat that as presentation output and escape or sanitize according to your rendering layer; the SQL lesson here is just that PostgreSQL can produce match context from the same query.

GIN indexes make text searchable

Full-text search becomes practical when the database can index the vector. PostgreSQL's usual index type for this is GIN, an inverted index shape: for each lexeme, the index can find rows containing it. Section 16.2 owns index internals; here, just create the index and prove the database registered it.

CREATE INDEX cafe_docs_search_idx
ON cafe_docs
USING gin (search_vector);

CREATE INDEX

SELECT c.relname AS index_name,
       am.amname AS index_type
FROM pg_class AS c
JOIN pg_index AS i
  ON i.indexrelid = c.oid
JOIN pg_am AS am
  ON am.oid = c.relam
WHERE c.relname = 'cafe_docs_search_idx'
ORDER BY c.relname;

      index_name      | index_type
----------------------+------------
 cafe_docs_search_idx | gin
(1 row)

Do not overread a tiny lesson table: the planner may still prefer a sequential scan on six rows, because there is almost nothing to scan. The important design move is stable: store or compute a tsvector, query it with @@, and add a GIN index when search becomes a real access path.

When PostgreSQL search is not enough

PostgreSQL full-text search is a strong fit for product catalogs, help docs, admin tools, internal notes, and "search this table well enough" features. Reach for a dedicated search engine when search itself is the product: typo tolerance, fuzzy matching, language-specific ranking beyond PostgreSQL's configuration, faceting across huge catalogs, autocomplete, personalization, or operational isolation from your transactional database. Keep the boundary honest: Postgres can be the source of truth while a search service is the read-optimized copy.

Next lesson closes Section 15 by reshaping result sets: pivoting rows into report columns, then unpivoting wide data back into analyzable rows.