Vector Search
PostgreSQL-compatible TiDB Cloud Starter provides pgvector-compatible vector storage and similarity search, including the VECTOR type, distance operators, HNSW indexes, and server-side embedding functions.
Enable vector search
The VECTOR type and vector operators are built in. To register the vector extension metadata, run:
CREATE EXTENSION IF NOT EXISTS vector;
To use server-side embedding functions, enable the embedding extension:
CREATE EXTENSION IF NOT EXISTS embedding;
Store vectors
Use VECTOR(n) to store fixed-dimension vectors.
For example:
CREATE TABLE documents (
id SERIAL PRIMARY KEY,
content TEXT NOT NULL,
embedding VECTOR(3)
);
You can insert vectors using vector literals:
INSERT INTO documents (content, embedding)
VALUES ('hello world', '[0.1, 0.2, 0.3]'::vector);
The number of dimensions in a vector must match the dimension declared by the target VECTOR(n) column.
Distance operators
PostgreSQL-compatible TiDB Cloud Starter supports the following pgvector-compatible distance operators:
For example, the following query performs a cosine-distance search:
SELECT
id,
content,
embedding <=> '[0.1, 0.2, 0.3]'::vector AS distance
FROM documents
ORDER BY distance
LIMIT 5;
The following utility functions are also available:
Create an HNSW index
HNSW (Hierarchical Navigable Small World) indexes accelerate approximate nearest-neighbor search.
Create an index whose operator class matches the distance metric used by your queries.
For cosine distance:
CREATE INDEX idx_documents_embedding
ON documents
USING hnsw (embedding vector_cosine_ops);
For L2 distance:
CREATE INDEX idx_documents_embedding_l2
ON documents
USING hnsw (embedding vector_l2_ops);
For inner product:
CREATE INDEX idx_documents_embedding_ip
ON documents
USING hnsw (embedding vector_ip_ops);
Configure HNSW index parameters
You can configure HNSW index build parameters when creating an index:
CREATE INDEX idx_documents_embedding
ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
You can configure the HNSW search expansion factor for the current session:
SET hnsw.ef_search = 100;
The default value of hnsw.ef_search is 40. A higher value can improve recall at the cost of additional query latency.
When an HNSW index is used
The optimizer can use an HNSW index when all of the following conditions are met:
- The query orders by a distance expression on the indexed vector column and a constant vector, or uses
vec_embed_cosine_distance()on that column with constant query text. - The query contains a
LIMITclause. - The HNSW index uses an operator class that matches the distance metric in the query.
- The HNSW index has finished building and is available for queries.
- The query does not contain a
WHEREclause.
For example:
SELECT id, content
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, 0.3]'::vector
LIMIT 10;
If these conditions are not met, the query falls back to an exact sequential scan. The result remains correct, but the HNSW index is not used.
Generate embeddings in SQL
The embedding extension provides server-side embedding functions.
Generate an embedding from text:
SELECT embedding('hello world');
You can also use embed_text() with an explicit model:
SELECT embed_text('text-embedding-v4', 'hello world');
The currently supported embedding dimensions are 256, 512, and 1024. The default output dimension is 1024.
To change the output dimension for the current session:
SET embedding.dimensions = 512;
SELECT embedding('hello world');
End-to-end example
The following example creates a table, generates embeddings, creates an HNSW index, and performs semantic search:
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS embedding;
SET embedding.dimensions = 1024;
CREATE TABLE docs (
id SERIAL PRIMARY KEY,
content TEXT NOT NULL,
vec VECTOR(1024)
);
INSERT INTO docs (content, vec) VALUES
('PostgreSQL is a relational database',
embedding('PostgreSQL is a relational database')),
('TiDB Cloud provides a managed database service',
embedding('TiDB Cloud provides a managed database service')),
('Vector search finds semantically similar documents',
embedding('Vector search finds semantically similar documents'));
CREATE INDEX idx_docs_vec
ON docs
USING hnsw (vec vector_cosine_ops);
SELECT content
FROM docs
ORDER BY vec_embed_cosine_distance(vec, 'managed database')
LIMIT 3;
The vec_embed_cosine_distance() query can use idx_docs_vec for cosine-distance search. You can add EXPLAIN before the SELECT statement to check whether the plan includes an HNSW Scan using this index.
Search with automatic embedding
You can embed query text and calculate distance in one function call.
The following example uses the docs table created in the preceding section. The docs.vec column stores 1024-dimensional embeddings generated by the default embedding model.
SELECT id, content
FROM docs
ORDER BY vec_embed_cosine_distance(vec, 'database for AI applications')
LIMIT 5;
The query embedding must use the same model and dimensions as the stored embeddings.
The following functions are available:
Limitations
The following limitations apply to vector search during the public preview:
- IVFFlat indexes are not supported. HNSW is the supported approximate nearest-neighbor index type.
- An HNSW index can contain only one vector column.
- The indexed vector column must declare an explicit dimension, such as
VECTOR(1024). Do not use a bareVECTORcolumn for HNSW indexing. - The table must have a single-column primary key. Tables without a primary key or with a composite primary key cannot use an HNSW index.
- If the primary key is
INTEGERorBIGINT, its values must be non-negative.UUIDandTEXTprimary keys do not have this restriction. - Partial HNSW indexes are not supported.
- Distance operations require vectors with matching dimensions.
- The
embedding()andembed_text()functions require a database user with sufficient privileges. - HNSW acceleration has query-shape restrictions. Queries that do not meet the requirements described in When an HNSW index is used use an exact sequential scan instead.