Posted inDatabase

PostgreSQL pg_trgm: Build Fuzzy Search, Typo-Tolerant Queries, and Autocomplete with Trigram Indexes

Database tutorial - IT technology blog
Database tutorial - IT technology blog

Quick Start — Get Fuzzy Search Running in 5 Minutes

After years of jumping between MySQL, PostgreSQL, and MongoDB across different projects, I kept running into the same UX problem: users typing “postgress” instead of “postgres” and getting zero results. Full-text search doesn’t help here — it’s exact token matching. That’s where pg_trgm became one of my most-reached-for PostgreSQL features.

First, enable the extension. It ships with PostgreSQL, no extra install needed:

-- Run as superuser or a user with CREATE privilege
CREATE EXTENSION IF NOT EXISTS pg_trgm;

Verify it’s active:

SELECT * FROM pg_extension WHERE extname = 'pg_trgm';

Now test the core function immediately:

SELECT similarity('postgress', 'postgres');
-- Returns: 0.7

SELECT similarity('database', 'databse');
-- Returns: 0.615

The similarity() function returns a float between 0 and 1. Values above ~0.3 usually mean the strings are related. That’s your entire quick start — two SQL lines and you have fuzzy matching.

A real search query looks like this:

SELECT title, similarity(title, 'postgress') AS score
FROM articles
WHERE similarity(title, 'postgress') > 0.3
ORDER BY score DESC
LIMIT 10;

Deep Dive — How Trigrams Actually Work

A trigram is a sequence of 3 consecutive characters. The extension breaks strings into overlapping trigrams and compares the sets. The similarity score = (shared trigrams) / (total unique trigrams in both strings).

-- See what trigrams a string produces
SELECT show_trgm('postgres');
-- {"  p"," po","gre","pos","res","sql","sql","ost","str","tgr"}
-- (PostgreSQL pads with spaces: "  p" and " po" are the leading trigrams)

Understanding this helps you predict when fuzzy search will work well and when it won’t. Very short strings (1-2 characters) produce almost no trigrams, so similarity scores become unreliable. My rule of thumb: fuzzy search really starts behaving predictably at 4+ characters.

Three Similarity Functions You Need to Know

Most tutorials only cover similarity(), but pg_trgm gives you three distinct functions:

  • similarity(a, b) — Compares the full strings. Best for matching short names, tags, or product codes where you expect the full string to roughly match.
  • word_similarity(word, text) — Checks if the word is similar to any contiguous extent of words in text. This is the one I use for search-as-you-type autocomplete — the user types a partial word and you match it against a longer title.
  • strict_word_similarity(word, text) — Similar to word_similarity but the extent must cover only whole words. More precise, fewer false positives.
-- A user types "dockerr compose"
SELECT word_similarity('dockerr compose', 'Getting started with Docker Compose on Ubuntu');
-- Returns: 0.571 — catches the typo and partial phrase match

SELECT similarity('dockerr compose', 'Getting started with Docker Compose on Ubuntu');
-- Returns: 0.24 — too low, the full strings are very different

For search boxes, word_similarity almost always gives better results than similarity.

GIN vs GiST Index — Which One to Pick

Without an index, pg_trgm does a full table scan. On 100k+ rows, that becomes painful. Two index types work with trigrams:

-- GIN index (recommended for most cases)
CREATE INDEX idx_articles_title_gin ON articles USING GIN (title gin_trgm_ops);

-- GiST index (alternative)
CREATE INDEX idx_articles_title_gist ON articles USING GIST (title gist_trgm_ops);

Here’s the practical difference from my experience:

  • GIN: Faster reads, larger index on disk, slower to build/update. Use this for tables with mostly reads.
  • GiST: Smaller index, faster to update, slightly slower queries. Better fit for write-heavy tables where the index gets rebuilt constantly.

For most search scenarios (read-heavy), GIN wins. I’ve seen query times drop from 800ms to under 5ms on a 500k-row table after adding a GIN trigram index.

Advanced Usage — Autocomplete, LIKE Acceleration, and Thresholds

Building a Search-as-You-Type Autocomplete

This is where pg_trgm really shines compared to just adding LIKE '%keyword%'. Here’s a pattern I use for autocomplete endpoints:

-- Autocomplete: user typed "kuber"
SELECT
  title,
  word_similarity('kuber', title) AS score
FROM articles
WHERE title % 'kuber'   -- the % operator uses the configured threshold
OR title ILIKE '%kuber%'
ORDER BY score DESC, title
LIMIT 8;

The % operator is the shorthand for similarity() > threshold. It leverages the GIN index automatically — the planner knows to use it.

You can tune the threshold globally or per session:

-- Check current threshold
SHOW pg_trgm.similarity_threshold;
-- Default: 0.3

-- Lower it to catch more typos (more false positives)
SET pg_trgm.similarity_threshold = 0.2;

-- Higher for stricter matching
SET pg_trgm.similarity_threshold = 0.4;

For word_similarity, the matching operator is <%:

-- word_similarity operator: word <% text
SELECT title FROM articles
WHERE 'kuber' <% title
ORDER BY word_similarity('kuber', title) DESC
LIMIT 5;

Accelerating LIKE and ILIKE with Trigram Indexes

One underused benefit of pg_trgm: it speeds up regular LIKE and ILIKE queries too. Without a trigram index, WHERE title ILIKE '%postgres%' does a full scan. With one, the planner can use the index:

-- This query will use your GIN trigram index automatically
SELECT * FROM articles
WHERE title ILIKE '%postgresql performance%';

-- Verify with EXPLAIN
EXPLAIN SELECT * FROM articles WHERE title ILIKE '%postgresql%';
-- Should show: Bitmap Index Scan on idx_articles_title_gin

This alone is a reason to add a trigram index even if you’re not doing fuzzy search — it makes wildcard LIKE fast.

Combining pg_trgm with Full-Text Search

Fuzzy search and full-text search solve different problems. I often combine them with a UNION or by scoring both:

-- Hybrid search: full-text first, fuzzy as fallback
SELECT title, ts_rank(to_tsvector('english', title), query) AS fts_score,
       similarity(title, 'postgress indexing') AS fuzzy_score
FROM articles,
     to_tsquery('english', 'postgres & indexing') AS query
WHERE to_tsvector('english', title) @@ query
   OR similarity(title, 'postgress indexing') > 0.25
ORDER BY (ts_rank(to_tsvector('english', title), query) + similarity(title, 'postgress indexing')) DESC
LIMIT 10;

This pattern handles both the precise case (correct spelling, returns FTS results) and the fuzzy case (typos, still returns something useful).

Practical Tips from Real Projects

Watch Your Index Size

GIN trigram indexes are significantly larger than B-tree indexes — sometimes 3-5x the size of the column data itself. On a 1GB text column, expect a 2-4GB GIN index. Monitor with:

SELECT
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE tablename = 'articles';

Short Strings Are Unreliable — Add a Length Guard

-- Only fuzzy-match when the query is long enough to be meaningful
SELECT title FROM articles
WHERE
  CASE
    WHEN length('ab') < 4 THEN title ILIKE '%' || 'ab' || '%'
    ELSE similarity(title, 'ab') > 0.3
  END
LIMIT 10;

Alternatively, just skip fuzzy matching in your application layer when the query is under 3 characters and use prefix matching instead.

Index Only the Columns You Search

Don’t add trigram indexes to every text column. Index the columns users actually type into: title, name, tags. Indexing a content or body column with trigrams usually isn’t worth it — the index becomes massive and queries still return too many results anyway. For body content, stick with full-text search (tsvector).

Use pg_trgm for Tag and Username Matching

One of my favorite use cases beyond article search: matching user-entered tags to a canonical tag list, or fuzzy-matching usernames for a “did you mean?” feature.

-- "Did you mean?" for tags
SELECT tag_name, similarity(tag_name, 'javascrpt') AS score
FROM tags
WHERE similarity(tag_name, 'javascrpt') > 0.4
ORDER BY score DESC
LIMIT 3;
-- Returns: javascript (0.72), javaScript (0.72), ...

Test Your Threshold Before Going to Production

The default threshold of 0.3 is a starting point, not a universal answer. Run this kind of sanity check against real data before deploying:

-- Sample your data to find the right threshold
SELECT
  threshold,
  COUNT(*) AS matches
FROM
  generate_series(0.1, 0.6, 0.05) AS threshold,
  articles
WHERE similarity(title, 'your typical search query') > threshold
GROUP BY threshold
ORDER BY threshold;

This shows you how many results you’d get at each threshold level. Too many results at 0.2, too few at 0.5 — find the range that feels right for your domain.

Having worked across MySQL, PostgreSQL, and MongoDB on different projects, each has real strengths. But for user-facing search with typo tolerance, pg_trgm is one of those features that PostgreSQL just does better than anything I’ve used elsewhere — and it’s already built in, no extra service to run.

Share:
Tags: