Full-Text Search vs. LIKE: When to Use Which for Enterprise Search

Look, you know the old mantra: "If all you have is a hammer, everything looks like a nail." In the world of SQL, `LIKE` is that trusty hammer. It works! You need to find "%john%" across a few thousand user records? Fine. No problem. But here's the thing: you're not running a lemonade stand database anymore. This is enterprise-level. When your user table hits a million rows and you're trying to find "philodendron maintenance tips" in a text blob? That `LIKE '%maintenance%'` scan is going to feel like you're trying to chop down a redwood with a butter knife. It's slow. It chokes on special characters. It has zero understanding of language. Time to put down the blunt instrument.

What Full-Text Search ACTUALLY Does (It's Not Magic)

It’s not magic. It's preprocessing. Think of it like organizing a library. A `LIKE` operator is you running through every single book, opening it, and scanning each line. Full-text search (FTS), like PostgreSQL's built-in system, is the librarian who pre-catalogs everything. It breaks text down into tokens (words), throws out the noise ('the', 'a', 'and'), reduces words to their root form ('running' becomes 'run'), and builds a special index. When you search for "ran quickly," the FTS engine consults its pre-built catalog. It knows "ran" stems from "run" and finds all related entries. Instantly. That's the game.

The Speed Difference Isn't Subtle. It's a Snail vs. a Sports Car.

Let's talk raw performance, because that's what your CFO cares about. The `LIKE` operator does something called a sequential scan. Row. By. Painful. Row. It can't use a normal B-tree index for patterns with a leading wildcard (`%search`). It's O(n) complexity. Your table grows, your query time grows linearly. Full-text search uses specialized index types, primarily GIN (Generalized Inverted Index). This index is built for exactly this job—looking up which rows contain which pre-processed tokens. It's O(log n). The difference? On a million-row table, a complex `LIKE` can take seconds or minutes. The FTS equivalent returns in milliseconds. It's the difference between a loading bar and a blink.

The Catch: FTS Isn't Perfect for Everything (Sorry)

Here's where people get tripped up. FTS is brilliant for *linguistic* search. Finding concepts, relevant documents, human language. But what about "Catherine" vs. "Katharine"? Or "Schwarzenegger"? A basic FTS configuration will fail those fuzzy, misspelling-heavy searches. That's where `LIKE` still has a weird, tiny niche... or more accurately, where PostgreSQL's `pg_trgm` extension comes in. Trigram indexes break text into three-character chunks, making them fantastic for that fuzzy, "I'm not sure how to spell it" matching. So the toolbox has multiple tools: GIN for FTS (language), GIN for trigrams (fuzzy), and yeah, `LIKE` for dead-simple, tiny-table stuff.

So, When Do I Actually Switch?

The rule of thumb is simple. Are you searching more than, say, 10,000 rows of text? You need FTS. Is your search a core feature of the application? You need FTS. Are users expecting Google-like relevance, not just literal substring matches? FTS. `LIKE` is for admin panels, filtering exact codes or usernames, or tiny lookup tables. The moment you smell performance pain or your product manager asks for "better search," it's time to migrate. Implementing it in PostgreSQL isn't even that hard: define a tsvector column, build a GIN index on it, and query with `to_tsquery`. The complexity is upfront. The payoff is every single query, forever.