pg_vectorsearch: A High-Velocity Alternative to pgvector's IVFFlat and HNSW

pg_vectorsearch is a specialized PostgreSQL extension engineered for high-performance Approximate Nearest Neighbor (ANN) search. It introduces the PRISM index, designed to outperform traditional indexing methods in both speed and resource efficiency.

[!WARNING] Migration Note: Upgrading between different prerelease versions may be problematic. It is recommended to simply update drop and recreate your indexes and the extension entirely.

---

🚀 The PRISM Index

PRISM (Partitioned Routing Index for Similarity Matching) is a native PostgreSQL index access method. While it shares a conceptual lineage with pgvector's IVFFlat, it incorporates cutting-edge partitioning, routing, and quantization strategies.

Implemented in C, PRISM ensures deep integration with the database engine for maximum throughput.

Performance Benchmarks

PRISM is designed to be significantly faster than both IVFFlat and HNSW during both the indexing phase and the query phase.

Technical Architecture

To achieve these gains, PRISM utilizes a hierarchical centroid tree to organize vectors into clusters. This tree is kept "warm" via the PostgreSQL buffer cache.

Key Technical Pillars:

• Quantization: Uses RaBitQ, which provides theoretical error bounds.
• Hardware Acceleration: SIMD-optimized distance calculations supporting AVX2, AVX512, and NEON.
• Dynamic Management: Supports INSERT, UPDATE, and DELETE operations.
• Maintenance: Uses prism_rebalance to handle posting-list splits.

The Search Workflow

The search process follows a multi-stage pipeline to minimize the number of full-precision distance calculations:

The RaBitQ mechanism allows the system to mathematically prove a vector cannot be a nearest neighbor using the formula: $\text{Estimated Distance} - \text{Error Bound} > \text{Current Best Distance}$ If this condition is true, the vector is skipped, drastically reducing the reranking workload.

---

🛠️ Implementation Guide

Depending on your current setup, you can integrate pg_vectorsearch in two ways:

Option 1: Using Native Types

Use this path for new projects to leverage vec32 (float32) or vec16 (float16) columns.

• Install the extension
• Define table with vec32 or vec16
• Populate data
• Create PRISM index

CREATE EXTENSION pg_vectorsearch;

-- Use STORAGE PLAIN to keep vectors inline. 
-- EXTERNAL (default) uses TOAST for values > 2kB, 
-- which may add hidden fetch costs during reranking.
CREATE TABLE items (
    id serial PRIMARY KEY,
    embedding vec32(3) STORAGE PLAIN
);

-- Load data before indexing for optimal sizing
INSERT INTO items (embedding) 
SELECT ARRAY[i::real, (i % 7)::real, (i % 5)::real]::vec32 
FROM generate_series(1, 1000) i;

-- Create the index using L2 operator class
CREATE INDEX ON items USING prism (embedding vec32_l2_ops);

-- Query using the '-' operator (nprobe=40 for ~0.95 recall)
SET prism.nprobe = 40;
SELECT * FROM items ORDER BY embedding - '[3,1,2]' LIMIT 5;

Option 2: Integrating with Existing pgvector Tables

If you already use pgvector, you don't need to migrate your data. vec32 and vec16 are binary-compatible with vector and halfvec.

-- Existing pgvector setup
CREATE EXTENSION vector;
CREATE TABLE documents (
    id bigserial PRIMARY KEY,
    body text,
    embedding vector(384)
);
CREATE INDEX documents_embedding_hnsw ON documents USING hnsw (embedding vector_l2_ops);

-- Add pg_vectorsearch alongside pgvector
CREATE EXTENSION pg_vectorsearch;

-- Build a PRISM index on the existing pgvector column
CREATE INDEX documents_embedding_prism ON documents USING prism (embedding vec32_l2_ops);

-- The PostgreSQL planner will typically prefer PRISM over HNSW due to efficiency.
EXPLAIN (COSTS OFF) 
SELECT id, body FROM documents 
ORDER BY embedding - (SELECT embedding FROM documents WHERE id = 1) 
LIMIT 5;

Summary of Operator Compatibility

Both native and compatible paths utilize the same operators:

• - : L2 Distance
• # : Inner Product
• = : Cosine Distance (via specific operator classes)