PostgreSQL Full-Text Search vs iLIKE — When to Use Which

The Simple Case: iLIKE

WHERE title ILIKE '%search%' is perfect for small tables (under 100k rows). It's simple, requires no setup, and works intuitively.

The problem: it can't use indexes on the %search% pattern, leading to sequential scans.

Full-Text Search

PostgreSQL's built-in full-text search uses document vectors (tsvector) and query vectors (tsquery):

SELECT * FROM posts
WHERE to_tsvector('english', title || ' ' || excerpt) 
@@ plainto_tsquery('english', 'search query');

GIN Indexes

Full-text search becomes fast with a GIN index:

CREATE INDEX idx_posts_fts ON posts
USING GIN (to_tsvector('english', title || ' ' || excerpt));

My Recommendation

Use iLIKE until your table hits ~50k rows and search becomes noticeably slow. Then migrate to full-text search with a GIN index.