Back to Blog

The Complete Guide to Text Search: PostgreSQL, DuckDB, and Beyond

Harsh Vardhan Goswami

Jul 4, 2025

Product Development

Modern applications demand sophisticated text search capabilities that go far beyond simple pattern matching. Whether you're building a content management system, an e-commerce platform, or a data analytics application, choosing the right text search strategy can dramatically impact both user experience and system performance. The landscape has evolved significantly, with database-native solutions becoming increasingly powerful alternatives to external search engines.

In this comprehensive guide, we'll explore four distinct approaches to text search: PostgreSQL's built-in tsvector, the enhanced pg_search extension, DuckDB's analytical FTS capabilities, and traditional external search engines like Elasticsearch. Each approach offers unique advantages depending on your specific requirements, dataset size, and infrastructure constraints.

PostgreSQL's Built-in Text Search: The Foundation

PostgreSQL has provided robust full-text search capabilities for over a decade through its tsvector and tsquery data types. This approach converts documents into a normalized, indexed format optimized for fast keyword searches.

How `tsvector` Works

PostgreSQL's tsvector represents documents as sorted lists of lexemes (normalized words), automatically handling stemming, stop-word removal, and duplicate elimination:

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT,
    search_vector tsvector
);

-- Convert text to searchable format
UPDATE articles 
SET search_vector = to_tsvector('english', title || ' ' || content);
-- Create index for fast searches

CREATE INDEX idx_search_vector ON articles USING GIN (search_vector);

-- Search for articles
SELECT id, title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'database & performance') AS query
WHERE search_vector @@ query
ORDER BY rank DESC;

Strengths and Limitations

Advantages

Zero external dependencies—pure PostgreSQL functionality
Tight integration with transactional data
Mature, battle-tested implementation
Low operational overhead

Limitations

Basic relevance ranking using ts_rank (no BM25)
No built-in typo tolerance
Limited support for complex proximity queries
Performance degrades with very large text corpora

PostgreSQL's native search works exceptionally well for small to medium datasets where exact lexeme matching suffices. A 2013 performance study showed significant improvements when using dedicated tsvector columns versus dynamic expression-based indexes, with search times dropping from 18-22 seconds to 400-1000ms on 1.6 million records.

The pg_search Extension: Enhanced PostgreSQL Search

The pg_search extension, available on platforms like Neon, brings advanced search capabilities directly into PostgreSQL without requiring separate infrastructure. It addresses many limitations of basic tsvector search while maintaining the benefits of database-native implementation.

Key Enhancements

BM25 Ranking: Instead of PostgreSQL's basic ts_rank, pg_search implements the industry-standard Okapi BM25 algorithm for relevance scoring:

-- Create BM25 index
CREATE INDEX article_search_idx 
    ON articles USING bm25 (id, title, content) 
    WITH (key_field = 'id');

-- Search with advanced ranking
SELECT title, paradedb.score(id) AS relevance
FROM articles
WHERE content @@@ 'performance database'
ORDER BY relevance DESC;

Typo Tolerance: The extension handles spelling variations and approximations, making search more forgiving for end users.

Advanced Query Features: Support for phrase search, proximity matching, and Boolean operators goes beyond basic tsquery capabilities.

Performance Impact

According to Neon's documentation, pg_search can deliver up to 1,000× faster search performance compared to native PostgreSQL FTS while providing more accurate relevance ranking. This dramatic improvement comes from optimized indexing and the superior BM25 algorithm.

However, pg_search currently has limited availability, primarily on Neon's managed PostgreSQL service in AWS regions.

DuckDB: The Analytical Search Engine

DuckDB represents a fundamentally different approach to text search, optimized for analytical workloads rather than transactional systems. Built as an in-process OLAP database, DuckDB excels at handling large datasets with complex analytical queries.

DuckDB's FTS Extension

DuckDB's full-text search extension provides SQLite FTS5-like functionality with analytical database performance:

-- Install and load (auto-loaded on first use)
INSTALL fts;
LOAD fts;

-- Create comprehensive FTS index
PRAGMA create_fts_index(
    'documents', 
    'document_id', 
    'title', 'content', 'author',
    stemmer = 'english',
    stopwords = 'english'
);

-- Search with BM25 scoring
SELECT document_id, title, 
       fts_main_documents.match_bm25(document_id, 'analytical database') AS score
FROM documents
WHERE score IS NOT NULL
ORDER BY score DESC;

Technical Architecture

DuckDB's FTS implementation leverages several advanced features:

Inverted Indexing: Creates efficient indexes similar to dedicated search engines

BM25 Scoring: Built-in support for the Okapi BM25 ranking algorithm

Multi-language Support: Extensive stemmer support for 25+ languages

Configurable Processing: Customizable stop words, regex patterns, and text normalization.

Performance Characteristics

DuckDB's columnar storage and vectorized execution provide significant advantages for analytical text processing:

Analytical Queries: 30-50× faster than traditional row-based databases for aggregation-heavy workloads
Memory Efficiency: Direct processing of columnar file formats like Parquet
Parallel Processing: Built-in multi-core query execution

However, DuckDB is specifically designed for analytical workloads, not transactional systems. It performs poorly on write-heavy operations compared to PostgreSQL.

Use Cases for DuckDB Search

DuckDB excels in scenarios where search is part of broader analytical workflows:

Data Science and Analytics: Searching large datasets as part of exploratory analysis
Log and Event Processing: Full-text search combined with time-series analytics
Business Intelligence: Text search integrated with aggregations and reporting
Research and Academic Work: Processing large document corpora

Companies like Hugging Face leverage DuckDB's FTS for dataset search across their repository, taking advantage of the seamless integration with analytical queries.

External Search Engines: The Dedicated Approach

External search engines like Elasticsearch, Apache Solr, and newer alternatives like Typesense represent the traditional approach to enterprise search. These systems provide unmatched search capabilities but require separate infrastructure and operational overhead.

When External Engines Excel

Advanced Text Processing

Sophisticated query parsing and analysis
Machine learning-powered relevance tuning
Real-time indexing and updates
Faceted search and complex filtering

Scalability

Distributed architecture for massive datasets
Horizontal scaling across multiple nodes
High-availability clustering
Geographic distribution

Specialized Features

Geospatial search capabilities
Advanced analytics and aggregations
Custom scoring and ranking algorithms
Rich ecosystem of plugins and extensions

The Trade-offs

External search engines come with significant operational complexity[^18][^17]:

Infrastructure Overhead: Separate clusters to deploy and maintain
Data Synchronization: Complex ETL pipelines to keep search indexes current
Cost Implications: Additional compute, storage, and operational expenses
Consistency Challenges: Potential data drift between primary database and search index

Research by academics comparing PostgreSQL, MongoDB, Elasticsearch, and Apache Solr found that while NoSQL and dedicated search engines provide faster query execution, PostgreSQL performed surprisingly well, especially for smaller datasets.

Performance Comparison: Real-World Benchmarks

Recent benchmarking studies provide insights into relative performance across different search approaches:

Database-Native vs. External Engines

A clinical data warehouse study comparing PostgreSQL with Apache Solr found that Solr provided dramatic performance improvements for text-heavy queries, often performing 10× faster than database solutions for complex text searches. However, the database performed better for simple attribute-based queries.

DuckDB vs. Traditional Databases

Performance comparisons show DuckDB's analytical strengths:

Data Upload: DuckDB averaged 0.48 seconds vs. SQLite's 0.68 seconds for 100,000 records
Data Retrieval: DuckDB: 0.56 seconds vs. SQLite: 0.79 seconds
Analytical Queries:** DuckDB shows 3-25× improvements over time for complex analytical workloads

However, SQLite outperforms DuckDB by 10-500× on transactional write workloads.

Text Search Specific Metrics

Research specifically focused on full-text search performance reveals:

PostgreSQL with tsvector: Excellent for small-medium datasets, performance degrades significantly beyond millions of records
DuckDB FTS: Superior for analytical text processing, especially when combined with aggregations
External Engines: Consistent performance regardless of dataset size, with the best features for complex text processing

Decision Framework: Choosing the Right Approach

Use PostgreSQL's Built-in `tsvector` When:

You have small to medium datasets (< 1 million documents)
Search requirements are straightforward (keyword matching)
You want zero operational overhead
Exact lexeme matching is sufficient
You need tight integration with transactional data

Consider `pg_search` (on Neon/AWS) When:

You need better relevance ranking (BM25)
Typo tolerance is important for user experience
You want advanced search features without external infrastructure
You're already using a compatible PostgreSQL service
You need up to 1,000× performance improvement over basic tsvector

Choose DuckDB FTS When:

Your search is part of analytical workflows
You're processing large datasets (millions to billions of records)
You need to combine text search with complex aggregations
You're working with columnar data formats (Parquet, etc.)
You can accept in-process, single-node limitations

Opt for External Search Engines When:

You have very large datasets (10M+ documents)
You need advanced features (faceting, geo-search, ML-powered relevance)
You require horizontal scaling and high availability
You have the infrastructure and operational capacity
Search is a core business function requiring specialized optimization

Implementation Examples

PostgreSQL with Enhanced Search

-- Basic tsvector approach
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    description TEXT,
    search_content tsvector
);

-- Trigger to maintain search content
CREATE OR REPLACE FUNCTION update_search_content()
RETURNS TRIGGER AS $$
BEGIN
    NEW.search_content = to_tsvector('english', 
        COALESCE(NEW.name, '') || ' ' || COALESCE(NEW.description, ''));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_products_search
    BEFORE INSERT OR UPDATE ON products
    FOR EACH ROW EXECUTE FUNCTION update_search_content();

DuckDB Analytical Search

import duckdb

# Connect and setup FTS
conn = duckdb.connect()
conn.execute("LOAD fts;")

# Create and index documents
conn.execute("""
    CREATE TABLE research_papers (
        paper_id VARCHAR,
        title VARCHAR,
        abstract VARCHAR,
        authors VARCHAR,
        publication_year INTEGER
    );
""")

# Build comprehensive index
conn.execute("""
    PRAGMA create_fts_index(
        'research_papers', 
        'paper_id', 
        'title', 'abstract', 'authors',
        stemmer = 'english'
    );
""")

# Analytical search with aggregations
results = conn.execute("""
    SELECT 
        publication_year,
        COUNT(*) as papers_found,
        AVG(score) as avg_relevance
    FROM (
        SELECT *, 
               fts_main_research_papers.match_bm25(
                   paper_id, 
                   'machine learning neural networks'
               ) AS score
        FROM research_papers
    ) papers
    WHERE score IS NOT NULL
    GROUP BY publication_year
    ORDER BY publication_year DESC;
""").fetchall()

Future Trends and Considerations

The text search landscape continues evolving rapidly:

Vector/Semantic Search Integration

Modern applications increasingly combine traditional keyword search with vector embeddings for semantic similarity. DuckDB has begun exploring this hybrid approach, while PostgreSQL extensions like pgvector enable vector operations alongside traditional FTS.

Performance Optimizations

Database vendors continue improving native search capabilities. PostgreSQL 14+ includes Bloom filters for analytical queries, while DuckDB shows continuous performance improvements—becoming 3-25× faster over recent versions.

Cloud-Native Solutions

Managed services increasingly offer sophisticated search capabilities. Neon's pg_search exemplifies this trend, providing advanced search without operational complexity.

AI-Enhanced Search

Machine learning integration for query understanding, result ranking, and personalization is becoming standard. This trend favors external search engines, though database-native solutions are beginning to incorporate AI capabilities.

Conclusion

The choice between database-native and external search solutions depends heavily on your specific requirements, scale, and operational constraints. PostgreSQL's built-in capabilities serve many applications well, especially with enhancements like pg_search. DuckDB offers compelling advantages for analytical workloads where search combines with complex data processing. External search engines remain the gold standard for large-scale, feature-rich search applications.

Rather than viewing these as competing approaches, consider them as complementary tools in your data architecture toolkit. Many successful applications use multiple search strategies: PostgreSQL for transactional data with basic search needs, DuckDB for analytical text processing, and Elasticsearch for user-facing search experiences.

The key is understanding your specific requirements—dataset size, query complexity, operational constraints, and performance expectations—then selecting the approach that best balances functionality, performance, and operational simplicity for your use case.

Zero-Downtime Schema Migrations for Self-Hosted Postgres with pgroll ›