At 2:15 AM last month, our on-call PagerDuty alert went off. Our standalone vector database cluster had started throwing 504 gateway timeouts under a sudden 4,000 RPM traffic spike.
Worse, our asynchronous CDC sync pipeline had lagged by 45 minutes, serving stale and corrupted search results to thousands of active users. We tore down that fragile two-database setup the next sprint. By migrating our vector operations directly into PostgreSQL with the pgvector extension, we eliminated data sync lag forever.
Quick Start (5 Min): Storing and Querying Embeddings
To run vector similarity queries, enable the extension and assign the vector data type to your column. Make sure to specify the exact dimensions output by your embedding model—such as 1536 for OpenAI’s text-embedding-3-small or 384 for lightweight Hugging Face models like all-MiniLM-L6-v2.
1. Enable the Extension and Create the Table
-- Enable pgvector extension
CREATE EXTENSION IF NOT EXISTS vector;
-- Create a table for documents with 1536-dimensional embeddings
CREATE TABLE documents (
id BIGSERIAL PRIMARY KEY,
title TEXT NOT NULL,
content TEXT NOT NULL,
category VARCHAR(50),
embedding vector(1536),
created_at TIMESTAMPTZ DEFAULT NOW()
);2. Insert Vector Data
-- Insert a record with its corresponding embedding vector
INSERT INTO documents (title, content, category, embedding)
VALUES (
'Postgres Connection Pooling',
'A deep dive into managing connection saturation using PgBouncer.',
'database',
'[-0.0023, 0.0142, -0.0311, ...]' -- 1536 float values
);3. Run Nearest Neighbor Search
-- Retrieve the top 5 most semantically relevant documents via Cosine Similarity
SELECT
id,
title,
1 - (embedding <=> '[-0.0023, 0.0142, -0.0311, ...]') AS similarity_score
FROM documents
ORDER BY embedding <=> '[-0.0023, 0.0142, -0.0311, ...]'
LIMIT 5;Deep Dive: Vector Math and Index Selection
Running a dual-database architecture—PostgreSQL for relational data and Pinecone, Qdrant, or Milvus for vectors—creates operational friction. You double your infrastructure costs, sacrifice transactional consistency, and add network hops. With pgvector, you retain ACID compliance, join embeddings directly with customer tables, and enforce strict foreign key constraints.
Understanding Distance Operators
Choose your operator based on how your embedding model calculates distance:
<=>Cosine Distance: Calculates angular distance regardless of vector magnitude. This is the right choice for OpenAI, Cohere, and most modern LLM text embeddings.<->Euclidean / L2 Distance: Measures direct geometric distance. Common in computer vision and audio classification models.<#>Negative Inner Product: Computes the negative dot product. Use this for pre-normalized unit vectors where dot product equals cosine similarity, saving computation cycles.
Choosing the Right Index: HNSW vs. IVFFlat
Exact nearest neighbor scans (sequential scans) work fine below 10,000 rows. Beyond that threshold, query latency climbs from 15ms to several seconds. Production workloads require an Approximate Nearest Neighbor (ANN) index.
-- 1. HNSW Index (Hierarchical Navigable Small World) - Recommended for Production
CREATE INDEX idx_documents_embedding_hnsw ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
-- 2. IVFFlat Index (Inverted File Flat) - Lower RAM, requires data before creation
CREATE INDEX idx_documents_embedding_ivfflat ON documents
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);| Index Type | Build Speed | Query Latency (1M rows) | RAM Consumption | Recall Rate |
|---|---|---|---|---|
| HNSW | Slower (~15-30 min / 1M vectors) | < 8ms | ~1.5 GB / 1M vectors | 97% – 99% |
| IVFFlat | Fast (~2-5 min / 1M vectors) | ~35-70ms | ~300 MB / 1M vectors | 85% – 92% |
Default to HNSW for user-facing applications. Unlike IVFFlat, HNSW builds reliably on empty tables, tolerates continuous writes without recall degradation, and delivers consistent sub-10ms response times.
Advanced Usage: Python Pipeline and Hybrid Search
Pure vector search often struggles with exact keyword hits like invoice numbers, model SKUs, or specialized product codes. Combining PostgreSQL full-text search (BM25-style tsvector) with dense semantic search via Reciprocal Rank Fusion (RRF) delivers the best search quality across both categories.
Production Hybrid Search Query (Reciprocal Rank Fusion)
WITH semantic_search AS (
SELECT id, RANK() OVER (ORDER BY embedding <=> $1) AS rank
FROM documents
ORDER BY embedding <=> $1
LIMIT 20
),
keyword_search AS (
SELECT id, RANK() OVER (ORDER BY ts_rank_cd(to_tsvector('english', content), query) DESC) AS rank
FROM documents, plainto_tsquery('english', $2) query
WHERE to_tsvector('english', content) @@ query
LIMIT 20
)
SELECT
d.id,
d.title,
COALESCE(1.0 / (60 + s.rank), 0.0) +
COALESCE(1.0 / (60 + k.rank), 0.0) AS rrf_score
FROM documents d
LEFT JOIN semantic_search s ON d.id = s.id
LEFT JOIN keyword_search k ON d.id = k.id
WHERE s.id IS NOT NULL OR k.id IS NOT NULL
ORDER BY rrf_score DESC
LIMIT 10;Python Integration with Psycopg 3
import psycopg
from pgvector.psycopg import register_vector
from openai import OpenAI
client = OpenAI(api_key="your-api-key")
def get_embedding(text: str) -> list[float]:
response = client.embeddings.create(
input=text,
model="text-embedding-3-small"
)
return response.data[0].embedding
# Connect to PostgreSQL and query 1536-dimensional embeddings
with psycopg.connect("postgresql://postgres:secret@localhost:5432/app_db") as conn:
register_vector(conn)
query_text = "How do I tune PgBouncer connection limits?"
query_vector = get_embedding(query_text)
with conn.cursor() as cur:
cur.execute("""
SELECT id, title, content
FROM documents
ORDER BY embedding <=> %s::vector
LIMIT 3;
""", (query_vector,))
for doc_id, title, snippet in cur.fetchall():
print(f"[{doc_id}] {title}: {snippet[:80]}...")Practical Tips: Surviving Production Traffic
1. Allocate maintenance_work_mem During Indexing
HNSW index builds require significant memory during graph construction. The default maintenance_work_mem (often 64MB) will force Postgres to swap temporary buffers to disk, turning a 10-minute build into a 4-hour stall. Bump it for your migration session:
SET maintenance_work_mem = '4GB';
CREATE INDEX CONCURRENTLY idx_documents_embedding_hnsw
ON documents USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);2. Use Partial Indexes for Multi-Tenant Workloads
Do not scan a global vector index with a generic WHERE tenant_id = 42 filter. Postgres will traverse the global HNSW graph first and prune non-matching records afterwards, tanking query performance for smaller tenants.
Create tenant-specific or filtered partial indexes instead:
-- Dedicated index partition for enterprise tenants
CREATE INDEX idx_tenant_42_vectors ON documents
USING hnsw (embedding vector_cosine_ops)
WHERE tenant_id = 42;3. Pin Vector Indexes in Shared Buffers with pg_prewarm
HNSW graph traversal requires random memory page lookups. If cold pages are evicted from RAM to NVMe storage, query latency can jump from 6ms to 250ms. Preload active indexes into cache immediately after database restarts or deployments:
CREATE EXTENSION IF NOT EXISTS pg_prewarm;
SELECT pg_prewarm('idx_documents_embedding_hnsw', 'buffer', 'main');
