Use pg_trgm Instead of Reverse-Column Indexes for Suffix LIKE Searches

knicely87

Searching for values that end with or contain a particular string can be tricky with a traditional index. A predicate such as:

				
					WHERE col1 LIKE '%XXX';

has a leading wildcard, so a normal ordered index on col1 generally can’t turn it into a simple range scan.

One workaround is to store the reversed value in a generated column and index it:

				
					CREATE TABLE t2 (
    id       BIGINT,
    col1     TEXT,
    col1_rev TEXT GENERATED ALWAYS AS (reverse(col1)) STORED,
    PRIMARY KEY (id HASH)
);

CREATE INDEX t2_col1_rev_idx
    ON t2 (col1_rev ASC);

You might hope the optimizer would see:

				
					WHERE col1 LIKE '%XXX';

and automatically transform it into an equivalent search against col1_rev. But that requires understanding the semantics of reverse(), interpreting the LIKE pattern, reversing the literal portion of the pattern, handling %, _, escape characters, collations, and then proving the transformation is equivalent. In practice, the optimizer doesn’t make that leap. The original question and reverse-column approach are shown in the source material for this tip

Fortunately, YugabyteDB has a much better tool for this kind of search: pg_trgm plus a GIN index.

Enter pg_trgm

The PostgreSQL pg_trgm extension breaks text into three-character sequences called trigrams. YugabyteDB supports pg_trgm, including the gin_trgm_ops operator class, specifically to accelerate searches such as:

				
					LIKE '%XXX'
LIKE '%XXX%'
ILIKE '%XXX%'

without changing the application predicate.

Instead of maintaining a reversed copy of the string, create one trigram index on the original column:

				
					CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX logs_token_trgm_idx
    ON user_logs
    USING GIN (session_token gin_trgm_ops);

YugabyteDB accepts the familiar PostgreSQL USING GIN syntax for Yugabyte-backed tables and automatically substitutes its distributed ybgin access method.

💡 The big advantage
Your application can continue issuing normal predicates such as LIKE '%XXX' or LIKE '%XXX%'. There is no generated reverse column, no reversed search string, and no application-side query rewrite.

Let’s See It in YugabyteDB

Create a small test table:

				
					CREATE TABLE user_logs (
    log_id        BIGINT,
    session_token TEXT,
    PRIMARY KEY (log_id HASH)
);

Add a few rows:

				
					INSERT INTO user_logs (log_id, session_token) VALUES
    (1, 'abc-123-token_X99'),
    (2, 'def-456-token_Y11'),
    (3, 'ghi-789-token_X99'),
    (4, 'jkl-012-token_Z88');

Now search for values ending in the pattern:

				
					EXPLAIN
SELECT *
FROM user_logs
WHERE session_token LIKE '%_X99';

The actual YugabyteDB test produced:

				
					                         QUERY PLAN
---------------------------------------------------------------
 Seq Scan on user_logs  (cost=0.00..102.50 rows=1000 width=40)
   Storage Filter: (session_token ~~ '%_X99'::text)
(2 rows)

So YugabyteDB is performing a sequential scan. The predicate appears as a Storage Filter, meaning the filtering can be applied in YugabyteDB’s storage layer rather than requiring every candidate row to be returned to the YSQL layer first. Even so, the underlying access path is still a sequential scan. The actual before-and-after plans from the test show this behavior.

One SQL detail worth noting: _ is itself a single-character wildcard in a LIKE pattern. If you specifically need a literal underscore, escape it explicitly.

Add the Trigram Index

Enable pg_trgm:

				
					CREATE EXTENSION IF NOT EXISTS pg_trgm;

Then create the index:

				
					CREATE INDEX logs_token_trgm_idx
    ON user_logs
    USING GIN (session_token gin_trgm_ops);

Now run the exact same application query:

				
					EXPLAIN
SELECT *
FROM user_logs
WHERE session_token LIKE '%_X99';

This time the test produced:

				
					                                      QUERY PLAN
------------------------------------------------------------------------------------------
 Index Scan using logs_token_trgm_idx on user_logs  (cost=8.00..16.08 rows=1000 width=40)
   Index Cond: (session_token ~~ '%_X99'::text)
(2 rows)

The important change is:

  • ● Before: Seq Scan on user_logs
  • ● After: Index Scan using logs_token_trgm_idx

Nothing about the application’s LIKE predicate changed. The new access path came entirely from adding the trigram index.

🔎 Don’t benchmark from this four-row demo.
The purpose of the example is to demonstrate the change in access path. For a real workload, test with representative data and use EXPLAIN (ANALYZE, DIST, COSTS OFF) to compare storage rows scanned, read requests, and execution time.

Why This Is Better Than the Reverse-Column Trick

A reverse-column index is really designed around one special case: converting a suffix search into a prefix search. Trigrams are more flexible because the same index can help with both suffix and substring searches.

Search Good Index Choice Why
LIKE 'ABC%' Range index, for example (col1 ASC) The known prefix can become an ordered range scan.
LIKE '%ABC' pg_trgm + GIN Handles a leading wildcard without reversing the column.
LIKE '%ABC%' pg_trgm + GIN Trigram indexing can accelerate substring searches as well.

Prefix Searches Are Different in YugabyteDB

If your workload only searches prefixes:

				
					WHERE session_token LIKE 'abc%';

you generally don’t need a trigram index. A normal range-sharded secondary index can be the better fit:

				
					CREATE INDEX logs_token_prefix_idx
    ON user_logs (session_token ASC);

The ASC is important in YugabyteDB. For a normal secondary index, the first index column defaults to HASH; explicitly specifying ASC creates an ordered range key that can support range access. YugabyteDB’s documentation demonstrates a prefix LIKE 'ca%' changing from a sequential scan to an index scan with conditions equivalent to value >= 'ca' AND value < 'cb' after creating an ASC range index.

So there are really two different problems: prefix matching wants ordered range access, while suffix and infix matching are where trigram indexing becomes particularly useful.

A YugabyteDB-Specific Detail: ybgin

Although the SQL looks PostgreSQL-like:

				
					CREATE INDEX logs_token_trgm_idx
    ON user_logs
    USING GIN (session_token gin_trgm_ops);

the underlying YugabyteDB access method is ybgin. YugabyteDB’s documentation notes that USING gin is automatically substituted with ybgin for a Yugabyte-backed relation. Unlike PostgreSQL’s bitmap-based GIN scans, YugabyteDB uses an Index Scan with its distributed storage implementation.

That explains why the plan from the demo says:

				
					Index Scan using logs_token_trgm_idx

rather than a PostgreSQL-style bitmap index plan.

⚠️ GIN Isn’t Free

A trigram GIN index is not automatically the right choice for every text column. GIN indexes store multiple index entries for a row, and YugabyteDB’s current implementation has its own tradeoffs and limitations. The documentation specifically notes that GIN indexes are still under active development, that updates can be relatively expensive, and that an index scan can actually cost more than a sequential scan when the predicate isn’t selective enough.

⚡ Index for the workload, not just the syntax.
A trigram GIN index is especially useful when suffix or substring searches are common and selective enough to avoid scanning a large portion of the table. Measure representative queries before and after adding the index, and remember that every additional index also adds write and storage overhead.

Final Takeaway

If an application needs:

				
					WHERE col1 LIKE '%XXX';

don’t immediately reach for a generated reverse(col1) column and force developers to rewrite their searches. In YugabyteDB, pg_trgm with a GIN index lets you index the original text column and keep the application’s normal LIKE predicate:

				
					CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX col1_trgm_idx
    ON t2
    USING GIN (col1 gin_trgm_ops);

The reverse-column technique still illustrates why ordinary ordered indexes struggle with leading wildcards, but trigram indexing gives YugabyteDB a native access path for suffix and infix searches—and does it without teaching every application how to reverse its search strings.

💡 Simple rule of thumb
For LIKE 'ABC%', think range index. For LIKE '%ABC' or LIKE '%ABC%', think pg_trgm + GIN—and verify the result with YugabyteDB’s distributed EXPLAIN ANALYZE.

Have Fun!