PostgreSQL pg_trgm: Fuzzy Search With Trigram Indexes

pg_trgm is a PostgreSQL extension that breaks text into three-character chunks called trigrams and compares strings by how many trigrams they share. It gives you two things the core database lacks: typo-tolerant fuzzy matching (“postgers” finds “postgres”) and index support for LIKE '%term%' and regex searches that would otherwise force a full table scan. It ships with PostgreSQL’s standard contrib modules, so in most installations it’s one command away.

This guide walks through enabling it, understanding similarity scores, and indexing for speed. The official reference is the pg_trgm page in the PostgreSQL documentation.

Step 1: Enable the extension

Run this once per database, as a user with permission to create extensions:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

For the examples below, assume a simple table:

CREATE TABLE products (
  id   bigserial PRIMARY KEY,
  name text NOT NULL
);

Step 2: See what a trigram is

The show_trgm function reveals how pg_trgm sees a string:

SELECT show_trgm('cat');
-- {"  c"," ca","at ","cat"}

Each word is lowercased and padded with two spaces in front and one behind, then sliced into every overlapping three-character window. Non-alphanumeric characters act as word separators. Two strings are “similar” when their trigram sets overlap heavily — a misspelling changes only a few trigrams, so most of the set survives.

Step 3: Measure similarity

The similarity function returns a score from 0 (no shared trigrams) to 1 (identical sets):

SELECT similarity('postgres', 'postgers');

The score is the number of shared trigrams divided by the number of distinct trigrams across both strings. You rarely call it alone, though. pg_trgm adds operators that are easier to index:

• a % b — true when similarity exceeds the threshold set by pg_trgm.similarity_threshold (0.3 by default).
• a <-> b — the distance, defined as one minus the similarity. Lower is closer, which makes it ideal for ORDER BY.
• a <% b and word_similarity(a, b) — compare a against the best-matching portion of b, useful when searching a short term inside a longer string.

A typical fuzzy search combines a filter with a ranking:

SELECT name, similarity(name, 'wireles headphnes') AS score
FROM products
WHERE name % 'wireles headphnes'
ORDER BY name <-> 'wireles headphnes'
LIMIT 10;

You can tune the threshold per session:

SET pg_trgm.similarity_threshold = 0.4;

Raise it for stricter matches; lower it to catch more distant typos at the cost of noise.

Step 4: Add an index

Without an index, every query above computes similarity against every row. pg_trgm provides operator classes for both GIN and GiST. If you’re fuzzy on how those index types differ in general, Postgres index types explained covers the background.

-- GIN: best for filtering with %, LIKE, ILIKE, and regex
CREATE INDEX products_name_trgm_gin
  ON products USING gin (name gin_trgm_ops);

-- GiST: also supports ORDER BY name <-> 'term' (nearest-neighbour)
CREATE INDEX products_name_trgm_gist
  ON products USING gist (name gist_trgm_ops);

Pick based on your dominant query:

	GIN (gin_trgm_ops)	GiST (gist_trgm_ops)
Filtering with %, LIKE, ILIKE, ~	Fast	Good
ORDER BY col <-> 'term'	Not supported	Supported
Index build time	Slower	Faster
Index size	Usually larger	Usually smaller
Write overhead	Higher	Lower

Many applications use GIN for “find matches” and accept a sort over the filtered result set, which is usually small. If you need fast top-N nearest matches on large tables, GiST’s distance ordering earns its keep.

Step 5: Speed up substring searches

The most underrated benefit of pg_trgm has nothing to do with typos. A standard B-tree index can serve LIKE 'abc%' (prefix) under the right collation, but it’s useless for LIKE '%abc%'. A trigram index handles both, plus case-insensitive and regex variants:

SELECT * FROM products WHERE name ILIKE '%headphone%';
SELECT * FROM products WHERE name ~* 'head(phone|set)s?';

The planner extracts trigrams from the pattern, uses the index to find candidate rows, then rechecks each candidate against the real condition. Confirm it’s working with EXPLAIN ANALYZE — look for a Bitmap Index Scan on your trigram index rather than a Seq Scan.

One caveat: patterns need at least three characters of literal text to produce useful trigrams. A search for '%ab%' can’t be narrowed by the index and will fall back to scanning.

pg_trgm vs full-text search

pg_trgm and PostgreSQL’s built-in full-text search solve different problems, and they combine well.

	pg_trgm	Full-text search
Matches on	Character overlap	Words and lexemes
Typo tolerance	Yes	No
Stemming (“running” → “run”)	No	Yes
Language awareness	None	Dictionaries and stop words
Best for	Names, SKUs, short fields, autocomplete	Articles, descriptions, documents

A common pattern is full-text search for long documents and pg_trgm for titles, usernames, and product names, where users misspell things and stemming doesn’t apply. Neither understands meaning; for that you’d move toward the techniques in semantic search vs keyword search.

Practical tips

• Index what you query. If you search lower(name), index lower(name). Better still, rely on trigrams already being case-insensitive and drop the lower() call.
• Combine with filters. A partial index such as WHERE active keeps the trigram index smaller when you only ever search a subset.
• Mind long text. Trigram indexes on large text columns grow quickly and slow writes. Keep them on short, user-facing fields.
• Tune the threshold with real data. Run a sample of real queries at several thresholds and pick the one where results stay relevant.

The takeaway

pg_trgm splits strings into trigrams and scores them by overlap, giving PostgreSQL typo-tolerant matching through %, <->, and similarity(). Add a GIN index with gin_trgm_ops for fast filtering and substring LIKE/ILIKE queries, or GiST with gist_trgm_ops when you need nearest-match ordering. Use it for short fields like names and titles, pair it with full-text search for long documents, and verify every index with EXPLAIN ANALYZE.