pgvector

pgvector is the open-source vector retrieval extension for PostgreSQL (GitHub 5k+ stars). Adds direct support for vector type + ivfflat / hnsw indexes + <=> cosine distance operator to PostgreSQL.

Why choose pgvector

• Zero ops cost: teams already using PostgreSQL don't need new components (Milvus / Qdrant).
• Transactional consistency: vector data and metadata update / rollback together, no dual-write sync.
• SQL expressive power: vector retrieval combines with regular filtering (WHERE category='xx' AND created_at > ...) in one WHERE.
• Ecosystem: Supabase / Neon / RDS / self-hosted PG all support.

Use cases

• Projects already on PG: embedding + metadata all in PG, no ETL.
• Lightweight RAG: data scale < 10M vectors, pgvector performance is enough.
• Don't want new components: compliance / ops cost sensitive.

Performance

• 100k scale: millisecond response, IVFFlat enough.
• Million scale: HNSW index, ~10-50ms recall.
• 10M+: recommend switching to dedicated vector DB (Milvus / Qdrant).

Usage example

-- Enable extension
CREATE EXTENSION vector;

-- Add column
ALTER TABLE articles ADD COLUMN embedding vector(1536);

-- Create HNSW index
CREATE INDEX ON articles USING hnsw (embedding vector_cosine_ops);

-- Retrieval
SELECT title, embedding <=> '[0.1, 0.2, ...]'::vector AS dist
FROM articles
WHERE category = 'AI'
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 10;

Limitations

• Large-scale performance: compared to Milvus / Qdrant, much slower at hundred-million scale.
• Filter performance: complex metadata filter goes through PG index, vector recall goes through HNSW, combined queries sometimes suboptimal.
• Operations: PG itself is heavy (connections, WAL, backup), adding pgvector doesn't make ops easier.

Compared with dedicated vector DBs

Practical experience

• Dimension ≤ 1536: OpenAI text-embedding-3-small is 1536-dim, pgvector performance is fine.
• HNSW vs IVFFlat: HNSW has higher recall but slower build, more memory; IVFFlat is fast but needs training data. < 1M data prefer IVFFlat, > 1M use HNSW.
• Half-precision storage: halfvec(1536) saves half disk / memory, near-zero performance loss.
• Filter order: filter WHERE first, then vector retrieval; reverse will be slow.

Alternatives

• pgvector + pgvectorscale (Timescale): ten-million vector optimized version.
• PostgresML: stuffs ML model inference into PG, even embedding is built-in.
• Direct migration: over ten-million vectors migrate to Qdrant / Milvus.