PostgreSQL pg_trgm: The Extension That Lets You Search Like the Docs Do, Without Standing Up Elasticsearch

PostgreSQL’s pg_trgm extension turns Postgres into a fuzzy text-search engine. It works by breaking every string into overlapping three-character pieces (trigrams) and indexing those, so similarity comparisons become set operations on the trigram index instead of O(n×m) full-string scans. The result: LIKE '%foo%' queries against tens of millions of rows that would otherwise take seconds finish in milliseconds, and typo-tolerant “did you mean” matches become a one-line query instead of a customer-engineering sprint.

The reason pg_trgm matters in 2026 is that most products reach for a dedicated search backend (Elasticsearch, Meilisearch, Typesense, Algolia) and pay the operational cost — another service to deploy, another index to keep in sync, another thing that goes down at 2am. For many apps, pg_trgm plus a GIN index on the right column gives you 80% of what those products do, against the same Postgres that already runs your transactions, without the second service. The trade-off is real (Elasticsearch is faster on large corpora and richer on text analysis), but for product search, name matching, log similarity, and “did the user mean this?” prompts, pg_trgm is the right shape.

Table of contents

• What pg_trgm actually does
• The four operations every developer needs
• How to install pg_trgm on a managed Postgres
• Indexes: when you need GIN and when you do not
• pg_trgm versus the alternatives
• Performance: what changes on a real table
• When pg_trgm is the wrong tool
• FAQ
• Closing thought

What pg_trgm actually does

pg_trgm adds three things to Postgres:

• A pg_trgm contrib module that ships with the Postgres source distribution. Officially supported, included in the standard postgresql-contrib package, available on every Postgres version since 9.1.
• Three SQL operators (%, <%, %>) and a similarity() function that compare strings using trigrams. % answers “are these two strings similar?” with a boolean, similarity() returns the score, the asymmetric <% and %> answer “does the left match the right pattern”.
• Two opclasses for GIN and GiST indexes that index the trigram decomposition of every row in a column. The opclasses (gin_trgm_ops for GIN, gist_trgm_ops for GiST) are the part that turns trigram queries into index lookups instead of full-table scans.

The “extension” part is the part that requires activation. A fresh Postgres install has the contrib modules shipped but not enabled; running CREATE EXTENSION pg_trgm; registers the module, installs the operators, and is committed to the database. On a self-managed Postgres that is the entire setup. On a managed Postgres (RunxBuild’s, Neon, Supabase, Aurora, RDS) the extension usually has to be allowlisted by the platform before it can be created.

The four operations every developer needs

After CREATE EXTENSION pg_trgm;, the four operations that actually come up in production code are:

1. Fuzzy equality with similarity()

SELECT slug, similarity(slug, 'fastpi-logger') AS score
FROM posts
WHERE slug % 'fastpi-logger'
ORDER BY score DESC
LIMIT 5;

% is a boolean operator that returns true when the similarity score is above a threshold (default pg_trgm.similarity_threshold = 0.3). For “find the closest match” use cases, similarity() returns the score and the application decides what to do with it.

2. Wildcard search with trigram indexes

SELECT title FROM articles WHERE title ILIKE '%kubernetes%';

Without pg_trgm, this is a sequential scan. With a GIN index on title using gin_trgm_ops, ILIKE with a leading wildcard becomes an index lookup. The same index serves % and <% operators.

3. Distance-based ordering

SELECT * FROM products ORDER BY name <-> 'chocolate bar' LIMIT 10;

The <-> operator returns the trigram distance (one minus similarity). Combined with LIMIT, this becomes the building block for “find the ten most similar rows” queries.

4. Index-accelerated LIKE

SELECT * FROM customers WHERE name LIKE '%Smith%';

For most apps the right answer is full-text search via tsvector. For fuzzy matching, name lookups, or any query with embedded typos, pg_trgm is the right tool.

The Postgres documentation has the full reference at postgresql.org/docs/current/pgtrgm.html. The Neon guide at neon.com/docs/extensions/pg_trgm covers installation on a managed Postgres. Tiger Data’s pg_trgm guide is the most practical tutorial-style walkthrough for production use.

How to install pg_trgm on a managed Postgres

On a self-managed install:

# Debian/Ubuntu
sudo apt install postgresql-contrib
sudo -u postgres psql -c "CREATE EXTENSION pg_trgm;" mydb

On a managed Postgres the steps are usually:

1. Confirm the platform supports pg_trgm. Almost all of them do; the allowlist is just a verification step.
2. Connect with the platform’s admin role (often postgres or a role with CREATE permission in the target database).
3. Run CREATE EXTENSION IF NOT EXISTS pg_trgm; from psql or the platform’s SQL console.
4. Verify with \\dx (list installed extensions) — pg_trgm | 1.6 | public | trigram matching should appear.

For RunxBuild Postgres specifically, the managed Postgres overview lists the extensions enabled by default and the ones that need to be turned on per database. The RunxBuild docs for services describe how to connect an app to the database over the private network.

Indexes: when you need GIN and when you do not

Trigram operators are useless fast on indexed columns and useless slow on non-indexed columns. The whole reason pg_trgm exists is the index. The recipe:

CREATE INDEX posts_title_trgm_idx ON posts USING GIN (title gin_trgm_ops);

GIN is the right choice for most cases — faster lookups, slightly slower inserts. Use GiST (with gist_trgm_ops) when the table is read-mostly and the index space matters; GiST is smaller, slower to query, faster to update.

A few practical rules:

• Index the column, not the expression. CREATE INDEX ON articles USING GIN (lower(title) gin_trgm_ops) works but stores the lowercased trigrams and only matches queries that also lowercase.
• Do not combine pg_trgm indexes with too many columns. Each GIN index on a text column grows roughly with the size of the column data. Pick the columns the application actually searches.
• The index is updated on every INSERT/UPDATE to the indexed column. For write-heavy tables, run the indexes on a separate schema or accept the insert penalty.
• Run VACUUM ANALYZE after the index is created to make the planner aware of the new statistics. pg_trgm-accelerated queries still benefit from up-to-date planner stats, even more so than B-tree indexes.

pg_trgm versus the alternatives

Use case	Best tool	Why
Fuzzy search over <10M rows	pg_trgm	One Postgres, no second service
Full-text search with stemming, ranking, multilingual	tsvector + GIN	Same database, native operator
Multilingual analyzers, faceted search at >10M rows	Elasticsearch, Meilisearch	Trigram is expensive on very large corpora
Domain-specific fuzzy (typo tolerance, prefix + suffix)	pg_trgm + tsvector	Combine both, use each where it shines
App-level fuzzy matching for unit tests, scripts	pg_trgm in a transient DB	Easy to set up, no separate service

A common pattern in production: tsvector for the in-language search results, pg_trgm for the “did you mean” feature next to the search box. Both live in the same Postgres, neither requires Elasticsearch.

Performance: what changes on a real table

The headline number is that an ILIKE '%term%' query against a 50-million-row table goes from 30+ seconds (sequential scan) to under 5 ms with a gin_trgm_ops GIN index. The trade-off is index size and write throughput. A GIN index on a column with average row width of ~200 bytes is roughly 1.5x to 3x the size of the underlying column data. For an 8 GB column that adds 12-24 GB of index. The insert penalty is around 2x for batched inserts and can spike higher under single-row inserts on a hot index.

For a small table (under a few hundred thousand rows), the index is overkill — sequential scan on a hot cache is fast enough and the index pays for itself only when the table grows past cache. For everything between 1M and 100M rows, the index pays off almost always.

When pg_trgm is the wrong tool

Two cases where pg_trgm is the wrong answer:

• Large corpus with rich text analysis. Trigram distance does not understand stemming, stop words, synonyms, or relevance ranking. A search for “running shoes” against a corpus of product names will match any string with three consecutive characters in common; it will not boost “running sneakers” above a literal hit. For this you need a real search backend or Postgres’ tsvector.
• Multilingual text. Trigrams work on any string, but the similarity scoring treats “café” and “cafe” as different because the trigrams don’t align. For non-English text the matcher is less useful than a tokenizing full-text index.

For both cases, the Postgres full-text search documentation is the right next step. The two extensions (pg_trgm and tsvector) often co-exist in the same database for different query patterns.

FAQ

Does pg_trgm work with JSONB?

Yes, with the right setup. The index goes on the JSONB value as text: CREATE INDEX ON events USING GIN ((data->>'name') gin_trgm_ops);. The same indexes work for queries against extracted fields, and the trigram match is local to the extracted string.

Is pg_trgm part of the Postgres core?

It ships with the source distribution in postgresql-contrib, but it is not loaded by default — you CREATE EXTENSION pg_trgm; to enable it. Most managed Postgres platforms expose it as an opt-in extension.

Can I use pg_trgm and full-text search on the same column?

Yes. Build both indexes; let the planner choose. tsvector wins for natural-language queries with stemming and ranking, pg_trgm wins for typo tolerance and similarity scoring. A combined query like “match by FTS, fall back to trigram” is straightforward.

Does pg_trgm slow down writes?

Yes, by roughly the cost of updating the GIN index on every INSERT and UPDATE. For a column-heavy table with sparse updates, the overhead is small. For a hot, fully-updated table, the penalty can be significant. Measure before betting.

Is pg_trgm an alternative to Elasticsearch?

For many apps, yes. For a product catalogue under 10M rows with simple fuzzy matching, pg_trgm on the same Postgres gives you the search feature without the second service. Above 10M rows, or with rich text analysis needs, a real search backend starts to win.

Closing thought

pg_trgm is one of those extensions that sits quietly in the Postgres contrib distribution and gets reinvented weekly as a startup founder discovers the search problem on week six of the project. Most apps do not need Elasticsearch as soon as they think they do — pg_trgm plus a GIN index on the searched column is enough for product lookups, name matching, and the “did you mean” feature. If you are sizing a deployment that will add pg_trgm to a managed Postgres, the RunxBuild hosting calculator gives a quick read on the database and storage costs together, and the managed Postgres docs walk through the extension setup on RunxBuild specifically.