liveBook · Manning

Pgvector

As explained in Designing AI Systems

pgvector is a PostgreSQL extension that provides vector data types, similarity operators, and indexes for storing and querying embeddings.

pgvector adds vector types, similarity operators, and indexes to PostgreSQL. It lets a retrieval service store embeddings alongside document chunks and metadata without introducing a separate vector database. section 5.4.2 section 5.4.3

A vector-store interface separates retrieval from the backend. A pgvector implementation translates that interface into PostgreSQL operations; another backend can replace it without changing the service. Using existing PostgreSQL infrastructure also lets vectors share its backup and monitoring practices. section 5.4.2 section 5.4.3

A chunk table stores readable text, its embedding, document and index identifiers, and JSONB metadata. The vector dimension must match the embedding model: changing models requires updating the column definition and re-embedding the chunks. Indexes on document and index identifiers support deletion during re-ingestion and index removal. section 5.4.3

Listing 5.18 PostgreSQL schema for vector storage with pgvector
CREATE EXTENSION IF NOT EXISTS vector;    #A
 
CREATE TABLE chunks (
    chunk_id VARCHAR(255) PRIMARY KEY,
    document_id VARCHAR(255) NOT NULL,    #B
    index_name VARCHAR(255) NOT NULL,    #B
    chunk_text TEXT NOT NULL,
    embedding vector(1536),    #C
    metadata JSONB DEFAULT '{}',    #D
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
CREATE INDEX idx_chunks_document ON chunks(document_id);
CREATE INDEX idx_chunks_index ON chunks(index_name);
CREATE INDEX idx_chunks_metadata 
➥ON chunks USING gin (metadata);    #E
 
CREATE INDEX idx_chunks_embedding ON chunks
    USING ivfflat (embedding vector_cosine_ops)
    WITH (lists = 100);    #F

The schema enables pgvector, defines a 1,536-dimensional embedding column, and adds an IVFFlat cosine index alongside lifecycle and metadata indexes. section 5.4.3

IVFFlat uses clustering for approximate nearest-neighbor search. The search implementation computes a similarity score from cosine distance, restricts results to a named index, and can add metadata filters. Returning chunk text and metadata with the score avoids a separate lookup for readable content. section 5.4.3 section 5.7

Listing 5.19 pgvectorStore search implementation
class PgvectorStore(VectorStore):
 
    def __init__(self, connection_string: str):
        self.conn = psycopg2.connect(connection_string)
 
    def search(
        self,
        index_name: str,
        query_embedding: List[float],
        top_k: int = 5,
        metadata_filters: Optional[Dict[str, str]] = None,
        score_threshold: Optional[float] = None
    ) -> List[SearchResult]:
 
        query = """
            SELECT chunk_id, document_id, chunk_text, metadata,
                   1 - (embedding <=> %s::vector) AS score
            FROM chunks
            WHERE index_name = %s
        """    #A
        params = [query_embedding, index_name]
 
        if metadata_filters:
            for key, value in metadata_filters.items():
                query += " AND metadata->>%s = %s"
                params.extend([key, value])    #B
 
        if score_threshold:
            query += " AND 1 - (embedding <=> %s::vector) >= %s"
            params.extend
➥([query_embedding, score_threshold])    #C
 
        query += " ORDER BY embedding <=> %s::vector LIMIT %s"
        params.extend([query_embedding, top_k])    #D
 
        cursor = self.conn.cursor()
        cursor.execute(query, params)
 
        return [
            SearchResult(
                chunk_id=row[0], document_id=row[1],
                text=row[2], metadata=row[3], score=row[4]
            )
            for row in cursor.fetchall()
        ]

The Python search method maps the vector-store interface to a PostgreSQL query with cosine scoring and parameterized index and metadata filters. section 5.4.3

With appropriate indexing and memory configuration, pgvector is suited to collections of roughly 10 million vectors. Purpose-built vector databases may scale further, but can add operational complexity, managed-service costs, or vendor lock-in. Hybrid retrieval can combine vector and keyword results using reciprocal rank fusion; keyword search remains optional in the vector-store interface. section 5.4.2 section 5.7

How Designing AI Systems covers it

How deep it goes
Works through a chunk schema, IVFFlat indexing, and a Python search method that translates the vector-store interface into SQL.
Examples
Illustrates a 1,536-dimensional chunk table and a filtered cosine-similarity query.
What you can do after
Apply it via PostgreSQL to store and retrieve embedded chunks with index and metadata filters.
How it connects
Connects embeddings, approximate nearest-neighbor search, metadata filtering, and hybrid search with reciprocal rank fusion.

Also explained in

Compare how 2 books explain pgvector →

Related concepts

This explainer was generated by Manning's knowledge graph from the text of Designing AI Systems. The section links point to the parts of the book it draws on.