Full-Text Search Without Elasticsearch

~14 min · extensions, fts

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

tsvector and tsquery

PostgreSQL ships a complete full-text search engine. tsvector stores normalised, stemmed lexemes; tsquery represents the search query. The match operator @@ tests whether a tsvector matches a tsquery. With a GIN index, this is millisecond search across millions of documents.

The pieces

to_tsvector('english', text) — convert text to a normalised vector.
to_tsquery('english', 'postgres & performance') — parse a query.
plainto_tsquery / websearch_to_tsquery — friendlier query parsers.
ts_rank / ts_rank_cd — relevance scoring.
ts_headline — generate a snippet with matched terms highlighted.

Storing the tsvector

For performance, materialise the tsvector as a generated column and index it with GIN. You compute the conversion once on write and reuse it on every search.

Code

Set up full-text search·sql

CREATE TABLE articles (
    id        INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title     TEXT NOT NULL,
    body      TEXT NOT NULL,
    tsv       tsvector GENERATED ALWAYS AS (
                to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''))
              ) STORED,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX articles_tsv_gin ON articles USING gin (tsv);

Search + rank + snippet·sql

WITH q AS (SELECT websearch_to_tsquery('english', 'postgres performance') AS query)
SELECT id, title,
       ts_rank(tsv, q.query)                            AS score,
       ts_headline('english', body, q.query,
                   'StartSel=<mark>, StopSel=</mark>')   AS snippet
FROM   articles, q
WHERE  tsv @@ q.query
ORDER  BY score DESC
LIMIT  10;

External links

PostgreSQL Full-Text Search

Exercise

Add a generated tsvector column + GIN index to a TEXT-heavy table. Run a full-text search with ranking and a highlighted snippet. Compare against a naive ILIKE '%term%' query — observe the speed difference.