LIKE in PostgreSQL: ILIKE, Wildcards, and Why It Ignores Your Index

The LIKE operator in PostgreSQL does simple pattern matching: % matches any run of characters, _ matches exactly one, and WHERE name LIKE 'A%' finds everything starting with A. Postgres adds ILIKE, the case-insensitive version, which is the one you usually want for user-facing search. The thing nobody tells you until it is slow: a leading-wildcard pattern like LIKE '%foo%' cannot use a normal index, so it scans the whole table - fine on a thousand rows, a problem on a million.

The syntax takes two minutes. The performance behavior is the part that decides whether LIKE is the right tool or a trap you grow into.

The wildcards
ILIKE: case-insensitive matching
Escaping % and _ when you mean them literally
Why LIKE ‘%foo%’ is slow, and what to do
How this fits the rest of the stack
FAQ

The wildcards

SELECT * FROM users WHERE name LIKE 'A%';    -- starts with A
SELECT * FROM users WHERE name LIKE '%son';  -- ends with son
SELECT * FROM users WHERE name LIKE '%ada%'; -- contains ada
SELECT * FROM users WHERE code LIKE 'A_C';   -- A, any one char, C

% is zero-or-more characters, _ is exactly one. That is the whole pattern language for LIKE - it is deliberately simpler than a regular expression. If you need real regex, Postgres has the ~ operator (~* for case-insensitive), but for prefix, suffix, and contains checks, LIKE is clearer and enough.

ILIKE: case-insensitive matching

SELECT * FROM users WHERE name LIKE 'ada%';   -- misses 'Ada'
SELECT * FROM users WHERE name ILIKE 'ada%';  -- matches 'Ada', 'ADA', 'ada'

ILIKE is Postgres-specific (it is not standard SQL) and is what you want for almost any human-facing search box, because users do not type consistent capitalization. The common alternative, WHERE LOWER(name) LIKE LOWER('ada%'), works too and is portable, but ILIKE reads better and lets Postgres consider a case-insensitive index directly.

Escaping % and _ when you mean them literally

-- Find rows whose code literally contains a percent sign
SELECT * FROM items WHERE code LIKE '%\%%' ESCAPE '\';

-- Or use a different escape char if backslash is awkward
SELECT * FROM items WHERE code LIKE '%!_%' ESCAPE '!';

If the value you are searching for actually contains % or _, you must escape it or the wildcard matches everything. The ESCAPE clause names the escape character. This bites people searching for things like discount codes (‘50% off’) or filenames with underscores, where the literal character is silently treated as a wildcard.

Why LIKE ‘%foo%’ is slow, and what to do

A B-tree index on a text column can satisfy LIKE 'foo%' - a prefix, anchored on the left, so the index range is well-defined. It cannot satisfy LIKE '%foo%', because a leading wildcard means the match could start anywhere, and there is no ordered range to seek. So a contains-search does a full sequential scan.

Prefix search ('foo%')? A regular B-tree index (with the right operator class, text_pattern_ops) makes it fast.
Contains or fuzzy search ('%foo%')? Use a trigram index: CREATE EXTENSION pg_trgm; then a GIN index with gin_trgm_ops. It makes ILIKE '%foo%' index-assisted.
Full-text search? For real search over documents, tsvector and tsquery are the right tool, not LIKE at all.

The rule: LIKE for prefix and exact-pattern checks, pg_trgm when you genuinely need substring search at scale, and full-text search when you are building an actual search feature. Using LIKE '%term%' as your search engine is fine until the table grows, and then it is the slow query at the top of every report.

How this fits the rest of the stack

LIKE is a good example of a feature that is correct everywhere and fast only sometimes - the performance cliff is invisible until the data finds it. Sizing a database for the queries you will actually run, indexes included, is the same forward-looking exercise. The RunxBuild hosting calculator shows a managed Postgres, its connections, and storage as line items, and the RunxBuild dashboard is where you provision it and watch which queries are doing the work.

Useful related references:

FAQ

What is the difference between LIKE and ILIKE in PostgreSQL?

LIKE is case-sensitive pattern matching; ILIKE is the case-insensitive version. LIKE ‘ada%’ would not match ‘Ada’, but ILIKE ‘ada%’ matches Ada, ADA, and ada. ILIKE is Postgres-specific and is the natural choice for user-facing search boxes, where people do not type consistent capitalization. The standard-SQL alternative is LOWER(col) LIKE LOWER(pattern).

What do % and _ mean in a Postgres LIKE pattern?

% matches zero or more characters and _ matches exactly one character. So LIKE ‘A%’ matches anything starting with A, LIKE ‘%son’ matches anything ending in son, and LIKE ‘A_C’ matches A followed by any single character then C. These two wildcards are the entire LIKE pattern language; for anything more complex use regular expressions with the ~ operator.

Why is LIKE ‘%foo%’ slow in PostgreSQL?

Because a leading wildcard means the match can start anywhere in the string, so a normal B-tree index has no ordered range to seek and Postgres falls back to a full sequential scan. Prefix patterns like ‘foo%’ can use an index; contains patterns like ‘%foo%’ cannot, unless you add a trigram (pg_trgm) GIN index designed for substring search.

How do I make LIKE searches use an index in Postgres?

For prefix searches, create a B-tree index with the text_pattern_ops operator class. For substring or case-insensitive contains searches, enable the pg_trgm extension and build a GIN index with gin_trgm_ops, which lets ILIKE ‘%term%’ use the index. For genuine document search, use full-text search with tsvector and tsquery instead of LIKE.

How do I search for a literal % or _ with LIKE?

Escape it and declare the escape character with the ESCAPE clause: WHERE code LIKE ’%%%’ ESCAPE ” finds values containing a literal percent sign. You can choose a different escape character, such as ESCAPE ’!’, if backslash is inconvenient. Without escaping, a literal % or _ in your search term is treated as a wildcard and matches far more than intended.