Vector Search

KalamDB supports vector search with native EMBEDDING(n) columns, cosine indexes, and SQL ranking via COSINE_DISTANCE(...).

Use this when you need semantic retrieval for agent memory, RAG document recall, or similarity search over user-scoped data.

What Vector Search Looks Like

The current, tested flow in KalamDB is:

1. Create one or more EMBEDDING(n) columns.
2. Build a vector index on each embedding column with ALTER TABLE ... CREATE INDEX ... USING COSINE.
3. Query nearest rows with ORDER BY COSINE_DISTANCE(...) LIMIT k.

Create a Vector Table

1CREATE NAMESPACE IF NOT EXISTS rag;2 3CREATE TABLE rag.documents_vectors (4  id BIGINT PRIMARY KEY,5  doc_embedding EMBEDDING(3),6  attachment_a_embedding EMBEDDING(3),7  attachment_b_embedding EMBEDDING(3)8) WITH (TYPE = 'USER');

Why this shape works well:

• Keep document metadata and files in your main table.
• Keep embeddings in a parallel table keyed by the same id.
• Use TYPE = 'USER' when each signed-in user should search only their own vectors.

Build Cosine Indexes

1ALTER TABLE rag.documents_vectors2  CREATE INDEX doc_embedding USING COSINE;3 4ALTER TABLE rag.documents_vectors5  CREATE INDEX attachment_a_embedding USING COSINE;6 7ALTER TABLE rag.documents_vectors8  CREATE INDEX attachment_b_embedding USING COSINE;

KalamDB currently documents and tests cosine similarity indexes. If you create multiple embedding columns, index each column you plan to search independently.

Insert Embeddings

Embeddings are inserted as JSON-like numeric arrays in SQL strings:

1INSERT INTO rag.documents_vectors (2  id,3  doc_embedding,4  attachment_a_embedding,5  attachment_b_embedding6) VALUES (7  1,8  '[1.0,0.0,0.0]',9  '[0.95,0.05,0.0]',10  '[0.90,0.10,0.0]'11);

Make sure the vector length matches the EMBEDDING(n) dimension declared in the schema.

Run a Similarity Query

1SELECT id2FROM rag.documents_vectors3ORDER BY COSINE_DISTANCE(doc_embedding, '[1.0,0.0,0.0]')4LIMIT 3;

Smaller COSINE_DISTANCE(...) values are more similar, so the nearest matches appear first.

You can search any indexed embedding column:

1SELECT id2FROM rag.documents_vectors3ORDER BY COSINE_DISTANCE(attachment_b_embedding, '[0.0,1.0,0.0]')4LIMIT 2;

Join Search Results Back to Documents

In most apps, vector search is only the retrieval step. After ranking the embedding rows, join back to your main document table:

1SELECT d.id, d.title, d.body2FROM rag.documents AS d3JOIN rag.documents_vectors AS v ON v.id = d.id4ORDER BY COSINE_DISTANCE(v.doc_embedding, '[1.0,0.0,0.0]')5LIMIT 5;

That pattern lets you keep rich rows, FILE attachments, and embeddings separate while still querying them together.

RAG Pattern With Files

One tested KalamDB scenario stores:

• a USER-scoped documents table with two FILE columns
• a USER-scoped vectors table keyed by the same id
• vector indexes persisted across flushes
• similarity queries that continue working across hot and cold storage

This is a good fit for:

• private agent memory per user
• semantic search over uploaded files
• document recall before LLM summarization or answer generation

Practical Notes

• Use TYPE = 'USER' for tenant-safe semantic search.
• Keep embedding generation in your application or worker pipeline; KalamDB stores and indexes the vectors.
• If you update or delete rows, the vector search results follow the table state.
• Vector search continues to work when some rows are still hot and others have already flushed to cold storage.

Related Docs

• Data Types
• Table DDL
• Built-in Functions
• TypeScript SDK Examples