Databases & System Design•Part 5: Real-Time Architecture, Replication & Scale (51 - 60)
Vector Databases (pgvector, Pinecone) & HNSW:
In Simple Words: Indexes high-dimensional AI embeddings. HNSW (Hierarchical Navigable Small World) provides sub-millisecond Approximate Nearest Neighbor (ANN) search across millions of vectors.
sql
-- PostgreSQL pgvector setup:
CREATE EXTENSION vector;
CREATE TABLE documents (
id SERIAL PRIMARY KEY,
content TEXT,
embedding vector(1536) -- OpenAI embedding size
);
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);
-- Similarity query using cosine distance (<=>):
SELECT content, 1 - (embedding <=> '[0.012, -0.045, ...]') AS similarity
FROM documents
ORDER BY embedding <=> '[0.012, -0.045, ...]'
LIMIT 5;