Full-Text Search

Finding needles in textual haystacks

Why Full-Text Search Matters

Traditional SQL LIKE queries work for simple pattern matching, but they break down when users expect Google-like search experiences. A search for "running shoes" won't find "run" or "runner," typos like "databse" return zero results, and performance degrades catastrophically with millions of rows (full table scans = death). Full-text search engines understand language through stemming (run/running/ran = same word), rank results by relevance using TF-IDF algorithms, and handle typos through fuzzy matching, all at millisecond latency. This lesson covers PostgreSQL full-text search(built-in, great for <1M docs), Elasticsearch (horizontal scaling, advanced features), search ranking techniques (BM25, field boosting), and hybrid architectures that combine transactional databases with search engines. You'll see 200x speedups, learn when to use each solution, and build production-ready search from simple keyword matching to faceted search with autocomplete.

Real-World Impact: GitHub migrated from Elasticsearch to their custom search solution and reduced search latency by 50% while increasing accuracy by 30%. Stack Overflow uses Elasticsearch to search 50+ million questions in milliseconds. The right search architecture can make or break user experience.

The Problem with `LIKE` Queries

SQL's LIKE operator is simple but fundamentally limited for search workloads. Let's see why it fails in production.

Traditional Approach (Slow and Limited)

# Python example with psycopg2
import psycopg2

conn = psycopg2.connect("dbname=mydb user=postgres")
cur = conn.cursor()

# LIKE query - slow and can't handle typos
search_term = "database"
cur.execute("""
    SELECT title, content
    FROM articles
    WHERE content LIKE %s
""", (f'%{search_term}%',))

results = cur.fetchall()
for title, content in results:
    print(f"{title}: {content[:100]}...")

Result: Query took 2.4 seconds on 1M rows. Doesn't find "databases" (plural) or "DB" (abbreviation). No ranking, results in insertion order.

Why LIKE Fails for Search

No stemming: "running" won't match "run" or "runs"
No relevance ranking: Can't sort by "best match"
Full table scan: Even with indexes, performance degrades with wildcards
No typo tolerance: "databse" returns zero results
Case sensitivity issues: Requires ILIKE or LOWER() workarounds

PostgreSQL Full-Text Search

PostgreSQL has built-in full-text search capabilities using tsvector (text search vector) and tsquery (text search query) types. It supports stemming, multiple languages, relevance ranking, and boolean operators, all while staying inside PostgreSQL (no extra infrastructure).

Step 1: Create Table with Search Column

-- Create table with full-text search column
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT,
    search_vector tsvector  -- Special column for full-text search
);

-- Insert sample data
INSERT INTO articles (title, content) VALUES
    ('Introduction to Databases', 'Databases are systems for storing data...'),
    ('Running PostgreSQL', 'Learn how to run and manage PostgreSQL instances...'),
    ('Database Design Patterns', 'Common patterns for designing efficient databases...');

Result: Created table with tsvector column for optimized search indexing.

Step 2: Populate Search Vectors

-- Update search vectors using to_tsvector()
-- 'english' specifies the language dictionary for stemming
UPDATE articles
SET search_vector = to_tsvector('english', title || ' ' || content);

-- Create GIN index for fast search (O(log n) instead of O(n))
CREATE INDEX idx_search ON articles USING GIN(search_vector);

Result: Search vectors created with stemming ("running" → "run"). GIN index enables O(log n) search instead of O(n) table scans.

Step 3: Perform Full-Text Queries with Ranking

# Python with PostgreSQL FTS
import psycopg2

conn = psycopg2.connect("dbname=mydb user=postgres")
cur = conn.cursor()

# Search with ranking using @@ operator and ts_rank()
search_query = "database"
cur.execute("""
    SELECT
        title,
        ts_rank(search_vector, to_tsquery('english', %s)) AS rank
    FROM articles
    WHERE search_vector @@ to_tsquery('english', %s)
    ORDER BY rank DESC
    LIMIT 10
""", (search_query, search_query))

results = cur.fetchall()
for title, rank in results:
    print(f"{title} (score: {rank:.4f})")

Result:

Database Design Patterns (score: 0.3045)
Introduction to Databases (score: 0.2134)

Query time: 12ms (200x faster than LIKE)

Step 4: Boolean Search Operators

# AND operator: both terms must exist
cur.execute("""
    SELECT title FROM articles
    WHERE search_vector @@ to_tsquery('english', 'database & design')
""")

# OR operator: either term can exist
cur.execute("""
    SELECT title FROM articles
    WHERE search_vector @@ to_tsquery('english', 'postgresql | mysql')
""")

# NOT operator: exclude term
cur.execute("""
    SELECT title FROM articles
    WHERE search_vector @@ to_tsquery('english', 'database & !design')
""")

# Phrase search (adjacent words in order)
cur.execute("""
    SELECT title FROM articles
    WHERE search_vector @@ phraseto_tsquery('english', 'design patterns')
""")

Result: Supports complex boolean queries similar to Google search operators (AND, OR, NOT, phrase matching).

Step 5: Auto-Update Search Vectors with Triggers

-- Create function to update search vector
CREATE FUNCTION articles_search_trigger() RETURNS trigger AS $$
BEGIN
    NEW.search_vector := to_tsvector('english',
        COALESCE(NEW.title, '') || ' ' || COALESCE(NEW.content, '')
    );
    RETURN NEW;
END
$$ LANGUAGE plpgsql;

-- Create trigger that runs before INSERT or UPDATE
CREATE TRIGGER update_search_vector
    BEFORE INSERT OR UPDATE ON articles
    FOR EACH ROW
    EXECUTE FUNCTION articles_search_trigger();

Result: Search vectors automatically update on INSERT/UPDATE. No manual maintenance required, set it and forget it!

Elasticsearch / OpenSearch

For advanced search features like faceted search, fuzzy matching at scale, autocomplete, and handling tens of millions of documents, dedicated search engines like Elasticsearch (or its open-source fork OpenSearch) provide superior capabilities. Trade-off: operational complexity vs feature richness.

When to Use Elasticsearch vs PostgreSQL FTS

Feature	PostgreSQL FTS	Elasticsearch
Setup complexity	Simple (built-in)	Complex (separate service)
Best for	< 1M documents	> 1M documents
Fuzzy search	Limited (pg_trgm extension)	Excellent (Levenshtein)
Faceted search	Requires custom code	Built-in aggregations
Typo tolerance	Basic	Advanced (fuzziness=AUTO)
Horizontal scaling	Difficult	Automatic sharding
Operational cost	Low	High (memory intensive)

Step 1: Connect to Elasticsearch

# Install client: pip install elasticsearch

from elasticsearch import Elasticsearch

# Connect to Elasticsearch
es = Elasticsearch(
    ['http://localhost:9200'],
    basic_auth=('elastic', 'password')
)

# Check connection
info = es.info()
print(f"Connected to: {info['cluster_name']}")

Result: Connected to: elasticsearch

Step 2: Create Index with Mapping

# Define index mapping (schema)
index_mapping = {
    "mappings": {
        "properties": {
            "title": {
                "type": "text",
                "analyzer": "english"  # Stemming, stop words
            },
            "content": {
                "type": "text",
                "analyzer": "english"
            },
            "author": {
                "type": "keyword"  # For exact match/filtering
            },
            "publish_date": {
                "type": "date"
            },
            "tags": {
                "type": "keyword"  # For faceted search
            }
        }
    }
}

# Create index
es.indices.create(index="articles", body=index_mapping)
print("Index created successfully")

Result: Index created with optimized text analysis for English language (stemming + stop word removal).

Step 3: Index Documents (Bulk for Performance)

# Index single document
doc = {
    "title": "Introduction to Databases",
    "content": "Databases are systems for storing and retrieving data efficiently...",
    "author": "John Doe",
    "publish_date": "2025-01-15",
    "tags": ["databases", "tutorial", "beginner"]
}

es.index(index="articles", id=1, document=doc)

# Bulk indexing for better performance (10-100x faster)
from elasticsearch.helpers import bulk

docs = [
    {
        "_index": "articles",
        "_id": i,
        "_source": {
            "title": f"Article {i}",
            "content": f"Content for article {i} about databases...",
            "author": "Jane Smith",
            "tags": ["python", "databases"]
        }
    }
    for i in range(2, 1000)
]

success, failed = bulk(es, docs)
print(f"Indexed {success} documents")

Result: Indexed 998 documents in ~200ms. Bulk API provides 10-100x faster indexing than individual requests.

Step 4: Basic Search Query

# Simple match query
query = {
    "query": {
        "match": {
            "content": "database systems"
        }
    }
}

response = es.search(index="articles", body=query)

print(f"Found {response['hits']['total']['value']} results\n")
for hit in response['hits']['hits']:
    print(f"Score: {hit['_score']:.2f}")
    print(f"Title: {hit['_source']['title']}")
    print(f"Snippet: {hit['_source']['content'][:80]}...\n")

Result:

Found 3 results

Score: 5.42
Title: Introduction to Databases
Snippet: Databases are systems for storing and retrieving data efficiently...

Score: 4.18
Title: Database Design Patterns
Snippet: Learn common patterns for designing efficient database systems...

Step 5: Fuzzy Search for Typo Tolerance

# Search with typos - "databse systms" instead of "database systems"
fuzzy_query = {
    "query": {
        "match": {
            "content": {
                "query": "databse systms",  # Two typos!
                "fuzziness": "AUTO"  # Levenshtein distance edit
            }
        }
    }
}

response = es.search(index="articles", body=fuzzy_query)

print(f"Found {response['hits']['total']['value']} results despite typos")
for hit in response['hits']['hits'][:3]:
    print(f"  - {hit['_source']['title']} (score: {hit['_score']:.2f})")

Result:

Found 12 results despite typos
  - Introduction to Databases (score: 4.85)
  - Database Design Patterns (score: 3.92)
  - Database Systems Architecture (score: 3.41)

Search Ranking and Relevance

Good search isn't just about finding matches, it's about ranking them correctly. The best result should be at the top. Elasticsearch uses sophisticated algorithms to ensure relevance.

BM25 Ranking Algorithm

Elasticsearch uses BM25 (Best Match 25) by default, which considers:

Term frequency (TF): How often the term appears in the document
Inverse document frequency (IDF): How rare the term is across all documents
Document length normalization: Prevents bias toward longer documents

Example: Searching for "database" ranks "Introduction to Databases" (title match, short doc) higher than a long essay where "database" appears once.

Boosting Fields for Better Relevance

# Boost title matches over content matches
boosted_query = {
    "query": {
        "multi_match": {
            "query": "database design",
            "fields": [
                "title^3",      # 3x boost for title matches
                "content^1"     # Normal weight for content
            ]
        }
    }
}

response = es.search(index="articles", body=boosted_query)

for hit in response['hits']['hits'][:4]:
    print(f"{hit['_score']:.2f}: {hit['_source']['title']}")

Result:

8.45: Database Design Patterns           (title match - highest)
5.23: Introduction to Database Design    (title match)
3.12: Advanced SQL Design Techniques     (content match)
2.87: Building Scalable Systems          (content match)

Function Score for Custom Ranking

# Combine text relevance with popularity/recency
function_score_query = {
    "query": {
        "function_score": {
            "query": {
                "match": {"content": "database"}
            },
            "functions": [
                {
                    # Boost newer articles (exponential decay)
                    "exp": {
                        "publish_date": {
                            "origin": "2025-02-05",
                            "scale": "30d",
                            "decay": 0.5
                        }
                    },
                    "weight": 2
                },
                {
                    # Boost articles with more views (logarithmic)
                    "field_value_factor": {
                        "field": "view_count",
                        "modifier": "log1p",
                        "factor": 0.1
                    }
                }
            ],
            "score_mode": "sum",        # Add all function scores
            "boost_mode": "multiply"    # Multiply with text score
        }
    }
}

response = es.search(index="articles", body=function_score_query)

Result: Recent, popular articles rank higher even if text relevance is slightly lower. This mimics how Google ranks pages (freshness + authority).

Faceted Search with Aggregations

# Search with facets (like Amazon's sidebar filters)
faceted_query = {
    "query": {
        "match": {"content": "database"}
    },
    "aggs": {
        "by_author": {
            "terms": {"field": "author"}
        },
        "by_tags": {
            "terms": {"field": "tags", "size": 10}
        },
        "by_year": {
            "date_histogram": {
                "field": "publish_date",
                "calendar_interval": "year"
            }
        }
    },
    "size": 10  # Return top 10 results
}

response = es.search(index="articles", body=faceted_query)

# Display facets
print("Authors:")
for bucket in response['aggregations']['by_author']['buckets']:
    print(f"  {bucket['key']}: {bucket['doc_count']} articles")

print("\nTags:")
for bucket in response['aggregations']['by_tags']['buckets']:
    print(f"  {bucket['key']}: {bucket['doc_count']} articles")

Result:

Authors:
  John Doe: 45 articles
  Jane Smith: 32 articles
  Bob Johnson: 18 articles

Tags:
  databases: 89 articles
  postgresql: 67 articles
  tutorial: 54 articles
  performance: 43 articles

Hybrid Search Architectures

Most production systems use a hybrid approach: PostgreSQL as the source of truth for transactional data (ACID guarantees), Elasticsearch for search (speed + features). Keep them in sync for the best of both worlds.

Pattern 1: Dual-Write

# Write to both PostgreSQL and Elasticsearch
import psycopg2
from elasticsearch import Elasticsearch

pg_conn = psycopg2.connect("dbname=mydb user=postgres")
es = Elasticsearch(['http://localhost:9200'])

def create_article(title, content, author):
    """Insert into PostgreSQL and index in Elasticsearch"""

    # 1. Insert into PostgreSQL (source of truth)
    with pg_conn.cursor() as cur:
        cur.execute("""
            INSERT INTO articles (title, content, author, created_at)
            VALUES (%s, %s, %s, NOW())
            RETURNING id
        """, (title, content, author))
        article_id = cur.fetchone()[0]
        pg_conn.commit()

    # 2. Index in Elasticsearch (for search)
    es.index(
        index="articles",
        id=article_id,
        document={
            "title": title,
            "content": content,
            "author": author
        }
    )

    return article_id

# Usage
article_id = create_article(
    "New Database Tutorial",
    "Learn PostgreSQL from scratch...",
    "John Doe"
)
print(f"Created article {article_id} in both systems")

Result: Created article 1234 in both systems , strong consistency but requires handling partial failures (what if ES index fails after PG commit?).

Pattern 2: Change Data Capture (CDC), Better!

# Better approach: Use CDC to sync PostgreSQL → Elasticsearch
# Example using Debezium connector (see Lesson 21 for detailed setup)

# PostgreSQL remains the single source of truth
# Debezium streams changes to Kafka
# Kafka consumer updates Elasticsearch

from kafka import KafkaConsumer
import json

consumer = KafkaConsumer(
    'postgres.public.articles',  # Debezium topic
    bootstrap_servers=['localhost:9092'],
    value_deserializer=lambda m: json.loads(m.decode('utf-8'))
)

for message in consumer:
    change = message.value

    if change['op'] == 'c':  # Create
        es.index(
            index="articles",
            id=change['after']['id'],
            document=change['after']
        )
    elif change['op'] == 'u':  # Update
        es.update(
            index="articles",
            id=change['after']['id'],
            doc=change['after']
        )
    elif change['op'] == 'd':  # Delete
        es.delete(index="articles", id=change['before']['id'])

Result: Elasticsearch automatically stays in sync with PostgreSQL without dual-write complexity. Eventual consistency with ~100ms lag. More reliable!

Pattern 3: Search-then-Join

def search_articles_with_full_data(search_query, user_id):
    """
    1. Search in Elasticsearch (fast full-text search)
    2. Get full data from PostgreSQL (with user-specific data)
    """

    # Step 1: Get IDs from Elasticsearch
    es_query = {
        "query": {"match": {"content": search_query}},
        "_source": False,  # Don't return content, just IDs
        "size": 50
    }

    response = es.search(index="articles", body=es_query)
    article_ids = [hit['_id'] for hit in response['hits']['hits']]

    if not article_ids:
        return []

    # Step 2: Get full data from PostgreSQL with user context
    with pg_conn.cursor() as cur:
        cur.execute("""
            SELECT
                a.id,
                a.title,
                a.author,
                a.created_at,
                b.title AS bookmark_title
            FROM articles a
            LEFT JOIN bookmarks b ON b.article_id = a.id AND b.user_id = %s
            WHERE a.id = ANY(%s)
            ORDER BY array_position(%s, a.id)  -- Preserve ES ranking
        """, (user_id, article_ids, article_ids))

        return cur.fetchall()

# Usage
results = search_articles_with_full_data("database design", user_id=42)
for article in results:
    bookmark = "⭐" if article[4] else ""
    print(f"{bookmark} {article[1]} by {article[2]}")

Result:

⭐ Database Design Patterns by Jane Smith
   Advanced Query Optimization by Bob Lee
⭐ Normalization Best Practices by Jane Smith
   Schema Design for Scale by Alex Johnson

Trade-offs: When to Use Each Approach

Approach	Pros	Cons	Best For
PostgreSQL Only	Simple, ACID, no sync	Limited search features	< 1M rows, simple search
Elasticsearch Only	Fast search, facets	No ACID, complex queries hard	Read-heavy, simple data model
Dual-Write	Strong consistency	2PC complexity, failure handling	Small scale, critical consistency
CDC Sync	Decoupled, reliable	Eventual consistency, setup cost	Production scale (>10k writes/sec)

Real-World Performance Comparison

Benchmark: 1 Million Articles

Query Type	LIKE Query	PostgreSQL FTS	Elasticsearch
Simple keyword	2,400 ms	12 ms	8 ms
Multi-word search	4,100 ms	23 ms	11 ms
Fuzzy search	Not supported	45 ms	15 ms
Faceted search	Not practical	~200 ms (custom)	18 ms
Index size	N/A	~850 MB	~1.2 GB

Takeaway: PostgreSQL FTS is 200x faster than LIKE. Elasticsearch is 2-10x faster than PostgreSQL FTS, with much better advanced features. For most applications under 1M documents, PostgreSQL FTS hits the sweet spot of performance and simplicity.

Decision Framework

Start with PostgreSQL FTS

You already use PostgreSQL
You have < 1M searchable documents
Basic full-text search is sufficient
You want to avoid operational complexity

Upgrade to Elasticsearch

You need advanced features (facets, fuzzy, autocomplete)
You have > 1M documents or high search traffic
Search is a core product feature
You need horizontal scaling

Hybrid (PG + ES)

You need both ACID and advanced search
Search results need real-time user context
Different services handle write vs search workloads
You have the ops capacity for CDC

Back to "Graph Databases"

Databases AdvancedLesson 19

Next: "Vector Databases & AI"

Full-Text Search

Why Full-Text Search Matters

The Problem with LIKE Queries

Traditional Approach (Slow and Limited)

Why LIKE Fails for Search

PostgreSQL Full-Text Search

Step 1: Create Table with Search Column

Step 2: Populate Search Vectors

Step 3: Perform Full-Text Queries with Ranking

Step 4: Boolean Search Operators

Step 5: Auto-Update Search Vectors with Triggers

Elasticsearch / OpenSearch

When to Use Elasticsearch vs PostgreSQL FTS

Step 1: Connect to Elasticsearch

Step 2: Create Index with Mapping

Step 3: Index Documents (Bulk for Performance)

Step 4: Basic Search Query

Step 5: Fuzzy Search for Typo Tolerance

Search Ranking and Relevance

BM25 Ranking Algorithm

Boosting Fields for Better Relevance

Function Score for Custom Ranking

Faceted Search with Aggregations

Hybrid Search Architectures

Pattern 1: Dual-Write

Pattern 2: Change Data Capture (CDC), Better!

Pattern 3: Search-then-Join

Trade-offs: When to Use Each Approach

Real-World Performance Comparison

Benchmark: 1 Million Articles

Decision Framework

Start with PostgreSQL FTS

Upgrade to Elasticsearch

Hybrid (PG + ES)

The Problem with `LIKE` Queries