Skip to main content
ZICQ

Wiki Infrastructure

pgvector (PostgreSQL Vector Extension)

Infrastructure
Aliases: pgvector PostgreSQL Vector Search pgvector extension ·2026-10-10

pgvector (PostgreSQL Vector Extension)

pgvector is the definitive open-source vector similarity search extension for PostgreSQL. By introducing native vector, halfvec, and sparsevec types paired with HNSW and IVFFlat approximate nearest neighbor (ANN) indexing and C-accelerated distance operators, pgvector allows engineering teams to deploy production AI search within their existing relational database infrastructure at minimal operational overhead.


1. Why Choose pgvector: Architectural Trade-offs

When designing AI architectures for RAG or semantic discovery, engineering teams evaluate between dedicated vector databases (such as Milvus and Qdrant) and relational extensions (Postgres + pgvector):

Dimension Dedicated Vector Databases (Milvus / Qdrant) PostgreSQL + pgvector
Operational Overhead High (standalone clusters, discrete replication, and custom monitoring) Minimal (reuses existing RDS, Supabase, or self-managed PG)
ACID Guarantees Weak / Eventual (dual-writes across vector and relational stores risk drift) Strict ACID (vector embeddings and relational entities commit/rollback atomically)
Complex Filtering & JOINs Limited to basic metadata tag filters; cannot execute deep relational JOINs Native SQL (seamlessly joins across user tables, billing records, and multi-tenant ACLs)
Maximum Scale Hundreds of millions to billions of vectors 1M to 50M vectors (delivering sub-10ms latencies with HNSW and quantization)

2. Core Index Architectures: HNSW vs. IVFFlat

pgvector implements two primary approximate nearest neighbor indexing algorithms:

2.1 HNSW (Hierarchical Navigable Small World)

The gold standard for high-accuracy, low-latency vector graph search:

  • Mechanics: Constructs a multi-layer hierarchy of proximity graphs. Searches traverse greedily from sparse upper layers down to dense lower layers in $O(\log N)$ time complexity.
  • Tuning Knobs:
    • m (maximum bidirectional links per node, default 16; recommended 16–32): Dictates graph connectivity density.
    • ef_construction (search depth during index build, default 64; recommended 64–128): Higher values increase build duration but yield higher recall.
    • hnsw.ef_search (dynamic candidate exploration depth at query time, default 40): Dynamically adjustable per session (e.g., SET hnsw.ef_search = 100; to prioritize recall).
  • Trade-offs: Outstanding recall (>95%–99%) and sub-5ms latency; requires significant RAM to cache graphs in memory.

2.2 IVFFlat (Inverted File Flat)

An inverted index grounded in k-means clustering:

  • Mechanics: Partitions vector space into $K$ Voronoi cells (lists). Queries inspect only the centroids closest to the target vector (probes).
  • Tuning Knobs: Set lists = sqrt(row_count). At query time, adjust precision via SET ivfflat.probes = 10;.
  • Trade-offs: Fast index creation and low memory footprint; requires representative training vectors before build, with recall sensitive to dimensional skew.

3. Distance Metrics & Production SQL

pgvector provides dedicated SIMD-accelerated operators for standard distance metrics:

-- Enable extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Define schema (e.g., 1536-dimensional OpenAI embeddings)
CREATE TABLE knowledge_base (
    id BIGSERIAL PRIMARY KEY,
    tenant_id INT NOT NULL,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    embedding vector(1536)
);

-- Construct HNSW index using cosine distance
CREATE INDEX ON knowledge_base 
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

Distance Operator Reference:

  1. Cosine Distance: <=> (vector_cosine_ops) $$D_{\cos}(u, v) = 1 - \frac{u \cdot v}{|u| |v|}$$ The standard choice for natural language semantic search.
  2. Euclidean Distance (L2): <-> (vector_l2_ops) $$D_{L2}(u, v) = \sqrt{\sum (u_i - v_i)^2}$$
  3. Negative Inner Product: <#> (vector_ip_ops) Returns $- (u \cdot v)$ (because Postgres sorts ASC by default; the negative sign places the largest dot product first). For normalized embeddings, inner product is mathematically equivalent to cosine distance while computing significantly faster.

4. Memory Optimization: halfvec & Binary Quantization

In high-concurrency production deployments, uncompressed high-dimensional vectors consume substantial RAM. pgvector delivers robust quantization mechanisms:

  1. Half-Precision Vectors (halfvec): Compresses 32-bit floats into 16-bit IEEE 754 half-precision values (2 bytes per dimension).
    ALTER TABLE knowledge_base ADD COLUMN embedding_half halfvec(1536);
    
    • Cuts memory and disk storage in half (from 6KB to 3KB per 1536d vector).
    • Real-world benchmarks report less than 0.1% recall regression.
  2. Binary Quantization (bit): Compresses the sign bit of each dimension into a single binary bit, leveraging CPU POPCNT instructions for rapid Hamming Distance (<~>) computation.
    • Yields a $32\times$ memory reduction.
    • Recommended pattern: perform coarse filtering with binary indexes to fetch top-100 candidates, followed by exact re-ranking in memory using full vectors for the top-10.

5. Hybrid Search & Iterative Filtering

5.1 Resolving the "Filter-First" Dilemma

When executing WHERE tenant_id = 101 ORDER BY embedding <=> $1 LIMIT 5, if tenant 101 occupies a tiny fraction of total rows, standard HNSW graph traversal can exhaust its search budget prematurely and return fewer than 5 rows.

  • Iterative Graph Scanning: pgvector 0.7+ introduces iterative graph traversal, expanding search boundaries dynamically until sufficient candidates matching the WHERE predicate are collected.

5.2 Native Hybrid Search with Reciprocal Rank Fusion (RRF)

Combines PostgreSQL Full-Text Search (FTS) and semantic vector similarity in a single query:

WITH semantic_search AS (
    SELECT id, RANK() OVER (ORDER BY embedding <=> $1) AS rank_sem
    FROM knowledge_base
    WHERE tenant_id = $2
    ORDER BY embedding <=> $1
    LIMIT 50
),
text_search AS (
    SELECT id, RANK() OVER (ORDER BY ts_rank_cd(to_tsvector('english', content), plainto_tsquery('english', $3)) DESC) AS rank_text
    FROM knowledge_base
    WHERE tenant_id = $2 AND to_tsvector('english', content) @@ plainto_tsquery('english', $3)
    LIMIT 50
)
SELECT COALESCE(s.id, t.id) AS id,
       COALESCE(1.0 / (60 + s.rank_sem), 0.0) +
       COALESCE(1.0 / (60 + t.rank_text), 0.0) AS rrf_score
FROM semantic_search s
FULL OUTER JOIN text_search t ON s.id = t.id
ORDER BY rrf_score DESC
LIMIT 10;

6. Sizing Rules & Production Tuning

  1. HNSW In-Memory Requirement: HNSW graphs must reside fully inside RAM (shared_buffers or operating system page caches). If disk I/O thrashing occurs, query latency deteriorates from 5ms to hundreds of milliseconds.
    • Memory sizing formula: $\text{Index Size} \approx (d \times 4 + m \times 8) \times N \text{ bytes}$.
    • Example: 1 million 1536-dimensional vectors ($m=16$) require $\approx 6.27\text{GB}$ of dedicated index RAM.
  2. Build Configuration: Before indexing, temporarily increase maintenance memory: SET maintenance_work_mem = '8GB'; and allocate max_parallel_maintenance_workers to match physical CPU cores to accelerate graph construction by several multiples.