web dev//SQL//PostgreSQL//pg_trgm

pg_trgm is a PostgreSQL extension that adds trigram-based fuzzy matching, and it is used for user-facing search over names, titles and short text. It splits every string into 3-character chunks (trigrams) and compares the overlap between the query and the stored text.

pg_trgm is a PostgreSQL extension that adds trigram-based fuzzy matching, and it is used for user-facing search over names, titles and short text. It splits every string into 3-character chunks (trigrams) and compares the overlap between the query and the stored text.

One line, CREATE EXTENSION pg_trgm;, enables it, and it is available on Supabase out of the box. similarity(a, b) returns a float from 0.0 to 1.0, a % b returns a boolean that is true above a threshold, and SET pg_trgm.similarity_threshold = 0.3; sets that cutoff.

A GIN index (CREATE INDEX idx ON table USING gin (column gin_trgm_ops)) makes % and similarity() fast on large tables. Without the index, every query is a full table scan.

The common pattern uses pg_trgm for user-facing search (names, titles, short text) and full-text search (tsvector) for body content. The two stack, as different tools for different jobs, and pgvector adds the third, semantic similarity over embeddings against the character similarity of trigrams: extend Postgres instead of bolting on another system.

At 300 rows a trigram search is instant, and at 1M rows it is still fast with the GIN index. Dedicated search (Elasticsearch, Meilisearch) is only needed at serious scale.