Search Engines and Full-Text Search

Intermediate · 12 min

Why LIKE queries don't scale for text search, how an inverted index fixes that, and when to reach for a dedicated search engine instead.

Before thisThe N+1 Query Problem and Eager Loading

myDBA · 12 min

Search Engines and Full-Text Search

The player loads from youtube-nocookie.com when you press play. Nothing reaches YouTube before that.

Taught by myDBAReport a problem with this video
Not started
Set this lesson's status

What you'll learn

  • A query like WHERE description LIKE '%term%' can't use a normal B-tree index, because the wildcard on both sides means the database can't jump to a starting point — it has to scan every row and check each one.
  • An inverted index flips the problem around: instead of indexing rows by their position, it indexes words by which rows contain them, so looking up a word is a fast lookup instead of a scan.
  • Postgres ships a real full-text search engine (tsvector, tsquery, GIN indexes, ts_rank) and for a large share of applications that's genuinely enough — it's one less service to run and operate.
  • A dedicated search engine like Elasticsearch, Algolia, or Meilisearch earns its operational cost when you need things Postgres's full-text search doesn't give you out of the box: fine-grained relevance tuning, faceted filtering, typo tolerance, and horizontal scale across a cluster.
  • The decision isn't 'which is better' in the abstract — it's whether your search needs have crossed the line from 'find rows containing these words' into 'build a genuine search product,' and that line is usually visible in your actual requirements, not a performance benchmark.

The query that looks fine until your table grows

Say you’re building search for a product catalog, and the obvious first move is:

SELECT * FROM products WHERE name LIKE '%running shoes%';

This works. It returns the right rows. It’ll also get slower, roughly linearly, as your table grows — and the reason is worth understanding, because it’s the whole reason dedicated search tooling exists.

A normal index on name (a B-tree, the default kind) is built to answer “find values starting with X” fast, by keeping values in sorted order so the database can jump straight to the right spot, the way you’d flip straight to the “R” section of a phone book. That works great for name LIKE 'running%' — a prefix match. But '%running shoes%' has a wildcard on both sides. The match could start anywhere inside the string. There’s no sorted position to jump to, because “contains this substring somewhere” isn’t something a sort order helps you find. So Postgres falls back to reading every row and checking each one by hand — a sequential scan. On a thousand rows, you won’t notice. On ten million, every search query is now scanning ten million rows.

Flip the index around

The fix is a different kind of index, built on a different idea: instead of indexing rows by their contents, index words by which rows contain them. This is called an inverted index, and it’s the same trick as the index at the back of a textbook. The book doesn’t make you flip through all 400 pages looking for every mention of “mitochondria” — the index at the back already lists “mitochondria: pages 12, 45, 201,” built once, ahead of time, so look-up is instant.

Concretely, an inverted index looks something like this, conceptually:

"running" → [row 5, row 12, row 203, row 891]
"shoes"   → [row 5, row 44, row 203, row 1200]
"blue"    → [row 12, row 203]

To find rows matching “running shoes,” you look up both word-lists and intersect them — a fast operation on two short lists of row IDs — instead of scanning the whole table. Building this index costs something up front (every insert or update has to update the relevant word-lists), but that’s a trade worth making: you pay a small, bounded cost on writes to get a large, scaling-independent win on reads, which is exactly the trade you want for something read far more often than it’s written.

This is the structural idea underneath every real text-search system, whether that’s Postgres’s built-in full-text search or a dedicated engine like Elasticsearch. They differ in what they build on top of that idea, not in whether they use one.

Postgres already has one built in

This is the part a lot of backend developers don’t realize until they need it: Postgres ships a genuine full-text search engine, not just the ability to use extensions to fake one. Two types do the work:

  • tsvector is what your searchable text gets turned into — a normalized, tokenized form of a document. “Running Shoes” becomes something like a list of lexemes ('run':1 'shoe':2), lowercased and stemmed so that “running,” “run,” and “runs” all collapse to match each other.
  • tsquery is the same normalization applied to a search term, with boolean operators available (running & shoe, running | walking).
ALTER TABLE products ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (to_tsvector('english', name || ' ' || description)) STORED;

CREATE INDEX idx_products_search ON products USING GIN (search_vector);

SELECT *, ts_rank(search_vector, query) AS rank
FROM products, to_tsquery('english', 'running & shoe') query
WHERE search_vector @@ query
ORDER BY rank DESC;

The GIN index here is Postgres’s actual inverted-index implementation — it’s doing exactly the word-to-rows lookup described above. ts_rank gives you basic relevance scoring, so better matches sort first instead of getting results back in arbitrary order. For a huge range of applications — a blog’s search box, an internal admin tool, a small-to-mid-size product catalog — this is genuinely enough, and it means one fewer service in your infrastructure to run, monitor, and keep in sync with your primary data.

When a dedicated engine earns its cost

Elasticsearch, Algolia, and Meilisearch exist because, past a certain point, “enough” stops being enough — and what you’re missing is specific, not vague:

  • Relevance ranking that goes beyond basic scoring. A dedicated engine lets you tune relevance across many weighted fields (title matches count more than body matches), apply decay functions (newer listings rank slightly higher), and combine signals in ways ts_rank isn’t built for.
  • Faceting. An e-commerce search page where you filter by category, price range, and brand while searching by keyword, with live counts next to each filter option (“Nike (42),” “Under $100 (18)”) — that’s a faceting engine’s native structure, bolted-on and slower in a general-purpose database.
  • Typo tolerance. Someone typing “runing shoez” and still finding the right products requires fuzzy matching that’s core to how these engines are built, not an afterthought.
  • Scale and search-specific operations. A dedicated engine is built to shard and replicate specifically for search workloads, and gives you operations (reindexing without downtime, synonym management, per-field analyzers) that are first-class features there and awkward workarounds in a general-purpose database.

The tradeoff for all of this is real operational cost: a separate service to deploy, monitor, and keep in sync with your source-of-truth database (usually via a sync job or change-data-capture pipeline, since the search index is now a derived copy of your data, not the data itself). That sync lag and operational surface is the price. Algolia and Meilisearch trade some of that operational burden back for you — Algolia as a fully hosted API, Meilisearch as a lighter-weight, easier-to-self-host alternative to the heavier Elasticsearch — in exchange for less low-level control than running Elasticsearch yourself.

The decision isn’t abstractly “which is faster.” It’s whether your actual product requirements have crossed from “find the rows containing these words” into “build a search experience” — faceting, typo tolerance, tuned relevance across a growing dataset. If they haven’t, Postgres’s tsvector/tsquery/GIN stack is faster to ship, simpler to operate, and plenty fast for a long time. If they have, that’s the signal to bring in a dedicated engine, not table size on its own.

Check yourself

  1. 01Why can't a database use a normal index to speed up WHERE column LIKE '%term%'?

    A standard B-tree index is sorted by the column's value, which lets the database jump straight to a starting point for a prefix match like 'term%'. A leading wildcard ('%term%') means the match could start anywhere inside the text, so there's no sorted starting point to jump to — the database has to read and check every row.

  2. 02What problem does an inverted index actually solve, structurally?

    It flips the lookup direction. Instead of storing 'row 5 contains this text' and scanning rows to find a word, it stores 'this word appears in rows 5, 12, and 47' directly, so finding every row containing a word becomes a direct lookup instead of a scan — the same trick a book's index at the back uses over flipping through every page.

  3. 03What does Postgres's built-in full-text search actually give you, and what's it missing compared to a dedicated engine?

    tsvector/tsquery with a GIN index gives you a real inverted index, boolean and phrase search, and basic relevance ranking via ts_rank — genuinely enough for a lot of apps. What it doesn't give you out of the box is the deep relevance tuning, faceted filtering, typo tolerance, and easy horizontal scaling that a dedicated engine like Elasticsearch, Algolia, or Meilisearch is built specifically around.

  4. 04What's a concrete sign that you've outgrown database-native full-text search and need a dedicated engine?

    When your product requirements start asking for things search engines are purpose-built for and Postgres only approximates — faceted filters next to results (filter by category and price while searching), a search box that tolerates typos, or relevance ranking tuned across many weighted fields — rather than just finding a row that contains the right words.

Notes

Private to you

Esc
↑↓ navigate↵ open? shortcuts

Keyboard shortcuts

Press Esc to close

Anywhere

⌘K
Search and commands
/
Search
?
This sheet
Esc
Close

Reading a lesson

J
Next lesson
K
Previous lesson
B
Bookmark