🔤LIKE vs full-text search

Pattern match vs token index

LIKE walks characters (often with sequential scans when patterns start with %). Full-text search tokenizes, normalizes language, and scores hits using inverted indexes, better for user-facing discovery, heavier to maintain on ingest.

⚖️Compare the models

Pick by workload, not habit.

Aspect	LIKE	Full-text
Matching model	Substring / simple wildcards	Token relevance + language rules
Ranking	No built-in score	Rank, highlight, and snippet helpers (engine-specific)
Indexability	Hard with leading % on B-trees	Inverted indexes (GiN, etc.) on lexemes

SQL

LIKE scan (illustrative)

SELECT paper_id FROM papers WHERE abstract LIKE '%CRISPR%';

✓Illustrative result set

paper_id
8812
9021

SQL

Full-text (PostgreSQL)

SELECT paper_id FROM papers
WHERE tsv @@ plainto_tsquery('english', 'CRISPR');

✓Illustrative result set

paper_id
8812
7650

Wrong tool for catalog search

Global search built on LIKE '%term%' across millions of rows.
Assuming FTS relevance matches business ranking without tuning weights or boosts.
Ignoring language packs for multilingual catalogs.

Prototype with plans and p95

Document which fields are exact match, substring (trigram), or tokenized.
Compare EXPLAIN and p95 on realistic row counts before launch.
Fallback to trigram for partial substring needs; reserve FTS for token relevance.

Product UX

Validation masks and admin filters often only need LIKE with bounded length, end-user discovery usually needs ranking and stemming; do not merge those requirements into one accidental query path.

TL;DR

Use LIKE for simple substring/prefix filters on modest data; use full-text search when you need tokenization, stemming, ranked results, and fast containment on large corpora. LIKE '%term%' on big tables is usually a full scan; FTS indexes trade write cost and complexity for search-time performance.