Storing Embeddings in a Database

The in-memory search engine from Lesson 5 keeps every document and vector in a Python list, which works for a demo but not for an application that needs to persist data across restarts, handle more documents than fit comfortably in memory, or let multiple processes query the same index. This lesson covers storing embeddings in an actual database — using PostgreSQL with the pgvector extension as the running example, since it is a widely used, realistic choice that lets a team keep vector search in the same database as the rest of their application data, without operating a second specialized system.

Why a Real Database Instead of an In-Memory List

Three concrete limitations of the in-memory approach motivate this move:

1. Persistence. An in-memory list disappears when the process restarts. A database keeps the data durably on disk, with the same reliability guarantees (backups, replication) as any other application data.
2. Scale beyond available RAM. A database can index and query far more vectors than would comfortably fit in one process's memory, and can page data in from disk as needed.
3. Concurrent access. A web application typically has multiple worker processes handling requests simultaneously. A shared database lets all of them query the same up-to-date index; a Python list held in one process's memory cannot be shared that way without extra infrastructure.

PostgreSQL With pgvector: The Idea

pgvector is a PostgreSQL extension that adds a vector column type and similarity operators directly usable in SQL. The core idea: store each document's embedding as a native column value, and let the database compute similarity as part of an ordinary SELECT query.

Note: pgvector is a real, actively maintained open-source extension, but exact installation steps, operator names, and default index parameters can change between versions. Confirm the current syntax against the pgvector project documentation (and your hosting provider's docs, if using a managed Postgres service) before running this in production.

A representative schema:

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
    id          BIGSERIAL PRIMARY KEY,
    external_id TEXT UNIQUE NOT NULL,
    content     TEXT NOT NULL,
    metadata    JSONB DEFAULT '{}',
    embedding   VECTOR(1536),
    model_name  TEXT NOT NULL,
    created_at  TIMESTAMPTZ DEFAULT now()
);

Why each column exists: external_id is a stable identifier from the source system (a ticket ID, a URL) used to update or delete a specific document later without depending on the database's internal auto-incrementing id. content keeps the original text so a search result can be displayed. metadata is a JSONB column for structured attributes (category, date, author) used for filtering — the subject of Lesson 7. embedding is the vector itself, declared with a fixed dimension (1536 here, matching a chosen embedding configuration) — pgvector requires the dimension to be fixed per column. model_name records which embedding model (and configuration) produced the vector.

Why store model_name at all? This directly addresses a mistake flagged in Lesson 2: vectors from different models, or the same model with different dimensions settings, are not comparable. Recording which model produced each stored vector makes it possible to detect and safely handle a migration — for example, re-embedding everything after switching models — instead of silently mixing incompatible vectors in the same similarity search.

Inserting Embeddings

INSERT INTO documents (external_id, content, metadata, embedding, model_name)
VALUES (
    'article-482',
    'Return policy for electronics: items can be returned within 30 days.',
    '{"category": "returns", "published_at": "2026-01-15"}',
    '[0.012, -0.034, 0.087, ...]',
    'text-embedding-4'
);

The vector is written as a bracketed list of numbers, which pgvector parses into its native vector type. In real code this string is generated from the Python list returned by the embeddings API — never typed by hand.

import json


def insert_document(conn, external_id: str, content: str, metadata: dict,
                     embedding: list[float], model_name: str) -> None:
    """Insert one document row. `conn` is a DB-API style connection
    (e.g. psycopg2 or psycopg3) with a `.cursor()` method.
    """
    vector_literal = "[" + ",".join(str(x) for x in embedding) + "]"
    with conn.cursor() as cur:
        cur.execute(
            """
            INSERT INTO documents (external_id, content, metadata, embedding, model_name)
            VALUES (%s, %s, %s, %s, %s)
            ON CONFLICT (external_id) DO UPDATE
                SET content = EXCLUDED.content,
                    metadata = EXCLUDED.metadata,
                    embedding = EXCLUDED.embedding,
                    model_name = EXCLUDED.model_name
            """,
            (external_id, content, json.dumps(metadata), vector_literal, model_name),
        )
    conn.commit()

ON CONFLICT (external_id) DO UPDATE makes this function an "upsert" — inserting a new row if external_id has not been seen, or updating the existing row if it has. Why does this matter? Real content changes over time: a document gets edited, re-embedded, or its metadata updated. Without an upsert, re-indexing the same source document would either fail (if external_id has a uniqueness constraint, which it does here) or create duplicate rows with stale copies alongside the new one. The upsert pattern makes re-indexing idempotent — running it twice with the same input produces the same end state as running it once.

Querying by Similarity

pgvector provides operators for common distance measures directly in SQL. The <=> operator computes cosine distance (1 minus cosine similarity, so smaller is more similar):

SELECT external_id, content, 1 - (embedding <=> %(query_vector)s) AS similarity
FROM documents
WHERE model_name = 'text-embedding-4'
ORDER BY embedding <=> %(query_vector)s
LIMIT 5;

Note: Operator names (<=> for cosine distance, <-> for Euclidean, <#> for negative inner product) and their exact semantics are specific to pgvector's current version — confirm against the extension's documentation before relying on them, as these details are exactly the kind of thing that can change between major versions.

Why ORDER BY ... LIMIT instead of fetching everything and sorting in Python? Doing the distance computation and sorting inside the database lets it use an index (covered next) to avoid scanning every row, and avoids pulling potentially millions of vectors across the network into the application process just to sort them there. This is the same principle as Lesson 4's point about vectorized batch comparison — push the numeric work to where it can be done most efficiently, which for a large, persisted collection is the database itself.

def search_documents(conn, query_embedding: list[float], model_name: str, limit: int = 5) -> list[dict]:
    vector_literal = "[" + ",".join(str(x) for x in query_embedding) + "]"
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT external_id, content, 1 - (embedding <=> %s) AS similarity
            FROM documents
            WHERE model_name = %s
            ORDER BY embedding <=> %s
            LIMIT %s
            """,
            (vector_literal, model_name, vector_literal, limit),
        )
        rows = cur.fetchall()
    return [{"id": r[0], "content": r[1], "similarity": float(r[2])} for r in rows]

Indexing for Speed at Scale

Without an index, pgvector computes the exact distance to every row for every query — correct, but slow once a table has hundreds of thousands or millions of rows. pgvector supports approximate-nearest-neighbor indexes (such as HNSW) that trade a small amount of accuracy for a large speed improvement:

CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops);

Note: Index types available (HNSW, IVFFlat), their build/query parameters, and which one is recommended by default have changed across pgvector releases. Check current documentation for the recommended index type and parameters for your version and dataset size before creating one in production.

Why "approximate" is an acceptable trade here: An approximate index occasionally misses the single mathematically closest vector in favor of one that is nearly as close, in exchange for query times that stay fast as the table grows into the millions of rows. For semantic search, where the difference between the 1st and 3rd most similar result is rarely meaningful to the end user, this trade is almost always worth it — an exact index only makes sense for small tables or when perfect recall is a hard requirement, which Lesson 9's evaluation methodology can help confirm one way or the other for a specific application.

Dedicated Vector Databases: A Brief Comparison

pgvector is one option among several. Dedicated vector databases (such as Pinecone, Weaviate, Qdrant, or Milvus) are purpose-built for vector search and can offer more specialized indexing options and scaling characteristics at very large sizes.

When to choose which: If an application already stores its data in PostgreSQL and the vector collection is in the range of thousands to low millions of documents, pgvector avoids the operational cost of running and syncing a second database — this is the practical default for most applications built with this course's stack. A dedicated vector database becomes more attractive at very large scale (tens of millions of vectors and beyond) or when a team's primary datastore is not relational at all.

Common Mistakes

• Storing vectors as plain text or JSON in a generic column instead of a native vector type. This works for storage but loses the ability to use similarity operators and specialized indexes directly in SQL, forcing similarity computation back into the application.
• Not recording which embedding model produced each vector. As covered above, this makes it easy to silently mix incompatible vectors after a model change, corrupting every similarity comparison that spans the mix.
• Building an index before understanding the accuracy/speed trade-off it makes. Approximate indexes are usually the right choice at scale, but blindly applying default parameters without reading current documentation can produce disappointing recall on a specific dataset.

Best Practices

• Use upserts keyed by a stable external ID, not raw inserts, so re-indexing content is idempotent and safe to run repeatedly.
• Store metadata alongside embeddings in the same row, not in a separate system, so filtering (Lesson 7) can be combined with similarity search in a single query.
• Confirm operator names, index types, and default parameters against current documentation before deploying, since this is precisely the kind of database-specific syntax that changes between versions, as flagged throughout this lesson.