Postgres by Example: Full-Text Search

PostgreSQL has a competent, built-in full-text search engine — no Elasticsearch, no Solr needed for most apps. The core abstractions are tsvector (a parsed-and-normalized representation of a document) and tsquery (a parsed search expression). With a GIN index on a tsvector, you get fast text search with stemming, ranking, and multi-language support, all in plain SQL.

What you'll learn:

• to_tsvector and to_tsquery
• The @@ operator that tests a vector against a query
• Configuring language (English, simple, etc.)
• Indexing with GIN for fast search
• Ranking results with ts_rank

-- Setup
DROP TABLE IF EXISTS docs_fts;
CREATE TABLE docs_fts (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title text NOT NULL,
  body  text NOT NULL
);

INSERT INTO docs_fts (title, body) VALUES
  ('Postgres tips',    'PostgreSQL is a powerful relational database with full-text search built in.'),
  ('SQL joins',        'Inner joins keep matching rows; left joins keep all left rows.'),
  ('Indexing',         'B-tree indexes speed up equality and range queries. GIN indexes are great for arrays and text search.'),
  ('Cats',             'Cats are not databases. This sentence exists to add noise.');

-- to_tsvector parses, normalizes, and removes stop words
SELECT to_tsvector('english', 'PostgreSQL has powerful full-text search') AS v;

-- to_tsquery turns a query string into a tsquery
SELECT to_tsquery('english', 'powerful & search') AS q;

-- @@ tests "does this document match this query?"
SELECT title
FROM docs_fts
WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('english', 'powerful & search');

-- websearch_to_tsquery: parses Google-style input (quotes, OR, -minus)
SELECT title
FROM docs_fts
WHERE to_tsvector('english', title || ' ' || body) @@ websearch_to_tsquery('english', 'index OR join -cats');

-- Add a generated tsvector column + GIN index for fast search at scale
ALTER TABLE docs_fts ADD COLUMN search_tsv tsvector
  GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;

CREATE INDEX docs_fts_search_idx ON docs_fts USING gin (search_tsv);

-- Rank results
SELECT title, ts_rank(search_tsv, websearch_to_tsquery('english', 'database search')) AS rank
FROM docs_fts
WHERE search_tsv @@ websearch_to_tsquery('english', 'database search')
ORDER BY rank DESC;

DROP TABLE IF EXISTS docs_fts;

to_tsvector('english', text) tokenizes the input, lowercases it, applies the English stemmer (so running and runs map to the same root), removes stop words (the, a, is), and returns a tsvector — a sorted list of normalized tokens with positions. Different configurations (english, simple, german, russian, ...) implement different stemming and stop-word rules.

to_tsquery parses the expression: & (and), | (or), ! (not), <-> (followed by). websearch_to_tsquery accepts a friendlier syntax — double-quoted phrases, OR keyword, - to exclude. The latter is what you want behind a user-facing search box.

The @@ operator tests whether a tsvector matches a tsquery. With a GIN index on the tsvector, this is the fast path PostgreSQL uses for text search. The pattern above uses a generated column that always reflects title || ' ' || body — no triggers needed, no risk of forgetting to update.

To run:

$ psql -f source/full-text-search.sql postgres
DROP TABLE
CREATE TABLE
INSERT 0 4
                              v
---------------------------------------------------------------
 'built':9 'database':5 'full-text':7 'postgresql':1 'powerful':3 'relat':4 'search':8
(1 row)
 ...

Common pitfalls:

• Calling to_tsvector('english', ...) in the WHERE clause without an expression index defeats indexing. Either index the expression or use a generated column.
• Choosing the wrong language: to_tsvector('english', russian_text) produces meaningless stems. Match the configuration to the data.
• LIKE '%foo%' is not full-text search. It is substring matching, slow without trigram indexes, and does not understand stemming or stop words. Use full-text search for natural-language queries.
• For multi-language data, you may need to store the language alongside the text and parameterize the configuration.

Tip: For substring or fuzzy matching that does not need stemming, the pg_trgm extension provides trigram-based similarity (similarity(a, b)) and an index that supports LIKE '%foo%' efficiently. Often used alongside full-text search.

Try it: Add more documents and search for them. Use <-> for phrase queries: to_tsquery('english', 'full <-> text') matches "full text" specifically. Add ts_headline('english', body, query) to highlight the matching snippet of each result.

Source: full-text-search.sql

Next: VACUUM and ANALYZE

Home: Postgres by Example