Full Text Search vs LIKE in PostgreSQL: Key Differences & Usage

In PostgreSQL, LIKE is a simple pattern matching operator that searches for substrings using wildcards, while full text search is a powerful feature designed to search natural language text efficiently using indexes and ranking. Full text search is faster and more accurate for large text data and complex queries, whereas LIKE is easier for simple substring matches.

Quick Comparison

This table summarizes the main differences between LIKE and full text search in PostgreSQL.

Feature	LIKE	Full Text Search
Purpose	Simple pattern matching	Natural language search with ranking
Syntax	Uses % and _ wildcards	Uses to_tsvector and to_tsquery functions
Performance	Slower on large text without indexes	Fast with GIN/GiST indexes
Case Sensitivity	Case-sensitive by default	Case-insensitive
Search Type	Substring matching	Lexeme-based matching with stemming
Ranking Results	No ranking, matches or no matches	Supports relevance ranking

Key Differences

LIKE is a simple operator that matches text patterns using wildcards like % for any sequence of characters and _ for a single character. It works well for small datasets or simple substring searches but can be slow on large text columns because it cannot use specialized text indexes effectively.

In contrast, full text search in PostgreSQL is designed to handle natural language queries. It breaks text into lexemes (words normalized by removing suffixes), ignores stop words, and supports ranking results by relevance. It uses special indexes like GIN or GiST to speed up searches, making it much faster and scalable for large text data.

Another key difference is case sensitivity: LIKE is case-sensitive unless you use ILIKE, while full text search is case-insensitive by default. Full text search also supports complex queries with logical operators, phrase search, and weighting, which LIKE cannot do.

Code Comparison

Here is an example of searching for rows containing the word 'cat' using LIKE:

sql

SELECT id, content FROM articles WHERE content LIKE '%cat%';

Output

Returns all rows where 'content' contains the substring 'cat', e.g. 'The cat sat on the mat.'

Full Text Search Equivalent

Here is how to perform a similar search using PostgreSQL full text search:

sql

SELECT id, content FROM articles WHERE to_tsvector('english', content) @@ to_tsquery('cat');

Output

Returns rows where 'content' contains the word 'cat' or its variants, ranked by relevance if ORDER BY is added.

When to Use Which

Choose LIKE when you need simple substring matching on small datasets or quick ad-hoc queries without setup. It is easy to use but not efficient for large text or complex searches.

Choose full text search when working with large text data, needing fast searches, relevance ranking, or natural language queries. It requires some setup with indexes but provides powerful and scalable search capabilities.

Key Takeaways

Use LIKE for simple, small-scale substring searches without indexing.

Use full text search for fast, scalable, and relevant natural language searches.

Full text search supports stemming, stop words, and ranking, unlike LIKE.

LIKE is case-sensitive by default; full text search is case-insensitive.

Indexing with GIN/GiST is essential for performance in full text search.