How does full-text search work under the hood (inverted indexes), and when do you reach for Elasticsearch vs. Postgres full-text search?

#Why LIKE doesn't scale for search

WHERE description LIKE '%wireless mouse%' has to inspect the text of every single row, because a B-tree index (the kind a normal CREATE INDEX builds) is sorted by the whole value and only helps with prefix matches (LIKE 'wireless%') — a leading wildcard defeats it entirely, so the database falls back to a full sequential scan of every row's text. That's fine for a few thousand rows; on a few million it's a multi-second query, and it gets slower in direct proportion to table size forever.

#The inverted index: flipping the direction of the lookup

Full-text search engines solve this by building an inverted index — inverted relative to the "row → words it contains" direction you'd naturally think of; instead it maps "word → which rows contain it":

Documents:
  doc1: "wireless mouse for laptop"
  doc2: "wireless keyboard and mouse combo"
  doc3: "laptop stand aluminum"

Inverted index (term → posting list of doc ids):
  wireless  -> [doc1, doc2]
  mouse     -> [doc1, doc2]
  laptop    -> [doc1, doc3]
  keyboard  -> [doc2]
  combo     -> [doc2]
  stand     -> [doc3]
  aluminum  -> [doc3]

A search for "wireless mouse" now becomes: look up the posting list for "wireless" ([doc1, doc2]), look up the posting list for "mouse" ([doc1, doc2]), and intersect them — an operation on two short sorted lists, not a scan of the original text. Building that index isn't just splitting on whitespace: a real pipeline tokenizes (splits into words, handling punctuation and case), normalizes/stems (running, ran, runs → run, so a search for one matches documents containing any), and drops stop words (the, a, and — too common to be useful for ranking or filtering). Relevance ranking on top of the matching set typically uses something like TF-IDF or the more modern BM25: a term that appears often in one document but rarely across the whole corpus (a distinctive word) scores that document higher than a term that's common everywhere (matches on "the" don't distinguish anything).

#Postgres full-text search: the inverted index, built in

Postgres? implements exactly this, natively, via tsvector (the processed, indexed document) and tsquery (the processed search term), backed by a GIN index — which is Postgres's general-purpose inverted-index structure:

ALTER TABLE products ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (to_tsvector('english', name || ' ' || description)) STORED;

CREATE INDEX products_search_idx ON products USING GIN (search_vector);

-- ranked search
SELECT id, name, ts_rank(search_vector, query) AS rank
FROM products, to_tsquery('english', 'wireless & mouse') AS query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;

to_tsvector does the tokenize/normalize/stem/stop-word-removal pipeline for you, and @@ is the "does this tsvector match this tsquery" operator, backed by the GIN index instead of a sequential scan. This gets you real relevance-ranked full-text search with zero new infrastructure, transactional consistency with the rest? of your data (a row update atomically updates its search vector), and no second system to keep in sync.

#Where Postgres search stops being enough

Postgres FTS is genuinely good for a large share of real search needs, but it starts to strain in specific, predictable ways:

• Typo tolerance / fuzzy matching. Postgres FTS matches on exact (stemmed) tokens; "wireles mouse" won't match "wireless mouse" without bolting on pg_trgm trigram similarity separately, and even then it's not the same quality as a purpose-built fuzzy matcher.
• Faceted search and aggregations at search time. "Show result counts per category, per price bucket, per brand, live, as the user types" is Elasticsearch's home turf (it's built on Lucene, with aggregations as a first-class query type); doing the same in Postgres means hand-rolled GROUP BY queries running alongside the search query, which gets expensive at scale.
• Horizontal scale of the index itself. GIN indexes live on one Postgres instance (read replicas help reads, not index-build/update throughput); Elasticsearch shards an index across many nodes natively, which matters once the corpus and query volume outgrow what one machine's index update rate can absorb.
• Search-specific relevance tuning. Elasticsearch exposes per-field boosting, custom analyzers per language, synonym lists, "more like this," and vector/hybrid (semantic + keyword) search as configuration; in Postgres, most of that is possible but is a project, not a setting.

🧠 Check your understanding
0/2 · 0/2 answered
1. Why can't a normal B-tree index speed up WHERE description LIKE '%mouse%' the way it speeds up an equality lookup?
LIKE queries are always executed on a read replicaB-tree indexes are disabled for text columns by defaultA B-tree is sorted by the whole value, so a leading wildcard match can't use that sort order and forces a full scanB-tree indexes only work on integer columns
2. What does an inverted index actually store, and why does it make search fast?
A mapping from each term to the list of documents containing it, so a search intersects short posting lists instead of scanning documentsA cache of the most recent search queriesA sorted copy of every document, for fast full scansA compressed backup of the whole table