Intermediate

pgvector

pgvector is an open-source PostgreSQL extension that adds vector similarity search to your existing PostgreSQL database. No new infrastructure needed — just add an extension.

What is pgvector?

pgvector brings vector search capabilities directly into PostgreSQL, the world's most popular open-source relational database. Key advantages:

  • No new database: Add vector search to your existing PostgreSQL setup.
  • Full SQL support: Combine vector similarity with JOINs, CTEs, transactions, and all PostgreSQL features.
  • ACID compliant: Vectors are part of your regular database with full transactional guarantees.
  • Familiar tooling: Use psql, pgAdmin, any PostgreSQL client, ORMs like SQLAlchemy.
  • Cost-effective: One database for relational data AND vectors.

Installation

On Linux/macOS

Terminal 
# Install pgvector (Ubuntu/Debian)
sudo apt install postgresql-16-pgvector

# Or build from source
git clone https://github.com/pgvector/pgvector.git
cd pgvector
make
sudo make install

With Docker

Terminal 
# Use the pgvector Docker image
docker run -d --name pgvector \
  -e POSTGRES_PASSWORD=mysecretpassword \
  -p 5432:5432 \
  pgvector/pgvector:pg16

Enable the Extension

SQL 
-- Enable pgvector in your database
CREATE EXTENSION IF NOT EXISTS vector;

Creating Vector Columns

SQL - Create Table with Vector Column 
-- Create a table with a vector column
CREATE TABLE documents (
    id BIGSERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    category TEXT,
    embedding vector(1536),     -- 1536-dimensional vector
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insert a document with its embedding
INSERT INTO documents (title, content, category, embedding)
VALUES (
    'Introduction to AI',
    'Artificial intelligence is the simulation of human intelligence...',
    'technology',
    '[0.012, -0.034, 0.056, ...]'  -- 1536 float values
);

Indexing (IVFFlat and HNSW)

Without an index, pgvector performs exact nearest neighbor search (brute force). For large datasets, create an index for fast approximate search.

SQL - Create Indexes 
-- HNSW index (recommended - better recall, no training needed)
CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- IVFFlat index (faster build, lower memory)
CREATE INDEX ON documents
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);
-- Note: IVFFlat requires data to be present before creating the index

-- Distance operators:
-- vector_cosine_ops   for cosine distance   (<=>)
-- vector_l2_ops       for L2 distance       (<->)
-- vector_ip_ops       for inner product      (<#>)

SQL Queries for Similarity Search

SQL - Similarity Search Queries 
-- Find 5 most similar documents (cosine distance)
SELECT id, title, content,
       1 - (embedding <=> '[0.012, -0.034, ...]') AS similarity
FROM documents
ORDER BY embedding <=> '[0.012, -0.034, ...]'
LIMIT 5;

-- Combine similarity search with SQL filters
SELECT id, title,
       1 - (embedding <=> $1) AS similarity
FROM documents
WHERE category = 'technology'
  AND created_at > NOW() - INTERVAL '30 days'
ORDER BY embedding <=> $1
LIMIT 10;

-- Use with JOINs
SELECT d.title, d.content, a.name AS author
FROM documents d
JOIN authors a ON d.author_id = a.id
ORDER BY d.embedding <=> $1
LIMIT 5;

Python with psycopg2

Python - Full Example 
import psycopg2
import openai
import numpy as np

# Connect to PostgreSQL
conn = psycopg2.connect(
    host="localhost",
    database="mydb",
    user="postgres",
    password="mysecretpassword"
)
cur = conn.cursor()

# Generate embedding for the query
client = openai.OpenAI()
query = "How do neural networks learn?"
response = client.embeddings.create(
    input=[query],
    model="text-embedding-3-small"
)
query_embedding = response.data[0].embedding

# Search for similar documents
cur.execute("""
    SELECT id, title, content,
           1 - (embedding <=> %s::vector) AS similarity
    FROM documents
    WHERE category = 'ai'
    ORDER BY embedding <=> %s::vector
    LIMIT 5
""", (query_embedding, query_embedding))

results = cur.fetchall()
for row in results:
    print(f"[{row[3]:.4f}] {row[1]}: {row[2][:80]}...")

conn.close()

Python with SQLAlchemy

Python - SQLAlchemy ORM 
from sqlalchemy import create_engine, Column, Integer, Text
from sqlalchemy.orm import declarative_base, Session
from pgvector.sqlalchemy import Vector

Base = declarative_base()

class Document(Base):
    __tablename__ = "documents"
    id = Column(Integer, primary_key=True)
    title = Column(Text)
    content = Column(Text)
    embedding = Column(Vector(1536))

engine = create_engine("postgresql://postgres:password@localhost/mydb")

# Query similar documents
with Session(engine) as session:
    results = (
        session.query(Document)
        .order_by(Document.embedding.cosine_distance(query_embedding))
        .limit(5)
        .all()
    )
    for doc in results:
        print(doc.title)

Performance Tuning

Parameter Effect Recommendation
HNSW m Connections per node 16 (default) to 64 for higher recall
HNSW ef_construction Build-time search width 64 (default) to 256 for better index quality
hnsw.ef_search Query-time search width 40 (default) to 200 for higher recall
IVFFlat lists Number of clusters sqrt(n) for n rows (e.g., 1000 for 1M rows)
ivfflat.probes Clusters searched per query sqrt(lists) (e.g., 32 for 1000 lists)
SQL - Tune Search Parameters 
-- Increase HNSW search accuracy (per session)
SET hnsw.ef_search = 100;

-- Increase IVFFlat probes (per session)
SET ivfflat.probes = 20;

-- Check index usage
EXPLAIN ANALYZE
SELECT id, title
FROM documents
ORDER BY embedding <=> '[...]'::vector
LIMIT 10;
✅
When to choose pgvector: Choose pgvector when you already use PostgreSQL and want to add vector search without introducing a new database. It is ideal for applications where vectors are tightly coupled with relational data (users, products, transactions) and you value ACID transactions and SQL familiarity.

💡 Try It Yourself

Set up pgvector with Docker, create a table with a vector column, insert 100 sample vectors, create an HNSW index, and run similarity queries. Compare query times with and without the index.

Use EXPLAIN ANALYZE to see whether PostgreSQL is using the HNSW index. Without the index, it will do a sequential scan.