Stop Forcing LLMs to Count: Shift Aggregations to the Database

The biggest weakness of a standard RAG (Retrieval-Augmented Generation) pipeline is its inability to handle quantitative reasoning. If you ask a copilot, "How many premium subscribers signed up in Q3?" it will likely retrieve a handful of relevant documents and attempt to count them manually. This is where the system breaks: LLMs are notoriously bad at counting, and they are limited by the context window, meaning they can't "see" the full dataset to perform a true aggregation.

The mistake most engineers make is trying to solve this via prompt engineering or by stuffing more chunks into the prompt. The real solution is to stop treating the LLM as the calculator and start treating it as the query generator.

The Failure of Vector Search for Aggregates

Vector databases are designed for semantic similarity, not arithmetic. When you perform a similarity search, you get the top-k nearest neighbors. If your dataset has 10,000 records that match a criteria, but your top_k is set to 20, the LLM only sees 20 records. It will confidently tell you there are 20 records, leading to a "hallucination" that is technically accurate to the context provided but factually wrong regarding the database.

The Solution: Text-to-SQL Hybrid RAG

Instead of retrieving raw text chunks, your agent should be capable of generating a structured query to the underlying database. By shifting the aggregation—SUM, AVG, COUNT—to the SQL engine, you ensure 100% accuracy.

For example, if you are using a PostgreSQL instance with pgvector, you shouldn't be retrieving rows to count them in Python. Instead, your agent should generate a query like:

SELECT count(*) FROM users WHERE subscription_tier = 'premium' AND created_at >= '2023-07-01';

The LLM then receives the single integer result (e.g., 452) and incorporates that number into its natural language response. This transforms the LLM from a flawed calculator into a sophisticated interface for a reliable data engine.

Implementing the Guardrails

To make this work in production, you need a strict schema mapping. I recommend providing the LLM with a minimized version of your DDL (Data Definition Language) in the system prompt. If you are using SQLAlchemy or a similar ORM, you can automate this by exporting the table schemas.

One common error when implementing this is the OperationalError: (psycopg2.OperationalError) connection to server lost. This usually happens when the LLM generates a complex join that exceeds the database timeout settings. To mitigate this, I suggest implementing a "Query Validator" layer between the LLM and the DB. This layer should check for:
1. Read-only access: Ensure the LLM cannot execute DROP or DELETE commands.
2. Limit clauses: Force a LIMIT 100 on any non-aggregate query to prevent memory overflows.
3. Type checking: Ensure the LLM isn't trying to perform a SUM() on a VARCHAR column.

Final Architecture Shift

The goal is a hybrid approach. Use vector search for "What is our philosophy on customer success?" and use Text-to-SQL for "How many customers churned last month?"

By decoupling semantic retrieval from quantitative aggregation, you remove the most common source of distrust in AI copilots. Let the LLM handle the language and let the database handle the math.