Finding a rare word in 5 million documents with ILIKE '%word%' scans the whole table — ~1.25 s. A tsvector + GIN inverted index finds the same rows in ~19 ms, and the index probe itself touches just 5 buffers — ~65× faster. The catch: it matches words, not arbitrary substrings.
Finding a word in a text column with LIKE '%word%' forces a full table scan — the leading wildcard defeats an ordinary index, so the database reads every row. An inverted index (the structure behind full-text search) instead stores, for each distinct word, the list of documents that contain it, so a lookup jumps straight to the matches. On 5 million short documents you will find a rare word with ILIKE in ~1.25 seconds reading the whole table, then with a tsvector + GIN index in ~19 ms whose index probe touches just 5 buffers — ~65× faster. The catch: the inverted index matches words, not arbitrary substrings.
Designing Data-Intensive Applications, 2nd ed. (Kleppmann & Riccomini, 2025), Chapter 4 — Storage and Retrieval, §"Full-Text Search":
a full-text search index … allows you to search for a word and find all the documents … that contain that word.
The book frames full-text search as a specialized inverted index, distinct from the key-lookup structures earlier in the chapter. Here you measure the gap against the naive substring scan, and meet the semantic catch that comes with it.
The surprise is about which queries an index can serve. These help; each line notes where it shows up.
LIKE '%x%' and the leading wildcard — a B-tree index orders strings by prefix, so it can accelerate LIKE 'x%' but not LIKE '%x%'; a leading wildcard forces a full scan.tsvector / tsquery / GIN — to_tsvector turns text into its lexemes; a GIN (Generalized Inverted iNdex) indexes them; @@ matches a tsquery against the tsvector.pg_trgm extension docs — the other tool, for when you genuinely need LIKE '%…%' to be fast (a Go-deeper below). If only one is new, make it #2 — the inverted index is the whole idea.
postgres:16 image (PostgreSQL 16.14). One docs table, 5,000,000 short documents; ~5,000 contain the rare word kestrel.
Five million short documents built from a small vocabulary; about one in a thousand also contains the rare word kestrel. We find those two ways:
WHERE body ILIKE '%kestrel%'. No index can help a leading wildcard, so Postgres reads and pattern-matches every row.tsvector column (body split into lexemes) with a GIN index, queried as WHERE body_tsv @@ to_tsquery('kestrel'). The index maps the word kestrel straight to the ~5,000 documents that contain it.We read EXPLAIN (ANALYZE, BUFFERS) for each. The whole exercise is one script, code/full_text_search.sql.
docker run -d --name ddia-ch4 -e POSTGRES_PASSWORD=study -e POSTGRES_DB=study postgres:16
sleep 3
docker cp full_text_search.sql ddia-ch4:/tmp/
docker exec ddia-ch4 psql -U postgres -d study -f /tmp/full_text_search.sqlDo not read the output yet — make each prediction first.
Predict. WHERE body ILIKE '%kestrel%' over 5,000,000 rows, no full-text index. How much of the table does Postgres read, and how long does it take?
A Parallel Seq Scan over the whole table — ~42,500 buffers, ~1.25 s even with two workers — to find the ~5,000 matches. The leading % means every one of the 5,000,000 rows must be read and tested.
Predict. Add a tsvector column and a GIN index, then query the word with @@. How many buffers does the index probe itself read, and how much faster is the whole query?
~19 ms — about 65× faster — and look at the last line: the Bitmap Index Scan that finds all 5,049 matching documents reads just 5 buffers. The word kestrel maps directly to its postings list. The remaining ~4,900 buffers are the heap fetches to count the matched rows; the search itself was almost free.
Predict. The ILIKE '%kes%' version would also match kestrel. Can the GIN index answer WHERE body ILIKE '%kes%' too?
No — and this is the semantic price. The inverted index is built on lexemes (whole words), so it answers "which documents contain the word kestrel," not "which contain the substring kes." to_tsquery('kes') would match the token kes, which no document has; ILIKE '%kes%' (substring) can't use the GIN index at all and falls back to the seq scan of Step 1. The 65× speedup comes with a change of question: word/phrase search, not arbitrary substring.
ILIKE '%kestrel%': Parallel Seq Scan, whole table (~42,500 buffers), ~1.25 s.LIKE '%…%'.LIKE '%kestrel%' asks whether a pattern appears anywhere in each string. A B-tree orders strings by their prefix, so it can narrow LIKE 'kestrel%' but has nothing to offer once the pattern can start at any position — the planner has no choice but to read every row and run the matcher, which is the Parallel Seq Scan over all 5,000,000 rows.
An inverted index turns the question around. When the tsvector is built, Postgres tokenizes each document into lexemes and, in the GIN index, records for every distinct word the sorted list of rows that contain it (its postings list). Querying @@ to_tsquery('kestrel') is then a lookup of one word in that dictionary followed by a walk of its postings — the 5-buffer Bitmap Index Scan — returning the ~5,000 matching row locations directly. The database never looks at documents that lack the word. That is the same idea as the index at the back of a book: you don't reread the book to find a term, you look the term up and get the page numbers.
LIKE '%kes%') needs a different structure — Postgres's pg_trgm trigram GIN index, which indexes 3-character grams — because the token-based index simply doesn't contain substrings. And the speed isn't free: the tsvector must be computed and the GIN index maintained on every insert and update, so full-text search trades write throughput and storage for read speed. This is exactly the book's point that full-text search is its own specialized index, not the key-lookup structures used elsewhere in the chapter.
to_tsquery('kestrel & alpha') and compare the plan to the single-word case. Predict whether the index intersects two postings lists or falls back to rechecking rows.CREATE EXTENSION pg_trgm; CREATE INDEX … USING gin (body gin_trgm_ops); then re-run ILIKE '%kes%'. Predict whether the trigram index now serves the substring query the tsvector couldn't.Sources: DDIA 2e, Ch. 4, §"Full-Text Search" · PostgreSQL manual, "Full Text Search," GIN indexes, and pg_trgm.