How does vector similarity search work in SQL databases (pgvector, MySQL, SQL Server, Oracle)?
How does vector similarity search work in SQL databases (pgvector, MySQL, SQL Server, Oracle)?
Vector search stores embeddings (arrays of floats produced by a model) in a column and returns the rows whose vectors are nearest to a query vector. It powers semantic search and retrieval-augmented generation (RAG) without leaving SQL.
CREATE EXTENSION vector;
CREATE TABLE documents (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
tenant_id BIGINT NOT NULL,
body TEXT NOT NULL,
embedding vector(1536) NOT NULL -- dimension must match your model
);
-- approximate nearest-neighbour index (HNSW; IVFFlat is the other option)
CREATE INDEX idx_documents_embedding ON documents USING hnsw (embedding vector_cosine_ops);
-- nearest 5 by cosine distance: <=> cosine, <-> L2, <#> negative inner product
SELECT id, body
FROM documents
WHERE tenant_id = 7
ORDER BY embedding <=> $1
LIMIT 5;
| Engine | Support |
|---|---|
| Oracle 23ai | VECTOR type, VECTOR_DISTANCE, HNSW and IVF indexes, FETCH APPROX FIRST n ROWS ONLY |
Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.