pgvector Setup

Embeddings are useless without a database that can store and search them efficiently. You could stuff vectors into a JSON file, but cosine similarity over 100,000 vectors requires a linear scan that gets slower with every document you add. pgvector adds a vector type, distance operators, and approximate nearest neighbor indexes directly to PostgreSQL. No separate service, no new query language, no data synchronization nightmares. This lesson gets pgvector installed, configured, and connected from Python.

What you'll learn

• pgvector is a PostgreSQL extension that adds a vector column type, three distance operators (<=> cosine, <-> Euclidean, <#> dot), and HNSW/IVFFlat indexes
• One command to install: CREATE EXTENSION vector; -- after that, PostgreSQL understands vectors natively
• HNSW indexes deliver sub-millisecond approximate nearest neighbor search on millions of vectors; without an index, every query scans the full table
• The Python side uses psycopg2 with the pgvector package to register the vector type as a SQLAlchemy-compatible column

The problem: PostgreSQL can't store vectors by default

PostgreSQL ships with integers, floats, text, JSON, and arrays. None of these let you run ORDER BY cosine_distance(embedding, query_vector) LIMIT 10 efficiently. You could store vectors as double precision[] arrays and compute cosine similarity in a stored procedure, but that means every query runs a full table scan -- O(n) in the number of rows. That's fine for 1,000 documents. It's a disaster for 1,000,000.

pgvector solves this by adding a native vector type with specialized indexes. The database knows these columns contain high-dimensional vectors and can use approximate nearest neighbor algorithms to find similar rows without scanning the entire table.

Options & when to use each

Option	What it's good for	What it costs you	When to pick it
pgvector on existing PostgreSQL	Zero new infrastructure, transactional consistency with your app data	Some operational overhead maintaining indexes; performance tuning needed at scale	You already use PostgreSQL and want vectors alongside your relational data
Pinecone / Weaviate / Chroma (standalone vector DB)	Purpose-built for vectors, managed hosting, simpler scaling	Separate service to deploy, monitor, and pay for; data lives in two places	You need sub-5ms latency at millions of vectors and don't mind managing another service
In-memory with numpy	Zero setup, fastest possible for small datasets	Linear scan; falls over past ~50,000 vectors	Prototyping, testing, tiny projects

Build it: install pgvector and create your first vector table

Step 1: Install the pgvector extension

Here is a concrete example demonstrating the implementation details.

# Ubuntu/Debian
sudo apt update
sudo apt install postgresql-16-pgvector

# macOS
brew install pgvector

# Or from source if you need the latest:
# git clone https://github.com/pgvector/pgvector.git
# cd pgvector && make && sudo make install

Step 2: Enable the extension in your database

Here is a concrete example demonstrating the implementation details.

-- Connect to your database
psql -U postgres -d your_database

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

-- Verify it's installed
SELECT * FROM pg_extension WHERE extname = 'vector';

The vector type is now available. You can create columns of type vector(N) where N is the fixed dimensionality.

Step 3: Create the documents table

Here is a concrete example demonstrating the implementation details.

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    metadata JSONB DEFAULT '{}'::jsonb,
    embedding VECTOR(768),  -- 768-dimensional vectors
    created_at TIMESTAMP DEFAULT NOW()
);

Pick the dimension to match your embedding model. If you're using OpenAI text-embedding-3-small with the dimensions=768 parameter, use VECTOR(768). If you're using the default 1536-dimensional output, use VECTOR(1536). The value in parentheses is enforced: pgvector rejects inserts with the wrong number of dimensions.

Step 4: Create an HNSW index

Without an index, every query scans all rows:

-- Create HNSW index for fast approximate nearest neighbor search
CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 200);

The vector_cosine_ops tells PostgreSQL to optimize for cosine distance. The parameters:

• m: Maximum number of connections per layer (16 is a good default; higher = more recall, more memory)
• ef_construction: Size of the dynamic candidate list during index building (200 is a good default; higher = better recall, slower build)

Step 5: Connect from Python

Here is a concrete example demonstrating the implementation details.

pip install psycopg2-binary pgvector

Here is a concrete example demonstrating the implementation details.

import psycopg2
from pgvector.psycopg2 import register_vector

# Connect to your database
conn = psycopg2.connect(
    host="localhost",
    port=5432,
    dbname="your_database",
    user="postgres",
    password="your_password"
)
conn.autocommit = True

# Register the vector type so psycopg2 can handle it
register_vector(conn)

# Verify the connection and extension
with conn.cursor() as cur:
    cur.execute("SELECT extversion FROM pg_extension WHERE extname = 'vector'")
    version = cur.fetchone()
    print(f"pgvector version: {version[0]}")

    # Test that the table exists
    cur.execute("""
        SELECT column_name, data_type
        FROM information_schema.columns
        WHERE table_name = 'documents'
        ORDER BY ordinal_position;
    """)
    for row in cur.fetchall():
        print(f"  {row[0]}: {row[1]}")

Expected output:

pgvector version: 0.7.4
  id: integer
  content: text
  metadata: jsonb
  embedding: USER-DEFINED
  created_at: timestamp without time zone

The USER-DEFINED type for the embedding column is correct -- pgvector registers the vector type as a custom PostgreSQL type.

HNSW vs IVFFlat: which index to use

Index	How it works	Best for	Tradeoff
HNSW	Builds a multi-layer graph; searches by traversing edges	Most use cases; very fast queries, builds incrementally	Slightly more memory than IVFFlat
IVFFlat	Clusters vectors into lists; only searches nearby clusters	Read-heavy workloads; smaller memory footprint	Must be rebuilt after significant data changes; slower builds

For RAG, HNSW is the default choice. It handles inserts, updates, and deletes without rebuilding, which matters when your document corpus changes.

-- HNSW (recommended for RAG)
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

-- IVFFlat (alternative)
CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

What goes wrong

Mistake	How you notice it	The fix
Forgetting register_vector(conn)	psycopg2 throws "can't adapt type 'list'" when you try to insert a Python list	Import and call register_vector(conn) before any vector operations. The pgvector library registers the adapter psycopg2 needs
Wrong vector dimension	PostgreSQL rejects the insert: "expected 768 dimensions, not 1536"	Match the dimension in your table definition to your embedding model's output. If you change models, ALTER the column: ALTER TABLE documents ALTER COLUMN embedding TYPE vector(768)
No index on the embedding column	Queries get slower linearly as you add documents; 100k rows takes seconds per query	Create an HNSW index. Without it, every similarity search runs a full table scan
Index built before data	The first queries are slow and the index never optimizes	Insert most of your data first, then create the index. HNSW handles incremental inserts fine but builds a better graph with more data upfront
PostgreSQL version too old	CREATE EXTENSION vector fails with "extension not available"	pgvector requires PostgreSQL 12+. Check your version with SELECT version(). Ubuntu 22.04 ships with PostgreSQL 14; older distros may need an upgrade

Confirm it worked

Run this test from Python:

import numpy as np
from pgvector.psycopg2 import register_vector

register_vector(conn)

# Insert a test row with a random embedding
test_embedding = np.random.randn(768).astype(np.float32).tolist()

with conn.cursor() as cur:
    cur.execute(
        "INSERT INTO documents (content, embedding) VALUES (%s, %s) RETURNING id",
        ("Test document for pgvector setup", test_embedding)
    )
    doc_id = cur.fetchone()[0]
    print(f"Inserted document {doc_id}")

    # Read it back and verify the embedding survived the round trip
    cur.execute("SELECT embedding FROM documents WHERE id = %s", (doc_id,))
    stored_embedding = cur.fetchone()[0]

    # Clean up
    cur.execute("DELETE FROM documents WHERE id = %s", (doc_id,))

# Verify the embedding is the same (allow tiny float differences)
import math
for original, stored in zip(test_embedding, stored_embedding):
    assert math.isclose(original, float(stored), rel_tol=1e-5), \
        f"Mismatch: {original} vs {stored}"

print("pgvector is working. Ready for real data.")

If this prints "pgvector is working. Ready for real data." without assertion errors, you're set up correctly. The next lesson fills this table with actual embeddings and runs real similarity searches.

Next: Storing and Querying