pg_trgm
A PostgreSQL extension that splits text into three-letter pieces, so LIKE '%foo%', regex and typo-tolerant searches can use an index.
- Type
- Open source
- Made by
- PostgreSQL Global Development Group
- License
- PostgreSQL License
- Facts checked
- 6 October 2026
What it is
pg_trgm ships with PostgreSQL as a contrib module and is switched on per database with CREATE EXTENSION pg_trgm. It breaks text into trigrams: groups of three consecutive characters. Characters that aren’t letters or digits are ignored, and each word gets two spaces in front and one behind, so “cat” becomes “ c”, “ ca”, “cat” and “at ”. Two strings are as similar as the share of trigrams they have in common. similarity() returns a score from 0 to 1, and the % operator is true when the score passes pg_trgm.similarity_threshold (0.3 by default). word_similarity() scores the best-matching stretch of a longer string, for searching inside titles or descriptions.
The speed comes from two index operator classes. gin_trgm_ops builds a GIN index, an inverted index that keeps, for each trigram, the list of rows that contain it. gist_trgm_ops builds a GiST index that squeezes each value into a fixed-size bitmap signature, 12 bytes by default. Both can answer LIKE, ILIKE, ~ and ~* with patterns that aren’t anchored at the start: PostgreSQL pulls the trigrams out of the pattern, finds the rows that contain them, then checks each candidate row against the real pattern. Only GiST can return the nearest matches by distance (ORDER BY name <-> 'query' LIMIT 10). It costs nothing beyond PostgreSQL itself, and Amazon RDS, Cloud SQL and Azure Database for PostgreSQL all offer it.
Why teams pick it
- Substring and regular-expression searches (
ILIKE '%wireless%',~ '(foo|bar)') can use an index, where a B-tree only helps with patterns anchored at the start, such asLIKE 'foo%' - Typo-tolerant lookups and “did you mean” suggestions with
similarity()and%, ranked by score; a GiST index returns the closest matches directly - Lives inside the database: the index is updated in the same transaction as the row, so there is no second system to feed or let drift out of date
- No dictionaries or stemming, so it works on names, product codes, file paths and mixed languages that word-based full-text search handles poorly
Watch out for
- Patterns shorter than three characters, or made only of punctuation (non-alphanumeric characters are ignored), yield no trigrams, and the search falls back to scanning the whole index; very common trigrams also leave many candidate rows to recheck
- GIN writes are expensive: every row adds one entry per distinct trigram. The pending list makes most writes fast but now and then one pays for a flush; GitLab measured flushes of about 0.5 to 3 seconds on merge-request descriptions
- GiST is lossy: its signatures produce false matches that PostgreSQL must recheck against the table, and longer signatures (up to 2,024 bytes) cut rechecks only by making the index larger
- Not a document search engine: no stemming, phrases, synonyms or BM25-style relevance, and on very short words one typo changes most of the few trigrams there are; equality is better served by a B-tree, and
<or>aren’t supported at all
Alternatives
What all search indexes have in common →
ElasticsearchCommercialThe distributed search and analytics engine built on Apache Lucene: JSON documents in, full-text, vector and log queries out, over a REST API.
OpenSearchOpen sourceThe Apache 2.0 fork of Elasticsearch and Kibana 7.10.2 that AWS started in 2021, now run by a Linux Foundation project.UrsaDBOpen sourceCERT Polska’s byte-trigram index for malware collections: it narrows a YARA hunt over terabytes of files down to a few candidates.How it fits with other technologies
What pg_trgm uses
Index a column for substring and fuzzy search · sql
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE TABLE products (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL
);
CREATE INDEX products_name_trgm ON products USING gin (name gin_trgm_ops);
SELECT show_trgm('cat'); -- {" c"," ca","at ",cat}
-- unanchored, case-insensitive substring search can use the index
SELECT id, name FROM products WHERE name ILIKE '%headphone%';
-- typo-tolerant: 'wireles hedphones' still finds 'Wireless Headphones'
SET pg_trgm.similarity_threshold = 0.3;
SELECT name, similarity(name, 'wireles hedphones') AS score
FROM products
WHERE name % 'wireles hedphones'
ORDER BY score DESC
LIMIT 10;GIN or GiST for trigrams#
Both operator classes speed up the same operators. They differ in what they store, which changes lookup speed, write cost and what they can sort by.
| GIN (`gin_trgm_ops`) | GiST (`gist_trgm_ops`) | |
|---|---|---|
| What it stores | Each trigram once, with the list of rows that contain it | One fixed-length bitmap signature per row (12 bytes by default, siglen 1 to 2,024) |
| False matches | None from the index itself; rows are still rechecked against LIKE and regex patterns | Yes: different trigrams can set the same bit, so more rows are fetched and rechecked |
| `LIKE`, `ILIKE`, `~`, `~*`, `%` | Yes | Yes |
| Nearest matches (`ORDER BY col <-> 'q' LIMIT n`) | No | Yes |
| Writes | Many index entries per row; batched in a pending list unless fastupdate is off | One entry per row |
| Tuning | gin_pending_list_limit, fastupdate; build speed depends on maintenance_work_mem | siglen: longer signatures mean fewer rechecks and a bigger index |
- PostgreSQL’s docs call GIN the preferred index type for full-text search, where the same two structures compete. Pick GiST when you need nearest-match ordering.
Other ways to search text in PostgreSQL#
B-tree with `text_pattern_ops`
prefix onlyA plain B-tree serves LIKE 'foo%' and ~ '^foo' (outside the C locale, build it with text_pattern_ops). Small and cheap to update, but useless for '%foo%'.
Built-in full-text search
wordstsvector columns with a GIN index match stemmed words and rank results with ts_rank. Better for documents and articles; pg_trgm’s docs suggest pairing the two to correct misspelled query words.
A separate search engine
another systemElasticsearch, OpenSearch or Meilisearch add relevance tuning, facets and typo handling across many fields, at the price of a second system that has to be kept in sync with the database.
See it in a real stack
No architecture uses pg_trgm yet. Drop it into the playground and wire it up yourself.Open playground →Where it came from2004–2020
A B-tree index can only help a pattern search that is anchored at the start of the string, so LIKE '%foo%' meant reading every row, and fuzzy matching meant computing a distance against every value. Teodor Sigaev and Oleg Bartunov, Moscow developers who maintained PostgreSQL’s GiST support and the tsearch2 full-text module, added a trigram module to contrib in May 2004. It shipped with PostgreSQL 8.0 in January 2005, with GiST support and similarity matching for things like misspelled words.
Others kept extending it. Guillaume Smet added GIN support in 8.3 (2008). In 9.1 (2011) Alexander Korotkov taught the indexes to answer LIKE and ILIKE, and Sigaev added nearest-neighbour search. Korotkov added indexed regular expressions in 9.3 (2013), and word similarity followed in 9.6 (2016) with Artur Zakirov. The result is that substring, regex and fuzzy search on an ordinary text column became an index lookup instead of a full table scan, without adding a search engine.
Why it’s built this way
- Trigrams instead of words
- Counting shared three-character groups needs no dictionary and no language rules, and a misspelled word still shares most of its trigrams with the right one. The docs call this simple idea very effective for measuring word similarity in many languages.
- Padding each word with spaces
- Two spaces in front and one behind give the start and end of every word trigrams of their own, so even short words produce something to compare, and matches at word boundaries count.
- The index finds candidates, the row decides
- Trigrams can say which rows might match but not whether they do, especially for regex. The index returns candidates and PostgreSQL rechecks each against the real operator, so results are always exact.
- Both a GiST and a GIN operator class
- GiST came first and later learned to order by distance; GIN, added for 8.3, keeps exact per-trigram row lists for faster lookups. The docs leave the choice to the workload.
Timeline
- 2004
Teodor Sigaev commits the trigram module to contrib in May
- 2005
Ships with PostgreSQL 8.0 in January, with GiST indexes
- 2008
PostgreSQL 8.3 adds GIN support
- 2011
9.1 indexes
LIKEandILIKEand adds nearest-neighbour search - 2013
9.3 indexes regular-expression searches
- 2016
9.6 adds
word_similarity() - 2020
PostgreSQL 13 lets GiST indexes set their signature length
- postgres/postgres on GitHub: “trgm - Trigram matching for PostgreSQL” (Teodor Sigaev, May 2004)
- PostgreSQL 8.0 release notes (new contrib/trgm, January 2005)
- PostgreSQL 8.3 release notes (GIN support for contrib/pg_trgm, February 2008)
- PostgreSQL 9.1 release notes (LIKE and ILIKE index searches, nearest-neighbour search, September 2011)
- PostgreSQL 9.3 release notes (indexing of regular-expression searches, September 2013)
- PostgreSQL 9.6 release notes (word similarity, September 2016)
Sources14 · facts checked 6 October 2026
- PostgreSQL docs: pg_trgm (trigrams, functions, operators, thresholds, GIN and GiST index support, authors)
- PostgreSQL docs: Index types (B-tree serves LIKE only when anchored at the start)
- PostgreSQL docs: Operator classes (text_pattern_ops)
- PostgreSQL docs: GIN indexes (fast update, pending list, create vs insert)
- PostgreSQL docs: Preferred index types for text search (GIN vs GiST, lossy signatures)
- PostgreSQL docs: Full text search, introduction (stemming, ranking)
- PostgreSQL 11 release notes (strict_word_similarity)
- PostgreSQL 13 release notes (GiST signature length for pg_trgm)
- pganalyze: Understanding Postgres GIN indexes, the good and the bad (GitLab trigram index pending-list flushes, December 2021)
- depesz: Waiting for 9.1, faster LIKE/ILIKE (index build time on 2 million rows, February 2011)
- PostgreSQL License
- Amazon RDS: PostgreSQL extensions supported on Amazon RDS
- Cloud SQL for PostgreSQL docs: Configure PostgreSQL extensions
- Microsoft Learn: Extensions and versions in Azure Database for PostgreSQL