paulserban.eu

Writing Edition

Paul Serban

Writing, snippets & book notes

Vol. 2026

August 11, 2026

Classical Lexical Search for RAG: Building Retrieval-Augmented Generation with PostgreSQL

Why full-text search, BM25, and tsvector still belong in your retrieval stack - even in the age of embeddings

Introduction

Retrieval-Augmented Generation (RAG) has become the default architecture for grounding large language models in facts they weren't trained on. Most tutorials jump straight to vector databases, embedding models, and approximate nearest-neighbor search. That's a reasonable default for many problems, but it quietly skips over a retrieval method that predates embeddings by decades and still outperforms them in a surprising number of production scenarios: classical lexical search.

Lexical search - also called keyword search, full-text search, or sparse retrieval - matches queries to documents based on term overlap, term frequency, and inverse document frequency rather than learned semantic representations. It's the technology behind search engines like Elasticsearch and Apache Solr, and it's also built directly into PostgreSQL. For teams already running Postgres as their system of record, this means you can stand up a competent RAG retrieval layer without introducing a new database, a new embedding pipeline, or a new operational surface area. This article walks through how lexical search works, how to implement it in Postgres, where it beats or loses to vector search, and how to combine the two in a hybrid retrieval pipeline.

Why Lexical Search Still Matters in a RAG Pipeline

It's tempting to treat embedding-based retrieval as a strict upgrade over keyword search. The reality is more nuanced. Dense vector retrieval is excellent at capturing semantic similarity - it will correctly match "car" to "automobile" even though the strings share no characters. But that strength is also a weakness: embeddings can blur past exact identifiers, product SKUs, error codes, legal citations, or proper nouns that a user is searching for verbatim. If someone searches for ORA-00942 or CVE-2023-4863, a well-tuned lexical index will find it instantly and precisely. A semantic embedding model, trained to generalize, may rank it no higher than loosely related text.

This matters enormously for RAG systems built on technical documentation, legal corpora, customer support tickets, or codebases - domains dense with exact tokens that carry disproportionate meaning. BM25, the scoring algorithm underlying most modern lexical search engines, has been a strong baseline in information retrieval research for decades, and multiple studies in the retrieval literature (including work cited in the BEIR benchmark paper by Thakur et al., 2021) have shown that BM25 remains a surprisingly tough baseline for many domain-specific retrieval tasks, sometimes outperforming dense retrievers trained on out-of-domain data. The practical takeaway isn't that embeddings are wrong - it's that lexical and semantic retrieval are complementary, and a serious RAG system should make deliberate use of both rather than assuming one subsumes the other.

How PostgreSQL's Full-Text Search Engine Works

PostgreSQL has shipped native full-text search since version 8.3, and it has matured considerably since. Rather than searching raw text with LIKE or regular expressions, Postgres full-text search converts text into a specialized data type called tsvector - a sorted list of distinct lexemes (normalized word forms) along with their positions in the original document. A search query is similarly converted into a tsquery, and Postgres matches the two using the @@ operator.

The normalization step is where a lot of the useful behavior lives. Postgres applies a text search configuration - a combination of a parser and a set of dictionaries - that handles stemming (reducing "running," "runs," and "ran" to a common root), stop-word removal (dropping words like "the" and "and" that carry little discriminative value), and tokenization of special cases like email addresses, URLs, and hyphenated words. English is well supported out of the box via the english configuration, and Postgres ships configurations for dozens of other languages. You can also build custom configurations layering in synonym dictionaries or domain-specific thesauruses, which is genuinely useful for RAG corpora full of jargon or abbreviations that general-purpose stemmers don't handle well.

Ranking is handled by two built-in functions: ts_rank and ts_rank_cd. Both compute a relevance score based on term frequency and, for ts_rank_cd ("cover density"), the proximity of matching terms to one another. Neither implements BM25 exactly - Postgres's native ranking is closer to a simplified TF-IDF variant - but it's good enough for many use cases, and extensions exist that layer true BM25 scoring on top, which we'll cover shortly.

Implementation: Building a Lexical Retrieval Layer in Postgres

Let's walk through a realistic implementation. Assume you have a documents table holding chunks of text you want to retrieve for a RAG pipeline - perhaps internal documentation, support articles, or ingested PDFs split into passages.

CREATE TABLE documents (
    id BIGSERIAL PRIMARY KEY,
    source_id TEXT NOT NULL,
    chunk_index INT NOT NULL,
    content TEXT NOT NULL,
    metadata JSONB DEFAULT '{}'::jsonb,
    search_vector TSVECTOR GENERATED ALWAYS AS (
        to_tsvector('english', content)
    ) STORED,
    created_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_documents_search_vector
    ON documents USING GIN (search_vector);

Using a generated column keeps the tsvector automatically in sync with the source text, which avoids the classic bug of a search index silently going stale after an UPDATE. The GIN (Generalized Inverted Index) index is the standard choice for full-text search in Postgres; it indexes each lexeme to the rows that contain it, making lookups fast even on large tables. GiST indexes are also supported and trade some query speed for faster, more incremental updates, which can matter if you're ingesting documents continuously rather than in batches.

Querying this table for RAG retrieval looks like this:

SELECT
    id,
    source_id,
    content,
    ts_rank_cd(search_vector, query) AS rank
FROM documents,
    plainto_tsquery('english', $1) AS query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

plainto_tsquery takes a raw user query string and converts it into a tsquery, applying the same normalization as the indexing side, and implicitly AND-ing the resulting terms together. For more control - phrase search, boolean operators, weighting - websearch_to_tsquery is often a better choice for user-facing search boxes, since it accepts a Google-style query syntax (quoted phrases, OR, minus-prefixed exclusions) that non-technical users already understand intuitively.

A production RAG retrieval function typically wraps this in application code so it can be composed with other retrieval steps:

import psycopg2
from psycopg2.extras import RealDictCursor

def lexical_search(conn, query_text: str, top_k: int = 10) -> list[dict]:
    sql = """
        SELECT id, source_id, content,
               ts_rank_cd(search_vector, query) AS rank
        FROM documents,
             websearch_to_tsquery('english', %s) AS query
        WHERE search_vector @@ query
        ORDER BY rank DESC
        LIMIT %s;
    """
    with conn.cursor(cursor_factory=RealDictCursor) as cur:
        cur.execute(sql, (query_text, top_k))
        return cur.fetchall()

This function slots directly into a RAG orchestration layer alongside a vector similarity call, with results from both merged before being passed to the language model as context.

Improving Relevance: BM25, Trigram Matching, and Weighted Fields

Postgres's built-in ranking functions are serviceable but not state-of-the-art. If your retrieval quality depends heavily on precise ranking - which it usually does in RAG, since the top few chunks are what actually reach the model's context window - it's worth reaching for the pg_search extension (part of the ParadeDB project) or the older pg_bm25-style approaches, which implement true BM25 scoring directly inside Postgres using custom index types. BM25 improves on simple TF-IDF by saturating term frequency (so a word appearing fifty times doesn't dominate a score fifty times more than a word appearing once) and normalizing for document length, both of which matter when your RAG chunks vary in size.

For cases where exact lexical matching is too brittle - typos, partial matches, fuzzy product names - the pg_trgm extension adds trigram-based similarity matching, which breaks text into overlapping three-character sequences and measures overlap between query and document trigrams. This is complementary to tsvector search rather than a replacement for it, and it pairs well with a GIN index of its own:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_documents_trgm
    ON documents USING GIN (content gin_trgm_ops);

SELECT id, content, similarity(content, 'kubernetis cluser') AS sim
FROM documents
WHERE content % 'kubernetis cluser'
ORDER BY sim DESC
LIMIT 10;

Another underused technique is field weighting. If your documents have a title and a body, you can combine them into a single tsvector with different weights, so matches in the title rank higher than matches buried in the body text:

UPDATE documents
SET search_vector =
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(content, '')), 'B');

Weights range from A (highest) to D (lowest), and ts_rank takes them into account automatically when computing relevance, giving you a cheap but effective relevance boost with almost no additional infrastructure.

Trade-offs and Pitfalls

Lexical search's biggest limitation is its blindness to meaning. It has no concept of synonymy beyond what's explicitly encoded in a dictionary or thesaurus, so a query for "how to terminate an employee" won't match a document that only discusses "offboarding staff" unless those terms are linked through a custom synonym dictionary. This is a real constraint for RAG systems answering natural-language questions, where users rarely phrase things the way the source documents do. Teams that rely purely on lexical search for conversational RAG often see a ceiling on recall that no amount of ranking tuning will fix, because the relevant document simply never surfaces as a candidate.

There's also an operational trap worth calling out: text search configuration choices are not neutral, and getting them wrong silently degrades quality rather than throwing errors. Using the wrong language configuration, failing to update it when your corpus shifts from English prose to a mix of English and embedded code snippets, or not accounting for the way the default parser tokenizes identifiers like user_id or getUserById can quietly starve your retrieval of matches. Code-heavy or highly technical corpora often benefit from a custom parser configuration or from pre-processing text (such as splitting camelCase and snake_case identifiers) before it ever reaches to_tsvector.

Finally, GIN indexes, while fast for reads, carry real write overhead. Every insert or update to an indexed document triggers index maintenance, and on high-throughput ingestion pipelines this can become a bottleneck. The fastupdate storage parameter on GIN indexes buffers changes to amortize this cost, but it introduces its own trade-off: a pending list that must periodically be flushed, which can cause unpredictable latency spikes on busy write workloads if not monitored and tuned with gin_pending_list_limit.

Best Practices for Lexical Search in RAG Systems

Treat lexical and semantic retrieval as two retrievers feeding a shared ranking stage rather than competing systems. A common and effective pattern, often called hybrid search, runs both a tsvector query and a pgvector cosine-similarity query against the same candidate pool, then merges results using a technique like Reciprocal Rank Fusion (RRF), which combines rankings from multiple retrieval systems without needing their scores to be on comparable scales. This single addition routinely improves recall more than tuning either retriever in isolation.

def hybrid_search(conn, query_text: str, query_embedding: list[float], top_k: int = 10):
    lexical_results = lexical_search(conn, query_text, top_k=20)
    vector_results = vector_search(conn, query_embedding, top_k=20)

    scores = {}
    for rank, row in enumerate(lexical_results):
        scores[row["id"]] = scores.get(row["id"], 0) + 1 / (60 + rank)
    for rank, row in enumerate(vector_results):
        scores[row["id"]] = scores.get(row["id"], 0) + 1 / (60 + rank)

    ranked_ids = sorted(scores, key=scores.get, reverse=True)[:top_k]
    return ranked_ids

Beyond hybridization, invest early in a text search configuration tailored to your corpus rather than accepting Postgres defaults indefinitely - this is cheap to do up front and expensive to retrofit once millions of rows have been indexed under a suboptimal configuration. Instrument retrieval quality with real queries and relevance judgments rather than relying on intuition; even a small, manually labeled evaluation set of twenty to fifty representative queries, checked against expected documents, catches regressions that are otherwise invisible until users complain. And keep index maintenance visible: monitor GIN index size, bloat, and pg_stat_user_indexes to catch degradation before it affects query latency, especially on tables with heavy update or delete traffic from re-ingestion jobs.

Key Takeaways

Conclusion

RAG architecture discussions tend to fixate on embedding models and vector databases, but production retrieval quality is rarely won or lost on vector search alone. Classical lexical search, built on decades of information retrieval research and available natively inside PostgreSQL, remains one of the highest-leverage tools for improving RAG recall and precision - particularly for technical, code-heavy, or identifier-dense corpora where exact matching matters. The practical path for most teams already running Postgres isn't to replace it with a specialized vector database, but to extend it: add pgvector for semantic search, keep tsvector for lexical search, and fuse the two at query time. That combination, implemented with the extensions and patterns above, gets you most of the retrieval quality of a dedicated search stack without adding a new system to operate.

References