Backend First Principles
Articles/Phase 5 — Asynchronous Systems & Distributed Processing

Full-Text Search & Elasticsearch: Inverted Indexing, BM25 Scoring & Search Architecture

Why SQL LIKE collapses at scale, Inverted Index data structures, BM25 relevance scoring, typo tolerance with fuzzy matching, PostgreSQL TSVECTOR vs Elasticsearch, and CDC synchronization.

Core Concept & First Principle

Relational databases collapse on text search because wildcard SQL LIKE queries force sequential full-table scans. Specialized search engines like Elasticsearch solve this with Inverted Indexing—mapping every word directly to its document locations. Combined with BM25 relevance scoring, field boosting, and Levenshtein typo tolerance, full-text search transforms multi-second database disk thrashing into sub-50ms search ranking.

1. The Fundamental Bottleneck: Why Relational Databases Struggle with Search

When building an initial prototype, backend engineers frequently implement search using simple SQL wildcard queries such as SELECT * FROM products WHERE description LIKE '%laptop%'. While this works instantly on 100 rows, it triggers a catastrophic architectural failure when scaled to production datasets.

The Librarian Analogy & The Wildcard Penalty
Imagine a librarian asked to find every book containing the word 'quantum'. Without a catalog index of words, the librarian must open every single book in the library, flip through every page sequentially, and check every word. In SQL, a leading wildcard '%laptop%' prevents the database B-Tree index from performing binary lookups, forcing the engine into a 100% Full Table Scan that reads every 8KB disk page from disk.
Comparison Table↔ Scroll horizontally
Search PatternDatabase Index UsabilityEngine ComplexityScale Behavior (1,000,000 Rows)
Exact Equality (= 'laptop')✅ B-Tree Index ActiveO(log N)Sub-millisecond (3 page reads)
Prefix Match (LIKE 'laptop%')✅ B-Tree Range Scan ActiveO(log N + K)Fast (~2–10ms)
Substring Wildcard (LIKE '%laptop%')❌ B-Tree Index DisabledO(N) Full Table ScanMultiple seconds (Reads all 100k disk pages)
Multi-Term ('%high%' AND '%laptop%')❌ Exponential Scan PenaltyO(N) Multi-Column FilterExtreme CPU saturation & connection pool starvation

2. Inverted Indexing: The Core Data Structure of Search Engines

Instead of storing data as row records and scanning through them, full-text search engines (Elasticsearch, OpenSearch, Apache Lucene) invert the relationship by creating an index of the terms themselves.

The Textbook Index Analogy
Think of the index at the back of an 800-page computer science textbook. Instead of reading the entire book to locate 'Binary Search Tree', you flip to the alphabetical index under 'B', find 'Binary Search Tree', and see the exact page numbers [142, 143, 208]. You jump directly to those pages in constant time.

01Analysis & Tokenization Pipeline

When a document is ingested, text strings pass through an analyzer that breaks sentences into discrete tokens, normalizes casing, and removes punctuation.
SQL / Execution Snippet
1Raw Text: "Ultra-Fast Laptop with M3 Max!"
2Tokens: ["ultra", "fast", "laptop", "with", "m3", "max"]

02Stop-Word Filtering & Stemming

Non-distinguishing grammatical words ('with', 'the', 'a', 'is') are filtered out. Stemming algorithms (Porter Stemmer) reduce words to their linguistic root so searches for 'running' or 'runs' match documents containing 'run'.

03The Inverted Index Posting List

The engine stores each unique dictionary term mapped directly to a 'Posting List'—a sorted list of Document IDs, term frequencies, and exact word offsets.
Comparison Table↔ Scroll horizontally
Dictionary TermDocument Frequency (DF)Posting List (Document IDs & Positions)
laptop3Doc #1 (pos: 3), Doc #42 (pos: 1), Doc #901 (pos: 8)
m32Doc #1 (pos: 5), Doc #88 (pos: 2)
ergonomic1Doc #104 (pos: 1)
mechanical4Doc #12 (pos: 2), Doc #104 (pos: 4), Doc #512 (pos: 1), Doc #780 (pos: 3)

3. Relevance Scoring: The BM25 Ranking Algorithm

In a relational SQL query, search is binary: a row either matches the WHERE predicate (TRUE) or it does not (FALSE). In real-world search, thousands of documents might match a keyword; the critical requirement is relevance ranking—ensuring the best results appear on page one.

01Term Frequency (TF)

How often does the search keyword appear in a specific document? If a product description mentions 'laptop' 5 times, it is likely more relevant than a product mentioning it once.

02Inverse Document Frequency (IDF)

How rare or common is the term across the entire index? Rare terms (e.g. 'ergonomic' or 'quantum') carry massive informational weight; ubiquitous terms (e.g. 'good' or 'device') carry very low weight.

03Field-Length Normalization

A match in a short 5-word product title carries significantly more density and intent than a match in a 5,000-word user manual.

04Field Boosting (Multipliers)

Search queries can assign custom weight multipliers to critical fields. For instance, matching in name^3 is scored 3x higher than a match in description^1.
SQL / Execution Snippet
1{
2 "query": {
3 "multi_match": {
4 "query": "laptop mechanical keyboard",
5 "fields": ["title^3", "tags^2", "description^1"]
6 }
7 }
8}

4. User Experience: Typo Tolerance & Type-Ahead Autocomplete

Real users make frequent typos (10%–15% of all queries). If a customer searches for lapotp instead of laptop, a relational database returns zero results, losing the sale.

Fuzzy Matching (Levenshtein Distance)

UX Pillar 1

Calculates the minimum number of single-character edits (insertions, deletions, substitutions, or transpositions) required to transform one string into another. For example, 'lapotp' has a Damerau-Levenshtein distance of 1 (transposition of 'o' and 't') from 'laptop'.

Edge N-Gram Tokenization

UX Pillar 2

Splits terms into progressive prefix substrings during indexing (e.g. 'mac' ➔ ['m', 'ma', 'mac']). Enables instantaneous sub-10ms type-ahead autocomplete suggestions as the user types each keystroke in the search bar.

Synonym Filtering

UX Pillar 3

Normalizes vocabulary variations using synonym graphs (e.g., mapping 'notebook', 'ultrabook', and 'laptop' as interchangeable concepts) so customers find relevant items regardless of specific dialect.

5. Practical Performance Comparison: SQL LIKE vs PostgreSQL TSVECTOR vs Elasticsearch

Real-world performance benchmarks across a dataset of 100,000 product reviews with text searches across 500-word review bodies:

Comparison Table↔ Scroll horizontally
Search ImplementationQuery P95 LatencyDisk / Memory FootprintRelevance RankingTypo Tolerance
PostgreSQL LIKE '%laptop%'2,850 ms – 6,200 msHigh disk page thrashing❌ None (Random row order)❌ Zero (Exact match only)
PostgreSQL TSVECTOR + GIN Index35 ms – 85 msModerate (~25% table size index)⚠️ Basic (ts_rank algorithm)⚠️ Requires pg_trgm extension
Elasticsearch Cluster8 ms – 22 msDistributed RAM / Inverted Index✅ Industry-standard BM25 + Boosting✅ Native Levenshtein Fuzzy Search

6. Production Architecture: Dual-Storage & Data Synchronization

In production system design, Elasticsearch is rarely used as the primary system of record because it prioritizes search performance over strict transactional consistency (ACID guarantees). Instead, backends use a Dual-Storage Architecture:

01PostgreSQL as Primary System of Record (ACID)

All INSERT, UPDATE, and DELETE mutations commit directly to PostgreSQL with strict foreign keys, constraints, and ACID durability.

02Asynchronous Event Sync (Message Queue / CDC)

PostgreSQL mutations trigger asynchronous background workers (via RabbitMQ/Redis or Change Data Capture via Debezium reading the Postgres WAL) to re-index the modified document in Elasticsearch.
SQL / Execution Snippet
1// PostgreSQL ➔ Event Queue ➔ Elasticsearch Worker
2async function handleProductUpdatedEvent(event: { productId: number }) {
3 const product = await db.query('SELECT * FROM products WHERE id = $1', [event.productId]);
4
5 await elasticsearchClient.index({
6 index: 'products',
7 id: String(product.id),
8 document: {
9 id: product.id,
10 title: product.title,
11 description: product.description,
12 price: product.price,
13 updatedAt: product.updated_at
14 }
15 });
16}

03Elasticsearch as Read-Optimized Search Engine

User search queries bypass PostgreSQL entirely and execute against Elasticsearch clusters in sub-20ms. The UI retrieves document IDs from Elasticsearch and optionally hydrates relations.

7. Multi-Language Code Implementations: Elasticsearch & PostgreSQL Full-Text Search

Production code patterns for querying full-text search across TypeScript (Elasticsearch), Go, and PostgreSQL TSVECTOR:

1import { Client } from '@elastic/elasticsearch';
2
3const client = new Client({ node: process.env.ELASTICSEARCH_URL || 'http://localhost:9200' });
4
5export async function searchProducts(userQuery: string, page = 1, limit = 20) {
6 const response = await client.search({
7 index: 'products',
8 from: (page - 1) * limit,
9 size: limit,
10 query: {
11 bool: {
12 must: [
13 {
14 multi_match: {
15 query: userQuery,
16 fields: ['title^3', 'category^2', 'description^1'],
17 fuzziness: 'AUTO', // Automatic typo tolerance (Levenshtein 1 or 2)
18 operator: 'or'
19 }
20 }
21 ],
22 filter: [
23 { term: { status: 'ACTIVE' } }
24 ]
25 }
26 },
27 highlight: {
28 fields: {
29 title: {},
30 description: {}
31 }
32 }
33 });
34
35 return {
36 totalHits: typeof response.hits.total === 'number' ? response.hits.total : response.hits.total?.value,
37 results: response.hits.hits.map(hit => ({
38 id: hit._id,
39 score: hit._score,
40 source: hit._source,
41 highlight: hit.highlight
42 }))
43 };
44}

8. The Backend Engineer’s Strategy: Choosing the Right Search Architecture

When designing search systems, follow this decision framework to balance operational complexity against user experience:

Comparison Table↔ Scroll horizontally
TechnologyWhen to ChooseWhen to AvoidOperational Overhead
SQL LIKE / EqualityExact identifiers (SKU, UUID, email lookup), tiny internal datasets (< 1,000 rows).Any free-form user search bar or substring querying.Zero overhead (Built-in).
PostgreSQL TSVECTOR / GINSmall to medium applications (10,000 to 500,000 records) wanting zero additional infrastructure.High-scale distributed search, heavy typo tolerance, complex multi-factor BM25 boosting.Low (Uses existing PostgreSQL database).
Elasticsearch / OpenSearchHigh-scale applications (> 500k documents), e-commerce catalogs, log analytics, multi-field relevance ranking.Simple CRUD applications with low traffic where managing a separate cluster is overkill.Moderate to High (Requires cluster management, JVM tuning, and data sync).
Production Checklist & Guidelines
Never use SQL LIKE '%...%' for user-facing search bars in production.
Start with PostgreSQL Full-Text Search (tsvector + GIN) if you have < 500,000 rows to avoid extra infrastructure.
Migrate to Elasticsearch/OpenSearch when you need fuzzy typo tolerance, autocomplete, and BM25 field boosting.
Keep PostgreSQL as your primary ACID source of truth and sync to Elasticsearch asynchronously via queues or CDC.
Always configure field boosting so matches in titles/names rank higher than descriptions.
Summary & Core Takeaways
Key Insights
1Relational databases collapse on search because wildcard LIKE queries force full-table sequential scans.
2Inverted Indexing maps every unique word directly to its document posting list, enabling instant lookups.
3The BM25 algorithm ranks search results based on Term Frequency (TF), Inverse Document Frequency (IDF), and Field Length.
4Fuzzy search uses Levenshtein distance to automatically correct user typos and return relevant products.
5PostgreSQL handles primary ACID transactions while Elasticsearch handles high-speed, relevance-ranked search queries.
Previous Lesson
Background Jobs & Task Queues: The Architecture of Scalable & Asynchronous Backends
Next Lesson
Error Handling & Fault Tolerance: Building Resilient Production Backends