If you’ve ever watched your PostgreSQL queries crawl through millions of rows while your users impatiently tap their fingers, you know the pain of unoptimized LIKE and ILIKE searches. These seemingly simple pattern-matching queries can bring even well-designed databases to their knees, turning what should be instant searches into multi-second ordeals.
The good news? With the right optimization techniques, you can transform those sluggish searches into lightning-fast operations.
Let’s dive into how you can achieve these dramatic performance improvements in your own PostgreSQL database.
1 – LIKE vs ILIKE: syntax and case-sensitivity
Understanding the fundamental difference between LIKE and ILIKE is crucial for optimization. LIKE performs case-sensitive pattern matching, while ILIKE ignores case differences entirely.
-- LIKE: Case-sensitive matching
SELECT * FROM users WHERE username LIKE 'John%';
-- Matches: 'John', 'Johnny', 'Johnson'
-- Doesn't match: 'john', 'JOHN'
-- ILIKE: Case-insensitive matching
SELECT * FROM users WHERE username ILIKE 'John%';
-- Matches: 'John', 'john', 'JOHN', 'Johnny', 'JOHNNY'
-- Using wildcards
SELECT * FROM products WHERE name LIKE '%phone%'; -- Contains 'phone'
SELECT * FROM products WHERE name ILIKE 'smart%'; -- Starts with 'smart' (any case)
SELECT * FROM products WHERE name LIKE '%_pro'; -- Ends with single char + 'pro'
While ILIKE seems more user-friendly, it comes with a significant performance cost that we’ll explore next.
2 – Why LIKE/ILIKE hurt performance
2.1 Full table scan explained
When PostgreSQL encounters a LIKE or ILIKE query, especially with a leading wildcard, if left unoptimized, it often resorts to a sequential scan—reading every single row in your table. Here’s what this looks like :
This is literally the worst case scenario for a query. PostgreSQL will read every single row in the table, which takes a lot of time.
2.2 Why a „basic“ B-tree index often can’t be used
B-tree indexes, PostgreSQL’s default index type, excel at finding exact matches and range queries. However, they’re built on sorted order, which makes them ineffective for:
- Patterns with leading wildcards (
%term) - Case-insensitive searches without proper function indexes
- Complex patterns in the middle of strings
A B-tree index on name can only accelerate queries where the pattern starts with known characters, like 'laptop%', because it can navigate directly to the sorted position where ‚laptop‘ entries begin.
3 – Fix #1: B-tree + suffix-only searches
For patterns that start with known characters (suffix wildcards only like 'lap%' and not '%lap%'), a standard B-tree index works brilliantly.
For ILIKE queries, to achieve the same effect, you need to create a functional index on the lowercased version of the column, and then use the LOWER() function in the query.
-- Create index for case-sensitive prefix searches
CREATE INDEX idx_products_name ON products(name);
-- This query can use the index efficiently
SELECT * FROM products WHERE name LIKE 'laptop%';
-- For case-insensitive prefix searches, use a functional index
CREATE INDEX idx_products_name_lower ON products(LOWER(name));
-- Rewrite your query to use LOWER()
SELECT * FROM products WHERE LOWER(name) LIKE LOWER('Laptop%');
-- Or simply:
SELECT * FROM products WHERE LOWER(name) LIKE 'laptop%';
This approach works perfectly when users search from the beginning of a field, such as autocomplete functionality.
4 – Fix #2: Trigram magic with pg_trgm + GIN
For the holy grail of pattern matching—fast searches anywhere in the string—PostgreSQL’s pg_trgm extension with GIN indexes is your best friend.
4.1 Installing pg_trgm and creating the index
-- Enable the extension (once per database)
CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- Create a GIN index with trigram support
CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);
-- For case-insensitive searches, index the lowercased version
CREATE INDEX idx_products_name_trgm_lower ON products USING GIN (LOWER(name) gin_trgm_ops);
Now your wildcard queries fly:
-- These queries can now use the GIN index
SELECT * FROM products WHERE name LIKE '%laptop%';
SELECT * FROM products WHERE name ILIKE '%LAPTOP%';
SELECT * FROM products WHERE LOWER(name) LIKE '%laptop%';
4.2 The „3-character“ edge-case and how to mitigate
Trigrams work by breaking text into 3-character chunks. Searches for patterns shorter than 3 characters can’t use the index effectively:
-- These won't use the trigram index efficiently
WHERE name LIKE '%a%' -- 1 character
WHERE name LIKE '%ab%' -- 2 characters
Mitigation strategies:
- Set a minimum search length in your application (3+ characters)
- For short patterns, consider using a separate strategy like full-text search
- My favorite, when applicable : if term is less than 3 characters, remove the leading wildcard, and use a B-tree index. If more than 3 characters, you can safely use ‚%abc%‘. Sometimes, the data consistency trade-off is worth it.
5 – When LIKE is not enough: full-text search vs external engines
5.1 In-database full-text search
PostgreSQL’s built-in full-text search offers powerful features beyond simple pattern matching:
-- Create a text search column
ALTER TABLE products ADD COLUMN search_vector tsvector;
-- Populate it with weighted content
UPDATE products SET search_vector =
setweight(to_tsvector('english', name), 'A') ||
setweight(to_tsvector('english', description), 'B');
-- Create a GIN index on the tsvector
CREATE INDEX idx_products_search ON products USING GIN (search_vector);
-- Search with ranking
SELECT name, ts_rank(search_vector, query) AS rank
FROM products, plainto_tsquery('english', 'gaming laptop') query
WHERE search_vector @@ query
ORDER BY rank DESC;
Benefits:
- Language-aware stemming (laptop/laptops match)
- Ranking and relevance scoring
- Phrase searching and boolean operators
- Stop word elimination
5.2 Elasticsearch / OpenSearch / Meilisearch
For ultimate search capabilities, consider dedicated search engines:
Elasticsearch/OpenSearch: Industry standard for complex search requirements
- Fuzzy matching and typo tolerance
- Faceted search and aggregations
- Near real-time indexing
- Distributed architecture for scale
Meilisearch: Modern, developer-friendly alternative
- Instant search as you type
- Built-in typo tolerance
- Simple setup and maintenance
- Great for small to medium datasets
Choose external engines when you need:
- Typo tolerance and fuzzy matching
- Complex relevance tuning
- Multi-language support with advanced analyzers
- Search across multiple data sources
6 – Checklist & FAQ
Before optimizing, answer these questions:
Does your pattern start with %?
If yes, you need GIN indexes with pg_trgm or consider full-text search. B-tree indexes won’t help.
Do you need case-insensitive search?
Use functional indexes with LOWER() instead of ILIKE for better performance. Create indexes on LOWER(column).
Is your average keyword < 3 chars?
Trigram indexes won’t be effective. Consider full-text search or enforce minimum search length.
How large is the table (≥ 1,000,000 rows)?
Large tables amplify the importance of proper indexing.
7 – Key takeaways
• Start with the right pattern: Prefix-only patterns (‚term%‘) can use simple B-tree indexes for massive speedups
• Embrace pg_trgm for flexibility: GIN indexes with trigrams handle any wildcard pattern at the cost of index size
• Case-insensitive doesn’t mean ILIKE: Functional indexes on LOWER() often perform better
• Measure everything: Use EXPLAIN (ANALYZE, BUFFERS) before and after each optimization. For ongoing monitoring, consider tools like QuerySharp to track query performance over time and catch regressions early.
• Know when to go beyond LIKE: Full-text search or dedicated search engines solve problems LIKE queries weren’t designed for
• Index maintenance matters: Remember to VACUUM and ANALYZE regularly, especially after bulk updates
• Consider your growth trajectory: What works at 100K rows might not at 10M rows—plan accordingly
Conclusion
Full disclosure: I’m the founder of QuerySharp, a PostgreSQL query optimization app that helps developers identify and fix slow queries like the ones we’ve discussed in this article.
While writing this guide, I realized how many teams struggle with the same LIKE/ILIKE performance issues we see in our user data every day. The optimization techniques above can dramatically improve your query performance, but the real challenge is often knowing which queries need optimization in the first place.
That’s exactly why we built QuerySharp—to automatically surface slow queries, track performance over time, and help you prioritize which optimizations will have the biggest impact on your users. If you’re dealing with performance issues beyond just LIKE queries, or want to proactively monitor your database health, give it a try.
Whether you use our tool or not, the techniques in this article will serve you well. The key is to measure, optimize, and monitor—your users (and your on-call schedule) will thank you.