Skip to content
All glossary terms

Full text search

How does full text search work in PostgreSQL?

Full text search matches documents by words rather than exact substrings, using tsvector, tsquery and the @@ operator. A GIN index makes it fast.

PostgreSQL's built-in full text search parses documents into tokens, normalizes them into lexemes (for example by reducing words to their stems) and stores the result as a tsvector. A search query is a tsquery, and the @@ operator tests whether a document matches it.

A text search configuration such as english decides how text is split into words, which common words are ignored and how stemming works. Ranking functions such as ts_rank order results by relevance. To make searches fast, index the tsvector expression with GIN, and use the same configuration in your queries as in the index.

Example

The example indexes the article text as a tsvector and finds the rows that contain both search and postgres. Only article 1 matches.

CREATE TABLE articles (id int PRIMARY KEY, body text NOT NULL);
INSERT INTO articles VALUES
  (1, 'Postgres supports full text search with tsvector and tsquery'),
  (2, 'Indexes make lookups fast');

CREATE INDEX articles_fts ON articles USING gin (to_tsvector('english', body));

SELECT id
FROM articles
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'search & postgres');

Plain SQL for stock PostgreSQL 16 or 17, no extensions needed. Run it in an empty database you can throw away.

Sources

Official PostgreSQL documentation (in English).

Related terms