What you will learn
How to keep embeddings next to the rest of your application data using Postgres and the pgvector extension, how to index them, and how to keep tenant filtering correct when you search.
Why a database you already run
Many teams already operate Postgres with backups, access control, and monitoring. pgvector adds a vector column type, distance operators, and approximate-nearest-neighbour indexes to it. That lets one transaction update a document's text, version, permissions, and embedding together — which makes the versioning and deletion rules from the ingestion lessons much easier to enforce. A dedicated vector database can still make sense at large scale or for specialised features; choose it when measurements show you need it, not by default.
The schema
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE chunks (
id text PRIMARY KEY,
tenant_id text NOT NULL,
document_id text NOT NULL,
version text NOT NULL,
active boolean NOT NULL DEFAULT false,
content text NOT NULL,
embedding vector(768) NOT NULL
);
CREATE INDEX chunks_embedding_hnsw ON chunks
USING hnsw (embedding vector_cosine_ops);
CREATE INDEX chunks_tenant_active ON chunks (tenant_id, active);
The dimension (768 here) must match your embedding model exactly. A different model or model version usually means a different dimension or a different vector space — rebuild the index rather than mixing vectors from two models in one column.
Searching
pgvector provides distance operators: <-> for Euclidean distance, <=> for cosine distance, and <#> for negative inner product. Cosine distance is 1 − cosine similarity, so smaller means closer.
SELECT id, content, 1 - (embedding <=> %(query)s::vector) AS similarity
FROM chunks
WHERE tenant_id = %(tenant)s AND active
ORDER BY embedding <=> %(query)s::vector
LIMIT 5;
The ORDER BY ... LIMIT shape is what allows Postgres to use the HNSW index. The tenant comes from your verified principal, never from request JSON or model output.
The filtering trap
An approximate index finds the nearest candidates first and the WHERE clause filters them afterwards. If a tenant owns only a small share of rows, the index can return fewer than five rows — or none — even though matching rows exist. Options, depending on your scale and pgvector version:
- Recent pgvector versions support iterative index scans, which keep scanning until enough rows pass the filter. Check the documentation for the version you install.
- Partition or use a separate index per large tenant.
- For small tenants, an exact scan over the tenant's rows can be fast enough.
Whichever you choose, add a test: a tenant with very few documents must still get its matching results. Missing results is a retrieval-quality bug, and returning another tenant's rows is a security bug — test both.
Passing vectors from Python
Embedding SDKs return lists of floats. pgvector accepts a text literal like [0.1,0.2,0.3], which you send as a query parameter — never by formatting values into the SQL string.
# academy-check
import math
def to_pgvector(values, dimension):
if len(values) != dimension:
raise ValueError(f"expected {dimension} values, got {len(values)}")
if not all(isinstance(v, (int, float)) and math.isfinite(v) for v in values):
raise ValueError("embedding contains a non-finite value")
return "[" + ",".join(repr(float(v)) for v in values) + "]"
assert to_pgvector([1, 0.5, -2], 3) == "[1.0,0.5,-2.0]"
for bad in ([1, 2], [1, float("nan"), 3]):
try:
to_pgvector(bad, 3)
except ValueError:
pass
else:
raise AssertionError(bad)
With a Postgres driver such as psycopg, pass the result as a parameter: cursor.execute(query, {"query": to_pgvector(vec, 768), "tenant": principal.tenant_id}). The pgvector project also publishes Python helpers that register the type for you.
Updates and deletion
Reuse the versioning pattern from the ingestion lab: insert a new version's chunks with active = false, verify the count and embeddings, then switch active for the old and new versions in one transaction. Deleting a document deletes its rows; also clear any answer cache that may still contain it.
Exercise
Run Postgres with pgvector locally (a container image or a managed service with the extension enabled). Load the 20-document collection from the retrieval lab for two tenants, where one tenant owns only two documents. Compare exact search with the HNSW index on your frozen questions: record recall at 5, latency, and whether the small tenant still receives its results.
Check: vector dimensions are validated before insert; tenant filtering is tested for both leaks and missing results; switching versions is atomic. The SQL in this lesson needs Postgres with pgvector and is not part of the dependency-free course checks — record which pgvector version you used.