Add Fuzzy Search with Postgres Trigram Indexes

Enable pg_trgm, build a GIN index on the search column, and query with similarity operators so misspellings still match fast.

~35 minpostgresqlsearchperformance

Task

Add typo-tolerant search to a Postgres table using trigram similarity and a supporting index.

When to use this

Users type a misspelling and get zero results because the search uses exact equality or a leading-wildcard LIKE that cannot use an index.

Prerequisites

The pg_trgm extension installed
Write access to create indexes

Steps

1. Enable the pg_trgm extension.

Expected output: Trigram functions and operators are available.

2. Create a GIN index using gin_trgm_ops on the search column.

Expected output: Similarity and LIKE queries can use the index.

3. Query with the % similarity operator and a threshold, or combine with word_similarity.

Expected output: Misspelled and partial terms match.

4. Tune pg_trgm.similarity_threshold and verify with EXPLAIN that the index is used.

psql -c "SET pg_trgm.similarity_threshold = 0.3; SELECT name FROM products WHERE name % 'iphnoe';"

Expected output: Latency stays low as the table grows.

Troubleshooting

Problem

Index not used

Solution

For LIKE make sure the pattern is index-friendly and the index uses gin_trgm_ops; verify with EXPLAIN ANALYZE.

Problem

Too many false positives

Solution

Raise the similarity threshold and combine trigram matching with ranking by similarity.

Limitations

Trigram search is not semantic; it matches character overlap, not meaning.
It adds index storage and write cost, and is best for short name-like fields rather than long documents.

Context

Add typo-tolerant search to a Postgres table using trigram similarity and a supporting index. Users type a misspelling and get zero results because the search uses exact equality or a leading-wildcard LIKE that cannot use an index.

Steps

Enable the pg_trgm extension. Expected: Trigram functions and operators are available.
Create a GIN index using gin_trgm_ops on the search column. Expected: Similarity and LIKE queries can use the index.
Query with the % similarity operator and a threshold, or combine with word_similarity. Expected: Misspelled and partial terms match.
Tune pg_trgm.similarity_threshold and verify with EXPLAIN that the index is used. Expected: Latency stays low as the table grows.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX products_name_trgm ON products
  USING gin (name gin_trgm_ops);
SELECT name FROM products
WHERE name % 'iphnoe'
ORDER BY similarity(name, 'iphnoe') DESC;

Verify

Trigram functions and operators are available.
Similarity and LIKE queries can use the index.
Misspelled and partial terms match.
Latency stays low as the table grows.

Limits

Trigram search is not semantic; it matches character overlap, not meaning.
It adds index storage and write cost, and is best for short name-like fields rather than long documents.