PostgreSQL Full Text Search

The LIKE operator searches for exact letter patterns. A search for "run" with LIKE misses "running" and "ran". Full text search understands words. It reduces words to their roots and ignores filler words such as "the" and "is".

  Text:   "The cats were running quickly"
  Step 1: remove filler  -> cats, running, quickly
  Step 2: find roots     -> cat, run, quick
  Search "cat runs"      -> matches

Think of a library catalogue. A good catalogue finds the book when you type "cooking" even if the title says "cooks".

Two Core Types

Full text search rests on two data types. One represents the stored text, and the other represents the search request.

tsvector

A tsvector is a document turned into a sorted list of root words with positions.

SELECT to_tsvector('english', 'The cats were running quickly');

Result: 'cat':2 'quick':5 'run':4

tsquery

A tsquery holds the search words and logic operators.

SELECT to_tsquery('english', 'cat & run');

The ampersand means AND. The pipe sign | means OR. The exclamation mark ! means NOT.

Matching and Searching

Matching operators and helper functions turn the two core types into working searches.

Matching with @@

The @@ operator asks whether a document matches a query.

SELECT to_tsvector('english', 'The cats were running quickly')
       @@ to_tsquery('english', 'cat & run');

The answer is true.

Searching a Table

CREATE TABLE articles (
  id    SERIAL PRIMARY KEY,
  title TEXT,
  body  TEXT
);

INSERT INTO articles (title, body) VALUES
('Garden Basics', 'Plant seeds in loose soil and water them daily'),
('Soil Health',   'Healthy soil holds water and feeds growing plants'),
('Kitchen Tips',  'Chop onions finely before cooking');

SELECT title
FROM articles
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'plant & water');

Both gardening articles match. The word "plants" and the word "plant" share one root.

Easier Query Writing

Users do not type ampersands. Two helper functions accept natural input.

Function	Behavior
plainto_tsquery	Joins all words with AND
phraseto_tsquery	Requires words in the same order
websearch_to_tsquery	Understands quotes, OR, and a minus sign like a web search box

SELECT title FROM articles
WHERE to_tsvector('english', body)
      @@ websearch_to_tsquery('english', 'soil -onions');

Ranking and Display

Good search pages sort the best matches first and show where the words appear.

Ranking Results

Some matches fit better than others. The ts_rank function scores each row so you can sort the best matches first.

SELECT title,
       ts_rank(to_tsvector('english', body),
               to_tsquery('english', 'soil')) AS score
FROM articles
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'soil')
ORDER BY score DESC;

Highlighting Matches

ts_headline wraps matching words in bold tags, which helps a search results page.

SELECT ts_headline('english', body, to_tsquery('english', 'water'))
FROM articles;

Speed and Limits

Indexes keep searches fast, and a few limits shape what search can do.

Making Search Fast

Converting every row to a tsvector at each search costs time. Store the converted value in a generated column and attach a GIN index.

ALTER TABLE articles
ADD COLUMN search_doc tsvector
GENERATED ALWAYS AS (
  to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''))
) STORED;

CREATE INDEX idx_articles_search
ON articles USING GIN (search_doc);

SELECT title FROM articles
WHERE search_doc @@ websearch_to_tsquery('english', 'growing plants');

  Search words --> GIN index --> matching row ids --> rows

A GIN index works like the index at the back of a book. It lists each word with the pages where the word appears.

Language Settings

The first argument, such as 'english', picks the rules for root words and filler words. Run \dF in psql to see the installed languages. Pick the language that matches your text.

Limits to Remember

• Full text search matches whole words, so partial words need a prefix search such as 'gard:*'.
• Spelling mistakes do not match. The pg_trgm extension handles similar spellings.
• Very large documents need careful indexing.

Practice Exercise

Create a table of five short news headlines. Add a generated tsvector column and a GIN index. Search for one word, then two words, and then use a minus sign to exclude a word. Rank the results and sort them by score.