Search without a search engine: full-text search in Postgres with tsvector, GIN and websearch_to_tsquery
Add fast, ranked, highlighted search to a Postgres table: tsvector, a generated column, a GIN index, websearch_to_tsquery, ts_rank and ts_headline.

Most apps need search before they need a search engine. A help centre, a list of notes, a catalogue of a few hundred thousand rows: users type a few words and expect the right rows near the top. Postgres has had a built-in answer for this for a long time, and it is small enough to set up in an afternoon.
This post builds the whole thing: a searchable table, an index that keeps it fast, a query that accepts what people really type, ranking, and highlighted snippets.
Why LIKE is not enough
WHERE body ILIKE '%invoice%' finds the letters, not the meaning. It will not match "invoices" when you search "invoice", it cannot rank results, and a leading wildcard cannot use a normal B-tree index, so it reads the whole table.
Full-text search fixes this by turning text into lexemes: lowercased, stripped of common words like "the", and reduced to a root form ("running" and "runs" both become "run"). Matching then happens on lexemes, not raw characters.
Two types do the work:
tsvector: a document turned into sorted lexemes with positions.tsquery: a search turned into lexemes joined by operators (&and,|or,!not,<->followed by).
The @@ operator asks "does this document match this query?"
SELECT to_tsvector('english', 'The quick brown foxes were running')
@@ to_tsquery('english', 'fox & run');
-- true
The 'english' argument is the text search configuration. It decides which words are dropped and how stemming works. Pick the one that matches your content.
A table to search
Here is a small articles table:
CREATE TABLE articles (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL,
body text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
Without any index, this works but scans every row:
SELECT title
FROM articles
WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('english', 'invoice');
Fine for a thousand rows, painful for a million.
Make it fast: a generated column and a GIN index
The Postgres documentation describes two ways to index text search. You can build an index directly on the to_tsvector(...) expression, or you can keep the vector in its own stored column. The stored column costs extra disk space but keeps your queries simple and independent of the server's default configuration, so I prefer it.
ALTER TABLE articles
ADD COLUMN search tsvector
GENERATED ALWAYS AS (
setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
setweight(to_tsvector('english', coalesce(body, '')), 'B')
) STORED;
CREATE INDEX articles_search_idx ON articles USING GIN (search);
Three details are worth noticing.
coalescekeeps one empty field from turning the whole vector intoNULL.setweightlabels lexemes from the title asAand from the body asB. Ranking can then treat a title hit as more important.- GIN is the usual index type here. It maps each lexeme to the rows that contain it, which is exactly the lookup a search needs. If you have read our post on JSONB and GIN indexes, the idea is the same.
Note
If you choose the expression-index route instead, the docs are firm about one rule: use the two-argument form of
to_tsvectorwith an explicit configuration name. The index is only used by queries that call it with the same configuration.
Because the column is generated, you never update it yourself. Insert or update title or body and Postgres recomputes it.
Accept what people actually type
to_tsquery is strict. It wants operators in the right places and raises a syntax error on input like invoice &. You do not want your search box to throw errors.
websearch_to_tsquery is built for raw user input and, per the documentation, never raises syntax errors. It understands the habits people already have from web search:
| User types | Meaning |
|---|---|
late invoice |
both words (AND) |
"late invoice" |
the words next to each other, in order |
invoice or receipt |
either word |
invoice -draft |
invoice, but not draft |
SELECT websearch_to_tsquery('english', '"late invoice" -draft');
-- 'late' <-> 'invoic' & !'draft'
The stemmed form invoic looks odd, and that is fine: the same stemmer processed the stored text.
Now the search itself:
SELECT id, title
FROM articles
WHERE search @@ websearch_to_tsquery('english', 'late invoice -draft')
LIMIT 20;
Rank the results
A match is not an order. ts_rank scores how well a document matches, using how often the lexemes appear and the weights you set. ts_rank_cd goes further and also considers how close together the matching words are.
SELECT id, title,
ts_rank(search, query) AS rank
FROM articles,
websearch_to_tsquery('english', 'late invoice') AS query
WHERE search @@ query
ORDER BY rank DESC, created_at DESC
LIMIT 20;
The default weights for labels D, C, B and A are 0.1, 0.2, 0.4 and 1.0. With the setup above, a word in the title counts more than twice as much as the same word in the body. You can pass your own array as the first argument if you disagree.
There is a cost to know about. The documentation points out that ranking has to read each matching document's vector, so it is often limited by I/O. If a very common word matches half the table, you will rank half the table. Keep the LIMIT, keep stop words out of the query, and consider asking users for more specific terms when results are huge.
Show why it matched
Search results feel much better with a snippet. ts_headline takes the original text and the query and returns an excerpt with the matches wrapped in markers.
SELECT id, title,
ts_headline('english', body,
websearch_to_tsquery('english', 'late invoice'),
'MaxFragments=2, MaxWords=25, MinWords=10, StartSel=<mark>, StopSel=</mark>')
AS snippet
FROM articles
WHERE search @@ websearch_to_tsquery('english', 'late invoice')
ORDER BY ts_rank(search, websearch_to_tsquery('english', 'late invoice')) DESC
LIMIT 10;
Two cautions from the docs, both important:
ts_headlinereads the original document, not the vector, so it can be slow. Only call it for the handful of rows you will actually show, not for every match. Putting it in a subquery over the already limited result is a good habit.- Its output is not guaranteed to be safe HTML. If the text can contain anything a user typed, strip or sanitize it before it reaches a page.
Warning
Do not put
ts_headlineoutput straight into a web page when the source text is user-written. Sanitize it, or escape the text first and add your own markers afterwards.
Pitfalls checklist
- Wrong configuration. Indexing with
'english'and querying with'simple'will miss matches. Use the same one everywhere. A stored column helps because the choice lives in one place. - Typos. Full-text search matches words, not near-words. For "did you mean" behaviour, look at the
pg_trgmextension alongside it. - Mixed languages. One configuration per language. The docs show choosing the configuration from a column if your rows differ.
- Meaning, not words. If users search with different words than your text uses, lexemes will not help. That is where vector search in Postgres comes in, and combining both is a common design.
- Check the plan. Run
EXPLAIN ANALYZEand confirm you see a bitmap scan on your GIN index. Our guide to reading EXPLAIN ANALYZE covers how.
When to reach for something bigger
A dedicated search engine earns its place when you need heavy faceting, typo tolerance everywhere, per-language analyzers at scale, or search that must be scaled independently from your main database. Until then, one table, one generated column and one index give you search that is transactional with your data: no sync jobs, no stale results, nothing extra to run.
Start with the five steps above, try it on real queries from your users, and add complexity only when those queries show you where it hurts.


