Full-text search in PostgreSQL — tsvector, GIN, russian

← Back to the section

When you need to search over text, the first impulse is to reach for Elasticsearch. But PostgreSQL searches text out of the box, without a separate service. Let's look at how it works and when it's good enough.

Why plain LIKE doesn't work

The most obvious approach is WHERE body LIKE '%buyer%'. It has two drawbacks.

First — speed. When searching with a leading %, PostgreSQL can't use a regular index and has to scan every row. On a thousand records it's unnoticeable; on a million it's a disaster.

Second — grammar. The query LIKE '%buyers%' won't find the row «the buyer chose a product», because the word form is different. Users type words in various forms, and LIKE doesn't account for that.

Full-text search solves both problems: it works through an index and understands stemming — reducing words to their root.

tsvector and tsquery — two key types

PostgreSQL stores processed text in the tsvector type: not text, but a list of lexemes — words reduced to their root form, with positions.

live example

SELECT to_tsvector('english', 'Buyers choose products in the catalog');
-- 'buyer':1 'catalog':6 'choos':2 'product':3

«Buyers» became «buyer» and «choose» became «choos» — the root that any form of the word maps to. The stop-words «in» and «the» are dropped as insignificant.

A search query is stored in the tsquery type:

live example

SELECT to_tsquery('english', 'buyer & catalog');
-- 'buyer' & 'catalog'

Match checking is done with the @@ operator:

live example

SELECT to_tsvector('english', 'Buyers choose products in the catalog')
    @@ to_tsquery('english', 'buyer & catalog');
-- true

The same mechanics are visible without a database. The stemmer here is a toy, three rules instead of a dictionary; the rest works the same way — text splits into stems, the stem is the key, the value is a list of documents.

live example

import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class SearchIndexDemo {

    private static final List<String> ENDINGS = List.of("ing", "ed", "es", "s");

    static String stem(String word) {
        String w = word.toLowerCase().replaceAll("[^a-z]", "");
        for (String end : ENDINGS) {
            if (w.endsWith(end) && w.length() - end.length() >= 4) {
                return w.substring(0, w.length() - end.length());
            }
        }
        return w;
    }

    public static void main(String[] args) {
        List<String> docs = List.of(
                "Buyers choose products in the catalog",
                "The refund returned money for a product",
                "Catalog updates run nightly");

        Map<String, List<Integer>> index = new LinkedHashMap<>();
        for (int id = 1; id <= docs.size(); id++) {
            for (String word : docs.get(id - 1).split(" ")) {
                index.computeIfAbsent(stem(word), k -> new ArrayList<>()).add(id);
            }
        }

        String query = "catalogs";
        long like = docs.stream().filter(d -> d.toLowerCase().contains(query)).count();
        System.out.println("LIKE '%" + query + "%' found documents: " + like);

        String lexeme = stem(query);
        System.out.println("query stem: " + lexeme);
        System.out.println("in the index: " + lexeme + " -> " + index.get(lexeme));
        for (int id : index.getOrDefault(lexeme, List.of())) {
            System.out.println("  found: " + docs.get(id - 1));
        }
    }
}

LIKE finds nothing, the stem lookup finds two documents out of three. In PostgreSQL that map is the GIN index, and stem is the configuration dictionaries.

How to store tsvector in a table

Computing to_tsvector() on every query is slow and index-less. Store the vector separately and index it instead.

Generated column (PostgreSQL 12+)

CREATE TABLE products (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title       text NOT NULL,
    body        text NOT NULL,
    search  tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(body, '')), 'B')
    ) STORED
);

CREATE INDEX ix_products_search ON products USING gin (search);

GENERATED ALWAYS AS ... STORED means PostgreSQL recomputes the column on every INSERT and UPDATE — no manual work.

setweight('A') marks tokens from the title as more important — this affects ranking. Weights A, B, C, D are available, from highest to lowest.

Trigger (PostgreSQL before 12)

CREATE TRIGGER products_search_update
BEFORE INSERT OR UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION
tsvector_update_trigger(search, 'pg_catalog.russian', title, description);

How to search

A basic query with ranking:

live example

SELECT id, title, ts_rank(search, q) AS rank
FROM products, to_tsquery('russian', 'наушники & беспроводной') q
WHERE search @@ q
ORDER BY rank DESC
LIMIT 20;

ts_rank returns a number — the higher, the better the match. On an equal number of matches, documents whose hits are in high-weight fields rank higher (A > B > C > D).

plainto_tsquery — for user input

to_tsquery requires proper syntax with &, |, !. Arbitrary user input landing there may fail the query with an error.

For user input use plainto_tsquery — it joins the words with AND, no special syntax:

live example

SELECT plainto_tsquery('english', 'buy a new product');
-- 'buy' & 'new' & 'product'

websearch_to_tsquery — Google-style

Available since PostgreSQL 11. It understands a minus sign for excluding words and quotes for phrase search:

live example

SELECT websearch_to_tsquery('english', 'buyer -child "new catalog"');
-- 'buyer' & !'child' & 'new' <-> 'catalog'

The minus became the ! negation, the quotes the <-> operator: the words must stand next to each other in that order.

Stemming configuration

PostgreSQL ships with several full-text search configurations:

live example

SELECT cfgname FROM pg_ts_config;
-- simple, english, russian, german, ...

• english — English-language stemming. «buy», «buys», «buying» become one token, and search finds all forms.
• simple — lowercasing only, no stemming applied. Useful for codes, identifiers, SKUs.

For English-language content you almost always want the english configuration.

Synonyms

To make a search for «postgres» also find «postgresql» and «pg», create a synonym dictionary:

CREATE TEXT SEARCH DICTIONARY my_synonyms (
    template = synonym,
    synonyms = 'my_synonyms'
);

CREATE TEXT SEARCH CONFIGURATION en_extended (COPY = english);
ALTER TEXT SEARCH CONFIGURATION en_extended
    ALTER MAPPING FOR word, asciiword
    WITH my_synonyms, english_stem;

In the file $SHAREDIR/tsearch_data/my_synonyms.syn:

postgresql postgres
pg postgres
postgres postgres

Two words per line: what was met and what to replace it with. The third line is not a typo — without it english_stem turns postgres into postgr and the three spellings stop matching.

GIN vs GiST

Two index types are suitable for full-text search:

In the vast majority of cases GIN is the choice — it's faster on search, and that usually matters more.

GIN accumulates changes in a buffer and flushes them in batches (fastupdate = on by default). With rare inserts and frequent reads the buffer can be turned off:

ALTER INDEX ix_products_search SET (fastupdate = off);

Match highlighting

ts_headline highlights the found words directly in the text:

live example

SELECT
    id,
    title,
    ts_headline('russian', description, q,
        'StartSel=<mark>, StopSel=</mark>, MaxFragments=2, MaxWords=20')
        AS snippet
FROM products, websearch_to_tsquery('russian', 'наушники') q
WHERE search @@ q
ORDER BY ts_rank(search, q) DESC
LIMIT 20;

MaxFragments=2 — show no more than two fragments; MaxWords=20 — the length of each fragment in words.

pg_trgm — fuzzy search and substrings

Full-text search won't help if you need to:

• find a product by part of its SKU (LIKE '%ABC%'),
• or correct a typo in a name (for example, «Robertsen» instead of «Robertson»).

For that there's pg_trgm: it breaks a string into three-letter groups (trigrams) and indexes them:

CREATE EXTENSION pg_trgm;

CREATE INDEX ix_customer_name_trgm
    ON customer USING gin (full_name gin_trgm_ops);

-- substring search with an index
SELECT * FROM customer WHERE full_name ILIKE '%robert%';

-- typo tolerance: % takes its threshold from pg_trgm.similarity_threshold
SELECT * FROM customer
WHERE full_name % 'robertsen'
ORDER BY similarity(full_name, 'robertsen') DESC
LIMIT 10;

One subtlety: the index works with the % operator, not with the function. WHERE similarity(full_name, 'robertsen') > 0.4 is computed for every row of the table — the function is fine for ordering, leave filtering to the operator.

A good combination: FTS for long text (article body, description), pg_trgm for short fields with typos (names, brands, codes). Past 100 characters pg_trgm loses its value — FTS is better there.

Pagination via keyset

OFFSET + LIMIT is slow on deep pages: the database collects the matches, scores every one of them, sorts them all — and then throws away the first few hundred to hand you page twenty.

Keyset helps less here than in ordinary queries: there is no index over relevance, it is computed on the fly, so everything still has to be scored and sorted. The gain is elsewhere — the database doesn't collect the discarded rows in memory, and that shows on deep pages. The only radical cure is a limit: don't let anyone go past a few pages, the way search engines do.

Pagination itself goes by the (rank, id) pair:

live example

SELECT id, title, ts_rank(search, q) AS rank
FROM products, websearch_to_tsquery('russian', 'коврик') q
WHERE search @@ q
  AND (ts_rank(search, q), id) < (1.0, 'prod-99')
ORDER BY rank DESC, id DESC
LIMIT 20;

$prev_rank and $prev_id are the values of the last record from the previous page.

When PostgreSQL FTS is enough, and when you need Elasticsearch

PostgreSQL FTS handles articles, products, comments, and tickets well — up to about 10 million documents and 100 queries per second.

Elasticsearch is worth considering if you need:

• far more than 10 million documents with complex ranking,
• automatic language detection in multilingual content,
• facets, aggregations, analytics over search results,
• sophisticated typo tolerance.

In short

• PostgreSQL FTS works through an index and understands grammar — unlike LIKE.
• tsvector is the indexable form of the text, tsquery the search query, @@ the match operator; a language configuration collapses word forms into one stem.
• Store the vector in a generated column (GENERATED ALWAYS AS ... STORED) indexed with GIN; setweight makes the title outweigh the body.
• Parse user input with plainto_tsquery or websearch_to_tsquery: to_tsquery fails on special characters.
• ts_headline highlights matches, pg_trgm covers typos and substrings on short fields.
• Up to ~10M documents and ~100 queries per second PostgreSQL is enough; beyond that — Elasticsearch.

What to read next

• Weights in full-text search (setweight, ts_rank) — how ranking is actually computed.
• Search: PostgreSQL FTS or Elasticsearch — a detailed comparison and the criteria for choosing.
• Index types in PostgreSQL — GIN, GiST, and others in detail.
• JSONB in PostgreSQL — the GIN index works on jsonb too.