Vector Search

ScramDB stores embeddings in a native VECTOR column next to the rest of your data and ranks rows by distance with ordinary SQL. 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.

For large corpora, add an anode index and the same query stops being a full scan: the engine serves it from the index in sub-linear time. See Vector Search in the SQL reference for the full surface, including the tuning knobs, and CREATE INDEX for the index itself.

Store embeddings

An embedding is a fixed-length list of numbers. Declare the column as VECTOR(n), where n is your model's dimension:

CREATE TABLE docs (
    id         BIGINT PRIMARY KEY,
    title      VARCHAR(200),
    body       TEXT,
    tenant_id  INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    embedding  VECTOR(3)
);

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 and insert them as array literals. Use the dimension your model actually produces: a 1536-dimension model needs VECTOR(1536).

Add the index

Build an anode index on the column, naming the metric you intend to search by:

CREATE INDEX docs_embedding_idx ON docs USING anode (embedding vector_cosine_ops);

Use vector_cosine_ops for cosine distance, vector_l2_ops for Euclidean, or vector_ip_ops for inner product. On a cluster, USING manode builds the distributed equivalent.

The index is optional: every query below returns the same rows without it, by scanning. What the index changes is how much work that takes.

Find the top matches (top-k)

Order by distance and take the first few. The <=> operator is cosine distance, so the nearest rows come first:

SELECT id
FROM docs
ORDER BY embedding <=> '[0.10, 0.20, 0.30]'
LIMIT 5;

cosine_distance(a, b) is the same thing spelled as a function, and ranks identically:

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

Note the ordering is ascending, because these are distances: smaller means more similar. This matters more than it looks. Ascending order over a distance is the shape the engine recognizes and serves from the index; ordering descending asks for the farthest rows instead, which no nearest-neighbour index can answer, so it falls back to a scan.

Combine similarity with your business filters

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:

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

Set a distance cutoff

Ask for everything within a radius instead of a fixed count:

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

Other metrics

Each metric has an operator and a function, and each has a matching opclass to index by:

SELECT id, embedding <-> '[0.10, 0.20, 0.30]' AS l2_distance
FROM docs
ORDER BY l2_distance
LIMIT 5;

Use <-> (or l2_distance) for Euclidean distance. For inner product, <#> returns the NEGATIVE inner product, which is what lets ascending order put the largest inner product first, the same convention pgvector uses; inner_product(a, b) returns the plain value and so sorts the other way. Index the column with the opclass that matches the metric you search by.

If you cannot use the VECTOR type

Some setups store embeddings as JSON text in an existing column and cannot change it. ScramDB's embeddings_* package functions work directly on that text, so similarity search still works without a schema migration:

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

INSERT INTO docs_text (id, title, 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]');

embeddings_cosine_similarity(a, b) takes two embeddings as JSON text and returns their cosine SIMILARITY, where 1.0 means identical direction, 0.0 unrelated, and -1.0 opposite. Similarity runs the opposite way to distance, so these queries order descending:

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

SELECT id, title
FROM docs_text
WHERE tenant_id = 42
ORDER BY embeddings_cosine_similarity(embedding, '[0.10, 0.20, 0.30]') DESC
LIMIT 5;

embeddings_dot(a, b) is the dot product, which ranks the same way as cosine similarity when your vectors are already unit length.

Understand what this costs. A TEXT column cannot carry a vector index, and a package function is not a distance the planner can serve from one, so these queries always scan every candidate row and call the function once per row. That is fine for a small table, and it is the wrong shape for a large one. If the table will grow, migrate the column to VECTOR(n) and use the indexed queries above.

These functions come from ScramDB's built-in packages, which you install and register once. See Install and use a package.

Notes and performance

• Distances sort ascending, similarities sort descending. <=> and cosine_distance return a distance, so nearest-first is ORDER BY ... LIMIT k with no DESC. Only the ascending-distance form can be served by an anode index.
• Skip empty embeddings. Exclude rows that have no embedding yet with WHERE embedding IS NOT NULL.
• Filter first when you can. A WHERE clause narrows the candidates the search has to consider, which helps whether or not an index is present.
• Match the opclass to the metric. An index built vector_cosine_ops serves cosine queries; searching it by a different metric falls back to a scan.
• 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.