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

CREATE NAMESPACE IF NOT EXISTS rag;
 
CREATE TABLE rag.documents_vectors (
  id BIGINT PRIMARY KEY,
  doc_embedding EMBEDDING(3),
  attachment_a_embedding EMBEDDING(3),
  attachment_b_embedding EMBEDDING(3)
) 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

ALTER TABLE rag.documents_vectors
  CREATE INDEX doc_embedding USING COSINE;
 
ALTER TABLE rag.documents_vectors
  CREATE INDEX attachment_a_embedding USING COSINE;
 
ALTER TABLE rag.documents_vectors
  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:

INSERT INTO rag.documents_vectors (
  id,
  doc_embedding,
  attachment_a_embedding,
  attachment_b_embedding
) VALUES (
  1,
  '[1.0,0.0,0.0]',
  '[0.95,0.05,0.0]',
  '[0.90,0.10,0.0]'
);

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

Run a Similarity Query

SELECT id
FROM rag.documents_vectors
ORDER BY COSINE_DISTANCE(doc_embedding, '[1.0,0.0,0.0]')
LIMIT 3;

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

You can search any indexed embedding column:

SELECT id
FROM rag.documents_vectors
ORDER BY COSINE_DISTANCE(attachment_b_embedding, '[0.0,1.0,0.0]')
LIMIT 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:

SELECT d.id, d.title, d.body
FROM rag.documents AS d
JOIN rag.documents_vectors AS v ON v.id = d.id
ORDER BY COSINE_DISTANCE(v.doc_embedding, '[1.0,0.0,0.0]')
LIMIT 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