Retrieval Is a Query: Search Where Your Business Data Lives

Retrieval used to sit behind a search box. In an AI application it is a step inside an agent's plan, and the agent rarely asks for "similar items" alone. A shopping assistant wants waterproof trail running shoes that are in stock, under $100 and ship to California. A support agent wants tickets like this one, from the same customer, opened since the last release.

Similarity is only one part of the request. The conditions are rows in tables that change all day: inventory, prices, tenants, permissions. Retrieval in an AI application is a database query with a similarity term in it.

Most teams still run it as two systems. Embeddings go to a vector database or a search cluster, business data stays in the database, a pipeline copies the filter columns across, and the application stitches the two results together.

That is a second copy of the data, a pipeline to keep it current, and access rules kept in step across two systems. When the business data already lands in PhoenixAI, 26.2 removes the second copy: vectors, filters and joins read the same rows in one query.

What PhoenixAI 26.2 adds

26.2 puts vector search, keyword relevance and embedding generation in the same SQL engine that already holds your tables. None of it needs a separate retrieval service; embedding() calls the model provider you register in SQL.

Put together, the shopping assistant's request is one query against live rows:

-- :q = the query vector, generated first with SELECT embedding('waterproof trail running shoes')
SELECT product_id, title, price, stock
FROM products
WHERE category = 'trail_running'
  AND price < 100
  AND stock > 0
  AND ship_region = 'CA'
ORDER BY approx_cosine_similarity(desc_emb, :q) DESC
LIMIT 10;

Every hard requirement sits in the WHERE clause, so the vector index only walks rows that qualify; soft wishes such as "waterproof" are left to the ranking. In EXPLAIN, the index stays in use with several predicates, with a LIKE, and when the top candidates are joined to another table afterwards. One form turns it off: a filter written as IN (subquery) falls back to an exact scan, so write it as plain predicates or a join. The same row and column policies apply as to any other query. That leaves two questions: how fresh the rows are, and what all this costs.

Writes show up on the next query

A new row is searchable as soon as its INSERT commits. We inserted a row whose vector matched a test query and ran that query straight away: it came back as the top hit on the first try, 5 times out of 5, about 15 ms after the commit. Deleting the top hit removed it from the very next query, also 5 times out of 5.

Two things in 26.2 make this work. A data segment smaller than 10,000 rows gets no vector index and is scanned exactly, so a small write needs no index build before it can be found. Deleted rows are filtered out during the vector search itself. By comparison, OpenSearch's default is to refresh an active index every second, so new documents typically become searchable within about a second (OpenSearch blog); we did not measure that here.

To change a filter column in place, use a primary key table. Our test table used a duplicate key, which does not accept UPDATE.

How it holds up against OpenSearch

We ran 26.2 against OpenSearch, the system most teams add when they need search, on public benchmarks. ClickHouse 26.9, which also ships a vector index, joins for the filtered test.

Competitors ran with default settings apart from the shared HNSW parameters. Latency and throughput are the median of three runs from one load client in the same VPC, OpenSearch's taken after its index was warm, and PhoenixAI's throughput uses prepared statements. A larger, multi-node run is next.

Search quality

Database-native search gives up little in quality. With identical HNSW settings, PhoenixAI's vector recall is within 0.02 of OpenSearch at every search depth we tried: 0.946 against 0.957 at efSearch 80, and 0.982 against 0.984 at efSearch 320.

VectorDBBench Cohere 1M, 300 queries per point, median of three runs.

Keyword relevance is close as well. On the ESCI product queries, PhoenixAI's BM25 scores 0.327 nDCG@10 against 0.342 for OpenSearch with its english analyzer. The gap is stemming: PhoenixAI's english parser lowercases and splits words but does not stem them, so "shoe" and "shoes" count as different terms. OpenSearch's standard analyzer, which does not stem either, scores 0.322.

Speed

Here a search engine is faster. Once its index is warm, OpenSearch answers a single query in about 5 to 8 ms and sustains about 1,000 queries per second at efSearch 80 and 510 at efSearch 320. PhoenixAI takes 13 to 15 ms and peaks at 442 and 301. Neither rejected a query at up to 128 concurrent clients.

VectorDBBench Cohere 1M, 3,000 queries per run at 16 to 128 concurrent clients, peak shown, median of three runs.

For PhoenixAI, send the query vector as a bound parameter. A 768-number vector written into the SQL text is parsed on every query; with a prepared statement, peak throughput at efSearch 80 rose from 264 to 442 queries per second. An agent that makes a few retrieval calls per step fits in that budget on one node. A public search box at thousands of queries per second needs more nodes, or a search engine.

Filtered search

The harder case is a query with a condition. We filtered on a label assigned at random to a fixed share of rows, from 0.1% to 50%. It is a synthetic stress test: the matching rows are scattered across the table.

VectorDBBench Cohere 1M label filters, 200 queries per point, latency measured at the client in the same VPC.

PhoenixAI and OpenSearch both keep recall under every filter: 0.96 to 1.0 for PhoenixAI, 0.97 to 1.0 for OpenSearch. OpenSearch is faster, at 5 to 10 ms against 17 to 36 ms, with PhoenixAI slowest when 0.5% to 1% of rows match. ClickHouse 26.9 searches first and filters afterwards by default, so when few rows qualify almost no candidates survive: recall is 0.02 at 1%. Its prefilter mode brings recall back to 1.0 by scanning every matching row, at about 800 ms per query.

Hybrid search

With a full-text index and a vector index on the same table, keyword and semantic ranking are two subqueries in one statement, fused with reciprocal rank fusion:

WITH kw AS (
  SELECT pid, ROW_NUMBER() OVER (ORDER BY score() DESC) AS r
  FROM products
  WHERE search('doc:ANY(waterproof trail running shoe)')
  ORDER BY score() DESC LIMIT 100
),
sem AS (
  SELECT pid, ROW_NUMBER() OVER (ORDER BY approx_cosine_similarity(emb, :q) DESC) AS r
  FROM products
  ORDER BY approx_cosine_similarity(emb, :q) DESC LIMIT 100
)
SELECT COALESCE(kw.pid, sem.pid) AS pid,
       COALESCE(1.0 / (60 + kw.r), 0) + COALESCE(1.0 / (60 + sem.r), 0) AS rrf
FROM kw FULL OUTER JOIN sem ON kw.pid = sem.pid
ORDER BY rrf DESC LIMIT 10;

Quality lands within 0.01 of OpenSearch's native hybrid query. With the small open embedding model we used, hybrid adds little over keyword search on either system, so read this as a syntax example rather than a quality claim. The PhoenixAI query takes 23 ms (keyword part 7 ms, vector part 10 ms); OpenSearch's native hybrid query takes 7 ms.

The trade

The left column is what a second system would take away. The right column is the price: raw speed, at a few hundred queries per second per node and 13 to 15 ms a query.

When to keep retrieval in PhoenixAI

Keep retrieval in PhoenixAI when the request carries business conditions. Three cases fit it well:

• Retrieval with conditions. Stock, price, tenant and region go in the WHERE clause, and a join to orders or accounts goes in the same statement. The vector index stays in use, and the application no longer stitches two result sets together.
• Data that keeps changing. New products, price changes and deletions are visible to the next query. There is no sync pipeline to run and no refresh interval to wait out.
• Data that already lives in PhoenixAI or your lakehouse. Add a vector index to an existing table with ALTER TABLE, built in minutes, and generate embeddings with embedding() in SQL. No second copy of the data, and the same access rules.

If your business data lives in Postgres and fits on one machine, pgvector applies the same idea there.

Where a search engine still wins

A few limits are worth knowing before you design around 26.2:

What this means for agent builders

Go back to the shopping assistant. Its request, waterproof trail running shoes that are in stock, under $100 and ship to California, is one SQL statement in PhoenixAI 26.2, and it reads the price and the stock as they are right now. That statement matches the recall of a dedicated search engine. What it gives up is raw speed: a few hundred queries per second per node instead of about a thousand.

For an agent whose every retrieval step carries business conditions, that is the right trade. One system, one copy of the data, one place for access rules, and nothing to keep in sync.

To try it, add a vector index to a table you already query, then move the conditions you already have into the WHERE clause:

ALTER TABLE products ADD INDEX idx_desc_emb (desc_emb) USING VECTOR (
  "index_type" = "hnsw", "metric_type" = "cosine_similarity",
  "is_vector_normed" = "false", "dim" = "768",
  "M" = "16", "efconstruction" = "128");

We build Context Base, our context layer for agents, on the same tables and index. Next we run this suite at larger scale on multi-node clusters.