How Does Full-Text Search Work in Relational Databases?
Learn how full-text search works in SQL databases: tokenization, inverted indexes, and relevance ranking versus LIKE queries.
Expected Interview Answer
Full-text search in a relational database uses a dedicated inverted-index structure that maps normalized words to the rows containing them, letting the engine find relevant documents by meaning and ranking instead of scanning every row with LIKE.
The database first runs a text-analysis pipeline over each document column: tokenizing into words, lowercasing, removing stop words, and stemming words to a root form (so "running" and "run" match). It stores the result as a tsvector or FULLTEXT-style inverted index, a compact map from each normalized token to the list of rows and positions where it appears. A query is parsed the same way into a tsquery-style structure, the engine intersects the token postings, and results are ranked by relevance signals such as term frequency and proximity, all far faster than a LIKE-based scan of every row.
- Finds relevant rows by meaning, not exact substring position
- Ranks results by relevance instead of returning an unordered set
- Scales to large text columns without a full table scan
- Supports stemming and stop-word handling out of the box
AI Mentor Explanation
A cricket almanac with fifty years of match reports is unusable if you must reread every report to find every mention of "century" — instead, the publisher builds a back-of-book index listing each key term alphabetically with the page numbers it appears on. Full-text search builds this same index automatically: it tokenizes every report into words, normalizes them, and stores which rows each word appears in. A search for "century" then jumps straight to the indexed pages instead of rereading the whole almanac.
Step-by-Step Explanation
Step 1
Analyze and tokenize text
The engine splits document columns into normalized tokens: lowercased, stop-words removed, stemmed to a root form.
Step 2
Build the inverted index
Each token is stored once with the list of rows (and positions) where it appears, e.g. a tsvector or FULLTEXT index.
Step 3
Parse the search query
The query string is analyzed the same way into a tsquery-style structure of required and optional tokens.
Step 4
Intersect and rank matches
The engine looks up postings for each token, intersects the row sets, and ranks results by term frequency and proximity.
What Interviewer Expects
- Explanation of tokenization, normalization, and stemming
- Understanding of the inverted index structure behind full-text search
- Awareness that ranking, not just matching, is part of the feature
- Ability to contrast full-text search with a LIKE-based scan
Common Mistakes
- Confusing full-text search with a plain LIKE "%word%" query
- Not mentioning that results are ranked by relevance
- Forgetting the index must be built and maintained ahead of query time
- Assuming full-text search understands semantic meaning like an embedding model
Best Answer (HR Friendly)
“Full-text search works by pre-processing text columns into normalized word tokens and building a special index that maps each word straight to the rows containing it, similar to the index at the back of a book. When you search, the database looks up your words in that index and ranks matches by relevance, instead of rereading every row like a plain LIKE query would.”
Code Example
-- Add a generated tsvector column and index it
ALTER TABLE Articles ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;
CREATE INDEX idx_articles_search ON Articles USING GIN (search_vector);
-- Query, ranked by relevance
SELECT id, title,
ts_rank(search_vector, query) AS rank
FROM Articles, plainto_tsquery('english', 'database indexing') AS query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;Follow-up Questions
- How does full-text search ranking (e.g. ts_rank) actually work?
- What is the difference between a GIN and a GiST index for full-text search?
- How would you support multiple languages in full-text search?
- When would you move full-text search to a dedicated engine like Elasticsearch?
MCQ Practice
1. What data structure powers full-text search in most relational databases?
Full-text search relies on an inverted index that maps normalized tokens to the rows and positions where they occur.
2. Why is a LIKE "%word%" query a poor substitute for full-text search on large tables?
A leading-wildcard LIKE cannot use a standard index, forcing a full scan, and it returns no ranked relevance score.
3. What does stemming do during full-text search indexing?
Stemming normalizes word variants to a shared root form so a search for "run" also matches "running" or "runs".
Flash Cards
What is an inverted index? — A structure mapping each normalized word token to the rows/documents that contain it.
What does tokenization do? — Splits text into normalized words: lowercased, stop-words removed, stemmed to a root form.
How does full-text search differ from LIKE? — It uses a pre-built index with relevance ranking instead of scanning every row for a substring.
What is ts_rank used for? — Scoring how relevant a matched row is based on term frequency and proximity, in PostgreSQL.