Building RAG Without a Vector Database: Leveraging DuckDB for Lightweight Full-Text Search on Budget VPS

Introduction: The Hidden Costs of the Vector-First Mentality

In the rapidly evolving landscape of Artificial Intelligence and Large Language Models (LLMs), Retrieval-Augmented Generation (RAG) has emerged as the gold standard for reducing hallucinations and grounding models in proprietary data. For the past few years, the tech industry has operated under a dominant paradigm: to build RAG, you must use a dedicated vector database like Pinecone, Milvus, Qdrant, or pgvector.

While vector embeddings and semantic search are incredibly powerful, they come with a steep tax. Vector databases are notoriously memory-hungry and computationally expensive. For startups, indie hackers, and enterprise innovation teams building internal tools, provisioning a managed vector database or running a heavy cluster on a Virtual Private Server (VPS) can quickly drain infrastructure budgets. But what if you could achieve exceptional RAG performance without a single vector embedding? Enter DuckDB and the resurgence of lightweight, highly optimized Full-Text Search (FTS).

The Core Challenge: Running RAG on a Budget VPS

When deploying production applications on a budget VPS (such as a 2GB RAM, 1-vCPU instance from DigitalOcean, Hetzner, or Linode), resources are severely constrained. Operating a standard vector-based pipeline on such infrastructure introduces several critical bottlenecks:

RAM Consumption: High-dimensional vector indexes (like HNSW) must reside entirely in memory to achieve acceptable query latency. A few hundred thousand documents can easily exhaust a 2GB VPS.
CPU Overhead: Generating embeddings using local models (like BERT-based embedding models) pins the CPU at 100%, causing service degradation for API endpoints.
Operational Complexity: Managing separate database clusters, handling index synchronization, and dealing with backup overhead increases the surface area for production failures.

By shifting our perspective from dense semantic search back to sparse lexical search, we can eliminate these constraints entirely while maintaining surprisingly high retrieval accuracy for structured and semi-structured business data.

Why DuckDB? The In-Process Analytical Powerhouse

DuckDB is an open-source, embedded, columnar relational database management system designed for Analytical Processing (OLAP). Often described as the "SQLite for Analytics," DuckDB runs directly inside your application process, eliminating network latency between your backend and the database.

Beyond its legendary speed at executing SQL analytical queries, DuckDB includes a powerful, mature Full-Text Search (FTS) extension. This extension implements the industry-standard BM25 (Best Matching 25) probabilistic relevance algorithm. BM25 scores documents based on term frequency and inverse document frequency, adjusted for document length. For many business use cases—such as searching through log files, customer support tickets, product catalogs, and structured documentation—BM25 often outperforms vector search by matching exact keywords, product IDs, and specific error codes that embeddings tend to blur together.

Architecture Overview: Vectorless RAG with DuckDB

The architecture of a DuckDB-powered FTS RAG system is elegantly simple. It strips away the embedding pipeline and the external database layer entirely:

Data Ingestion: Raw text documents (Markdown, PDF, JSON) are parsed and loaded into a local DuckDB database file (e.g., storage.duckdb).
Indexing: The DuckDB FTS extension tokenizes the text, stems the words, removes stop words, and builds an inverted index directly on the disk.
Retrieval: When a user submits a natural language query, the system runs a standard SQL query utilizing the FTS index to retrieve the top $K$ most relevant document chunks based on their BM25 score.
Generation: The retrieved text chunks are formatted into a prompt context and sent to an LLM API (such as OpenAI, Anthropic, or an ultra-lightweight local model like Llama 3 via Ollama) to generate the final response.

By keeping everything in-process and utilizing disk-backed columnar storage, DuckDB can index millions of rows while consuming less than 250MB of RAM, making it a perfect match for low-tier VPS environments.

Step-by-Step Implementation

1. Environment Setup and Data Ingestion

First, ensure you have DuckDB installed in your runtime environment (Python, Node.js, or Go). In this guide, we will use Python for its popularity in the AI ecosystem. You can install DuckDB via pip: pip install duckdb. Next, we initialize our database and create a table to hold our knowledge base chunks.

import duckdb

# Connect to a local persistent database file
conn = duckdb.connect('rag_knowledge.db')

# Create a table for documents
conn.execute("""
CREATE TABLE IF NOT EXISTS documents (
    id VARCHAR,
    title VARCHAR,
    content TEXT,
    metadata VARCHAR
);
""")

2. Building the Full-Text Search Index

To enable BM25 search capabilities, we must load the FTS extension and initialize the index on our target table and column. DuckDB simplifies this process down to a few macro executions:

# Load the FTS extension
conn.execute("INSTALL fts; LOAD fts;")

# Create the FTS index on the 'documents' table using the 'content' column
conn.execute("PRAGMA create_fts_index('documents', 'id', 'content');")

Behind the scenes, DuckDB creates underlying control tables that store the vocabulary, document frequencies, and term positions necessary for ultra-fast, sub-millisecond retrieval speeds.

3. Querying and Content Retrieval

When a user poses a question, we execute a search query against our FTS index. DuckDB provides a helper function that automatically calculates the BM25 score and ranks results in descending order:

query = "How to configure SSL on Nginx server?"

# Execute full-text search query
result = conn.execute(f"""
    SELECT id, title, content, score
    FROM (
        SELECT *, fts_main_documents.match_bm25(id, '{query}') AS score
        FROM documents
    )
    WHERE score IS NOT NULL
    ORDER BY score DESC
    LIMIT 3;
""").fetchall()

for row in result:
    print(f"Score: {row[3]:.4f} | Title: {row[1]}")

Optimizing RAG Performance: The Hybrid Approach

While keyword-based BM25 search is incredibly fast and memory-efficient, pure keyword matching can sometimes miss conceptual alignment (e.g., matching "automobile" when the user searched for "car"). To mitigate this limitation without reintroducing heavy vector databases, we can apply two highly effective optimization techniques directly on our VPS:

Query Expansion via LLM

Before querying DuckDB, pass the user's natural language query to an LLM with a prompt asking it to generate alternative keywords, synonyms, and related technical terms. Feed the expanded query into DuckDB's FTS engine. This bridges the semantic gap using the intelligence of the LLM while keeping database infrastructure requirements flat.

Cross-Encoder Reranking

To maximize precision, retrieve a slightly larger pool of candidates from DuckDB (e.g., top 20 documents). Then, use an ultra-lightweight local Cross-Encoder model (such as ms-marco-MiniLM-L-6-v2 via the HuggingFace sentence-transformers library) to re-score and re-rank those 20 documents. Cross-encoders are far more accurate at determining relevancy than standard embedding models because they perform simultaneous attention across the query and the document chunk. Since you are only ranking 20 documents rather than searching through millions, the CPU overhead on your VPS is negligible (taking only tens of milliseconds).

Production Considerations on a VPS

Deploying DuckDB in a production RAG environment requires adhering to a few best practices regarding concurrency and data mutations:

Concurrency Management: DuckDB supports multiple readers but only a single writer. When running within a web application framework (like FastAPI or Express), ensure your database connections are configured correctly. It is often best to keep DuckDB in read-only mode for the API workers, and use a separate background worker process to rebuild or update the database file when documentation updates occur.
Index Maintenance: When data in the main table is inserted, updated, or deleted, the FTS index does not update automatically in real-time. You must periodically refresh the index using PRAGMA drop_fts_index() followed by PRAGMA create_fts_index(). For typical business RAG applications where documentation changes daily or weekly rather than by the second, a scheduled cron job during low-traffic hours is an ideal strategy.

Conclusion: Pragmatic AI Infrastructure

Building high-performance AI applications does not require over-engineering your infrastructure or spending thousands of dollars on cloud-native vector databases. By looking back at proven, hyper-optimized information retrieval techniques like BM25 and pairing them with modern, in-process analytical engines like DuckDB, you can run a fully capable, blazing-fast RAG system on a $5 to $10 per month VPS.

This pragmatic architecture lowers the barrier to entry for developers, dramatically reduces operational complexity, and proves that sometimes, the smartest way forward in AI engineering is to make your stack smaller, simpler, and significantly leaner.