# Vector and Full-Text Search Explained: Why AI Agents Need Both

> Source: <https://www.pingcap.com/blog/vector-and-full-text-search-for-ai-agents/>
> Published: 2026-09-30 00:03:48+00:00

## **Key Takeaways**

- Hybrid retrieval catches the identifiers embeddings miss and the paraphrased questions keyword search misses.
- Reciprocal rank fusion merges keyword and semantic results by rank, without reconciling incompatible scores.
- Agent retrieval depends on tenant filters, permissions, and fresh transactional data as much as on document similarity.
- Unifying SQL and vector data removes index sync jobs; Dify.AI cut infrastructure costs 80% by consolidating on TiDB Cloud.

Ask an AI support agent about error E-4012 and it may come back with a confident answer about E-4021. The embeddings for the two codes sit almost on top of each other, so the agent pulled the wrong runbook, and nothing in the logs says retrieval failed.

Vector and full-text search are two different ways to retrieve information. Full-text search matches the literal terms in a query against an inverted index and ranks results with a scoring function such as BM25. Vector search compares embeddings, which are numeric representations of meaning, to find content that says something similar in different words.

Each one is weak where the other is strong. Hybrid retrieval runs both and merges the results, which gives AI agents better recall than embeddings alone. This guide explains how each method works, how rank fusion combines them, and what agent retrieval needs beyond search.

## **What Are Vector and Full-Text Search?**

Full-text search retrieves documents that contain the query’s terms. Vector search retrieves documents whose meaning is close to the query’s. Both return a ranked list, but they disagree about what counts as a match.

### **Full-Text Search Finds the Words You Actually Typed**

Full-text search stores tokens in an inverted index, a lookup table from each term to the documents that contain it. BM25, the most common scoring function, rewards a term that appears often in a document, discounts terms that show up in nearly every document, and adjusts for length so a 10,000-word page doesn’t win just by being long.

Keyword matching wins when the query is an identifier. A developer pasting ERR_CONN_REFUSED or a buyer searching for SKU TX-4471-B wants documents containing that string, and BM25 ranks them at the top.

### **Vector Search Finds Meaning Beyond Exact Wording**

Vector search runs each document chunk through an embedding model, which turns text into a list of several hundred to a few thousand numbers. Texts with similar meaning land near each other. The system embeds the query the same way, and the database returns the nearest vectors by cosine or Euclidean distance. An approximate nearest neighbor index such as HNSW keeps this fast at scale.

Semantic similarity wins when the user and the document use different words. “I got charged twice this month” shares no useful terms with a help article titled “Refunds for duplicate transactions,” yet an embedding model places them close together.

So the two methods answer different questions. Full-text search asks which documents contain these terms. Vector search asks which documents mean something like this.

## **Why Vector and Full-Text Search Work Better Together**

Vector and full-text search work better together because each one catches the queries the other misses. Keyword search handles identifiers, embeddings handle paraphrased intent, and merging them gives an agent a candidate set that covers both. That matters because retrieval failures rarely look like errors. The system returns something plausible, the model writes a fluent answer on top, and nobody notices until a customer does.

### **Where Embeddings Fall Short**

Embedding models are trained to capture meaning, and identifiers carry very little of it. Codes like E-4012 and E-4021, or part numbers that differ by one character, often produce nearly identical vectors. Policy language has the same problem. A “30-day refund window” and a “90-day refund window” are semantically close and contractually very different.

Internal product names cause a related failure. If the embedding model never saw a term in training, it maps it to whatever it happens to resemble.

### **Where Keyword Matching Falls Short**

BM25 only knows the words it sees. A user who types “how do I stop being billed” will miss the article titled “Cancel your subscription” unless someone maintains a synonym list by hand. Conversational queries, which is how people talk to agents, also dilute keyword scores with filler like “I was wondering if.”

### **Why Hybrid Retrieval Is the Real Answer**

Hybrid search runs a keyword query and a vector query against the same corpus, then merges the two ranked lists. A document that contains the error code and also describes the right symptoms rises to the top. A document that matches only one signal still makes the candidate set, so neither blind spot is fatal.

The case is stronger for agents than for people. A person scanning results can spot a bad one and rephrase. An agent may issue dozens of retrieval calls in one task, mixing identifier lookups with open questions, and rarely knows when a call came back wrong. PingCAP’s walkthrough of [vector search and hybrid search](https://docs.pingcap.com/ai/vector-search-hybrid-search/) shows one working implementation.

## **What AI Agent Retrieval Really Needs Beyond Embeddings**

AI agent retrieval needs semantic recall, keyword matching, and relational filtering, often in the same request. Agents also depend on permissions and on operational data that changed seconds ago, which document similarity alone can’t provide.

### **Retrieval for Agents Is More Than Document Similarity**

A RAG chatbot answers questions from a document set. An agent does work. Before replying to a ticket, a support agent may need help articles, the customer’s plan, their recent tickets, and the output of a diagnostic tool it ran two steps earlier. Only the first is a similarity search. The rest is structured state in tables.

Internal copilots look similar. An engineer asking why the checkout service paged the on-call team last night needs incident notes (semantic), the exact alert name (keyword), and the deploys that shipped in that window (a SQL query on timestamps).

### **Metadata Filters and SQL Constraints Change the Result Set**

Filters decide which documents are eligible at all. In a multi-tenant SaaS product, every retrieval call needs a tenant condition, and product version or region often matter too.

Where the filter runs changes the answer. Pull the top 10 vectors and then drop the ones from other tenants, and you might be left with two results, or none. The usual fixes are to over-fetch a larger candidate pool before filtering, or to filter first when the condition is selective enough that an exact search over the remaining rows is cheap.

Permissions are the strict version of this. An internal copilot that retrieves a document the user isn’t allowed to read has leaked it the moment that text lands in the prompt. Access control belongs inside the query, before anything reaches the model.

### **Fresh Operational Data Matters as Much as Semantic Recall**

Agents act on the current state of the business. If an order shipped 30 seconds ago but your search index syncs from the primary database overnight, the agent will tell the customer it hasn’t shipped.

Transaction-aware workflows raise the stakes. A refund agent has to confirm a refund hasn’t already gone out before issuing one, which requires a consistent read of current data. A stale copy in a separate index can turn into a duplicate payout. This is why picking the [best vector database for RAG and LLM applications](https://www.pingcap.com/ai/) is really a decision about your whole data layer.

## **How Hybrid Search Works in a SQL Retrieval Stack**

Hybrid search runs a keyword query and a vector query side by side, filters both candidate sets, and merges them into one ranking. In a SQL stack, each of those steps is a query against the same tables. The flow usually looks like this:

1. Run a full-text query and keep the top candidates by BM25 score, often somewhere between 20 and 100.
2. Run a vector query and keep the top candidates by distance.
3. Apply tenant, permission, and metadata filters so every candidate is eligible.
4. Fuse the two lists into one ranking.
5. Optionally rerank the top results with a model that reads the query and each document together.

### **BM25 and Vector Similarity Play Different Roles**

The two scores live on different scales. A BM25 score has no fixed ceiling and depends on the corpus, while cosine distance falls between 0 and 2. Adding them produces rankings that shift as the corpus grows, which is the problem fusion solves.

### **Fusion and Reranking Improve Final Relevance**

Reciprocal rank fusion (RRF) ignores raw scores and looks only at positions. Each document earns 1 / (k + rank) from every list it appears in, and those values are summed. Most implementations set the constant k to 60. A document ranked second for keywords and fifth for vectors beats one ranked first in a single list, because agreement between signals counts for more than one strong signal.

RRF needs no training data or score normalization, so it’s the usual default. Weighted fusion makes sense once evaluation data shows one signal deserves more influence. A cross-encoder reranker can then rescore the top 20 or so results, at the cost of extra latency per call.

### **SQL Keeps Joins and Filters in the Same Retrieval Path**

When text, vectors, and metadata share a table, each candidate query is ordinary SQL. In [TiDB](https://www.pingcap.com/what-is-tidb/), the two candidate queries for a support agent might look like this. The first vector query over-fetches and then filters, so it can use the HNSW index. That works when the tenant holds a large share of the data. For a small tenant, the top 300 overall may include few of its rows, so the second version filters by tenant first and runs an exact distance search on just those rows. The vector literal is truncated for readability, so treat this as illustrative rather than copy-paste ready:

— Keyword candidates, filtered to one tenant and product version

SELECT id, title FROM kb_articles

WHERE tenant_id = 42 AND product_version = ‘7.2’

AND fts_match_word(‘E-4012’, body)

ORDER BY fts_match_word(‘E-4012’, body) DESC

LIMIT 50;

— Semantic candidates: nearest neighbors first, then filter

SELECT id, title FROM (

SELECT id, title, tenant_id, product_version,

VEC_COSINE_DISTANCE(embedding, ‘[0.012, -0.044, …]’) AS d

FROM kb_articles

ORDER BY VEC_COSINE_DISTANCE(embedding, ‘[0.012, -0.044, …]’)

LIMIT 300

) t

WHERE tenant_id = 42 AND product_version = ‘7.2’

ORDER BY d LIMIT 50;

— Small tenant: filter first, then exact distance search

SELECT id, title FROM kb_articles

WHERE tenant_id = 42 AND product_version = ‘7.2’

ORDER BY VEC_COSINE_DISTANCE(embedding, ‘[0.012, -0.044, …]’)

LIMIT 50;

Note that fts_match_word matches tokens rather than exact strings. The parser can split E-4012 into parts, so a document mentioning E-4021 may still match partially. The correct document should rank higher, but this isn’t exact phrase matching.

Your application can fuse the two result sets with RRF. Alternatively, PyTiDB runs both searches and fuses the results in a single hybrid search call. Either way, joining the winners to an accounts table is one more SQL statement. TiDB’s [native full-text search features](https://docs.pingcap.com/ai/vector-search-full-text-search-sql/) cover the indexing side in more detail.

| **Retrieval signal** | **Best at** | **Common failure mode** | **Why AI agents need it** | 
| BM25 full-text | Error codes, SKUs, names, rare keywords | Misses paraphrases and synonyms | Agents often receive identifiers pasted straight from logs or tickets | 
| Vector similarity | Paraphrased, conversational questions | Confuses near-identical identifiers and numbers | Users describe problems in their own words | 
| Metadata filters | Tenant, version, region, status scoping | Filtering after retrieval leaves too few results | Keeps answers inside the right customer and product context | 
| Permissions in the query | Enforcing who can see what | Leaks content when checked after retrieval | Anything in the prompt can end up in the answer | 
| Transactional reads | Current order, account, and ticket state | Stale copies in a separately synced index | Agents take actions that depend on what is true right now | 

## **What Teams Get Wrong About Vector and Full-Text Search**

The most expensive assumption is that embeddings are enough. They often look enough in a demo, where test questions are phrased like the documents. Production traffic is messier. It is full of pasted stack traces, order numbers, and internal shorthand.

The second mistake is architectural. Teams put text in a search engine, vectors in a vector store, and metadata in the primary database, then connect them with sync jobs and no clear consistency model. A customer deletes a document and it stays retrievable in one index for hours. A permission change reaches the database but not the vector store. Each copy is individually fine. Together they disagree.

Chunking causes subtler trouble. Splitting a document at fixed token counts can separate an error code from the paragraph explaining it, so neither chunk ranks well on its own.

### **Checklist: Signs Your Retrieval Stack Is Missing a Search Signal**

- A query for an exact SKU, error code, or ticket ID doesn’t return the matching document in the top three.
- Tenant and permission filters run in application code after results come back.
- Nobody can say how long it takes for a changed row to show up in search results.
- Deleting a document means updating more than one system.
- You can’t explain why a result ranked first using both keyword and semantic signals.

## **Where TiDB Fits in the Hybrid Retrieval Stack**

TiDB fits as the database that holds the text, the embeddings, and the transactional records an agent needs, and queries all three with SQL. It works as a unified data foundation for retrieval rather than a vector-only store.

### **Why Unified Retrieval Matters for Production AI Apps**

A common first-generation RAG stack runs a search engine for text, a vector store for embeddings, and an OLTP database for customer data. Each has its own indexing lag and permission model, and the glue code between them tends to be the least tested part of the application.

A unified retrieval layer keeps the text, the embeddings, and the business records in the same tables. One query language covers all of it, and one consistency model decides what the agent sees.

### **TiDB Connects Search, Transactions, and Fresh Application Data**

TiDB is a distributed SQL database that scales horizontally and keeps ACID transactions strongly consistent across nodes. It is MySQL compatible, which matters here because existing MySQL drivers, ORMs, and team skills carry over. A team already on MySQL keeps its drivers, ORMs, and SQL skills. Retrieval comes from a small set of TiDB functions, such as fts_match_word for BM25-ranked keyword search and VEC_COSINE_DISTANCE for semantic search, that work inside the queries your team already writes. Our overview of [vector search](https://docs.pingcap.com/ai/vector-search-overview/) goes deeper on that path.

Retrieval runs next to the transactional data. TiDB has a native VECTOR data type, and TiFlash replicas serve HNSW indexes for approximate nearest neighbor search. Full-text search ranks with BM25 and uses a multilingual parser, so content in different languages can share a table. The pytidb Python SDK wraps both in a hybrid search API with RRF or weighted fusion. Vector search is available on TiDB Cloud Starter and TiDB Self-Managed. Full-text search is currently available only on TiDB Cloud Starter in supported AWS regions.

Production teams already run this consolidation pattern. [Dify.AI consolidated nearly half a million database containers into one TiDB Cloud system](https://www.pingcap.com/case-study/dify-consolidates-massive-database-containers-into-one-unified-system-with-tidb/), storing content and vector embeddings in the same tables for its RAG features, and reports an 80% cut in infrastructure costs. [APTSell runs its AI sales copilot on TiDB Cloud Starter](https://www.pingcap.com/case-study/aptsell-tidb-powering-sustained-growth-by-an-ai-sales-copilo/), combining transactional data, knowledge graph traversal, and semantic vector search in a single data layer. The same approach shows up in our playbook on [RAG with embedded vector database](https://www.pingcap.com/playbook/embed-vector-db-build-rag/).

## **How TiDB Supports AI Retrieval Without More Data Sprawl**

Every system you add to a retrieval stack brings another sync job, another access model, and another place for data to drift. Putting SQL, vector search, and full-text search in one database shrinks that surface. Deletes take effect everywhere at once because there is no separate sync pipeline to manage. The same query that ranks results also enforces permissions. Agents read the rows your application just wrote.

A unified database isn’t the right call for every workload. A search-only product with billions of vectors and no transactional data may be better served by a dedicated engine, and our guide to the [best vector databases for RAG](https://www.pingcap.com/compare/best-vector-database/) walks through those tradeoffs. For AI applications where retrieval has to respect live business data, one engine is simpler to operate and easier to trust.

Evaluating the best vector database for RAG and LLM applications? [See how TiDB handles vector search, full-text search, and SQL in one engine](https://www.pingcap.com/ai/), then run your first hybrid query on [TiDB Cloud Starter](https://tidbcloud.com/free-trial/), choosing a supported AWS region when you create it.”

## **Vector and Full-Text Search FAQs**

### **Is Vector Search Better Than Full-Text Search?**

- Neither wins overall. Each solves a different retrieval problem.
- Full-text search wins on identifiers, error codes, and rare keywords.
- Vector search wins on paraphrased and conversational questions.
- Hybrid search combines both and is usually the safer production default.

### **When Should AI Agents Use Hybrid Search?**

- When queries mix identifiers with open-ended intent, like “why does error E-4012 keep appearing after the update?”
- When the corpus is full of product names, SKUs, or internal jargon the embedding model may not know.
- When one missed document can trigger a wrong action rather than a slightly weaker answer.

### **What Is BM25 in Full-Text Search?**

- A ranking function that scores how well a document matches the query terms.
- It weighs term frequency, how rare each term is across the corpus, and document length.
- It needs no training and remains a strong baseline for keyword relevance.

### **What Is Reciprocal Rank Fusion?**

- A way to merge ranked lists using positions instead of raw scores.
- Each document earns 1 / (k + rank) per list, summed across lists. k is often 60.
- Teams use it because BM25 scores and vector distances aren’t on comparable scales.

### **Do You Need SQL for AI Agent Retrieval?**

- Yes, when results depend on tenant, permission, region, or date filters.
- Joins let an agent pull account, order, or ticket context alongside retrieved documents.
- A transactional database gives agents current data instead of a lagging copy.
- Static document Q&A with no user-specific context can work without it.

Experience modern data infrastructure firsthand.

## TiDB Cloud Dedicated

A fully-managed cloud DBaaS for predictable workloads

## TiDB Cloud Starter

A fully-managed cloud DBaaS for auto-scaling workloads