Text-to-SQL is not enough

When you build a conversational assistant on top of an application, users ask questions that only your database can answer. In Trova, our executive recruitment product, users create jobs and search candidates by talking to AI agents. They then ask about history, metrics and summaries of everything they have created: what roles did I fill in healthcare last year, fetch me candidates with leadership experience in oncology and haematology, how many assignments closed successfully last quarter.

Answering those is hard even for a human who knows the schema intimately.

Why the data makes this hard

Look at a handful of assignments created by a real user. Each has different fields filled and others left empty. There is no consistency in user behaviour, and no pattern stable enough to write generalised rules against.

If the user asks to list their past assignments from healthcare, the assistant should return three of those four jobs. To build that, we wanted to use what reasoning models can now do with Text-to-SQL. Give an LLM table schemas with column descriptions and it will generate a query that runs against the application database with reasonable accuracy, particularly with an EXPLAIN plan tool validating it before execution.

Shortcomings of pure Text-to-SQL

Metadata questions work fine. "How many assignments did I create last year?" needs nothing but the schema, and the model gets it right.

"List my past assignments from healthcare" is a different problem. The model would add a filter matching the literal string healthcare against the sector column, and the query returned nothing, because character matching on one column is not what the question meant.

The obvious fix is to send the contents of the sector column to the model so it can build a better filter. That works only for low-cardinality columns, and it costs context window. Worse, it still does not solve the real case: users who leave sector empty, where the designation, company, description or attached documents are what tell you this is a healthcare assignment. That content does not fit in a context window, and designing to make it fit is the wrong shape of solution. Add multiple tables and joins and the problem compounds.

The remedy: retrieval inside the query

This problem was solved long before LLMs, and it is called information retrieval. We applied retrieval augmented generation so agents can pull targeted information out of the database rather than trying to reason about its contents.

In short: the incoming user query is converted to an embedding vector. On the database side, each table has a designated embedding column built from the columns relevant to user matching, and cosine similarity is computed with the <=> operator between the query vector and the row vectors. A job generates embeddings for new and changed rows. The agent writes SQL using the encoded query to retrieve relevant rows, without needing to understand the contents of the table at all, and then drafts a reply from what comes back.

So when a user asks for healthcare assignments, the model needs only the schema to write the query. It passes the embedded user query inside the SQL and gets back the rows that matter. Every major database now offers a vector type; in Postgres it is the pgvector extension, which we installed on the application database before designing per-table embeddings and updating our agent tools to use them.

That is the shape of the solution. What follows is what it takes to make it correct, because three things will quietly give you wrong answers if you do not handle them.

Overfiltering: the failure that returns no error

This is the one to get right first. With an approximate index, pgvector applies WHERE filters after the index scan, not during it. With HNSW and the default hnsw.ef_search of 40, a condition matching 10% of rows leaves roughly four results on average. In the pathological case the index fetches its candidates, the filter removes every one, and the query returns zero rows.

That is exactly our motivating example. A tenant-scoped query over a multi-tenant table is a highly selective filter over an approximate index. The query succeeds, returns fewer rows than actually exist, and the agent presents them as a complete answer. No error is raised anywhere in the stack.

-- Without these, the filter runs after the index scan and
-- a tenant-scoped query can silently return fewer rows than exist.
SET LOCAL hnsw.iterative_scan = 'relaxed_order';
SET LOCAL hnsw.ef_search      = 100;
SET LOCAL hnsw.max_scan_tuples = 20000;

SELECT id, title
FROM   assignments
WHERE  owner_id = $1
ORDER  BY embedding <=> $2
LIMIT  20;

Iterative index scans arrived in pgvector 0.8.0 and keep scanning until enough results are found or max_scan_tuples is reached. Use relaxed_order unless ranking precision genuinely matters, in which case strict_order preserves exact distance ordering at a small cost. Index the columns you filter on, not just the vector.

One further consideration for multi-tenant products: sharing an approximate index across tenants means one tenant's vectors affect recall and speed for everyone else. The documented answer is list partitioning or separate tables, and it is an isolation argument as much as a quality one.

Empty has to be a valid answer

Cosine distance always returns the nearest rows. It has no concept of relevance, only of order.

If a user has no healthcare assignments at all, a top-k query returns their k least-dissimilar jobs, and an agent that was asked for healthcare assignments will write them up as healthcare assignments. Nothing in the pipeline flagged a problem, because nothing failed.

SELECT id, title, embedding <=> $2 AS distance
FROM   assignments
WHERE  owner_id = $1
  AND  embedding <=> $2 < 0.45   -- tune against labelled data
ORDER  BY distance
LIMIT  20;

You need a distance threshold, tuned against a labelled set rather than guessed, and the agent needs to treat an empty result as a real answer it can give. "I could not find any healthcare assignments" is correct. Four unrelated jobs described as healthcare is not.

Dense retrieval alone is not enough

Our two example questions split cleanly. Healthcare is a semantic category, and dense embeddings handle it well. Oncology and haematology are precise clinical terms, and exact terminology is where dense retrieval is weakest, because the embedding blurs the distinction the user was relying on.

Postgres already ships the other half. Full-text search gives you lexical matching, and the two rankings combine with Reciprocal Rank Fusion. No new infrastructure, no second datastore, no consistency problem you did not previously have.

WITH dense AS (
  SELECT id, row_number() OVER (ORDER BY embedding <=> $2) AS rank
  FROM   assignments WHERE owner_id = $1
  ORDER  BY embedding <=> $2 LIMIT 50
),
lexical AS (
  SELECT id, row_number() OVER (ORDER BY ts_rank_cd(search_tsv, q) DESC) AS rank
  FROM   assignments, plainto_tsquery('english', $3) q
  WHERE  owner_id = $1 AND search_tsv @@ q LIMIT 50
)
SELECT id, sum(1.0 / (60 + rank)) AS score
FROM   (SELECT * FROM dense UNION ALL SELECT * FROM lexical) r
GROUP  BY id ORDER BY score DESC LIMIT 20;

Keeping embeddings fresh

The approach needs plumbing to keep running, and the most visible cost is lag. A row inserted now is not retrievable until its embedding exists, which shows up to users as information the assistant claims not to have.

A scheduled job is the obvious implementation and the wrong default. Write the row id to an outbox table in the same transaction, or fire a trigger with LISTEN and NOTIFY, and have a worker embed on that signal. Lag drops from a batch interval to seconds, and you stop re-scanning tables looking for work.

The costs worth budgeting are not table locks, since updating a vector column is ordinary row-level MVCC. They are table bloat and autovacuum pressure from frequent updates, embedding API rate limits and spend, and backfill time whenever you change models. That last one is the reason to keep the embedding column and its generation logic loosely coupled to everything else.

Choosing an embedding model

We use text-embedding-3-small from OpenAI. It is a defensible default and worth re-evaluating periodically, because this part of the stack moves faster than the rest of it.

One hard constraint shapes the choice more than benchmark scores do. The pgvector type stores up to 16,000 dimensions, but HNSW and IVFFlat indexes handle only 2,000. A 3,072-dimension embedding cannot be indexed as a plain vector and needs halfvec, quantization, or Matryoshka truncation to a smaller size. At 1,536 dimensions, text-embedding-3-small indexes cleanly, which is a real advantage that has nothing to do with retrieval quality.

Whatever you pick, measure it. Recall at k against a labelled set of real user questions is the only way to know whether a model change helped, and it is also what turns the threshold in the previous section from a guess into a number.

In conclusion

To let conversational agents answer questions that need your application database, Text-to-SQL on its own is not enough. Combining it with information retrieval and safety guardrails handles the questions that require knowing what is inside your tables, not just their shape.

A more robust approach in line with agentic memory and context will be published soon.