Builders Spotlight — pgvector

pgvector

An open-source PostgreSQL extension that adds native vector search capabilities, created by Andrew Kane in 2021 and since shaped by contributors including Heikki Linnakangas and Jonathan Katz.

The problem it set out to solve

Machine learning teams had to maintain separate infrastructure: PostgreSQL for transactional data and a specialized vector database for embeddings. This meant dual data pipelines, dual consistency headaches, and operational complexity that felt unnecessary. The insight was simple but powerful: if you’re already using Postgres, why should vectors be exiled to a separate system?

The key insight

Vector search doesn’t require a fundamentally different database architecture—it requires a different index structure. By adding a vector data type and specialized indexing (HNSW, IVFFlat) to PostgreSQL, you can treat embeddings as a first-class citizen alongside your other data. This collapses the data stack. You get ACID transactions, SQL joins, and vector similarity search in one place. The trade-off is clear: you’re not optimizing only for vector workloads anymore, but for most applications, that’s actually the right trade-off.

How it works (in plain terms)

pgvector adds a vector column type to Postgres that stores fixed-dimensional embeddings. You create an index on the vector column using either HNSW (hierarchical navigable small world, fast but memory-hungry) or IVFFlat (inverted file with flat lists, faster to build and lighter on memory, but lower recall at the same speed). Queries use SQL operators: <-> for L2 distance, <=> for cosine distance, and <#> for negative inner product. The beauty is that you can now join vector search results with your regular relational data in a single query—no network round-trips, no application-level fusion logic.

Note on pgvector security: Run v0.8.7 or later. Three CVEs this year hit the index build paths: CVE-2026-3172 (HNSW parallel build buffer overflow, 0.6.0–0.8.1), CVE-2026-18022 (IVFFlat integer wraparound, 32-bit only, before 0.8.6), and CVE-2026-103484 (IVFFlat out-of-bounds write, before 0.8.7). All three need a database user who can build indexes, but the last two can lead to arbitrary code execution. Managed Postgres providers ship their own pgvector builds, so check the version yours reports with SELECT extversion FROM pg_extension WHERE extname = 'vector';.

What it looks like in practice

-- Create table with embeddings
CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  content TEXT,
  category TEXT,
  embedding vector(1536)
);

-- Add index for fast search (the operator class must match the query operator)
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

-- Search + filter in one query ($1 is the query embedding)
SELECT id, content, embedding <=> $1 AS distance
FROM documents
WHERE category = 'important'
ORDER BY embedding <=> $1
LIMIT 10;

Why it matters

• Operational simplicity — One database to monitor, backup, and scale instead of juggling Postgres + vector DB. Your existing Postgres skills transfer directly.
• Strong consistency — ACID transactions mean your embeddings and metadata stay synchronized. No eventual-consistency surprises when a vector DB lags behind Postgres.
• SQL composability — You can join embeddings with relational queries, filter by metadata before or after vector search, and use window functions on results. This is genuinely more expressive than most specialized vector DBs.

Where to go next

• pgvector GitHub — Documentation, installation, and performance tuning guides
• pgvector v0.5.0: Faster semantic search with HNSW indexes — Supabase’s benchmarks from when HNSW landed