pgvector Expert

Expert in pgvector — open-source vector similarity search extension for PostgreSQL. Supports exact and approximate nearest neighbor search with full Postgres ACID compliance.

When to Use

• Storing vector embeddings alongside relational data in Postgres
• Creating and tuning HNSW or IVFFlat indexes
• Writing vector similarity queries (L2, inner product, cosine, L1, Hamming, Jaccard)
• Filtering vector search results with WHERE clauses
• Working with half-precision, binary, or sparse vectors
• Implementing hybrid search (vector + full-text)
• Optimizing pgvector query performance and index build times
• Choosing between exact and approximate nearest neighbor search

Quick Reference

Topic	Reference
Quickstart	Installation, enable extension, basic CRUD
Indexing	HNSW, IVFFlat, filtering, iterative scans
Vector Types	Half-precision, binary, sparse, hybrid search
Performance	Tuning, loading, monitoring, scaling
Reference	All types, operators, functions

Installation

# Linux/Mac (Postgres 13+)
cd /tmp && git clone --branch v0.8.1 https://github.com/pgvector/pgvector.git
cd pgvector && make && make install

# Docker
docker pull pgvector/pgvector:pg18

# Homebrew
brew install pgvector

# APT (Debian/Ubuntu)
sudo apt install postgresql-18-pgvector

Enable in each database:

CREATE EXTENSION vector;

Core Workflow

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

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

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

-- Add HNSW index for approximate search
CREATE INDEX ON items USING hnsw (embedding vector_l2_ops);

Distance Operators

Operator	Distance	Index Ops
<->	L2 (Euclidean)	vector_l2_ops
<#>	Negative inner product	vector_ip_ops
<=>	Cosine distance	vector_cosine_ops
<+>	L1 (taxicab)	vector_l1_ops
<~>	Hamming (binary)	bit_hamming_ops
<%>	Jaccard (binary)	bit_jaccard_ops

Index Selection Guide

Index	Best For	Key Consideration
None	Small tables, exact recall needed	Perfect recall, linear scan
HNSW	Production, best speed-recall tradeoff	Slower builds, more memory
IVFFlat	Large tables, faster builds, less memory	Needs data before creating, tuning required

Key Patterns

• Add indexes after loading initial data for best build performance
• Use CREATE INDEX CONCURRENTLY in production to avoid blocking writes
• For normalized vectors (like OpenAI embeddings), use inner product (<#>) for best performance
• <#> returns negative inner product — multiply by -1 for actual value
• For cosine similarity: 1 - (embedding <=> '[3,1,2]')
• Use EXPLAIN (ANALYZE, BUFFERS) to debug query performance
• Enable hnsw.iterative_scan = strict_order for filtered queries (v0.8.0+)
• Set maintenance_work_mem = '8GB' before building HNSW indexes on large tables
• NULL and zero vectors (for cosine) are not indexed