Why the Python list stops working

The list-of-tuples "index" from the last two topics computes cosine similarity against every stored vector on every query — an O(N) brute-force scan. That's fine for 500 chunks and hopeless for 5 million: you'd re-score millions of 1536-dimensional vectors per request. A vector database exists to make similarity search fast and durable at scale. It gives you four things the list can't:

1. Approximate nearest-neighbor (ANN) indexes — sub-linear search instead of scanning everything.
2. Persistence & scale — data survives restarts, shards across machines, handles concurrent writes.
3. Metadata filtering in the engine — combine a WHERE clause with vector search efficiently.
4. Operational features — updates/deletes, backups, replication, hybrid (keyword + vector) search.

Approximate nearest-neighbor (ANN)

Exact nearest-neighbor means "compare against all N vectors" — guaranteed correct, but O(N). ANN trades a little accuracy for a huge speedup: it uses a pre-built index to look at only a fraction of the vectors and still find almost all the true top-k. The quality of that "almost" is recall:

recall@k = (true top-k results actually returned) / k.

A well-tuned ANN index hits 95–99% recall while touching a tiny slice of the data. Here's the exact (slow) baseline so you can see what ANN approximates — you'll compare against it in Exercise 1:

import numpy as np

def exact_top_k(query: np.ndarray, vectors: np.ndarray, k: int = 5):
    # vectors: shape (N, dim) — all stored embeddings stacked into one matrix.
    # Cosine similarity of the query against EVERY row, vectorised (no Python loop):
    sims = vectors @ query / (np.linalg.norm(vectors, axis=1) * np.linalg.norm(query))
    #      └ (N,dim)@(dim,) = (N,) dot products   └ per-row vector length   └ query length
    top = np.argsort(sims)[::-1][:k]     # indices of the k highest sims (argsort asc → reverse → take k)
    return top, sims[top]                # return the winning row indices and their scores
# This is O(N): correct, but every query re-scores all N vectors. ANN's whole job is to avoid that.

The three similarity metrics

Retrieval ranks by a distance/similarity measure. Three show up constantly:

Metric	Measures	Use when	pgvector op
Cosine similarity	angle between vectors (direction only)	general text retrieval — the default	<=> (distance = 1 − cosine)
Dot product (inner product)	direction and magnitude	vectors whose length carries meaning; fast when normalized	<#> (negative inner product)
Euclidean / L2	straight-line distance in space	when absolute position matters (some image/embedding models)	<->

Hands-on: pgvector

pgvector is a Postgres extension that adds a vector type and ANN indexes. It's the pragmatic default when you already run Postgres: your chunks, metadata, and embeddings live in one database with real SQL.

1. Schema

CREATE EXTENSION IF NOT EXISTS vector;         -- enable the pgvector extension (once per database)

CREATE TABLE chunks (
  id        bigserial PRIMARY KEY,             -- auto-incrementing id (like an autoincrement pk)
  source    text,                              -- metadata: which document this chunk came from
  section   text,                              -- metadata: heading/section (from the chunking topic)
  content   text,                              -- the chunk text itself
  embedding vector(1536)                       -- the embedding — 1536 dims for text-embedding-3-small
);

2. Insert embeddings

import psycopg                                  # the Postgres driver for Python

conn = psycopg.connect("postgresql://localhost/rag")   # open a connection (like a DB client)

def insert_chunk(source, section, content, embedding):
    conn.execute(
        # %s are PARAMETERIZED placeholders — the driver safely substitutes values (never string-concat SQL):
        "INSERT INTO chunks (source, section, content, embedding) VALUES (%s, %s, %s, %s)",
        (source, section, content, embedding),  # a tuple of the 4 values, in order
    )
    conn.commit()                               # persist the write (transactions are explicit)

# `embedding` is a Python list[float] from embed(content); pgvector accepts it directly.
insert_chunk("pricing.md", "Plans", "The Pro plan is €49/month…", embed("The Pro plan is €49/month…"))

3. Query by distance

def search(question: str, k: int = 5):
    q = embed(question)                          # embed the query into the SAME space as the chunks
    rows = conn.execute(
        # Order rows by cosine DISTANCE to the query (<=>); smallest distance first = most similar.
        "SELECT source, section, content, embedding <=> %s AS distance "
        "FROM chunks "
        "ORDER BY embedding <=> %s "             # the ANN index (below) makes this fast
        "LIMIT %s",                              # top-k
        (q, q, k),                               # bind the query vector twice + k
    ).fetchall()                                 # fetch all result rows (a list of tuples)
    return rows
# Note: cosine DISTANCE = 1 − cosine SIMILARITY, so a distance of 0.1 ≈ similarity 0.9.

Metadata filtering — in the engine

In the chunking topic you filtered chunks in Python after scoring. A vector DB does it inside the query with an indexed WHERE, which is far faster because it narrows the candidate set the engine has to consider:

def search_filtered(question: str, source: str, k: int = 5):
    q = embed(question)
    rows = conn.execute(
        "SELECT content, embedding <=> %s AS distance "
        "FROM chunks "
        "WHERE source = %s "                     # metadata pre-filter: only chunks from this document
        "ORDER BY embedding <=> %s "             # then rank the survivors by vector distance
        "LIMIT %s",
        (q, source, q, k),
    ).fetchall()
    return rows
# This is how you scope retrieval to one tenant, one document, or a date range at scale.

Tuning the ANN index (HNSW)

The dominant ANN index is HNSW (Hierarchical Navigable Small World) — a layered graph you traverse to reach a query's neighborhood in roughly logarithmic time. It exposes a direct recall ↔ latency knob:

-- Build an HNSW index for cosine distance (vector_cosine_ops matches the <=> operator):
CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);        -- build-time quality: bigger = better recall, slower build

-- Per-query knob: how many candidates to explore. Higher = better recall, higher latency.
SET hnsw.ef_search = 100;                      -- raise it if recall is low; lower it if queries are slow

• m / ef_construction — build-time: larger values build a richer graph (better recall, more memory, slower indexing). Set once.
• ef_search — query-time: the live recall/latency dial. Tune it against your golden set.

The alternative index family is IVF (inverted file / IVFFlat): it clusters vectors into lists and searches only the nearest lists (probes is its knob). IVF builds faster and uses less memory; HNSW generally gives better recall at a given latency. HNSW is the usual default now.

The landscape: pgvector vs. Qdrant vs. Azure AI Search

Option	Sweet spot	Notes
pgvector	you already run Postgres; ≤ ~10M vectors	one DB for data + vectors + SQL; simplest ops; HNSW/IVFFlat
Qdrant	dedicated, high-scale vector workloads	purpose-built ANN, strong filterable indexes, payload filtering, hybrid search
Azure AI Search	Azure-native RAG, enterprise search	managed hybrid (keyword + vector) + semantic reranking; integrates with Azure OpenAI

Others you'll hear (Pinecone, Weaviate, Milvus, pgvector-alternatives) occupy similar niches. The decision rule: start with pgvector if you already have Postgres; reach for a dedicated engine when scale, filtering complexity, or hybrid-search needs outgrow it. On Azure, Azure AI Search is the default because of its managed hybrid retrieval and reranking (revisited in the Deployment track).

Pitfalls

• Brute-forcing at scale. An O(N) scan is fine for a demo and a liability in production — index it.
• Metric/normalization mismatch. Using dot product on non-normalized vectors ranks by length, not meaning. Normalize, or use cosine.
• Trusting default recall. ANN is approximate; measure recall@k against an exact baseline and your golden set, especially for filtered queries.
• Ignoring the filter interaction. Selective WHERE clauses can starve an ANN index — verify recall on filtered queries.
• One-size index config. ef_search/probes are real dials; leaving them at defaults leaves recall or latency on the table.

Recap

• Past a few thousand vectors, replace the Python list with a vector DB for ANN search, persistence, in-engine filtering, and ops.
• ANN trades a little accuracy (measured as recall@k) for a large speedup vs. exact O(N) search.
• Pick a metric: cosine is the text default; dot product and L2 matter mainly for non-normalized vectors — normalize and they converge.
• pgvector gives you the whole loop in SQL: vector columns, <=> distance queries, and WHERE metadata filtering in the engine.
• HNSW is the default index; ef_search is your live recall ↔ latency knob — tune it on the golden set.
• Choose the store by fit: pgvector if you already run Postgres, Qdrant for dedicated scale/filtering, Azure AI Search for Azure-native hybrid RAG.
• Worked examples map to the exercises: exact_top_k baseline (Ex 1), pgvector search() (Ex 2), HNSW + ef_search sweep (Ex 3), and the store-selection decision (Ex 4).

import numpy as np, time

N, DIM = 5_000, 1536
vectors = np.random.randn(N, DIM)          # fake "embeddings"
query = np.random.randn(DIM)

def exact_top_k(query, vectors, k=5):
    sims = vectors @ query / (np.linalg.norm(vectors, axis=1) * np.linalg.norm(query))
    top = np.argsort(sims)[::-1][:k]
    return top, sims[top]

t = time.perf_counter()
idx, scores = exact_top_k(query, vectors)
print(idx, f"{(time.perf_counter()-t)*1000:.1f} ms")

import psycopg
conn = psycopg.connect("postgresql://postgres:x@localhost/postgres")
conn.execute("CREATE EXTENSION IF NOT EXISTS vector")
conn.execute("""CREATE TABLE IF NOT EXISTS chunks (
    id bigserial PRIMARY KEY, source text, section text,
    content text, embedding vector(1536))""")
conn.commit()

corpus = [
    ("pricing.md", "Plans", "The Pro plan is €49/month billed annually."),
    ("pricing.md", "Plans", "The Free plan includes 100 messages per month."),
    ("security.md", "Data", "All data is encrypted at rest with AES-256."),
    # …a few more…
]
for source, section, content in corpus:
    conn.execute(
        "INSERT INTO chunks (source, section, content, embedding) VALUES (%s,%s,%s,%s)",
        (source, section, content, embed(content)))
conn.commit()

def search(q, k=3):
    qv = embed(q)
    return conn.execute(
        "SELECT source, content, embedding <=> %s AS distance "
        "FROM chunks ORDER BY embedding <=> %s LIMIT %s", (qv, qv, k)).fetchall()

for row in search("How much is the Pro plan?"):
    print(row)     # the €49 pricing chunk should be first (smallest distance)

CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

truth, _ = exact_top_k(embed(q), all_vectors, k=10)   # exact top-10 = ground truth
for ef in (10, 40, 100):
    conn.execute(f"SET hnsw.ef_search = {ef}")
    t = time.perf_counter()
    approx = [r[0] for r in conn.execute(
        "SELECT id FROM chunks ORDER BY embedding <=> %s LIMIT 10", (embed(q),)).fetchall()]
    recall = len(set(approx) & set(truth)) / len(truth)
    print(f"ef={ef}: recall={recall:.2f}  {(time.perf_counter()-t)*1000:.1f} ms")