ILIKE
Cheat Sheet
Prime Use Case
Use ILIKE when you need to perform flexible text searches where the casing of the stored data or the user input is unpredictable or inconsistent.
Critical Tradeoffs
- Simplicity of syntax vs. lack of SQL-standard portability
- Ease of implementation vs. significant performance overhead on large datasets
- Case-insensitivity vs. the inability to leverage standard B-Tree indexes
Killer Senior Insight
In a production environment, ILIKE is often a performance 'landmine'; while it solves the casing problem, it defaults to a Sequential Scan unless you explicitly back it with a Trigram index (pg_trgm) or a functional index.
Recognition
Common Interview Phrases
Common Scenarios
- Global search bars in web applications
- Filtering email addresses in a CRM
- Normalizing user-generated tags for reporting
Anti-patterns to Avoid
- Using ILIKE on a primary key or UUID column
- Using ILIKE when the collation of the database is already case-insensitive
- Applying ILIKE to columns that should have been normalized to lowercase at the ingestion layer
The Problem
The Fundamental Issue
The SQL standard LIKE operator is case-sensitive, meaning 'Apple' and 'apple' are treated as distinct values, leading to missed results in user-facing searches.
What breaks without it
Incomplete search results leading to poor user experience
Requirement for complex and verbose LOWER(column) LIKE LOWER('%value%') syntax
Data fragmentation where 'Admin' and 'admin' are treated as different entities
Why alternatives fail
Standard LIKE requires exact case matching which is brittle for human-entered data
Manual case-folding (LOWER/UPPER) is repetitive and can be less efficient than engine-level case-insensitive operators
Mental Model
The Intuition
Imagine a librarian searching for a book title; a standard search requires the exact capitalization on the cover, whereas an ILIKE search ignores the 'shape' of the letters and only cares about the characters themselves.
Key Mechanics
The database engine performs case-folding on both the column value and the pattern
It evaluates the pattern matching algorithm (NFA/DFA) against the folded strings
By default, it iterates through every row (Sequential Scan) because standard B-Tree indexes are sorted by binary value, not case-insensitive value
Framework
When it's the best choice
- Small to medium-sized tables where readability is prioritized over micro-optimizations
- Prototyping search functionality in PostgreSQL environments
- When the pattern is anchored at the end or middle, making standard indexes less useful anyway
When to avoid
- High-concurrency production systems with millions of rows
- When portability to MySQL or SQL Server is required (they use different mechanisms for case-insensitivity)
- When the search term is a prefix (e.g., 'term%'), as LIKE with a proper collation would be faster
Fast Heuristics
Tradeoffs
Strengths
- Extremely readable and concise syntax
- Built-in support for Unicode case-folding in modern Postgres versions
- Eliminates the need for redundant LOWER() function calls
Weaknesses
- Non-standard SQL (Postgres only), reducing code portability
- Incompatible with standard B-Tree indexes, leading to O(N) complexity
- Higher CPU overhead compared to case-sensitive LIKE
Alternatives
When it wins
When you need the query to be portable across different SQL dialects (MySQL, Oracle, Postgres).
Key Difference
Explicitly transforms both sides to lowercase before comparison; can be indexed using a Functional Index.
When it wins
When a column is almost always searched case-insensitively (e.g., email, username).
Key Difference
A PostgreSQL extension type that treats all comparisons as case-insensitive by default, simplifying all queries on that column.
When it wins
When searching through large bodies of text or requiring stemming and ranking.
Key Difference
Uses inverted indexes and linguistic rules rather than simple character matching.
When it wins
When you must use ILIKE on large datasets and need to support 'contains' (%pattern%) searches.
Key Difference
Breaks strings into 3-character chunks to allow index-assisted fuzzy and case-insensitive matching.
Execution
Must-hit talking points
- Mention that ILIKE is a PostgreSQL extension and not part of the SQL standard.
- Highlight the performance implications: ILIKE usually triggers a full table scan.
- Discuss indexing strategies like functional indexes on LOWER(column) or using the pg_trgm extension.
- Explain the difference between ILIKE and case-insensitive collations.
Anticipate follow-ups
- Q:How would you optimize an ILIKE '%term%' query on a table with 100 million rows?
- Q:What is the difference between ILIKE and using a case-insensitive collation at the database level?
- Q:How does ILIKE handle non-ASCII characters or different locales?
Red Flags
Assuming ILIKE is available in all SQL databases.
Why it fails: It is a Postgres-specific keyword; using it in MySQL or SQL Server will result in a syntax error.
Using ILIKE with a leading wildcard (%term) and expecting it to be fast.
Why it fails: Leading wildcards prevent the use of standard B-Tree indexes, and ILIKE further compounds this by ignoring the sort order of the index.
Neglecting the impact of Locales/LC_CTYPE.
Why it fails: Case-insensitivity rules change based on the database locale (e.g., the 'Turkish I' problem), which can lead to unexpected search results.