Vector databases, HNSW and pgvector

How it works: vector databases

1. Store a record per chunk: an ID, the embedding vector, the chunk text, and metadata (document ID, source, date, tenant, permissions).
2. Query by similarity: "give me the k records whose vectors are nearest to this query vector", using cosine, dot product or Euclidean distance.
3. Exact (brute-force) search compares against every vector. Perfect results, fine for up to roughly tens of thousands of vectors.
4. Approximate nearest neighbour (ANN) indexes skip most comparisons. They might miss a few true neighbours (lower recall) but are hundreds of times faster at scale.
5. Upserts and deletes keep the index in sync with your documents (ingestion).
6. Common choices (as of November 2026): pgvector (Postgres), Qdrant, Weaviate, Milvus, Pinecone (managed), Chroma and LanceDB (embedded, easy local start), and Elasticsearch / OpenSearch (search engines with vector support, great for hybrid search).

How it works: HNSW indexes

1. HNSW (hierarchical navigable small world) links each vector to a handful of its nearest neighbours, forming a graph.
2. It builds several layers: the top layer has few points with long-distance links (motorways); lower layers are denser (local streets); the bottom layer has every point.
3. To search, start at the top, greedily hop to whichever neighbour is closer to the query, drop down a layer, repeat, and finally explore a candidate list at the bottom to collect the top k.
4. Build-time knobs: m (links per node, more = better recall, more memory) and ef_construction (effort while building). pgvector defaults: m = 16, ef_construction = 64.
5. Query-time knob: ef_search (candidate list size; pgvector default 40). Raise it for better recall, lower it for speed.
6. Trade-offs: HNSW is fast and accurate but uses a lot of memory and is slower to build; IVF-style indexes are smaller but usually less accurate at the same speed.

How it works: pgvector

1. Enable it once per database: CREATE EXTENSION vector; (as of November 2026 the latest release is 0.8.7).
2. Add a column: embedding vector(1536) (the dimension must match your embedding model).
3. Insert vectors like any other column.
4. Query nearest neighbours: ORDER BY embedding <=> '[...]' LIMIT 5. Operators: <=> cosine distance, <-> L2 distance, <#> negative inner product.
5. Add an HNSW index for speed: CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops);
6. Join and filter with normal SQL: vectors live next to your users, tenants and permissions tables, inside transactions.

How it works: metadata filtering

1. Store filterable fields on each chunk: tenant/customer ID, document type, language, date, access groups.
2. Filter at query time: "nearest 5 chunks where tenant_id = 42 and year ≥ 2025".
3. Know the ANN trap: approximate indexes find nearest candidates first and filter afterwards. If only 10% of vectors match the filter, most candidates get thrown away and you get too few results (pgvector's docs: with ef_search 40 and 10% selectivity, about 4 rows).
4. Fixes: pgvector's iterative index scans (SET hnsw.iterative_scan = strict_order; or relaxed_order), a higher ef_search, partial indexes or partitions per tenant, or vector databases with built-in filtered search.
5. Use filters for security, not just relevance: permission filters must be applied on the server for every query (access control in RAG).

import numpy as np
 
class TinyVectorStore:
    """What a vector database does, minus the clever index: store, filter, rank."""
    def __init__(self):
        self.vectors, self.items = [], []
    def add(self, vector, text, **metadata):
        v = np.asarray(vector, dtype=float)
        self.vectors.append(v / np.linalg.norm(v))          # normalise once, so dot = cosine
        self.items.append({"text": text, **metadata})
    def search(self, query, k=2, where=None):
        q = np.asarray(query, dtype=float); q /= np.linalg.norm(q)
        scores = np.array(self.vectors) @ q                  # cosine similarity with every vector
        order = np.argsort(-scores)
        hits = [(float(scores[i]), self.items[i]) for i in order
                if not where or all(self.items[i].get(k2) == v for k2, v in where.items())]
        return hits[:k]
 
db = TinyVectorStore()
db.add([0.9, 0.1, 0.0], "Refunds within 30 days (UK)", country="UK", year=2026)
db.add([0.8, 0.2, 0.1], "Refunds within 14 days (DE)", country="DE", year=2026)
db.add([0.9, 0.0, 0.1], "Refunds within 60 days (UK, old)", country="UK", year=2023)
db.add([0.0, 0.1, 0.9], "Shipping takes 3 days", country="UK", year=2026)
 
query = [1.0, 0.1, 0.0]                                       # "refund policy" as a vector
print("no filter    :", [(round(s, 2), i["text"]) for s, i in db.search(query)])
print("UK, 2026 only:", [(round(s, 2), i["text"]) for s, i in db.search(query, where={"country": "UK", "year": 2026})])

no filter    : [(1.0, 'Refunds within 30 days (UK)'), (0.99, 'Refunds within 60 days (UK, old)')]
UK, 2026 only: [(1.0, 'Refunds within 30 days (UK)'), (0.01, 'Shipping takes 3 days')]

CREATE EXTENSION IF NOT EXISTS vector;
 
CREATE TABLE chunks (
  id        bigserial PRIMARY KEY,
  doc_id    text NOT NULL,
  tenant_id int  NOT NULL,
  content   text NOT NULL,
  embedding vector(1536)            -- must match the embedding model's dimension
);
 
CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops);   -- fast cosine search
SET hnsw.iterative_scan = strict_order;   -- keep scanning when filters remove candidates
 
-- The 5 most similar chunks for tenant 42 ($1 = the query embedding from your app)
SELECT doc_id, content, 1 - (embedding <=> $1) AS cosine_similarity
FROM chunks
WHERE tenant_id = 42
ORDER BY embedding <=> $1
LIMIT 5;