LIKE vs. PGroonga in PostgreSQL: Which One Should You Use?

Introduction

When it comes to searching text in PostgreSQL, many developers start with LIKE or ILIKE. While these operators work fine for simple queries, they become inefficient for large datasets and complex multilingual search needs. Enter PGroonga, a powerful full-text search extension that significantly improves performance, especially for languages like Japanese, Chinese, and Korean.

In this blog, we’ll compare LIKE vs. PGroonga, highlighting their differences, performance, and best use cases.

---

What is LIKE in PostgreSQL?

LIKE and ILIKE are basic pattern-matching operators in PostgreSQL:

• LIKE is case-sensitive
• ILIKE is case-insensitive
• % is used as a wildcard for any number of characters
• _ is used as a wildcard for a single character

Example of LIKE Query in Laravel Eloquent:

$searchTerm = 'database';
$results = Document::where('content', 'LIKE', "%{$searchTerm}%")->get();

Limitations of LIKE

• ❌ Slow for large datasets (full table scan required)
• ❌ No proper support for Japanese, Chinese, or Korean
• ❌ No ranking or relevance-based results
• ❌ Does not work well for full-text search

---

What is PGroonga?

PGroonga is a PostgreSQL extension that provides high-speed full-text search with support for multiple languages, including English, Japanese, Chinese, and Korean.

Why PGroonga is Better Than LIKE

• ✅ Super fast (indexed search instead of full table scan)
• ✅ Understands word boundaries for Japanese, Chinese, and Korean
• ✅ Works on normal text columns and JSONB fields
• ✅ Provides ranking for search relevance

Example of PGroonga in Laravel Eloquent:

$searchTerm = 'データベース'; // or 'database'
$results = Document::whereRaw("content &@~ ?", [$searchTerm])->get();

---

Performance Comparison: LIKE vs. PGroonga

Feature	LIKE / ILIKE	PGroonga
Speed	🐢 Slow (full table scan)	⚡ Super fast (indexed search)
Index Support	❌ No	✅ Yes
Case Sensitivity	❌ LIKE is case-sensitive, ILIKE is case-insensitive	✅ Supports case-insensitive search
Multilingual Support	❌ No proper handling of Japanese/Chinese/Korean	✅ Perfect for multilingual search
Ranking of Results	❌ No ranking, just matches	✅ Results ranked by relevance
Works on JSONB	❌ No	✅ Yes

---

How to Set Up PGroonga in PostgreSQL

Step 1: Install the PGroonga Extension

Run this command in PostgreSQL:

CREATE EXTENSION pgroonga;

Step 2: Create an Index for Faster Search

For normal text columns:

CREATE INDEX documents_pgroonga_idx ON documents USING pgroonga (content);

For JSONB fields:

CREATE INDEX documents_pgroonga_jsonb_idx ON documents USING pgroonga ((data->>'content'));

Step 3: Use PGroonga Search in Laravel

$searchTerm = 'データベース';
$results = Document::whereRaw("content &@~ ?", [$searchTerm])->get();

---

Conclusion: When to Use What?

• For small datasets or simple searches → LIKE is fine
• For large datasets, multilingual search (Japanese, Chinese, Korean) → ✅ Use PGroonga
• For ranking search results and better performance → ✅ Use PGroonga
• For JSONB fields and flexible text search → ✅ Use PGroonga

🚀 Want to boost your PostgreSQL search performance? Switch to PGroonga today!