TiDB Vector Search in Practice (2026): VECTOR Index, Hybrid Retrieval & RAG Pipeline

Why you need to read this

In the LLM era, vector search has graduated from an optional experiment to essential infrastructure. But maintaining a separate vector database (Milvus, Pinecone, Weaviate) alongside your business database creates three pains:

• Dual writes: every business record insertion must write to both databases—two-phase commits and try/catch can still leave inconsistencies.
• Sync lag: after a business data update, the vector-side index rebuild creates a window of stale retrieval results.
• Doubled ops: two systems mean two deployments, two monitoring stacks, two backup schedules, two capacity plans.

Starting from version 7.5, TiDB natively integrates vector capabilities into its distributed SQL database. You no longer need to synchronize two systems—inside TiDB you get OLTP, OLAP, and vector search in one place.

This article covers: the VECTOR type, HNSW/IVF index internals and selection, hybrid query patterns, a complete RAG pipeline, and performance tuning.

---

Core concepts

The VECTOR data type

TiDB introduces VECTOR(n), where n is the dimension (max 16384). Vector values use JSON array literals.

CREATE TABLE products (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(256),
  description TEXT,
  embedding VECTOR(1536),           -- matches OpenAI text-embedding-3-small default dim
  category VARCHAR(64),
  price DECIMAL(10,2),
  created_at DATETIME DEFAULT NOW()
);

Inserting a vector is just like any other column:

INSERT INTO products (name, description, embedding, category, price)
VALUES (
  'Wireless Noise-Cancelling Headphones',
  '40mm drivers, active noise cancellation, 30-hour battery',
  '[0.0123, -0.0456, 0.0789, ...]',     -- 1536 float32 values
  'Electronics',
  299.00
);

Vector distance functions

TiDB ships four distance functions:

Function	Meaning	Best for
VEC_COSINE_DISTANCE(a, b)	Cosine distance (1 - cosine similarity)	Semantic text retrieval (most common)
VEC_L2_DISTANCE(a, b)	Euclidean distance (squared)	Image/geometric features
VEC_INNER_PRODUCT(a, b)	Negative inner product (-<a,b>)	Cosine equivalent when normalized
VEC_L1_DISTANCE(a, b)	Manhattan distance	Sparse vectors / niche use

All distance functions work in ORDER BY and WHERE clauses, treated identically to ordinary columns.

---

Vector indexes: HNSW and IVF

Without an index, every similarity search is a full-table scan with distance computation—unacceptable latency at millions of rows. TiDB provides two approximate nearest neighbor (ANN) index types.

HNSW (Hierarchical Navigable Small World)

HNSW is the industry gold standard for low-latency ANN. Its core idea is a multi-layer "skip-list graph":

• Bottom layer (Layer 0): contains every vector, edges are dense, drives high recall.
• Upper layers: nodes are randomly promoted with decreasing probability, edges are sparse, enabling fast long-range jumps.
• Query : starts at the topmost layer, greedily descends—each layer uses local neighbor edges to quickly approach the true nearest neighbors.

HNSW characteristics:

• Fast queries: typically 1–3 ms for top-10 ANN at millions of vectors.
• High memory: stores graph structures (~50–100 edges per node), additional memory is 2–3× the raw vector data.
• Slow build: inserts nodes one at a time, generating neighbor edges—time complexity O(N·logN·M), where M is max neighbors per layer.
• Best for high-QPS online services.

ALTER TABLE products
ADD VECTOR INDEX idx_embedding (embedding)
WITH (
  distance = 'cosine',
  type = 'hnsw',
  m = 16,                -- max neighbors per layer (default 16)
  ef_construction = 64   -- search width during build (default 64)
);

IVF (Inverted File)

IVF uses the clustering idea: partition all vectors into k clusters via k-means. At query time, find the nearest clusters first, then compute exact distances only within those candidate clusters.

• Fast build: k-means clustering + assignment, far less work than HNSW.
• Low memory: only stores cluster centroids (tens of KB) + inverted lists (ID arrays), essentially zero extra memory.
• Slower queries: must scan multiple clusters, latency typically 2–5× higher than HNSW.
• Best for write-heavy, memory-constrained, or prototype scenarios.

ALTER TABLE products
ADD VECTOR INDEX idx_embedding (embedding)
WITH (
  distance = 'cosine',
  type = 'ivf',
  nlist = 256            -- number of clusters (default 128)
);

HNSW vs IVF decision matrix

Dimension	HNSW	IVF
Query latency (p50)	1–3 ms	5–15 ms
Build time	Slow (multiples of IVF)	Fast
Extra memory	2–3× vector data	Minimal (centroids + lists)
Recall (top-10)	95–99%	90–97% (nlist-dependent)
Incremental inserts	Supported, graph may degrade	Supported, distribution may drift
Recommended for	Online, low-latency, high QPS	Batch writes, tight memory, prototypes

---

Hybrid retrieval: TiDB's killer feature

Most RAG scenarios aren't just "find 5 document chunks similar to the query." Real-world needs are:

• "Find the 5 most similar articles from the last 7 days in the 'Backend' category."
• "Find the 10 most similar products priced between $100–$500 with positive stock."

Here you need precise SQL filtering + vector similarity ranking in a single operation—and TiDB's optimizer schedules both in one SQL statement.

Hybrid query example

SELECT id, name, description, price,
       VEC_COSINE_DISTANCE(embedding, :query_embedding) AS dist
FROM products
WHERE category = 'Electronics'
  AND price BETWEEN 100 AND 500
  AND status = 'active'
ORDER BY dist
LIMIT 10;

Execution plan:

1. The optimizer recognizes the VECTOR INDEX and uses it for ANN recall (producing a candidate set, sized ~LIMIT * ef_search).
2. The WHERE clause filters the candidate set—discarding non-matching rows.
3. The surviving rows are sorted by dist and the top 10 are returned.

The key insight: filtering happens after vector recall but before exact distance sorting. The database doesn't compute distances on the entire table first—it uses the index for a fast semantic prune, then applies precise filters, then sorts. This bring latency from seconds down to milliseconds at millions of rows.

Semantic reranking

For scenarios demanding higher precision, add a reranking step after the ANN round:

-- Step 1: ANN recall + business filter (fast)
SELECT id, name, embedding
FROM products
WHERE category = 'Electronics'
ORDER BY VEC_COSINE_DISTANCE(embedding, :query_embedding)
LIMIT 50;

-- Step 2: In application layer, rerank the 50 candidates with a
-- cross-encoder model, or do an exact distance sort in TiDB
-- (now safe since candidates are down to 50)

---

Building a RAG pipeline with TiDB

Step 1: Chunking and embedding

from openai import OpenAI
import tiktoken

client = OpenAI()

def chunk_text(text: str, max_tokens: int = 512) -> list[str]:
    enc = tiktoken.get_encoding("cl100k_base")
    paragraphs = [p.strip() for p in text.split("\n\n") if p.strip()]
    chunks, current, current_tokens = [], "", 0
    for p in paragraphs:
        tokens = len(enc.encode(p))
        if current_tokens + tokens > max_tokens:
            chunks.append(current)
            current, current_tokens = p, tokens
        else:
            current += "\n\n" + p
            current_tokens += tokens
    if current:
        chunks.append(current)
    return chunks

def embed(texts: list[str], model="text-embedding-3-small") -> list[list[float]]:
    resp = client.embeddings.create(input=texts, model=model)
    return [r.embedding for r in resp.data]

Step 2: Store in TiDB

import pymysql

conn = pymysql.connect(host="127.0.0.1", user="root", database="rag_db")

for i, (chunk, emb) in enumerate(zip(chunks, embeddings)):
    emb_str = "[" + ",".join(f"{v:.6f}" for v in emb) + "]"
    conn.cursor().execute(
        "INSERT INTO documents (id, content, embedding, category) VALUES (%s, %s, %s, %s)",
        (i, chunk, emb_str, "tech_blog")
    )
conn.commit()

Step 3: Build the index

ALTER TABLE documents
ADD VECTOR INDEX idx_doc_embedding (embedding)
WITH (distance = 'cosine', type = 'hnsw', m = 16, ef_construction = 64);

For bulk imports: load data first, then build the index—much faster than row-by-row online building.

Step 4: RAG retrieval

def rag_query(question: str, category: str | None = None, top_k: int = 5) -> str:
    q_emb = embed([question])[0]
    q_emb_str = "[" + ",".join(f"{v:.6f}" for v in q_emb) + "]"

    if category:
        rows = conn.cursor().execute(
            """SELECT content, VEC_COSINE_DISTANCE(embedding, %s) AS dist
               FROM documents WHERE category = %s ORDER BY dist LIMIT %s""",
            (q_emb_str, category, top_k)
        ).fetchall()
    else:
        rows = conn.cursor().execute(
            """SELECT content, VEC_COSINE_DISTANCE(embedding, %s) AS dist
               FROM documents ORDER BY dist LIMIT %s""",
            (q_emb_str, top_k)
        ).fetchall()

    return "\n\n---\n\n".join(r[0] for r in rows)

Step 5: End-to-end query

context = rag_query("How to choose a vector index in TiDB?", category="tech_blog")
response = client.chat.completions.create(
    model="gpt-4o",
    messages=[
        {"role": "system", "content": f"Answer using the following:\n\n{context}"},
        {"role": "user", "content": "How to choose a vector index in TiDB?"}
    ]
)
print(response.choices[0].message.content)

From document ingestion to RAG Q&A—no separate vector database needed. TiDB handles it all.

---

Performance and tuning

1. Dimension and storage

• VECTOR(1536) is 6 KB per row (1536 × 4 bytes float32). 1 million rows = 6 GB raw vectors; with HNSW index ~15–20 GB.
• If your tolerance for precision is moderate, use Matryoshka embeddings or PCA to truncate to 256–512 dimensions—cut storage and index memory by more than half.

2. Bulk import

• Use LOAD DATA or batch INSERT (500–1000 rows per batch), disable autocommit, commit per transaction.
• Load data first, create the vector index after—raise tidb_ddl_reorg_batch_size to accelerate index build.

3. Query tuning

• LIMIT and index recall: the vector index internal search width scales with LIMIT. A larger LIMIT triggers a broader ANN search. The internal search count is roughly 3–5× LIMIT.
• Covering indexes: if the query touches few columns, a covering index can eliminate the table-lookup after the vector index scan.
• Partition pruning: if your data is naturally partitioned by time or category, vector index + partition pruning dramatically shrinks the search space.

4. Recall-vs-latency tradeoffs

• Larger m and ef_construction improve index quality and recall, but take longer and use more memory.
• ef_search (search width) can be tuned at query time—higher values boost recall but increase latency.

SET SESSION tidb_vector_index_ef_search = 40;   -- default 20
SELECT ... ORDER BY VEC_COSINE_DISTANCE(embedding, '[...]') LIMIT 10;

---

Comparison with other vector databases

Dimension	TiDB	Milvus	Pinecone	pgvector
Hybrid query (vector+SQL)	✅ Native single SQL	✅ Partial	❌ (app-layer)	✅ Native
ACID transactions	✅ Distributed	❌	❌	✅ Single-node
Horizontal scaling	✅ Native	✅	✅ (SaaS)	❌
SQL compatibility	✅ MySQL-compatible	❌	❌	✅ PostgreSQL
Deployment complexity	Medium (distributed)	Med-High	Low (SaaS)	Low (PG ext)
Vector search latency (1M)	2–15 ms	2–10 ms	5–20 ms (network)	3–10 ms

TiDB's unique advantage: you never have to choose between transactional consistency and vector search capability. Same cluster, same SQL, same transaction—that's the real value of native integration.

---

FAQ

Q1: How does TiDB's vector index fundamentally differ from pgvector?

Both support SQL + vector hybrid queries. The key difference: TiDB is distributed by design—pgvector's index is single-node; when data volume and QPS outgrow a single machine you need a distributed shim (e.g., Citus + pgvector). TiDB scales out natively.

Q2: When should I use a dedicated vector database instead of TiDB?

If your use case is purely "store vectors, search vectors"—no SQL filtering, no transactions, no JOINs with business tables—a dedicated vector DB (Milvus, Qdrant) has deeper micro-optimizations for pure vector search. But the moment you need vector results to be part of a business view, or vector search + precise filters + transactional operations in one request, TiDB's advantage becomes clear.

Q3: How do vector indexes work in distributed TiDB?

Each TiKV node builds a local vector index for the Regions it hosts. At query time, TiDB pushes ANN searches down to each TiKV node for parallel execution, then aggregates and sorts at the TiDB layer—a natural benefit of TiDB's MPP (Massively Parallel Processing) architecture.

Q4: Is migrating from pgvector to TiDB complex?

Not complex. Both use VECTOR(n) + JSON array literal format. Export to CSV, then LOAD DATA into TiDB. The main adaptation is index creation syntax (TiDB's WITH clause uses slightly different parameter names).

Q5: Does HNSW degrade with heavy inserts in production?

Yes, but you can periodically ALTER TABLE ... REBUILD VECTOR INDEX to restore graph quality. If your workload is heavy write + realtime query, use IVF (more insert-friendly) or plan a periodic rebuild strategy.

---

Conclusion

TiDB's vector search isn't about beating dedicated vector databases on a benchmark. It's about eliminating the sync gap between your transactional and vector stores. When you need to "write an order, check stock, and search products" in one system, you no longer need to route vector search to a separate service—one SQL, one execution.

Recommended learning path:

1. Launch a local TiDB node with tiup playground;
2. Create a table with a VECTOR column + HNSW index;
3. Load 100k rows from a public dataset (e.g., Hugging Face sentence-transformers/all-MiniLM-L6-v2 Wikipedia snippet embeddings);
4. Test pure vector search, pure SQL filtering, and hybrid queries—measure performance and recall;
5. Run EXPLAIN ANALYZE to see how the optimizer fuses the vector index with SQL filtering.