PostgreSQL pg_trgm — Trigram Similarity Functions

Complete reference for PostgreSQL pg_trgm extension functions covering trigram similarity scoring, fuzzy string matching, word similarity, and GIN/GiST index support for fast fuzzy searches. Includes similarity(), word_similarity(), and the % and <-> operators. Updated for PostgreSQL 16.

5 functions

Jump to function

set_limit show_limit show_trgm similarity word_similarity

What are PostgreSQL pg_trgm — Trigram Similarity Functions?

pg_trgm is a PostgreSQL extension that provides text similarity functions based on trigram (three-character n-gram) matching. similarity() returns a value from 0.0 to 1.0 indicating how similar two strings are, and the % operator tests whether two strings meet the similarity threshold set by pg_trgm.similarity_threshold. pg_trgm GIN or GiST indexes dramatically accelerate fuzzy string searches and LIKE/ILIKE queries on large text columns.

set_limit

set_limit() available since pg_trgm PG 8.3+. The equivalent GUC pg_trgm.similarity_threshold was introduced in PG 9.6+ and is preferred in modern deployments.→ real

Sets the current similarity threshold used by the % operator (default 0.3). Returns the new threshold.

DeveloperData Eng

Signature

set_limit ( real ) → real

Parameters

Parameter	Type	Description
threshold	real	Similarity threshold between 0 and 1

Examples

sql

SELECT set_limit(0.5);

→0.5

sql

SELECT set_limit(0.1); SELECT name FROM products WHERE name % 'phone';

→More permissive fuzzy matching

sql

DO $ BEGIN PERFORM set_limit(0.4); END $;
SELECT name FROM brands WHERE name % $1;

→Threshold set inside a PL/pgSQL block for a focused search

sql

SELECT set_limit(0.3); -- reset to default after custom search

→0.3

⚠Anti-Pattern— set_limit() is session-global — persists across queries in pooled connections

`set_limit(0.6)` sets the threshold for the entire session. In connection-pooled applications, a subsequent query from a different request may inherit this elevated threshold, silently returning fewer (or no) fuzzy matches than expected.

✓ Instead: Use the GUC instead: `SET LOCAL pg_trgm.similarity_threshold = 0.6` which automatically resets at transaction end, making it safe in pooled environments.

`set_limit` changes a session-level value. In multi-threaded applications, prefer setting it via a GUC: `SET pg_trgm.similarity_threshold = 0.5`. This is cleaner and persists correctly for the session.

example

SET pg_trgm.similarity_threshold = 0.4;
SELECT name FROM products WHERE name % $1;

→Fuzzy search with explicit threshold

show_limit

show_limit() available since pg_trgm PG 8.3+. In PG 9.6+ you can also inspect the threshold via SHOW pg_trgm.similarity_threshold.→ real

Returns the current similarity threshold used by the % operator.

DeveloperData Eng

Signature

show_limit ( ) → real

Examples

sql

SELECT show_limit();

→0.3 (default)

sql

SELECT set_limit(0.5); SELECT show_limit();

→0.5 — confirms the threshold was updated

sql

DO $ BEGIN RAISE NOTICE 'Current trgm threshold: %', show_limit(); END $;

→Logs current threshold inside a PL/pgSQL procedure for debugging

sql

SELECT show_limit() AS before, set_limit(0.4) AS after;

→Shows old and new threshold in one query for audit logging

⚠Anti-Pattern— Relying on show_limit() to verify a SET pg_trgm.similarity_threshold GUC change

`set_limit()` and `pg_trgm.similarity_threshold` GUC operate independently in some PostgreSQL versions. After `SET pg_trgm.similarity_threshold = 0.5`, calling `show_limit()` may still return the old value, misleading debugging.

✓ Instead: To verify the GUC, use `SHOW pg_trgm.similarity_threshold`. To verify `set_limit`, use `show_limit()`. Don't cross-verify between them.

Use `show_limit` to inspect and `set_limit` to change the session's similarity threshold. Reset to default (0.3) with `set_limit(0.3)` after a custom search operation.

example

SELECT show_limit(); -- Check current threshold

→0.3

show_trgm

show_trgm() available since the pg_trgm extension was introduced in PG 8.3+.→ text[]

Returns an array of all the trigrams in the given string. Useful for debugging trigram-based searches.

DeveloperData Eng

Signature

show_trgm ( text ) → text[]

Parameters

Parameter	Type	Description
string	text	String to extract trigrams from

Examples

sql

SELECT show_trgm('cat');

→{" c"," ca","at ","cat"}

sql

SELECT array_length(show_trgm(name), 1) AS trigram_count FROM products;

→Number of trigrams per product name

sql

SELECT trgm FROM unnest(show_trgm('hello')) AS t(trgm) WHERE trgm = ANY(show_trgm('hell'));

→Shared trigrams between 'hello' and 'hell'

sql

SELECT word, array_length(show_trgm(word), 1) AS n_trgm FROM (VALUES ('a'), ('ab'), ('abc'), ('abcd')) v(word);

→Shows that words < 3 chars produce fewer/no useful trigrams

⚠Anti-Pattern— Using show_trgm() in production queries for filtering or sorting rows

`show_trgm()` is a diagnostic function that extracts all trigrams from a string. Using it inside a WHERE or ORDER BY to filter rows is expensive and not indexable — it computes and returns a full array for every row scanned.

✓ Instead: Use indexed trigram operators (`%`, `<->`) for production queries. Reserve `show_trgm()` for debugging: inspecting why two strings score unexpectedly high or low.

The similarity score is `|A ∩ B| / |A ∪ B|` where A and B are trigram sets. Use `show_trgm` to inspect which trigrams are generated for your strings when debugging unexpected similarity scores.

example

SELECT show_trgm('hello'), show_trgm('hell');

→Compare trigram sets to understand the similarity score

Common Gotchas

⚠

Stale statistics after bulk load — planner uses wrong row estimates

After INSERT of millions of rows, the query planner still uses the old row count until ANALYZE runs. This leads to terrible plans.

similarity

pg_trgm extension available since PG 8.3+. GIN/GiST index support for the % operator introduced in PG 9.1+.→ real

Returns a number between 0 and 1 representing how similar the two strings are, based on trigram overlap. Requires the pg_trgm extension.

DeveloperData Eng

Signature

similarity ( text, text ) → real

Parameters

Parameter	Type	Description
string1	text	First string to compare
string2	text	Second string to compare

Examples

sql

SELECT similarity('hello world', 'hello word');

→0.625

sql

SELECT similarity('PostgreSQL', 'PostgreSQL');

→1.0

sql

SELECT name FROM products WHERE similarity(name, 'wrieless mouse') > 0.3 ORDER BY similarity(name, 'wrieless mouse') DESC;

→Fuzzy product search with typo tolerance

sql

SELECT user_input, query, similarity(user_input, query) AS score FROM search_log WHERE similarity(user_input, query) < 0.5 ORDER BY score;

→Audit log of low-confidence fuzzy matches for review

⚠Anti-Pattern— Calling similarity() in WHERE without a trigram index causes full table scan

Without a GIN or GiST index on the column, `WHERE similarity(col, $1) > 0.3` scans every row and computes the full trigram similarity for each — O(n) cost that degrades catastrophically at scale.

✓ Instead: Create a GIN index first: `CREATE INDEX ON products USING GIN (name gin_trgm_ops)`. Then use the `%` operator in WHERE: `WHERE name % $1 ORDER BY similarity(name, $1) DESC LIMIT 10`.

The `%` operator checks `similarity(a,b) >= pg_trgm.similarity_threshold` (default 0.3). Create a GIN index: `CREATE INDEX ON t USING GIN (col gin_trgm_ops)` for O(log n) fuzzy search instead of full table scan.

example

CREATE EXTENSION pg_trgm;
CREATE INDEX ON products USING GIN (name gin_trgm_ops);
SELECT name, similarity(name, $1) AS sim FROM products WHERE name % $1 ORDER BY sim DESC LIMIT 10;

→Fast fuzzy product search

word_similarity

word_similarity() introduced in PG 9.6+. The <% and %> operators (word similarity operators) also require PG 9.6+.→ real

Returns the greatest similarity between the trigrams of the first string and any continuous extent of trigrams of the second string. Better for searching a word within a longer string.

DeveloperData Eng

Signature

word_similarity ( text, text ) → real

Parameters

Parameter	Type	Description
search_term	text	Shorter search string
document	text	Longer string to search within

Examples

sql

SELECT word_similarity('word', 'two words here');

→0.8

sql

SELECT word_similarity('pg', 'PostgreSQL database');

→Similarity of short query in longer text

sql

SELECT name, word_similarity('apple', name) AS ws FROM products WHERE 'apple' <% name ORDER BY ws DESC LIMIT 5;

→Products whose name contains a word close to 'apple'

sql

SELECT word_similarity('jscript', lang_name) AS ws, lang_name FROM programming_languages ORDER BY ws DESC LIMIT 3;

→Closest language names to a misspelled 'jscript'

⚠Anti-Pattern— Swapping the needle and haystack arguments reverses word_similarity semantics

`word_similarity(A, B)` and `word_similarity(B, A)` return very different values. The first argument is the 'needle' being searched for within the second 'haystack'. Swapping them silently computes the wrong direction of similarity.

✓ Instead: Always pass the shorter search term as the first argument and the longer document/name as the second: `word_similarity('search_term', long_text_column)`.

`word_similarity(query, text)` measures how well the query matches a contiguous word in the text. More accurate than `similarity` when searching for a term within a longer product name or description.

example

SELECT name FROM products WHERE $1 <% name ORDER BY word_similarity($1, name) DESC LIMIT 5;

→Products whose name contains something like the query

PostgreSQL pg_trgm — Trigram Similarity Functions

Jump to function

What are PostgreSQL pg_trgm — Trigram Similarity Functions?

set_limit

Signature

Parameters

Examples

show_limit

Signature

Examples

show_trgm

Signature

Parameters

Examples

Common Gotchas

similarity

Signature

Parameters

Examples

word_similarity

Signature

Parameters

Examples

Related PostgreSQL Categories