Postgres Full-Text vs Trigram vs Meilisearch for Typo Search

A user types “mourinio” and your search returns nothing, while the same user swears the record exists. Postgres full-text search behaves that way by design, and the fix is usually smaller than adding a search service. This is for teams whose search box sits on a Postgres database and who need to decide whether trigrams are enough or whether a dedicated engine has earned its keep.

Why full-text search returns nothing for a misspelling

Full-text search works on lexemes. Text and query both pass through a dictionary chain that lowercases, drops stop words and stems: rats becomes rat, so “fat rats” finds the row about a fat rat. The dictionaries normalise word forms; they don’t correct spelling, and the Postgres documentation describes no mechanism that would (Controlling text search (opens in a new tab)). One wrong letter produces a different lexeme, and a different lexeme matches nothing.

That limitation aside, it’s a good engine for the queries people actually type. Use websearch_to_tsquery, which accepts OR, - for exclusion and quoted phrases, rather than to_tsquery, which returns a syntax error on input a user could plausibly type. Keep the vector in a generated column with a GIN index, since “GIN indexes are the preferred text search index type” and GiST is lossy: “the index might produce false matches, and it is necessary to check the actual table row to eliminate such false matches” (Text search indexes (opens in a new tab)).

ALTER TABLE players
  ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('simple', coalesce(name, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(club, '')), 'B')
  ) STORED;

CREATE INDEX players_search_idx ON players USING GIN (search_vector);

-- The query a search box should run.
SELECT id, name, club, ts_rank_cd(search_vector, query) AS rank
FROM players, websearch_to_tsquery('english', $1) AS query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;

Two notes on that snippet. Proper nouns go through the simple configuration, because English stemming mangles names. And ranking is not free: “Ranking can be expensive since it requires consulting the tsvector of each matching document, which can be I/O bound and therefore slow”, so cap the result set before you sort a large match list.

Trigrams find the misspelling, and also the wrong row

pg_trgm compares strings by three-character groups: “A trigram is a group of three consecutive characters taken from a string”, each word padded with two leading and one trailing space (pg_trgm (opens in a new tab)). “mourinio” and “mourinho” share most of their trigrams, so similarity is high and the row comes back. Nothing here understands words; it’s string overlap, which is exactly why it survives a typo.

The operator you use decides the threshold. % compares whole strings against pg_trgm.similarity_threshold (default 0.3). <% compares the query against the closest run of words in the target, with pg_trgm.word_similarity_threshold (default 0.6), and <<% forces word boundaries with its own threshold (default 0.5). For a search box over a name column, the word-similarity operators are usually the right pick: a short query against a long title scores poorly on whole-string similarity, and the defaults reflect that.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX players_name_trgm_idx ON players USING GIST (name gist_trgm_ops);

-- %> and <->> are the word-similarity pair: the query against the closest run of words in `name`.
-- Closest first; GiST supports the distance operator, GIN does not.
SELECT id, name, club, word_similarity($1, name) AS score
FROM players
WHERE name %> $1
ORDER BY name <->> $1
LIMIT 10;

Read the index choice carefully. GIN is usually faster for plain matching, but “GIN indexes” cannot efficiently implement distance-based queries such as ORDER BY dist LIMIT 10, which is the query a “did you mean” list actually needs; GiST can, at the cost of lossiness and a siglen parameter (default 12) you may want to raise for longer strings. There’s a second trap: “A pattern with no extractable trigrams will degenerate to a full-index scan”, so two-character queries scan.

The real failure mode is not missing rows, it’s confident wrong ones. Lower the threshold until “mourinio” matches and other names cross the line too, and since trigram similarity has no idea which row is more popular or more recently used, they arrive with the same authority as the right answer. A practitioner write-up that inspired this comparison reported exactly that shape on a set of misspelled footballer names: trigram search found more of them than full-text search, and returned wrong players alongside (one developer’s own run on 1,760 records, dev.to (opens in a new tab)). Treat it as an illustration of the trade-off, not as a measurement of your data.

Combine them before you add a service

The practical Postgres answer is not one or the other. Run full-text search first, and fall back to trigrams only when it returns too few rows:

• exact and stemmed matches come back ranked, with phrase support and field weights;
• the fallback catches the typo, and because it only runs on a near-empty result set, its cost lands on the rare query rather than every query;
• you can label the fallback in the UI (“Showing results for…”), which is honest about what happened and is exactly what a user expects.

Tune the threshold against real queries, not guesses. Pull a week of searches that returned zero rows from your logs, run them through the fallback at 0.3, 0.4 and 0.5, and look at how many return the right row and how much noise arrives with it.

When a dedicated engine earns its keep

Meilisearch and its peers do typo tolerance as a first-class feature rather than a fallback. In Meilisearch it’s on by default, sized by word length: minWordSizeForTypos defaults to oneTypo: 5 and twoTypos: 9, so a five-character term tolerates one typo and a nine-character term two, with per-word and per-attribute exclusions available (Meilisearch specification (opens in a new tab)). That sizing rule is the thing trigram thresholds approximate badly: a single global similarity number treats short and long queries the same, and word length is what actually determines how forgiving matching should be.

The cost is a second system holding a copy of your data: an indexing pipeline, a reindex path when the schema changes, its own memory and its own failure mode where search works but the results are three hours stale. Reach for it when you need typo tolerance plus the things an engine bundles (instant search as you type, faceting, synonyms, per-field ranking rules), or when your search traffic is large enough that keeping it off the primary database matters. Below that, the sync work usually costs more than it returns, and the same money buys a bigger Postgres instance; the VPS versus managed platform teardown covers how quickly a second service changes the bill.

One more consideration if you’re building retrieval for an LLM feature. Keyword search is half of hybrid retrieval: “Dense retrieval adds semantic similarity, so paraphrases can rank together. Sparse retrieval adds weighted term matching for exact words and identifiers” (Qdrant (opens in a new tab)). If your keyword half already lives in Postgres and your corpus is modest, the same full-text plus trigram pair can serve it, and results are merged by position rather than by score, because “Dense similarity is bounded, while BM25’s magnitude depends on how many query terms match and how rare they are in the corpus”.

Start with full-text search and a GIN index, add a word-similarity trigram fallback for empty result sets, and measure both on a hundred real queries from your own logs before you consider a search service. If the fallback’s noise is what pushes you over, that’s a threshold problem; if the missing feature is instant search, faceting or synonyms, that’s an engine problem, and only the second one is worth another moving part.

Sources

• Controlling text search (PostgreSQL) (opens in a new tab)
• Text search indexes (PostgreSQL) (opens in a new tab)
• pg_trgm (PostgreSQL) (opens in a new tab)
• Typo tolerance setting API (Meilisearch specification) (opens in a new tab)
• Hybrid search (Qdrant) (opens in a new tab)
• I misspelt 12 footballers’ names (dev.to) (opens in a new tab)