Why You Don't Need Pinecone: Scaling pgvector in PostgreSQL for 10M+ Embeddings with HNSW

The AI boom spawned a wave of specialized vector database startups charging hundreds of dollars per month for hosted vector storage. But separating your vector embeddings into an external database (like Pinecone or Qdrant) introduces complex cross-network latency, duplicate data synchronization pipelines, and two separate databases to back up and secure.

With pgvector and its high-performance HNSW (Hierarchical Navigable Small World) indexing engine, your existing PostgreSQL database can easily store and query over 10 million vector embeddings with sub-10ms query times.

Creating and Querying HNSW Vector Indexes in PostgreSQL

-- Enable pgvector extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Create table with 1536-dimensional embeddings (OpenAI format)
CREATE TABLE enterprise_documents (
  id BIGSERIAL PRIMARY KEY,
  content TEXT NOT NULL,
  metadata JSONB,
  embedding vector(1536)
);

-- Build HNSW index with Cosine similarity distance
CREATE INDEX ON enterprise_documents 
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 200);

-- Hybrid query: filter by metadata and perform similarity search in a single query
SELECT id, content, 1 - (embedding <=> $1) AS similarity_score
FROM enterprise_documents
WHERE metadata->>'department' = 'engineering'
ORDER BY embedding <=> $1
LIMIT 5;