Beyond LIKE: Fuzzy Search in PostgreSQL with pg_trgm

Most string searches in Postgres start with LIKE '%keyword%'. It’s the first tool out of the box, and it works fine — until a user types ‘Mcdonalds’ instead of ‘McDonald’s’, or remembers half of a product name and gets nothing back.

This post walks through pg_trgm, the extension I reach for whenever LIKE isn’t cutting it but Elasticsearch is overkill. I’ll cover the basics, the operator and function pairs that actually matter in production, and the index decision that trips up a lot of people the first time around.

1. What trigrams actually are

Fuzzy search means matching strings that aren’t quite identical — accounting for typos, casing, missing characters, small variations. Postgres handles this with trigrams via the pg_trgm extension.

A trigram is a three-character slice of a string. The similarity score between two strings is the Jaccard similarity of their trigram sets: |A ∩ B| / |A ∪ B|. Two strings that share lots of three-character chunks score high; two that share none score zero.

Before slicing, pg_trgm does a few things to the input:

1. Lowercases everything (search is case-insensitive)
2. Drops non-alphanumeric characters
3. Pads each word with two leading spaces and one trailing space
4. Slides a 3-character window across, deduplicating

So 'apple' becomes six trigrams:

{"  a", " ap", "app", "ppl", "ple", "le "}

The padding is what makes prefix and suffix matches contribute meaningful trigrams — without it, short strings would barely overlap.

2. Enabling the extension

CREATE EXTENSION IF NOT EXISTS pg_trgm;

Once it’s installed, show_trgm() lets you see exactly how a string gets sliced:

SELECT show_trgm('PostgreSQL');
-- {"  p"," po",esq,gre,"ql ",res,sql,stg,tgr,pos,ost}

Useful for sanity-checking your assumptions when results don’t look right.

3. similarity vs word_similarity (this matters more than you’d think)

pg_trgm ships with two similarity functions, and picking the wrong one will quietly tank your search quality.

similarity() — full strings against full strings

SELECT similarity('Apple iPhone', 'apple ipone');
-- ~0.6

That’s the one most tutorials show. The catch shows up when your query is shorter than what you’re searching against:

SELECT similarity('iphone', 'Apple iPhone 15 Pro Max');
-- ~0.29

The string clearly contains ‘iPhone’. The score is below the default threshold of 0.3. The Jaccard formula puts the union in the denominator, so the longer your target string, the more your score gets diluted.

word_similarity() — short query inside longer text

SELECT word_similarity('iphone', 'Apple iPhone 15 Pro Max');
-- 1.0

word_similarity finds the best-matching extent inside the target string and measures against that. For most real product-name and title searches, this is what you want.

There’s also strict_word_similarity(), which respects word boundaries more aggressively. Worth knowing about for cases where you don’t want partial-word matches.

4. The operators

Each similarity function has a paired operator. The % family returns booleans (for filtering); the <-> family returns distance (for sorting).

Operator	Meaning	Backed by
a % b	Are these similar enough?	similarity
a <% b	Is a similar to some chunk of b?	word_similarity
a <<% b	Is a similar to some word in b?	strict_word_similarity
a <-> b	Distance (1 - similarity)	similarity
a <<-> b	Distance based on word similarity	word_similarity

Filtering looks like this:

SELECT * FROM products
WHERE name % 'iphne';

Sorting by closeness — the autocomplete pattern — looks like this:

SELECT name, name <-> 'Mcdonalds' AS distance
FROM restaurants
ORDER BY distance
LIMIT 5;

That ORDER BY ... <-> ... LIMIT N pattern is KNN search. Remember it. It’s the reason section 7 exists.

5. Tuning the threshold

The cutoff for what counts as “similar enough” with % is a session variable, default 0.3:

SHOW pg_trgm.similarity_threshold;  -- 0.3
SET pg_trgm.similarity_threshold = 0.5;  -- stricter

This is the first knob to reach for when search quality is off.

• Lower threshold → more recall, more noise
• Higher threshold → cleaner results, more misses

The word-similarity variants have their own thresholds (pg_trgm.word_similarity_threshold and pg_trgm.strict_word_similarity_threshold), tunable independently. Worth keeping that in mind if you’re using more than one operator family in the same app.

6. You will need an index

pg_trgm recomputes trigrams per row at query time. Without an index, you’re doing a full table scan with extra steps. On a million rows, expect query times in the seconds-to-tens-of-seconds range.

-- GIN
CREATE INDEX idx_products_name_trgm_gin
  ON products USING GIN (name gin_trgm_ops);

-- GiST
CREATE INDEX idx_products_name_trgm_gist
  ON products USING GIST (name gist_trgm_ops);

Which one? That’s the next section, because the answer isn’t obvious.

7. GIN vs GiST: it’s about your query, not your data size

Most resources frame this as “GIN is faster but heavier, GiST is lighter but slower.” That’s true at a high level. But the decision in practice usually comes down to one specific thing.

The general tradeoff

Property	GIN	GiST
Search speed	Faster	Decent
Build / update speed	Slow	Fast
Index size	Larger	Smaller
Best for	Static data, read-heavy	Frequently updated data

The thing nobody tells you up front

GiST supports ORDER BY ... <-> ... with index acceleration. GIN doesn’t.

This is in the official docs, and it’s the single most important factor for the autocomplete and “closest match” use cases. If you build a GIN index and then run a KNN query, the index can filter candidates but the sort happens after — meaning you pay the full cost of sorting every matching row.

I’ve seen this go wrong in production more than once. Someone benchmarks WHERE name % 'foo', sees GIN crush GiST, builds GIN, then six weeks later adds an autocomplete feature and wonders why latency spiked.

A decision table that actually maps to use cases

Query pattern	Index
WHERE col % 'query' (filter only)	GIN
LIKE '%pattern%' or ILIKE acceleration	GIN (usually faster)
ORDER BY col <-> 'query' LIMIT N (KNN, autocomplete)	GiST (required)
Frequently INSERTed/UPDATEd column	GiST
Static reference data, very high read volume	GIN

GiST tuning: siglen

Postgres 13 added a signature length parameter to the GiST trigram opclass:

CREATE INDEX idx_products_name_trgm_gist
  ON products USING GIST (name gist_trgm_ops(siglen=64));

GiST approximates trigram sets as bitmap signatures. The default of 12 bytes leads to a lot of false positives on bigger datasets, which means more rechecks against the heap. Bumping siglen to 64 or 128 trades index size for fewer false hits. On any sizable table, it’s worth A/B testing with EXPLAIN (ANALYZE, BUFFERS).

Always EXPLAIN

If you think your index isn’t getting used, don’t guess:

EXPLAIN (ANALYZE, BUFFERS)
SELECT name FROM products
WHERE name % 'iphne'
ORDER BY name <-> 'iphne'
LIMIT 10;

Look for Bitmap Index Scan or Index Scan. Watch for Rows Removed by Index Recheck getting too large. If you put a GIN index on a KNN query, this is where you’ll see it.

8. pg_trgm vs FTS vs Elasticsearch

pg_trgm is good at one specific thing — character-level fuzzy matching — and it’s bad at most other things people associate with “search.” It doesn’t know that ‘NYC’ and ‘New York City’ refer to the same place. It doesn’t understand that ‘car’ and ‘automobile’ are synonyms. It can’t weight matches across multiple fields, can’t rank by BM25, can’t do stemming.

That’s not a flaw, it’s the scope. Knowing what pg_trgm can’t do tells you when to reach for something else.

Tool	Strong at	Weak at
pg_trgm	Typo tolerance, short strings, zero infra cost	Semantic matching, stemming, weighted multi-field
Postgres FTS (tsvector)	Stemmed exact matching, multilingual dictionaries, weights	Typo tolerance
Elasticsearch	Multi-field BM25 ranking, analyzers, hundreds of millions of rows	Operational overhead, sync complexity

pg_trgm is usually the right call when:

• You’re searching one or a few columns
• You’re in the hundreds of thousands to low millions of rows
• The content is identifiers, names, product titles — not natural language paragraphs
• Ranking quality and synonyms aren’t critical

For natural-language search where ranking matters, don’t try to bend pg_trgm into doing it. Either move to FTS or accept that you need a real search engine.

One thing worth knowing: pg_trgm and Postgres FTS aren’t competitors. The official docs themselves describe a pattern where FTS handles primary search and trigrams handle “did you mean” suggestions for misspelled inputs. They compose well.

9. Wrapping up

That’s the core of it. Most production problems I’ve seen with pg_trgm come down to picking the wrong index for the query pattern, or using similarity when word_similarity would have been right. A short checklist before you ship:

1. Is the query pattern filter-only or KNN-style sorting? → GIN vs GiST
2. Is the search query shorter than what you’re searching? → word_similarity, not similarity
3. Have you set similarity_threshold against actual data, not the 0.3 default?
4. Did you EXPLAIN (ANALYZE, BUFFERS) to confirm the index is being used the way you expected?

Cover those four and you’ve avoided most of the production sharp edges.