pgvector: Vector Search and Embeddings in PostgreSQL

In the age of AI, data isn't just rows and columns—it's embeddings. Whether you're building a semantic search engine, a recommendation system, or a RAG (Retrieval-Augmented Generation) pipeline, you need to store and query high-dimensional vectors efficiently. Enter pgvector: an open-source extension that turns PostgreSQL into a vector database. It allows you to store vectors alongside your relational data and perform similarity searches using exact or approximate nearest neighbor algorithms. This means you can keep your existing PostgreSQL infrastructure while adding vector capabilities, avoiding the operational complexity of a separate vector database. In this tutorial, you'll learn how to install pgvector, create vector columns, index them for performance, and run similarity queries. We'll also cover production pitfalls, debugging techniques, and a real-world incident where a missing index caused a production outage. By the end, you'll be able to integrate vector search into your PostgreSQL applications with confidence.

Installing pgvector

pgvector is available as a PostgreSQL extension. Installation varies by OS. On Ubuntu/Debian, you can install from apt: sudo apt install postgresql-16-pgvector. For other versions, compile from source: clone the repo, run make && make install. Then enable the extension in your database: CREATE EXTENSION vector;. Verify with SELECT * FROM pg_extension;. You'll need superuser privileges. After installation, you can create vector columns with the vector(n) type, where n is the number of dimensions.

-- Enable the extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Check installed version
SELECT extversion FROM pg_extension WHERE extname = 'vector';

Creating Vector Columns and Inserting Data

Define a vector column with a fixed dimension. For example, if you use OpenAI embeddings (1536 dimensions), create vector(1536). Insert data using array notation: '[0.1, 0.2, ...]'::vector. You can also generate vectors from Python or other clients. Here's an example table for storing product embeddings.

-- Create table with vector column
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    description TEXT,
    embedding vector(3)  -- small dimension for demo
);

-- Insert sample data
INSERT INTO products (name, description, embedding) VALUES
('Laptop', 'A powerful laptop', '[0.1, 0.2, 0.3]'),
('Phone', 'A smartphone', '[0.4, 0.5, 0.6]'),
('Tablet', 'A tablet device', '[0.7, 0.8, 0.9]');

Performing Similarity Search

pgvector supports three distance operators: L2 distance (<->), inner product (<#>), and cosine distance (<=>). Use ORDER BY with the operator and LIMIT to get nearest neighbors. Example: find products similar to a query vector.

-- Find top 2 products closest to [0.2, 0.3, 0.4]
SELECT id, name, embedding <=> '[0.2, 0.3, 0.4]'::vector AS distance
FROM products
ORDER BY embedding <=> '[0.2, 0.3, 0.4]'::vector
LIMIT 2;

Indexing for Performance

Without an index, pgvector performs exact search (O(n)). For large datasets, use approximate nearest neighbor (ANN) indexes. pgvector offers IVFFlat (Inverted File with Flat) and HNSW (Hierarchical Navigable Small World). IVFFlat is faster to build and uses less memory; HNSW provides better recall and query speed but is more resource-intensive. Choose based on your trade-offs.

-- IVFFlat index (requires training data)
CREATE INDEX ON products USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

-- HNSW index (no training needed)
CREATE INDEX ON products USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 200);

Using pgvector with Python and ORMs

pgvector integrates with popular Python libraries like psycopg2, SQLAlchemy, and Django. For psycopg2, use the register_vector adapter. Example: store and query embeddings from Python.

import psycopg2
from pgvector.psycopg2 import register_vector

conn = psycopg2.connect(database="test")
register_vector(conn)
cur = conn.cursor()

# Insert
cur.execute("INSERT INTO products (name, embedding) VALUES (%s, %s)",
            ("Camera", [0.2, 0.3, 0.4]))

# Query
cur.execute("SELECT id, name FROM products ORDER BY embedding <=> %s LIMIT 3",
            ([0.1, 0.2, 0.3],))
for row in cur.fetchall():
    print(row)

Production Considerations and Monitoring

In production, monitor index build times, query latency, and recall. Use EXPLAIN ANALYZE to ensure indexes are used. Set ivfflat.probes for IVFFlat to control accuracy-speed trade-off (default 1, increase for better recall). For HNSW, set hnsw.ef_search per query. Also consider partitioning large tables by vector dimension or using separate tables for different embedding models.

-- Set probes for IVFFlat (session level)
SET ivfflat.probes = 10;

-- Set ef_search for HNSW (session level)
SET hnsw.ef_search = 100;

-- Verify index usage
EXPLAIN ANALYZE SELECT id, name FROM products ORDER BY embedding <=> '[0.1,0.2,0.3]'::vector LIMIT 5;

Hybrid Search: Combining Vector and Relational Filters

Often you need to filter by metadata before vector search. For example, find products similar to a query but only in a specific category. pgvector supports combining WHERE clauses with ORDER BY. However, ensure the filter is selective to avoid scanning many rows. Use composite indexes or partial indexes for performance.

-- Filter by category then order by similarity
SELECT id, name, category
FROM products
WHERE category = 'Electronics'
ORDER BY embedding <=> '[0.1,0.2,0.3]'::vector
LIMIT 5;

-- Create a partial index for better performance
CREATE INDEX ON products USING ivfflat (embedding vector_cosine_ops) WHERE category = 'Electronics';