Vector Search

ScramDB does vector similarity search as a built-in function, right next to the rest of your SQL. You store embeddings alongside your regular columns, and you rank rows by how close their embedding is to a query vector. Because it is the same table your application already writes to, similarity search always runs on current data, and you can combine it with any WHERE, JOIN, or GROUP BY you like, all in one query. There is no separate vector database to keep in sync.

Note: The embeddings_* functions come from ScramDB's built-in packages. Install and register them once first, see Install and use a package.

Store embeddings

An embedding is a list of numbers. Store each one as a JSON array in a text column:

CREATE TABLE docs (
    id         BIGINT PRIMARY KEY,
    title      VARCHAR(200),
    body       TEXT,
    tenant_id  INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    embedding  TEXT              -- JSON array, e.g. '[0.12, -0.03, 0.88]'
);

INSERT INTO docs (id, title, body, tenant_id, embedding) VALUES
    (1, 'Intro to ScramDB', '...', 42, '[0.10, 0.20, 0.30]'),
    (2, 'Vector search',    '...', 42, '[0.11, 0.19, 0.31]'),
    (3, 'Loading data',     '...', 42, '[0.90, 0.10, 0.05]');

Generate the embeddings with whatever model you already use, then insert the resulting arrays as text. ScramDB does not require a special vector column type.

Score similarity between two vectors

embeddings_cosine_similarity(a, b) takes two embeddings (each a JSON array in text) and returns their cosine similarity as a number:

• 1.0 means the vectors point the same way (most similar).
• 0.0 means they are unrelated.
• -1.0 means they point in opposite directions.

SELECT embeddings_cosine_similarity('[0.10, 0.20, 0.30]', '[0.11, 0.19, 0.31]');

Both vectors must have the same number of elements.

Find the top matches (top-k)

To find the rows most similar to a query embedding, order by the similarity score and take the first few:

SELECT id
FROM docs
ORDER BY embeddings_cosine_similarity(embedding, '[0.10, 0.20, 0.30]') DESC
LIMIT 5;

Select the score too when you want to show it or apply a cutoff:

SELECT
    id,
    title,
    embeddings_cosine_similarity(embedding, '[0.10, 0.20, 0.30]') AS score
FROM docs
ORDER BY score DESC
LIMIT 5;

Combine similarity with your business filters

This is where one live copy of your data pays off. Because the search is ordinary SQL over your real table, you can narrow the candidates with any condition before ranking them, in the same statement. Restrict to a tenant, a recent time window, or a category, and rank what is left:

SELECT id, title
FROM docs
WHERE tenant_id = 42
  AND created_at >= DATE '2024-01-01'
ORDER BY embeddings_cosine_similarity(embedding, '[0.10, 0.20, 0.30]') DESC
LIMIT 5;

Set a minimum-similarity cutoff so weak matches never come back:

SELECT id, title
FROM docs
WHERE tenant_id = 42
  AND embeddings_cosine_similarity(embedding, '[0.10, 0.20, 0.30]') >= 0.75
ORDER BY embeddings_cosine_similarity(embedding, '[0.10, 0.20, 0.30]') DESC;

Dot product

When your vectors are already normalized to unit length, the dot product ranks them the same way cosine similarity does and is a hair cheaper to compute. Use embeddings_dot(a, b):

SELECT id
FROM docs
ORDER BY embeddings_dot(embedding, '[0.10, 0.20, 0.30]') DESC
LIMIT 5;

Notes and performance

• Skip empty embeddings. These functions expect a value in every argument. If some rows have no embedding yet, exclude them with WHERE embedding IS NOT NULL.
• Similarity is computed for the rows your query considers. It compares your query vector against each candidate row, so the tighter you make the WHERE clause, the less work each search does. Pre-filtering by tenant, category, or recency (as shown above) is the most effective way to keep searches fast on a large table.
• One query, live data. The match you get back reflects every write that has committed, with nothing to reindex and no second system to reconcile.