TiDB Vector Search: Building AI Applications with TiDB's Vector Capabilities

Why TiDB Vector Search Is a Game-Changer in 2026

Traditional AI application architecture has a pain point: you need a relational database for business data and a vector database for embeddings. Two databases mean data synchronization, consistency maintenance, and doubled operational costs.

TiDB 8.0+ vector search changes everything — one database supporting both SQL and vector search. You can combine relational filtering and semantic retrieval in a single query. That's the power of HTAP + Vector.

Feature	TiDB Vector	Milvus	Pinecone	Weaviate
SQL Support	Native	None	None	GraphQL
Vector Search	Native	Native	Native	Native
Transactions	ACID	Limited	None	Limited
HTAP	Yes	No	No	No
Deployment	Self-host/Cloud	Self-host/Cloud	Cloud only	Self-host/Cloud
Hybrid Query	SQL+Vector	Scalar filter	Metadata filter	GraphQL filter
Horizontal Scale	Auto	Manual	Auto	Manual
Max Dimensions	16384	32768	2048	65535
Index Type	HNSW	IVF/HNSW	Auto	HNSW
Open Source	Yes	Yes	No	Yes

---

TiDB 8.0+ Vector Index Setup

Installation and Connection

pip install tidb-vector sqlalchemy pymysql

Creating Tables with Vector Columns

from sqlalchemy import create_engine, Column, Integer, String, Text
from sqlalchemy.orm import declarative_base, Session
from tidb_vector.sqlalchemy import VectorType

Base = declarative_base()

class Document(Base):
    __tablename__ = "documents"

    id = Column(Integer, primary_key=True)
    content = Column(Text)
    category = Column(String(50))
    source = Column(String(100))
    embedding = Column(VectorType(1536))

engine = create_engine(
    "mysql+pymysql://root:@localhost:4000/testdb",
    echo=True
)
Base.metadata.create_all(engine)

Creating Vector Indexes

CREATE VECTOR INDEX idx_doc_embedding ON documents(embedding)
WITH METRIC = 'cosine', DIMENSION = 1536;

SELECT id, content, category,
       vec_cosine_distance(embedding, @query_vector) AS distance
FROM documents
WHERE category = 'tech'
ORDER BY distance
LIMIT 10;

---

Complete Python Implementation: Insert, Search, Hybrid Query

Inserting Vector Data

import openai
from sqlalchemy.orm import Session

client = openai.OpenAI(api_key="sk-xxx")

def get_embedding(text: str) -> list[float]:
    response = client.embeddings.create(
        model="text-embedding-3-small",
        input=text
    )
    return response.data[0].embedding

def insert_documents(session: Session, documents: list[dict]):
    for doc in documents:
        embedding = get_embedding(doc["content"])
        document = Document(
            content=doc["content"],
            category=doc["category"],
            source=doc["source"],
            embedding=embedding
        )
        session.add(document)
    session.commit()

with Session(engine) as session:
    docs = [
        {"content": "TiDB is a distributed HTAP database", "category": "database", "source": "docs"},
        {"content": "Vector search enables semantic similarity retrieval", "category": "ai", "source": "docs"},
        {"content": "Kubernetes is a container orchestration platform", "category": "devops", "source": "docs"},
        {"content": "RAG combines retrieval and generation for better LLM answers", "category": "ai", "source": "blog"},
    ]
    insert_documents(session, docs)

Vector Search

from tidb_vector.sqlalchemy import vec_cosine_distance

def vector_search(session: Session, query: str, top_k: int = 10):
    query_embedding = get_embedding(query)

    results = session.query(
        Document.id,
        Document.content,
        Document.category,
        Document.source,
        vec_cosine_distance(Document.embedding, query_embedding).label("distance")
    ).order_by("distance").limit(top_k).all()

    return results

Hybrid Query: SQL + Vector

This is TiDB vector search's most powerful feature — combining relational filtering and semantic retrieval in one query:

def hybrid_search(
    session: Session,
    query: str,
    category: str = None,
    source: str = None,
    top_k: int = 10
):
    query_embedding = get_embedding(query)

    q = session.query(
        Document.id,
        Document.content,
        Document.category,
        Document.source,
        vec_cosine_distance(Document.embedding, query_embedding).label("distance")
    )

    if category:
        q = q.filter(Document.category == category)
    if source:
        q = q.filter(Document.source == source)

    results = q.order_by("distance").limit(top_k).all()
    return results

---

RAG Application with TiDB

from openai import OpenAI

class TiDBRAG:
    def __init__(self, engine, openai_api_key: str):
        self.engine = engine
        self.llm_client = OpenAI(api_key=openai_api_key)

    def retrieve(self, query: str, top_k: int = 5) -> list[dict]:
        with Session(self.engine) as session:
            results = vector_search(session, query, top_k)
            return [
                {"content": r.content, "category": r.category, "distance": r.distance}
                for r in results
            ]

    def generate(self, query: str, context: list[dict]) -> str:
        context_text = "\n".join([f"[{c['category']}] {c['content']}" for c in context])
        prompt = f"""Answer based on the following references. If not found, say so.

References:
{context_text}

Question: {query}

Answer:"""

        response = self.llm_client.chat.completions.create(
            model="gpt-4o",
            messages=[{"role": "user", "content": prompt}],
            temperature=0.1
        )
        return response.choices[0].message.content

    def query(self, question: str, top_k: int = 5) -> str:
        context = self.retrieve(question, top_k)
        answer = self.generate(question, context)
        return answer

---

Comparison with Dedicated Vector DBs

Dimension	TiDB Vector	Milvus	Pinecone
Data Model	Relational+Vector	Pure Vector	Pure Vector
Query Language	SQL	SDK/REST	SDK/REST
Transactions	ACID	Eventual	None
Ops Complexity	Medium	High	Low
Best For	Business+AI hybrid	Pure vector search	Managed vector search

---

5 Common Pitfalls

1. Vector Dimension Mismatch

# Table: VectorType(1536) → OpenAI ada-002
# Using text-embedding-3-large → 3072 ✗ Will error

class DocumentLarge(Base):
    __tablename__ = "documents_large"
    id = Column(Integer, primary_key=True)
    content = Column(Text)
    embedding = Column(VectorType(3072))

2. Missing Vector Index

CREATE VECTOR INDEX idx_embedding ON documents(embedding)
WITH METRIC = 'cosine', DIMENSION = 1536;

3. Unoptimized Batch Insert

session.bulk_save_objects(documents)
session.commit()

4. Inconsistent Distance Metric

-- Index uses cosine, query must also use cosine
SELECT *, vec_cosine_distance(embedding, @query) AS dist
FROM documents ORDER BY dist LIMIT 10;

5. Missing Connection Pool

engine = create_engine(
    "mysql+pymysql://root:@localhost:4000/testdb",
    pool_size=20,
    max_overflow=10,
    pool_timeout=30,
    pool_recycle=3600,
    pool_pre_ping=True
)

---

10 Error Troubleshooting

#	Symptom	Possible Cause	Resolution
1	Dimension mismatch error	Embedding dim ≠ table def	Check model output and VectorType
2	Empty search results	No vector index	SHOW INDEX FROM documents
3	Extremely slow search	Missing HNSW index	Create vector index
4	Connection timeout	Pool exhausted	Adjust pool_size/max_overflow
5	Abnormal distance	Metric mismatch	Use same metric for index and query
6	OOM error	Batch too large	Insert in batches of 1000-5000
7	Low precision	ef_search too small	Increase ef_search
8	Insert failure	NaN/Inf in vector	Validate embeddings before insert
9	Slow hybrid query	No B-tree index on filter cols	Create B-tree indexes
10	Wrong sort order	Distance vs similarity confusion	Smaller cosine distance = more similar

---

Performance Tuning

CREATE VECTOR INDEX idx_doc_embedding ON documents(embedding)
WITH METRIC = 'cosine', DIMENSION = 1536,
     EF_CONSTRUCTION = 256,
     M = 16;

SET SESSION tidb_vector_ef_search = 128;

Parameter	Default	Range	Description
EF_CONSTRUCTION	64	64-256	Build precision
M	16	12-48	Graph connectivity
ef_search	64	64-512	Query precision

---

Recommended Tools

• JSON Formatter: Use /en/json/format to format JSON configurations
• Base64 Encoder: Use /en/encode/base64 for encoding/decoding embeddings
• Hash Calculator: Use /en/encode/hash for data integrity verification

---

Summary: TiDB vector search unifies SQL and vector retrieval in one database, making it one of the best choices for building AI applications in 2026. It eliminates the data synchronization pain between relational and vector databases, supporting ACID transactions + semantic retrieval hybrid queries. Key practices: ensure dimension matching, create HNSW indexes, use batch inserts, maintain metric consistency, and configure connection pools. For RAG applications, TiDB's hybrid query capability lets you combine semantic retrieval with precise relational filtering — something dedicated vector databases struggle to achieve elegantly.