Vector databases and embeddings

Lesson preparation and outcomes

Prerequisites: Retrieval ranking and basic vector arithmetic.

By the end, you can: Select an index from measured requirements and migrate embedding spaces consistently.

Worked scenario and practice feedback

Old-space document/query vectors are both [1,0]; new-space pairs are [0,1]. Matched dot products are 1, but a new query against the old document scores 0 despite identical dimension. Model revision and preprocessing determine compatibility.

Guided and independent practice: Compute all pairings, then independently simulate a space mismatch, source deletion and rollback. Optionally use an existing local database; no installation or service purchase is required.

Answer criteria: Mismatched space rejects before query; corpus/query migration is consistent; deletion reaches cache/index; exact versus approximate search uses the same judgment set. If the local pack or interpreter is unavailable, trace the synthetic input, expected output and failure guard on paper; label execution unverified. Required practice uses no paid API calls, accounts, purchase, operational data or actual deployment.

Lesson 05 introduced retrieval in memory. A vector store becomes useful when measured workload and persistence requirements justify it. This lesson covers the four you’ll see in the wild, when to use each, and how to migrate your existing RAG to pgvector without rewriting it.

Part 1 What a vector database actually adds.

An in-memory numpy array can store and search vectors. It’s fast for small datasets. So what does a vector database give you that numpy doesn’t?

• Persistence. Vectors survive restarts. No re-embedding on every boot.
• Concurrent access. Multiple processes can query at once without race conditions.
• Approximate nearest neighbor (ANN). Sub-linear search. ANN trades some recall for reduced search work; actual latency depends on hardware, filters, index parameters and workload.
• Metadata filtering. “Find similar chunks WHERE source = ‘policy-docs’” without scanning the whole index.
• Updates. Add and remove vectors without rebuilding.

There is no universal 10,000-chunk boundary. Decide from measured latency and recall, concurrent updates, persistence, backups, access filtering and operational ownership. Exact search can be sufficient in a small production system. Do not load untrusted pickle files; use reviewed data formats such as JSON and arrays with pickle disabled.

Part 2 The four to know.

Tool	Lives in	Use when
pgvector	Postgres extension	You already use Postgres. You want one database. Consider it if transaction and access rules already live in Postgres.
Chroma	Embedded Python library	Quick prototype. Lives on disk in a folder. No infrastructure.
Pinecone	Managed SaaS	You want someone else to run the database. You’re willing to pay per month and per query.
Weaviate	Self-hosted or managed	You want richer features (hybrid search, multi-tenancy) and are willing to run infrastructure.

Treat these products as examples of deployment choices. Existing operational competence, required features and allowed data boundary matter more than the lesson tier. A library, a self-managed database and a managed service have different responsibilities; none is automatically best.

Part 3 pgvector in code.

Optional implementation reference: in your own authorized synthetic environment, install a compatible Postgres/pgvector setup using its current platform instructions. The paper exercise requires no installation. If you choose this reference, then:

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE chunks (
    id           SERIAL PRIMARY KEY,
    source       TEXT NOT NULL,
    chunk_index  INT NOT NULL,
    text         TEXT NOT NULL,
    embedding    VECTOR(1536),
    UNIQUE (source, chunk_index)
);

-- Load representative data before creating IVFFlat.
-- Choose lists/probes from measured recall and workload, not this fixed demo.

In Python:

import psycopg
import numpy as np
from pgvector.psycopg import register_vector

conn = psycopg.connect("postgresql://localhost/sd_rag")
register_vector(conn)

# Match this reference's VECTOR(1536) column and cosine-distance contract.
# A Python list is adapted as a PostgreSQL array, not a pgvector value.
def prepare_embedding(value):
    vector = np.asarray(value, dtype=np.float32)
    if vector.shape != (1536,) or not np.isfinite(vector).all():
        raise ValueError("Expected 1536 finite values in the selected embedding space")
    if not np.any(vector):
        raise ValueError("Cosine distance requires a nonzero vector")
    return vector

# Insert (run once per chunk)
def insert_chunk(source, idx, text, embedding):
    embedding = prepare_embedding(embedding)
    with conn.cursor() as cur:
        cur.execute(
            "INSERT INTO chunks (source, chunk_index, text, embedding) "
            "VALUES (%s, %s, %s, %s) "
            "ON CONFLICT (source, chunk_index) DO UPDATE SET text = EXCLUDED.text, embedding = EXCLUDED.embedding",
            (source, idx, text, embedding),
        )
    conn.commit()

# Search
def search(query_embedding, k=5, source_filter=None):
    query_embedding = prepare_embedding(query_embedding)
    sql = "SELECT source, chunk_index, text, embedding <=> %s AS distance FROM chunks"
    params = [query_embedding]
    if source_filter:
        sql += " WHERE source = %s"
        params.append(source_filter)
    sql += " ORDER BY embedding <=> %s LIMIT %s"
    params.extend([query_embedding, k])
    with conn.cursor() as cur:
        cur.execute(sql, params)
        return cur.fetchall()

<=> is pgvector’s cosine distance operator. Lower is closer. Start with exact search, load representative vectors, then optionally create IVFFlat and measure recall/latency under the same filters. An index is not an authorization control.

Part 4 Indexing strategies.

Two index types you’ll see:

• IVFFlat: partitions vectors into clusters. Fast to build, fast queries, lower recall (you may miss the absolute best match). Evaluate against recall/latency and filtered workloads.
• HNSW: hierarchical navigable small world. Typically a different speed/recall trade-off with higher build/memory demands; benchmark the installed implementation and filter workload rather than treating quality and speed as opposites.

Tune lists (IVFFlat) or m and ef_construction (HNSW) as your dataset grows. Don’t tune them before you have a problem.

The lists parameter in IVFFlat is the number of partitions. Rough rule (from the pgvector docs): lists = rows / 1000 for up to 1M rows, and lists = sqrt(rows) above 1M.

Part 5 Migrating from numpy.

You already have a working RAG from Lesson 05. Two cases:

Case 1: keep the same embedding model

# Load existing vectors
import json
vectors = np.load("vectors.npy", allow_pickle=False)
with open("chunks.json") as f:
    chunks = json.load(f)

# Insert into pgvector
for chunk, vec in zip(chunks, vectors):
    insert_chunk(chunk["source"], chunk["i"], chunk["text"], vec)

No re-embedding is needed only if model revision, preprocessing, normalization, dimension configuration and corpus content are unchanged. Validate metadata, source IDs, exact retrieval results, access filters and deletion behavior before switching reads.

The registered psycopg adapter handles numpy.ndarray or pgvector.Vector; normalize insert and query parameters at the boundary rather than passing Python lists as SQL arrays. The 1536-dimensional column is an illustrative configuration, not a universal embedding size. Update the schema and shape check together for your selected embedding space. The two-dimensional paper exercise does not use this database schema. See the official pgvector Python adapter and psycopg list adaptation.

Case 2: change the embedding model

A vector’s length is only its shape. Equal-dimensional vectors from different embedding models or revisions can inhabit incompatible semantic spaces. Track an embedding-space identifier containing model revision, dimensions, preprocessing and normalization policy. Query and document vectors must use the same space.

Build a new versioned index for a changed space; re-embed the approved corpus and queries consistently. Compare recall on fixed cases, verify filters and deletions, and retain a rollback route. Fixed-dimension columns require an appropriate new column or table when shape changes. Never blindly mix old and new rows because their dimensions happen to match.

Interface/reference limits

Use the workflow reference to distinguish model capability, deployment and harness controls. Provider snippets in this lesson are illustrative, have not been executed against an actual provider in this revision, and require current SDK/model capability verification before separately authorized use.

Primary source checked October 2, 2026: pgvector IVFFlat guidance.