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); #FThe 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

