Vector Databases (AI/Embeddings)

Retrieve similar vectors, then evaluate relevance

A vector index searches numerical representations produced by an embedding model. It can retrieve passages that use different words for a related idea, but similarity is not a guarantee that an answer is correct, authorized, or useful. Relational databases can support vector extensions and traditional search can support synonyms; this is not a strict split between “SQL matches words” and “vectors understand meaning.”

Start with the representation

Choose an embedding model appropriate to the language and task. Store its model/version and preprocessing rules with the collection. Query and stored vectors must inhabit the same space: two models returning 1,536 numbers do not automatically produce comparable coordinates.

Normalization also matters. Cosine similarity compares direction; dot product includes vector magnitude unless vectors are normalized. Euclidean distance measures straight-line separation. Use the metric expected by the model and reject malformed or zero vectors where the chosen metric requires a nonzero norm.

import math

def cosine_similarity(a, b):
    if len(a) != len(b) or not a:
        raise ValueError("Vectors must have the same nonzero dimension")
    if not all(math.isfinite(x) for x in [*a, *b]):
        raise ValueError("Coordinates must be finite")
    norm_a = math.sqrt(sum(x * x for x in a))
    norm_b = math.sqrt(sum(x * x for x in b))
    if norm_a == 0 or norm_b == 0:
        raise ValueError("Cosine similarity is undefined for a zero vector")
    return sum(x * y for x, y in zip(a, b)) / (norm_a * norm_b)

This teaching function assumes ordinary, reasonably scaled embedding values. Production numerical libraries handle precision and extreme values more robustly.

Exact search is the baseline

For a small evaluation set, compute distances to every eligible vector and select the nearest K. This costs work proportional to collection size and dimension, but provides the baseline against which approximate search can be measured.

Approximate nearest-neighbour (ANN) indexes prune the search. They can miss neighbours, and their speed depends on parameters, data distribution, filtering, memory, and hardware. Neither HNSW nor IVF provides a universal “billions of vectors in milliseconds” or logarithmic worst-case guarantee.

A small pgvector experiment

With the pgvector extension installed, these three-dimensional vectors are illustrative numbers, not real text embeddings:

CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE lab_passages (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id bigint NOT NULL,
    body text NOT NULL,
    embedding vector(3) NOT NULL
);
INSERT INTO lab_passages (tenant_id, body, embedding) VALUES
    (1, 'example A', '[1,0,0]'),
    (1, 'example B', '[0.8,0.2,0]'),
    (2, 'another tenant', '[1,0,0]');

-- Cosine distance; a lower value is closer.
SELECT id, body, embedding <=> '[1,0,0]' AS distance
FROM lab_passages
WHERE tenant_id = 1
ORDER BY embedding <=> '[1,0,0]'
LIMIT 2;

The trusted application must bind the authorized tenant, not accept any tenant ID supplied by the caller. Apply permissions before retrieved content is sent to a model. At larger scale, test an ANN index with the same filters; approximate filtering can change how many eligible results are returned.

The pgvector project documentation defines operators, exact/approximate indexes, and filtering behavior. Review the version-specific instructions before creating an index or changing search parameters.

Measure retrieval quality separately from generation

For a held-out question set, record which passages should support the answer. Measure nearest-neighbour recall against exact search, passage relevance, and end-to-end answer support separately. A vector result can be close in embedding space yet refer to an obsolete policy or the wrong product version.

Try an exact identifier, a misspelled identifier, a paraphrase, and a question with no answer in the collection. Keyword search may win on identifiers; a hybrid retriever can combine keyword and vector candidates. A reranker adds another quality/latency trade-off. No-match behavior should be explicit rather than forcing an answer from the least-bad candidate.

Keep updates and deletions in the design

Document changes need re-embedding, and model upgrades may require building a new collection. Record source IDs and versions so an obsolete passage can be traced and removed. Decide how deletions propagate to replicas, caches, and generated-answer stores.

As an exercise, delete a passage after it was cached, then repeat a related query under two user roles. The test should verify freshness and access control, not merely that the index still returns K vectors. See RAG architecture, database indexing, and model serving.