Full-Text Search

LIKE '%word%' compares characters. It cannot rank results, it finds letters hiding inside other words, it does not know that runs and running are forms of the same word, and without a special index it reads every row. PostgreSQL has a built-in answer: full-text search. A document is reduced to a set of normalized words (a tsvector), a search is a set of words joined by logic (a tsquery), and the @@ operator asks whether the query matches the document.

Everything below was run on PostgreSQL 18.6 (postgres:18.6, aarch64, Docker Desktop on a Mac, September 2026). The sql fences run top to bottom in one empty database, the outputs are pasted from psql, and the timings are single runs from this Mac.

The dataset

Eight short articles, small enough to read every result but with enough overlap (running, runner, shoes, trail, waterproof) to make ranking interesting:

-- sql/setup.sql
CREATE TABLE articles (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title text NOT NULL,
  body  text NOT NULL
);
INSERT INTO articles (title, body) VALUES
  ('Trail running shoes',          'Lightweight trail shoes grip wet rock and mud. A good runner rotates two pairs of shoes.'),
  ('Marathon training plan',       'Twelve weeks of running: long runs on Sunday, easy runs midweek. The runner who ran the most miles improved fastest.'),
  ('Caring for leather boots',     'Clean leather boots after each hike, then condition the leather. Waterproofing wax protects the stitching.'),
  ('Waterproof jackets compared',  'We tested five waterproof jackets in heavy rain. The best jacket kept us dry and never wetted out.'),
  ('Stretching after a run',       'A short stretching routine after a run reduces stiffness. Runners often skip stretching.'),
  ('Choosing trail food',          'Nuts, dried fruit and cheese travel well. Avoid heavy food before a long run.'),
  ('Rain gear for cyclists',       'Cyclists need waterproof shoe covers and a rain jacket that breathes.'),
  ('Shoe care',                    'Wash muddy shoes with a soft brush. Never machine-dry running shoes.');

Two new types

A tsvector is a sorted list of lexemes (normalized words) with their positions. Producing one drops stop words such as “the” and “were”, lowercases, and stems: it reduces each word to a root using the rules of a text search configuration. SHOW default_text_search_config tells you which one is the default here:

-- sql/tsvector.sql
SHOW default_text_search_config;
SELECT to_tsvector('english', 'The quick brown foxes were running quickly');
SELECT to_tsvector('english', title) FROM articles WHERE id IN (1, 5) ORDER BY id;

 default_text_search_config
----------------------------
 pg_catalog.english
(1 row)

              to_tsvector
---------------------------------------
 'brown':3 'fox':4 'quick':2,7 'run':6
(1 row)

        to_tsvector
----------------------------
 'run':2 'shoe':3 'trail':1
 'run':4 'stretch':1
(2 rows)

Note the default is pg_catalog.english on this server. initdb derives it from the locale of the cluster (under the C locale it would be simple, which does no stemming and drops no stop words), so always pass the configuration explicitly, as every query in this lesson does (docs). In the first line, “quick” and “quickly” both became quick (positions 2 and 7), “foxes” became fox, “running” became run.

Stemming joins forms that follow rules, and only those. running matches run, but the irregular past tense ran is a different lexeme, and mice does not become mouse. The dictionaries are rule-based; they are not a thesaurus:

-- sql/stemming.sql
-- stemming maps different forms to one lexeme; irregular forms are NOT joined
SELECT to_tsvector('english', 'I ran home')                               AS ran_home,
       to_tsvector('english', 'I ran home') @@ plainto_tsquery('english', 'running') AS running_matches_ran,
       to_tsvector('english', 'I run home') @@ plainto_tsquery('english', 'running') AS running_matches_run,
       to_tsvector('english', 'mice geese')                               AS irregular;

     ran_home     | running_matches_ran | running_matches_run |     irregular
------------------+---------------------+---------------------+-------------------
 'home':3 'ran':2 | f                   | t                   | 'gees':2 'mice':1
(1 row)

Building a query

A tsquery is a set of lexemes with & (and), | (or), ! (not) and <-> (followed by). PostgreSQL has four constructors, from strictest to friendliest:

-- sql/tsquery-forms.sql
SELECT plainto_tsquery('english', 'trail running shoes')            AS plain,
       to_tsquery('english', 'trail & (shoes | boots) & !leather')  AS operators,
       phraseto_tsquery('english', 'trail running shoes')           AS phrase,
       websearch_to_tsquery('english', '"trail shoes" -leather')    AS websearch;

          plain           |                 operators                  |            phrase            |            websearch
--------------------------+--------------------------------------------+------------------------------+---------------------------------
 'trail' & 'run' & 'shoe' | 'trail' & ( 'shoe' | 'boot' ) & !'leather' | 'trail' <-> 'run' <-> 'shoe' | 'trail' <-> 'shoe' & !'leather'
(1 row)

plainto_tsquery ANDs every word. to_tsquery takes operator syntax and is strict about it (a bare space is a syntax error, below). phraseto_tsquery requires the words in order. websearch_to_tsquery reads what users actually type: quotes make a phrase, or is OR, - is NOT, and it never raises a syntax error on user input, which is why it is the one to put behind a search box.

-- @expect-error 42601
SELECT to_tsquery('english', 'trail shoes');

ERROR:  syntax error in tsquery: "trail shoes"

Matching with the @@ operator

tsvector @@ tsquery is true when the document matches. Because runs and run and running share the stem run, a search for “runs” finds all five articles that use any of those forms:

-- sql/match.sql
SELECT id, title
FROM articles
WHERE to_tsvector('english', title || ' ' || body) @@ plainto_tsquery('english', 'runs')
ORDER BY id;

 id |         title
----+------------------------
  1 | Trail running shoes
  2 | Marathon training plan
  5 | Stretching after a run
  6 | Choosing trail food
  8 | Shoe care
(5 rows)

Compare with LIKE, which matches characters, not words. It finds the literal runs (one article) and, worse, finds letters inside unrelated words:

-- sql/like-contrast.sql
-- LIKE compares characters: it finds the literal "runs" but not run / running
SELECT id, title FROM articles WHERE title || ' ' || body ILIKE '%runs%' ORDER BY id;
-- and it finds letters inside other words
SELECT 'a lovely brunch' ILIKE '%run%' AS like_says,
       to_tsvector('english', 'a lovely brunch') @@ plainto_tsquery('english', 'run') AS fts_says;

 id |         title
----+------------------------
  2 | Marathon training plan
(1 row)

 like_says | fts_says
-----------+----------
 t         | f
(1 row)

Ranking results

A match is not a good match. ts_rank scores by term frequency; ts_rank_cd also rewards words that appear close together (cover density). To make a title hit count more than a body hit, weight the parts with setweight (A is heaviest; the defaults are 1.0 for A, 0.4 for B, 0.2 for C and 0.1 for D). Search “trail or shoes”:

-- sql/ranking.sql
SELECT id, title,
       round(ts_rank(setweight(to_tsvector('english', title), 'A') || setweight(to_tsvector('english', body), 'B'),
                     websearch_to_tsquery('english', 'trail or shoes'))::numeric, 4) AS rank,
       round(ts_rank_cd(setweight(to_tsvector('english', title), 'A') || setweight(to_tsvector('english', body), 'B'),
                        websearch_to_tsquery('english', 'trail or shoes'))::numeric, 4) AS rank_cd
FROM articles
WHERE (setweight(to_tsvector('english', title), 'A') || setweight(to_tsvector('english', body), 'B'))
      @@ websearch_to_tsquery('english', 'trail or shoes')
ORDER BY rank DESC, id;

 id |         title          |  rank  | rank_cd
----+------------------------+--------+---------
  1 | Trail running shoes    | 0.6822 |  3.2000
  8 | Shoe care              | 0.3479 |  1.8000
  6 | Choosing trail food    | 0.3040 |  1.0000
  7 | Rain gear for cyclists | 0.1216 |  0.4000
(4 rows)

“Trail running shoes” wins because both words are in its title (weight A). “Shoe care” beats “Choosing trail food” (0.3479 against 0.3040): shoe occurs three times in it (once in the title, twice in the body) while trail occurs once in the other. ts_headline builds a snippet with the matched words marked, for the results page:

-- sql/headline.sql
SELECT id, ts_headline('english', body, websearch_to_tsquery('english', 'trail or shoes'),
                       'StartSel=<b>, StopSel=</b>, MaxWords=12, MinWords=5') AS snippet
FROM articles
WHERE to_tsvector('english', body) @@ websearch_to_tsquery('english', 'trail or shoes')
ORDER BY id;

 id |                 snippet
----+-----------------------------------------
  1 | <b>trail</b> <b>shoes</b> grip wet rock
  7 | <b>shoe</b> covers and a rain
  8 | <b>shoes</b> with a soft brush
(3 rows)

Indexing the search

Recomputing to_tsvector for every row on every search is slow. Two designs avoid that. The clean one is a generated column holding the tsvector, plus a GIN index on it. The column is kept in sync by PostgreSQL itself, and the query is short:

-- sql/generated-column.sql
ALTER TABLE articles ADD COLUMN search tsvector
  GENERATED ALWAYS AS (setweight(to_tsvector('english', title), 'A') || setweight(to_tsvector('english', body), 'B')) STORED;
CREATE INDEX articles_search_idx ON articles USING gin (search);
SELECT id, title FROM articles WHERE search @@ websearch_to_tsquery('english', 'trail shoes -leather') ORDER BY id;

ALTER TABLE
CREATE INDEX
 id |        title
----+---------------------
  1 | Trail running shoes
(1 row)

The same query with parameters, from each driver (the same websearch_to_tsquery text goes in as a bind value, so it is safe to expose to user input):

-- sql/param-search.sql
SELECT title
FROM articles
WHERE search @@ websearch_to_tsquery('english', $1)
ORDER BY ts_rank(search, websearch_to_tsquery('english', $1)) DESC, id
\bind 'trail or shoes -leather' \g

psql

-- sql/param-search.sql
SELECT title
FROM articles
WHERE search @@ websearch_to_tsquery('english', $1)
ORDER BY ts_rank(search, websearch_to_tsquery('english', $1)) DESC, id
\bind 'trail or shoes -leather' \g

Node.js

const res = await pool.query(
  `SELECT title
   FROM articles
   WHERE search @@ websearch_to_tsquery('english', $1)
   ORDER BY ts_rank(search, websearch_to_tsquery('english', $1)) DESC, id`,
  ['trail or shoes -leather'],
);

Python

cur.execute(
    """SELECT title
       FROM articles
       WHERE search @@ websearch_to_tsquery('english', %s)
       ORDER BY ts_rank(search, websearch_to_tsquery('english', %s)) DESC, id""",
    ("trail or shoes -leather",) * 2,
)
rows = cur.fetchall()

Go

rows, err := conn.Query(ctx,
    `SELECT title
     FROM articles
     WHERE search @@ websearch_to_tsquery('english', $1)
     ORDER BY ts_rank(search, websearch_to_tsquery('english', $1)) DESC, id`,
    "trail or shoes -leather")
defer rows.Close()

Rust

let rows = sqlx::query(
    "SELECT title
     FROM articles
     WHERE search @@ websearch_to_tsquery('english', $1)
     ORDER BY ts_rank(search, websearch_to_tsquery('english', $1)) DESC, id",
)
.bind("trail or shoes -leather")
.fetch_all(&pool)
.await?;

         title
------------------------
 Trail running shoes
 Shoe care
 Choosing trail food
 Rain gear for cyclists
(4 rows)

$ (each snippet run once against the articles above; multi-line driver output joined onto one line)
Node.js (pg 8.23.0):    [ 'Trail running shoes', 'Shoe care', 'Choosing trail food', 'Rain gear for cyclists' ]
Python (psycopg 3.3.6): ['Trail running shoes', 'Shoe care', 'Choosing trail food', 'Rain gear for cyclists']
Go (pgx v5.11.0):       Trail running shoes / Shoe care / Choosing trail food / Rain gear for cyclists
Rust (sqlx 0.9.0):      Trail running shoes / Shoe care / Choosing trail food / Rain gear for cyclists

Note the column is STORED. PostgreSQL 18 makes VIRTUAL the default for generated columns, but a virtual column is computed on read and cannot be indexed:

-- @expect-error 0A000
CREATE TABLE virtual_demo (
  id   int,
  body text,
  doc  tsvector GENERATED ALWAYS AS (to_tsvector('english', body)) VIRTUAL
);
CREATE INDEX ON virtual_demo USING gin (doc);

CREATE TABLE
ERROR:  indexes on virtual generated columns are not supported

What the index buys

On 100 000 articles of 30 words each (drawn from a 3 000-word vocabulary) the same search, before and after the GIN index:

-- sql/big-articles.sql
CREATE TABLE big_articles (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title  text NOT NULL,
  body   text NOT NULL
);
-- 100 000 articles of 30 words each, drawn from a vocabulary of 3 000 words (w0 ... w2999)
INSERT INTO big_articles (title, body)
SELECT 'Article ' || g,
       (SELECT string_agg('w' || (('x' || substr(md5(g::text || ':' || i::text), 1, 6))::bit(24)::int % 3000), ' ')
        FROM generate_series(1, 30) AS i)
FROM generate_series(1, 100000) AS g;
ALTER TABLE big_articles ADD COLUMN search tsvector GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;
ANALYZE big_articles;
SELECT count(*) AS rows, pg_size_pretty(pg_relation_size('big_articles')) AS heap FROM big_articles;

CREATE TABLE
INSERT 0 100000
ALTER TABLE
ANALYZE
  rows  | heap
--------+-------
 100000 | 66 MB
(1 row)

-- sql/fts-no-index.sql
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF)
SELECT count(*) FROM big_articles WHERE search @@ to_tsquery('english', 'w17 & w42');

                                  QUERY PLAN
-------------------------------------------------------------------------------
 Aggregate (actual time=16.778..16.783 rows=1.00 loops=1)
   ->  Seq Scan on big_articles (actual time=0.905..16.771 rows=15.00 loops=1)
         Filter: (search @@ '''w17'' & ''w42'''::tsquery)
         Rows Removed by Filter: 99985
 Planning Time: 0.303 ms
 Execution Time: 16.803 ms
(6 rows)

-- sql/fts-gin.sql
CREATE INDEX big_articles_search_idx ON big_articles USING gin (search);
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF)
SELECT count(*) FROM big_articles WHERE search @@ to_tsquery('english', 'w17 & w42');
SELECT pg_size_pretty(pg_relation_size('big_articles_search_idx')) AS gin_size;

CREATE INDEX
                                               QUERY PLAN
--------------------------------------------------------------------------------------------------------
 Aggregate (actual time=0.258..0.261 rows=1.00 loops=1)
   ->  Bitmap Heap Scan on big_articles (actual time=0.156..0.255 rows=15.00 loops=1)
         Recheck Cond: (search @@ '''w17'' & ''w42'''::tsquery)
         Heap Blocks: exact=15
         ->  Bitmap Index Scan on big_articles_search_idx (actual time=0.076..0.076 rows=15.00 loops=1)
               Index Cond: (search @@ '''w17'' & ''w42'''::tsquery)
               Index Searches: 1
 Planning Time: 0.330 ms
 Execution Time: 0.277 ms
(9 rows)

 gin_size
----------
 15 MB
(1 row)

16.8 ms became 0.28 ms for 15 matching articles; the price is a 15 MB GIN index on a 66 MB table. The index gets bigger with the vocabulary and the text volume, and it slows inserts and updates, as any index does.

A contrast: trigrams and accents

Full-text search matches whole, correctly spelled words. A typo (runing) stems to a different lexeme and finds nothing. For fuzzy matching, the pg_trgm extension compares three-letter fragments; and unaccent strips accents so café matches cafe (pg_trgm, unaccent):

-- sql/trigram.sql
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS unaccent;
SELECT similarity('runing', 'running') AS typo_score,
       'runing' % 'running'            AS typo_matches,
       to_tsvector('english', 'runing') @@ plainto_tsquery('english', 'running') AS fts_matches_typo,
       unaccent('caf' || chr(233) || ' cr' || chr(232) || 'me') AS unaccented;

CREATE EXTENSION
CREATE EXTENSION
 typo_score | typo_matches | fts_matches_typo | unaccented
------------+--------------+------------------+------------
  0.6666667 | t            | f                | cafe creme
(1 row)

The typo scores 0.67 similarity and matches with the % operator, while full-text search finds nothing. Use pg_trgm for “did you mean” and autocomplete, full-text search for relevance-ranked prose.

Reading AI-generated code

An assistant is asked to make article search fast. It writes an expression index on to_tsvector('english', body) and a search query, with a comment saying the index handles it:

🐛 Spot the bug

-- AI comment: "GIN index on the article text - search is now fast"
CREATE INDEX big_articles_body_gin ON big_articles USING gin (to_tsvector('english', body));

SELECT id, title FROM big_articles
WHERE to_tsvector(body) @@ to_tsquery('w17 & w42');

Show the answer

The index and the query use different expressions: the index is on to_tsvector('english', body), the query calls the one-argument to_tsvector(body), which uses default_text_search_config. To the planner these are different functions, so the index cannot be used, and the query computes a tsvector for every row: 573 ms for 100 000 short rows.

-- sql/stb-wrong.sql
CREATE INDEX big_articles_body_gin ON big_articles USING gin (to_tsvector('english', body));
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF)
SELECT id, title FROM big_articles
WHERE to_tsvector(body) @@ to_tsquery('w17 & w42');

CREATE INDEX
                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Gather (actual time=48.420..573.384 rows=15.00 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on big_articles (actual time=37.433..570.271 rows=5.00 loops=3)
         Filter: (to_tsvector(body) @@ to_tsquery('w17 & w42'::text))
         Rows Removed by Filter: 33328
 Planning Time: 0.258 ms
 Execution Time: 573.417 ms
(8 rows)

The fix is to repeat the expression exactly, with the configuration spelled out in both places. The search drops from 573 ms to 0.16 ms. (The generated-column design above avoids this class of bug: one column, one expression, written once.)

-- sql/stb-fixed.sql
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF)
SELECT id, title FROM big_articles
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'w17 & w42');

                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on big_articles (actual time=0.105..0.135 rows=15.00 loops=1)
   Recheck Cond: (to_tsvector('english'::regconfig, body) @@ '''w17'' & ''w42'''::tsquery)
   Heap Blocks: exact=15
   ->  Bitmap Index Scan on big_articles_body_gin (actual time=0.096..0.096 rows=15.00 loops=1)
         Index Cond: (to_tsvector('english'::regconfig, body) @@ '''w17'' & ''w42'''::tsquery)
         Index Searches: 1
 Planning Time: 0.331 ms
 Execution Time: 0.155 ms
(8 rows)

Mental Model

Full-text search is a pipeline: parse the text into tokens, throw away stop words, stem the rest with a dictionary, and store lexemes with positions (tsvector); do the same to the user’s query, joined by operators (tsquery). @@ is set logic over lexemes, not string matching, so it is fast to index (GIN maps each lexeme to its documents) and blind to anything the dictionaries do not connect, such as irregular forms and typos. Ranking is a separate step over the matches.

Tips / gotchas

• Always pass the configuration (to_tsvector('english', ...)); never depend on default_text_search_config.
• For search boxes use websearch_to_tsquery: it never raises syntax errors on user input.
• Index a stored generated tsvector column with GIN. A virtual generated column cannot be indexed.
• An expression index is used only when the query repeats the same expression, configuration included.
• Weight title over body with setweight; rank with ts_rank or ts_rank_cd and order by it.
• Stemming is rule-based: ran is not run; typos need pg_trgm.

🐘 Search is a feature, not just a query

@@ answers “does this match?”. A real search box also needs ranking, snippets, typo tolerance, filters and pagination. PostgreSQL gives you the building blocks in one database with transactional consistency; a dedicated search engine is worth its operational cost only when you need much more than that.

Trade-offs

Option	Benefit	Cost
LIKE '%x%' / ILIKE	No setup, exact characters	Seq Scan unless trigram-indexed; no stemming, ranking or word boundaries
Full-text search + GIN	Word-aware, ranked, 16.8 ms to 0.28 ms on 100 000 rows	15 MB index; stemming rules, no typo tolerance
pg_trgm	Typo tolerance, arbitrary substrings	Larger index; similarity, not language
Stored generated tsvector	One expression, always in sync, indexable	Extra disk for the column; writes recompute it
Expression index	No extra column	Queries must repeat the exact expression (573 ms when they did not)

Common Misconceptions

• “Full-text search understands language.” It applies dictionaries: running matches run, ran does not.
• “FTS is just a faster LIKE.” It matches lexemes, ranks, and cannot find substrings like %un%.
• “The default config is simple.” It is english here (derived from the locale); simple under C.
• “The index handles the query if the column matches.” The whole expression must match, configuration included.
• “I can index a virtual generated column.” PostgreSQL 18 refuses: indexes on virtual generated columns are not supported.

Which operator tests whether a tsvector matches a tsquery?

= @@ ~~ @>

Which function is the safest choice for parsing raw text a user typed into a search box?

to_tsquery plainto_tsquery only websearch_to_tsquery to_tsvector

Does to_tsvector('english', 'I ran home') @@ plainto_tsquery('english', 'running') match?

Yes, ran and running share a root No: stemming is rule-based and ran is a different lexeme from run Only with the simple configuration It raises an error

Why does a search using to_tsvector(body) ignore an index built on to_tsvector('english', body)?

GIN cannot index text The two expressions are different functions to the planner, so the index does not match The index was not analyzed to_tsvector cannot be indexed

You define doc tsvector GENERATED ALWAYS AS (...) VIRTUAL and try to create a GIN index on it. What happens?

It works and is faster PostgreSQL 18 refuses: indexes on virtual generated columns are not supported; use STORED The index is created but ignored The column is converted to STORED automatically

Submit

Mark as complete