pgvector

Overview

pgvector is an open-source extension for PostgreSQL that adds support for vector operations and similarity search. It enables storing, indexing, and querying vector data directly within your PostgreSQL database.

With pgvector, you can build AI applications such as semantic search, recommendation systems, image search, and natural language processing (NLP) while leveraging your existing PostgreSQL infrastructure.

Details

PostgreSQL Extension

pgvector is implemented as a standard PostgreSQL extension, supporting PostgreSQL 13 and later. It can be easily added to existing PostgreSQL databases and allows vector data manipulation using SQL queries.

Vector Data Types

pgvector supports multiple vector data types:

• vector: Single-precision floating-point (float32) vectors
• halfvec: Half-precision floating-point (float16) vectors (reduced storage size)
• bit: Binary vectors
• sparsevec: Sparse vectors (optimized for data with many zero values)

Index Types

pgvector provides two indexing algorithms for fast approximate nearest neighbor search:

HNSW (Hierarchical Navigable Small World)

• Constructs a multilayer graph structure
• Generally offers better query performance (speed-recall tradeoff) than IVFFlat
• Slower build times and higher memory usage
• Can create indexes on empty tables (no training required)
• Supports up to 4,000 dimensions (half-precision) or 64,000 dimensions (binary quantization)

IVFFlat (Inverted File Flat)

• Partitions vectors into lists using k-means clustering
• Faster build times and lower memory usage than HNSW
• Lower query performance compared to HNSW
• Recommended to create index after table has data

Distance Functions

pgvector supports the following distance functions:

• L2 distance (Euclidean): <->
• Inner product: <#> (returns negative inner product)
• Cosine distance: <=>
• L1 distance (Manhattan): <+>
• Hamming distance (for binary vectors): <~>
• Jaccard distance (for binary vectors): <%>

Latest Version Improvements (v0.8.0)

pgvector 0.8.0, released in 2024, brings significant performance improvements especially for queries with filters:

• Iterative index scans provide up to 9.4x speedup for filtered queries
• PostgreSQL's query planner selects more efficient indexes
• Improved recall for both HNSW and IVFFlat after filtering

Benefits

• Leverage Existing Infrastructure: Easy integration with existing PostgreSQL databases
• ACID Compliance: Inherits PostgreSQL's reliability and transaction guarantees
• SQL Interface: Vector operations through standard SQL queries
• Multi-language Support: Accessible from any language with a PostgreSQL client
• Scalability: Horizontal scaling via replication, Citus, and other approaches
• High Performance: Latest versions can handle tens of millions of high-dimensional vectors
• Unified Development: Manage relational and vector data in a single database

Drawbacks

• Compared to Specialized Vector Databases: Lacks some advanced features of dedicated vector databases
• Memory Usage: Large HNSW indexes consume significant memory
• Build Time: Index construction can be time-consuming for large datasets
• Tuning Required: Parameter adjustment needed for optimal performance

Key Links

• GitHub - Source code and documentation
• PostgreSQL Official Announcement - Latest release information

Usage Examples

Basic Usage

-- Enable the extension
CREATE EXTENSION vector;

-- Create table with vector column
CREATE TABLE items (
  id bigserial PRIMARY KEY,
  embedding vector(3)
);

-- Insert vector data
INSERT INTO items (embedding) VALUES 
  ('[1,2,3]'), 
  ('[4,5,6]');

-- Nearest neighbor search by L2 distance
SELECT * FROM items 
ORDER BY embedding <-> '[3,1,2]' 
LIMIT 5;

-- Search by cosine similarity
SELECT *, 1 - (embedding <=> '[3,1,2]') AS cosine_similarity 
FROM items 
ORDER BY embedding <=> '[3,1,2]' 
LIMIT 5;

Creating HNSW Index

-- Basic HNSW index
CREATE INDEX ON items USING hnsw (embedding vector_l2_ops);

-- HNSW index with parameters
CREATE INDEX ON items USING hnsw (embedding vector_l2_ops) 
WITH (m = 16, ef_construction = 64);

-- Set search parameters
SET hnsw.ef_search = 100;

Creating IVFFlat Index

-- IVFFlat index with 100 lists
CREATE INDEX ON items USING ivfflat (embedding vector_l2_ops) 
WITH (lists = 100);

-- Set search parameters
SET ivfflat.probes = 10;

Search with Filtering

-- Nearest neighbor search with category filter
SELECT * FROM items 
WHERE category_id = 123 
ORDER BY embedding <-> '[3,1,2]' 
LIMIT 5;

-- Create partial index for specific category
CREATE INDEX ON items USING hnsw (embedding vector_l2_ops) 
WHERE (category_id = 123);

Advanced Usage

-- Index with binary quantization
CREATE INDEX ON items USING hnsw ((binary_quantize(embedding)::bit(3)) bit_hamming_ops);

-- Using sparse vectors
CREATE TABLE sparse_items (
  id bigserial PRIMARY KEY,
  embedding sparsevec(5)
);

INSERT INTO sparse_items (embedding) VALUES 
  ('{1:1,3:2,5:3}/5');

-- Vector aggregation
SELECT category_id, AVG(embedding) 
FROM items 
GROUP BY category_id;

Performance Tuning

-- Increase maintenance work memory for faster index builds
SET maintenance_work_mem = '8GB';

-- Set parallel workers
SET max_parallel_maintenance_workers = 7;

-- Monitor index build progress
SELECT phase, round(100.0 * blocks_done / nullif(blocks_total, 0), 1) AS "%" 
FROM pg_stat_progress_create_index;

pgvector is an excellent choice for adding powerful vector search capabilities while leveraging existing PostgreSQL infrastructure. With ACID compliance, SQL interface, and a mature ecosystem, it enables building enterprise-grade AI applications.