Data & storage / 4 / 15
Full-text search
A marketplace searches its catalogue with `LIKE '%word%'`. At 4 million products every search reads every row, and it still misses products written with another apostrophe, in Cyrillic or with an Uzbek suffix. We build real full-text search: an inverted index over analysed tokens, BM25 ranking, an index that follows the database's change log, shards with scatter-gather, and typo tolerance.
- 14 min
- Intermediate
- 11 steps
- Search p95
- 45 ms
- Rows read per search
- 50 000
- Relevant products missed
- —
- Searches with no results
- 3 %
- Write to searchable
- 0 s
- request
- reply
- redelivery
- duplicate
- lost
- call: the reply returns on the same line
Numbers are a model scenario, not measurements
The real request
Search API → Products DBRequest
SELECT id, title, price_minor FROM products
WHERE lower(title || ' ' || description) LIKE '%choynak%'12
ORDER BY sold_count DESC3
LIMIT 20;
-- 50 000 products, 45 ms: good enough for the first year4
LIKE— Compares the text character by character, for every row.%choynak%— A wildcard on both sides: the word can be anywhere in the text.sold_count— The only ranking: popular first, whatever the text says.50 000 products— Small enough that reading everything is still fast.
Build it yourselfCheat sheetCompare lessons
Course: 74 lessons
01 Foundations
02 Scaling
03 Data & storage
04 Communication
05 Reliability
06 Architecture
07 Security
08 Shipping
09 Whole systems
10 Business processes
11 Case studies