Better Patterns for Case-Insensitive Search in YSQL

Jim Knicely

Case-insensitive search is a common requirement for usernames, email addresses, product names, search boxes, and many other application fields.

A previous YugabyteDB Tip, Case Insensitive Search in YSQL, we looked at an important limitation of CITEXT in YugabyteDB.

CITEXT still provides convenient case-insensitive comparison semantics, allowing queries such as:

				
					WHERE c2 = 'A';

But because YugabyteDB currently cannot create a regular index directly on a CITEXT column, indexed searches typically require an expression index such as:

				
					CREATE INDEX t_c2_idx ON t (UPPER(c2));

and the query must use the same expression:

				
					WHERE UPPER(c2) = 'A';

At that point, much of the convenience that made CITEXT attractive in the first place has been reduced.

So if you’re designing a new schema, it’s worth asking:

  • Are there better ways to model case-insensitive search in YSQL?

The answer depends on the kind of search you need.

For exact case-insensitive lookups, a stored generated column can provide a normalized value that can be indexed directly.

For substring and wildcard searches such as:

				
					ILIKE '%byte%'

the pg_trgm extension together with YugabyteDB’s distributed ybgin index is a better fit.

💡 Start with the search pattern
Exact case-insensitive equality and case-insensitive substring searches are different access patterns. For exact matches, consider a stored generated column containing a normalized value. For wildcard and substring searches such as ILIKE '%search%', pg_trgm with ybgin is generally the better fit.

Option 1: Stored Generated Columns for Exact Matches

Stored generated columns are available in PostgreSQL 15-compatible YugabyteDB releases.

A stored generated column is calculated whenever a row is inserted or updated and occupies storage just like a regular column. YugabyteDB specifically notes that generated columns can be useful for precomputed values used for indexing, sorting, and filtering.

For case-insensitive search, we can use a generated column to maintain a normalized lowercase copy of a value.

Consider an email address:

				
					CREATE TABLE emails (
    id SERIAL PRIMARY KEY,
    raw_email TEXT,
    search_email TEXT GENERATED ALWAYS AS (LOWER(raw_email)) STORED
);

The application writes only the original value:

				
					INSERT INTO emails (raw_email)
VALUES ('Lucy.VanPelt@Peanuts.com');

YugabyteDB automatically calculates search_email.

Query the table:

				
					SELECT * FROM emails;

Result:

				
					.id |      raw_email           |       search_email
----+--------------------------+--------------------------
  1 | Lucy.VanPelt@Peanuts.com | lucy.vanpelt@peanuts.com
(1 row)

Now create an index on the generated column:

				
					CREATE INDEX emails_search_idx
ON emails(search_email);

Because search_email is a regular TEXT column, it can be indexed directly.

YugabyteDB uses its default distributed lsm access method for indexes on YugabyteDB tables.. not PostgreSQL’s physical B-tree implementation.

Now an exact case-insensitive lookup becomes:

				
					SELECT *
FROM emails
WHERE search_email = 'lucy.vanpelt@peanuts.com';

Verify the Index with EXPLAIN (ANALYZE, DIST)

One of the nice things about YugabyteDB is that DIST lets us see what happened at the distributed storage layer.

				
					EXPLAIN (ANALYZE, DIST)
SELECT *
FROM emails
WHERE search_email = 'lucy.vanpelt@peanuts.com';

The test produced:

				
					Index Scan using emails_search_idx on emails
. Index Cond: (search_email = 'lucy.vanpelt@peanuts.com')

. Storage Table Read Requests: 1
. Storage Table Rows Scanned: 1

. Storage Index Read Requests: 1
. Storage Index Rows Scanned: 1

The index located the matching entry, and YugabyteDB fetched the corresponding row from the base table. Only one index row and one table row were scanned in the test.

🔎 Why use a generated column?
A generated column makes the normalized value an explicit part of the schema. YugabyteDB maintains it automatically whenever the source column changes, and the normalized TEXT value can be indexed directly using a regular YugabyteDB LSM index.

Generated Columns vs. Expression Indexes

A generated column is not the only option for exact case-insensitive searches.

You can still use an expression index:

				
					CREATE INDEX emails_lower_idx
ON emails (LOWER(raw_email));

and query using:

				
					SELECT *
FROM emails
WHERE LOWER(raw_email) = 'ucy.vanpelt@peanuts.com';

That remains a perfectly valid approach.

The difference is mostly about where you want the normalization logic to live.

With an expression index:

				
					LOWER(raw_email)

is part of the index definition and must also be expressed appropriately in the query.

With a generated column:

				
					search_email

becomes a first-class column in the schema.

⚠️ Generated columns don’t make the original column case-insensitive
The application still needs to query the generated column, such as search_email, and the search value should be normalized as well. A generated column moves the normalization logic into the schema, but it does not automatically make queries against raw_email case-insensitive.

That distinction is important.

The generated-column approach is useful when you want the normalized representation to be explicit and reusable throughout the schema and application.

Option 2: pg_trgm + ybgin for Substring Searches

Exact equality is only one kind of case-insensitive search.

Suppose an application needs to search product names using:

				
					WHERE product_name ILIKE '%byte%'

A leading wildcard presents a very different indexing problem.

This is where PostgreSQL’s pg_trgm extension and YugabyteDB’s distributed ybgin implementation become useful.

First, enable the extension:

				
					CREATE EXTENSION IF NOT EXISTS pg_trgm;

Now create a test table:

				
					CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    product_name TEXT
);

Insert a few rows:

				
					INSERT INTO products (product_name)
VALUES
    ('YugabyteDB Managed'),
    ('YugabyteDB Anywhere'),
    ('PostgreSQL');

Create the trigram index:

				
					CREATE INDEX products_name_trgm_idx
ON products
USING GIN (product_name gin_trgm_ops);

Now search for byte anywhere in the value:

				
					SELECT *
FROM products
WHERE product_name ILIKE '%byte%';

Result:

				
					.id |    product_name
----+---------------------
  1 | YugabyteDB Managed
  2 | YugabyteDB Anywhere
(2 rows)

Verify ybgin with EXPLAIN (ANALYZE, DIST)

Run:

				
					EXPLAIN (ANALYZE, DIST)
SELECT *
FROM products
WHERE product_name ILIKE '%byte%';

The test produced:

				
					Index Scan using products_name_trgm_idx on products
. Index Cond: (product_name ~~* '%byte%'::text)

. Storage Table Read Requests: 1
. Storage Table Rows Scanned: 2

. Storage Index Read Requests: 1
. Storage Index Rows Scanned: 2

Instead of scanning every row in the base table, YugabyteDB used the trigram index to identify the two matching rows.

🔎 Why trigrams?
A predicate such as ILIKE '%byte%' has no fixed beginning because the leading % can match any number of characters. pg_trgm breaks strings into three-character sequences and indexes those components, making substring and wildcard searches much more index-friendly.

A YugabyteDB-Specific Detail: GIN Means ybgin

This is an important place where PostgreSQL terminology can become misleading.

When you run:

				
					CREATE INDEX products_name_trgm_idx
ON products
USING GIN (product_name gin_trgm_ops);

YugabyteDB is not simply using PostgreSQL’s underlying GIN storage implementation.

For YugabyteDB-backed tables, GIN indexes are implemented using YugabyteDB’s distributed ybgin access method. YugabyteDB documentation also allows ybgin to be specified explicitly.

⚠️ Remember: this is YugabyteDB, not PostgreSQL storage
YSQL provides PostgreSQL-compatible syntax, but YugabyteDB uses its distributed DocDB storage engine underneath. Regular YugabyteDB indexes use the lsm access method, while distributed GIN indexes use ybgin. Avoid assuming PostgreSQL’s physical B-tree or GIN implementation is being used underneath YSQL.

What About ILIKE 'byte' Without Wildcards?

Here’s an interesting result from the same test.

The wildcard search:

				
					SELECT *
FROM products
WHERE product_name ILIKE '%byte%';

produced:

				
					Seq Scan on products
  Storage Filter: (product_name ~~* 'byte'::text)

  Storage Table Read Requests: 1
  Storage Table Rows Scanned: 3

Why?

The test table contains only three rows.

The optimizer decided it was cheaper to scan those three rows than to traverse the trigram index.

That does not mean pg_trgm can never help with an exact ILIKE predicate. It means the optimizer chooses the access path it estimates will be cheapest for the data and query.

This is exactly why you should verify behavior using:

				
					EXPLAIN (ANALYZE, DIST)

rather than assuming an index will, or should, be used for every query.

What About a ICU Collation Option?

If you search for PostgreSQL solutions to this problem, another recommendation you may find is a non-deterministic ICU collation.

For example:

				
					CREATE COLLATION case_insensitive (
    provider = icu,
    locale = 'und-u-ks-level2',
    deterministic = false
);

This can provide elegant case-insensitive comparison behavior in PostgreSQL.

But it is not currently a YugabyteDB workaround.

Testing it in YugabyteDB returns:

				
					ERROR: nondeterministic collation is not supported

The current YugabyteDB PostgreSQL 15 compatibility documentation also explicitly lists nondeterministic collations as not yet implemented.

⚠️ PostgreSQL-compatible doesn’t mean every PostgreSQL technique applies
Non-deterministic ICU collations are a useful PostgreSQL technique for case-insensitive comparisons, but they are not currently supported in YugabyteDB. When researching PostgreSQL solutions, always verify that the specific feature and indexing behavior are supported by YugabyteDB.

Which Approach Should You Use?

Here is the quick comparison:

Use Case Approach Example
Exact case-insensitive lookup Stored generated column + regular LSM index search_email = 'john@example.com'
Expression-based exact lookup Expression index LOWER(email) = LOWER(...)
Substring / wildcard search pg_trgm + ybgin ILIKE '%byte%'
Direct CITEXT indexing Not currently supported Use one of the alternatives above
Non-deterministic ICU collation Not currently supported deterministic = false

YugabyteDB documentaion currently lists indexes on complex data types including CITEXT among unsupported PostgreSQL features.

A Simple Decision Guide

For an exact lookup such as:

				
					WHERE email = 'john@example.com'

where the comparison needs to be case-insensitive, consider normalizing the value into a stored generated column:

				
					search_email TEXT
GENERATED ALWAYS AS (LOWER(raw_email)) STORED

and index:

				
					CREATE INDEX emails_search_idx
ON emails(search_email);

If your application is already comfortable using expressions, an expression index may be even simpler:

				
					CREATE INDEX emails_lower_idx
ON emails(LOWER(raw_email));

For substring or wildcard search:

				
					WHERE product_name ILIKE '%byte%'

reach for:

				
					pg_trgm + ybgin

And if you are migrating an existing PostgreSQL application that already depends on CITEXT, keeping CITEXT may still make sense for compatibility… even though indexed access requires additional consideration.

Final Takeaway

CITEXT can still be useful in YugabyteDB, particularly when maintaining compatibility with an existing PostgreSQL schema or application.

But for a new schema, it is worth choosing the case-insensitive search strategy based on the actual access pattern.

For exact lookups, a stored generated column gives you an explicit normalized value that YugabyteDB can index using its distributed LSM index.

For wildcard and substring searches:

				
					ILIKE '%search%'

use pg_trgm with ybgin.

And expression indexes remain a simple option when using LOWER() or UPPER() in the application query is acceptable.

Most importantly, don’t assume the access path.

Verify it:

				
					EXPLAIN (ANALYZE, DIST)

That tells you not only whether YugabyteDB selected an index, but also how much work occurred at the distributed storage layer.

✅ Quick rule of thumb
Exact case-insensitive lookup? Consider a generated normalized column or an expression index. Searching inside strings with ILIKE '%...%'? Reach for pg_trgm and ybgin. Then confirm the actual access path with EXPLAIN (ANALYZE, DIST).

Have Fun!

I took this picture about 10 years ago when my high school buddies and I visited NYC for the first time together.

Hard to believe it has been 25 years since the tragedy of September 11, 2001.

I’ll be back in New York City for the Postgres Summit, September 30–October 2, and I plan to revisit the World Trade Center site and the 9/11 Memorial. It’s a place that always puts the events of that day, and the lives lost, into perspective. 🇺🇸