pgvector

pgvector is the open-source Postgres extension that adds vector similarity search to ordinary Postgres tables. It has become the default starting point for most US teams building RAG and semantic search in 2026 — not because it is the most scalable option, but because it lets you do vector search without standing up new infrastructure, without learning a new query language and without giving up the relational features (joins, transactions, foreign keys) that make Postgres reliable.

What pgvector adds:

• A new vector column type with a configurable dimensionality (typically 768, 1536 or 3072 to match common embedding models).
• Distance operators: <-> (Euclidean), <#> (negative inner product), <=> (cosine).
• Two index types: IVFFlat (older, fast to build, decent recall) and HNSW (newer, slower to build, much higher recall — the default in 2026).
• Standard SQL composability — vector queries can be combined with WHERE clauses, JOINs and any other relational logic.

A typical pgvector query in production looks like a normal SQL statement: SELECT id, title and content FROM your documents table WHERE tenant_id matches the current tenant and status is 'published', ORDER BY the embedding column compared to the query vector with the cosine distance operator, LIMIT 10. The vector search composes naturally with the other WHERE clauses; no special syntax required.

Why this is the workhorse pattern:

• No separate vector DB to operate — one less moving part, one less bill.
• Rich filtering for free — multi-tenant scoping, status checks, date ranges, all in the same query.
• Joins — combine vector search results with related tables (authors, categories, tags) in a single query.
• Transactional consistency — a document and its embedding can be inserted in the same transaction; no eventual-consistency drift.
• Existing Postgres tooling — backups, replication, monitoring, migrations all work as-is.

The 2026 ecosystem around pgvector:

• Supabase — Postgres-as-a-service with pgvector enabled out of the box; popular for Next.js/Vercel-style stacks.
• Neon — serverless Postgres with pgvector and branching; gaining traction.
• AWS RDS for Postgres, Aurora — pgvector available; the enterprise default.
• Self-hosted Postgres — install the extension and you are done.
• pgvectorscale (TimescaleDB) — extension that scales pgvector to hundreds of millions of vectors with disk-based indexing.

The honest limitations:

• Scale ceiling — pgvector with HNSW handles tens of millions of vectors comfortably. Beyond ~50–100M, dedicated vector DBs (Pinecone, Qdrant, Milvus) start winning on cost and performance.
• Index build time — HNSW indexes can take hours to build on large datasets.
• Memory pressure — HNSW indexes live in memory for fast queries; budget for a beefy Postgres instance or use pgvectorscale for disk-based variants.
• Limited hybrid search — combining BM25 and vector requires extensions or application-level merging.

For a US team in 2026, the rule of thumb is: start with pgvector if Postgres is already in your stack; only graduate to a dedicated vector DB when measurement shows pgvector is the bottleneck. Most products never need the upgrade.