01 - pgvector Tutorial: Turn PostgreSQL into a Vector Database

# Prerequisites: PostgreSQL 14+ already installed
sudo apt-get update
sudo apt-get install -y postgresql-server-dev-16

# Clone and build pgvector
git clone --branch v0.8.0 https://github.com/pgvector/pgvector.git
cd pgvector
make
sudo make install

# Enable the extension in your database
sudo -u postgres psql -d your_database -c "CREATE EXTENSION vector;"
    

# docker-compose.yml
version: '3.8'
services:
  postgres:
    image: pgvector/pgvector:pg17
    environment:
      POSTGRES_DB: vectordb
      POSTGRES_USER: admin
      POSTGRES_PASSWORD: secret_password
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql

volumes:
  pgdata:
    

-- init.sql: automatically executed on first startup
CREATE EXTENSION IF NOT EXISTS vector;
    

# Quick start without docker-compose
docker run -d \
  --name pgvector-db \
  -e POSTGRES_DB=vectordb \
  -e POSTGRES_PASSWORD=secret \
  -p 5432:5432 \
  pgvector/pgvector:pg17

# Connect and enable pgvector
docker exec -it pgvector-db psql -U postgres -d vectordb \
  -c "CREATE EXTENSION vector;"
    

-- On Supabase, pgvector is already available.
-- Just enable the extension:
CREATE EXTENSION IF NOT EXISTS vector;

-- Verify the installation
SELECT extversion FROM pg_extension WHERE extname = 'vector';
-- Result: 0.8.0
    

-- Enable the extension (if not already done)
CREATE EXTENSION IF NOT EXISTS vector;

-- Documents table for a RAG system
CREATE TABLE documents (
    id BIGSERIAL PRIMARY KEY,
    title VARCHAR(500) NOT NULL,
    content TEXT NOT NULL,
    source VARCHAR(255),
    category VARCHAR(100),
    embedding vector(1536),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Add a column comment for documentation
COMMENT ON COLUMN documents.embedding IS
    'Embedding generated with OpenAI text-embedding-3-small (1536 dim)';
    

-- Single insert
INSERT INTO documents (title, content, source, embedding)
VALUES (
    'Introduction to PostgreSQL',
    'PostgreSQL is a powerful and reliable open-source relational database...',
    'docs/postgres-intro.md',
    '[0.023, -0.045, 0.067, 0.089, -0.012, ...]'  -- 1536-dimensional vector
);

-- Batch insert
INSERT INTO documents (title, content, source, category, embedding)
VALUES
    ('Chapter 1', 'Chapter 1 content...', 'book.pdf', 'tutorial',
     '[0.11, -0.22, 0.33, ...]'),
    ('Chapter 2', 'Chapter 2 content...', 'book.pdf', 'tutorial',
     '[0.44, -0.55, 0.66, ...]'),
    ('FAQ', 'Frequently asked questions...', 'faq.md', 'support',
     '[-0.12, 0.34, -0.56, ...]');
    

-- COSINE DISTANCE <=> (most commonly used for text)
-- Result: 0 = identical, 2 = opposite
SELECT title, content,
       embedding <=> '[0.1, 0.2, ...]' AS cosine_distance
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]' ASC
LIMIT 5;

-- L2 (EUCLIDEAN) <->
-- Result: 0 = identical, increasing values = farther apart
SELECT title, content,
       embedding <-> '[0.1, 0.2, ...]' AS l2_distance
FROM documents
ORDER BY embedding <-> '[0.1, 0.2, ...]' ASC
LIMIT 5;

-- INNER PRODUCT (NEGATED) <#>
-- Result: more negative values = more similar
SELECT title, content,
       (embedding <#> '[0.1, 0.2, ...]') * -1 AS similarity
FROM documents
ORDER BY embedding <#> '[0.1, 0.2, ...]' ASC
LIMIT 5;
    

-- Find the 5 most similar documents to a query vector
SELECT
    id,
    title,
    content,
    1 - (embedding <=> $1) AS similarity  -- $1 = query vector
FROM documents
ORDER BY embedding <=> $1
LIMIT 5;
    

-- Filter by category and date
SELECT
    id, title, content,
    1 - (embedding <=> $1) AS similarity
FROM documents
WHERE category = 'tutorial'
  AND created_at > NOW() - INTERVAL '30 days'
ORDER BY embedding <=> $1
LIMIT 5;

-- JOIN with other tables: find similar docs with author info
SELECT
    d.title,
    d.content,
    a.name AS author_name,
    1 - (d.embedding <=> $1) AS similarity
FROM documents d
JOIN authors a ON d.author_id = a.id
WHERE a.verified = true
ORDER BY d.embedding <=> $1
LIMIT 10;

-- Exclusion: find similar but NOT the document itself
SELECT
    id, title,
    1 - (embedding <=> (SELECT embedding FROM documents WHERE id = 42)) AS similarity
FROM documents
WHERE id != 42
ORDER BY embedding <=> (SELECT embedding FROM documents WHERE id = 42)
LIMIT 5;
    

-- Enable iterative scan for HNSW
SET hnsw.iterative_scan = relaxed_order;  -- or strict_order

-- Set the maximum number of tuples to scan
SET hnsw.max_scan_tuples = 20000;

-- Now queries with restrictive filters work correctly
SELECT id, title,
       1 - (embedding <=> $1) AS similarity
FROM documents
WHERE category = 'rare'  -- category with few documents
ORDER BY embedding <=> $1
LIMIT 10;
-- Without iterative scan: might return 0-3 results
-- With iterative scan: always returns up to 10 results

-- Available modes:
-- off           = traditional behavior (default)
-- strict_order  = maintains exact distance ordering
-- relaxed_order = approximate ordering (better performance)
    

import openai
import psycopg2
from psycopg2.extras import execute_values

# Configure the OpenAI client
client = openai.OpenAI(api_key="sk-...")

def get_embedding(text: str, model: str = "text-embedding-3-small") -> list[float]:
    """Generate an embedding for a text using OpenAI."""
    response = client.embeddings.create(
        input=text,
        model=model
    )
    return response.data[0].embedding  # list of 1536 floats

# Connect to PostgreSQL
conn = psycopg2.connect(
    host="localhost",
    database="vectordb",
    user="admin",
    password="secret_password"
)
cur = conn.cursor()

# Generate and insert embeddings
documents = [
    ("Introduction to Python", "Python is a versatile programming language..."),
    ("Advanced PostgreSQL", "Recursive CTEs allow hierarchical queries..."),
    ("Docker for Developers", "Docker containers isolate applications..."),
]

for title, content in documents:
    embedding = get_embedding(content)
    cur.execute(
        "INSERT INTO documents (title, content, embedding) VALUES (%s, %s, %s)",
        (title, content, str(embedding))
    )

conn.commit()
cur.close()
conn.close()
    

from sentence_transformers import SentenceTransformer
import psycopg2
import numpy as np

# Load the model (automatically downloaded the first time)
model = SentenceTransformer('all-MiniLM-L6-v2')  # 384 dimensions

# Generate embeddings for a batch of texts
texts = [
    "Python is a versatile programming language",
    "Recursive CTEs enable hierarchical queries in SQL",
    "Docker containers isolate applications",
]
embeddings = model.encode(texts)  # shape: (3, 384)

# Connect and insert
conn = psycopg2.connect(
    host="localhost", database="vectordb",
    user="admin", password="secret_password"
)
cur = conn.cursor()

# NOTE: the column must be vector(384), not vector(1536)
for text, embedding in zip(texts, embeddings):
    cur.execute(
        "INSERT INTO documents (title, content, embedding) VALUES (%s, %s, %s)",
        (text[:100], text, str(embedding.tolist()))
    )

conn.commit()
cur.close()
conn.close()
    

-- HNSW index with default parameters
CREATE INDEX idx_documents_embedding_hnsw
ON documents
USING hnsw (embedding vector_cosine_ops);

-- HNSW index with custom parameters
CREATE INDEX idx_documents_embedding_hnsw
ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (
    m = 24,                -- connections per node (default: 16, range: 2-100)
    ef_construction = 128  -- candidates during build (default: 64, range: 4-1000)
);

-- Tune ef_search for queries (default: 40)
SET hnsw.ef_search = 100;  -- higher = more accurate but slower
    

-- IMPORTANT: IVFFlat requires data in the table BEFORE creating the index
-- (it needs to perform k-means clustering on existing data)

-- IVFFlat index
CREATE INDEX idx_documents_embedding_ivf
ON documents
USING ivfflat (embedding vector_cosine_ops)
WITH (
    lists = 100   -- number of clusters (default: 100)
);

-- Tune the number of clusters to explore during search
SET ivfflat.probes = 10;  -- default: 1, recommended: sqrt(lists)

-- Rule of thumb for "lists":
-- rows < 1M:    lists = rows / 1000
-- rows >= 1M:   lists = sqrt(rows)
    

"""
Knowledge Base with PostgreSQL + pgvector
Requirements: pip install openai psycopg2-binary
"""
import openai
import psycopg2
from dataclasses import dataclass

# === Configuration ===

OPENAI_API_KEY = "sk-..."
DB_CONFIG = {
    "host": "localhost",
    "database": "vectordb",
    "user": "admin",
    "password": "secret_password",
}
EMBEDDING_MODEL = "text-embedding-3-small"
EMBEDDING_DIM = 1536


@dataclass(frozen=True)
class SearchResult:
    id: int
    title: str
    content: str
    similarity: float
    category: str


# === Embedding functions ===

client = openai.OpenAI(api_key=OPENAI_API_KEY)

def get_embedding(text: str) -> list[float]:
    """Generate an embedding for a text."""
    response = client.embeddings.create(
        input=text,
        model=EMBEDDING_MODEL,
    )
    return response.data[0].embedding


def get_embeddings_batch(texts: list[str]) -> list[list[float]]:
    """Generate embeddings for a batch of texts (more efficient)."""
    response = client.embeddings.create(
        input=texts,
        model=EMBEDDING_MODEL,
    )
    return [item.embedding for item in response.data]


# === Database setup ===

def setup_database() -> None:
    """Create the table and indexes."""
    conn = psycopg2.connect(**DB_CONFIG)
    cur = conn.cursor()

    cur.execute("CREATE EXTENSION IF NOT EXISTS vector;")

    cur.execute(f"""
        CREATE TABLE IF NOT EXISTS kb_documents (
            id BIGSERIAL PRIMARY KEY,
            title VARCHAR(500) NOT NULL,
            content TEXT NOT NULL,
            category VARCHAR(100) DEFAULT 'general',
            embedding vector({EMBEDDING_DIM}),
            created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
        );
    """)

    cur.execute("""
        CREATE INDEX IF NOT EXISTS idx_kb_embedding_hnsw
        ON kb_documents
        USING hnsw (embedding vector_cosine_ops)
        WITH (m = 24, ef_construction = 128);
    """)

    cur.execute("""
        CREATE INDEX IF NOT EXISTS idx_kb_category
        ON kb_documents (category);
    """)

    conn.commit()
    cur.close()
    conn.close()
    print("Database setup complete.")


# === Document insertion ===

def insert_documents(
    documents: list[dict],
    batch_size: int = 50,
) -> None:
    """Insert documents with automatically generated embeddings."""
    conn = psycopg2.connect(**DB_CONFIG)
    cur = conn.cursor()

    for i in range(0, len(documents), batch_size):
        batch = documents[i : i + batch_size]
        texts = [doc["content"] for doc in batch]
        embeddings = get_embeddings_batch(texts)

        for doc, emb in zip(batch, embeddings):
            cur.execute(
                """INSERT INTO kb_documents (title, content, category, embedding)
                   VALUES (%s, %s, %s, %s)""",
                (doc["title"], doc["content"],
                 doc.get("category", "general"), str(emb)),
            )

        print(f"Inserted {min(i + batch_size, len(documents))}/{len(documents)} documents")

    conn.commit()
    cur.close()
    conn.close()


# === Similarity search ===

def search(
    query: str,
    limit: int = 5,
    category: str | None = None,
    min_similarity: float = 0.3,
) -> list[SearchResult]:
    """Search for documents similar to the query."""
    query_embedding = get_embedding(query)

    conn = psycopg2.connect(**DB_CONFIG)
    cur = conn.cursor()

    # Enable iterative scan for restrictive filters
    cur.execute("SET hnsw.iterative_scan = relaxed_order;")

    if category:
        cur.execute(
            """SELECT id, title, content, category,
                      1 - (embedding <=> %s::vector) AS similarity
               FROM kb_documents
               WHERE category = %s
               ORDER BY embedding <=> %s::vector
               LIMIT %s""",
            (str(query_embedding), category,
             str(query_embedding), limit),
        )
    else:
        cur.execute(
            """SELECT id, title, content, category,
                      1 - (embedding <=> %s::vector) AS similarity
               FROM kb_documents
               ORDER BY embedding <=> %s::vector
               LIMIT %s""",
            (str(query_embedding), str(query_embedding), limit),
        )

    results = [
        SearchResult(
            id=row[0], title=row[1], content=row[2],
            category=row[3], similarity=row[4],
        )
        for row in cur.fetchall()
        if row[4] >= min_similarity
    ]

    cur.close()
    conn.close()
    return results


# === Main ===

if __name__ == "__main__":
    # 1. Setup
    setup_database()

    # 2. Insert sample documents
    sample_docs = [
        {
            "title": "Introduction to PostgreSQL",
            "content": "PostgreSQL is an open-source RDBMS with full ACID support.",
            "category": "database",
        },
        {
            "title": "PostgreSQL Indexes",
            "content": "B-trees are the default index type. HNSW is used for vectors.",
            "category": "database",
        },
        {
            "title": "What is RAG",
            "content": "RAG combines retrieval and generation for document-based answers.",
            "category": "ai",
        },
        {
            "title": "Docker for Developers",
            "content": "Docker isolates applications in lightweight, portable containers.",
            "category": "devops",
        },
        {
            "title": "Embeddings and Vector Search",
            "content": "Embeddings transform text into vectors for similarity search.",
            "category": "ai",
        },
    ]
    insert_documents(sample_docs)

    # 3. Search documents
    print("\n--- Search: 'how do vector databases work' ---")
    results = search("how do vector databases work", limit=3)
    for r in results:
        print(f"  [{r.similarity:.3f}] {r.title} ({r.category})")

    print("\n--- Filtered search for category 'ai' ---")
    results = search("PostgreSQL tutorial", limit=3, category="ai")
    for r in results:
        print(f"  [{r.similarity:.3f}] {r.title} ({r.category})")
    

pip install langchain-postgres langchain-openai psycopg
    

from langchain_postgres import PGVector
from langchain_openai import OpenAIEmbeddings
from langchain_core.documents import Document

# Configure the embedding model
embeddings = OpenAIEmbeddings(
    model="text-embedding-3-small",
    openai_api_key="sk-...",
)

# NOTE: langchain-postgres uses psycopg3 (not psycopg2)
CONNECTION_STRING = "postgresql+psycopg://admin:secret@localhost:5432/vectordb"

# Create the vector store
vector_store = PGVector(
    embeddings=embeddings,
    collection_name="langchain_docs",
    connection=CONNECTION_STRING,
    use_jsonb=True,  # JSONB metadata for efficient filtering
)

# Add documents
docs = [
    Document(
        page_content="pgvector turns PostgreSQL into a vector database",
        metadata={"source": "tutorial.md", "chapter": 1},
    ),
    Document(
        page_content="HNSW is the recommended index for most use cases",
        metadata={"source": "tutorial.md", "chapter": 2},
    ),
    Document(
        page_content="LangChain simplifies building RAG systems",
        metadata={"source": "rag-guide.md", "chapter": 1},
    ),
]

vector_store.add_documents(docs)

# Similarity search
results = vector_store.similarity_search(
    "how to create a vector index",
    k=3,
)
for doc in results:
    print(f"[{doc.metadata['source']}] {doc.page_content[:80]}...")

# Search with scores
results_with_scores = vector_store.similarity_search_with_score(
    "what is pgvector",
    k=3,
)
for doc, score in results_with_scores:
    print(f"[Score: {score:.4f}] {doc.page_content[:80]}...")

# Use as a retriever in a RAG chain
retriever = vector_store.as_retriever(
    search_type="similarity",
    search_kwargs={"k": 5},
)

# The retriever can be used directly in LangChain chains
relevant_docs = retriever.invoke("install pgvector docker")
    

from psycopg2.extras import execute_values

# WRONG: one INSERT at a time (slow)
for doc in documents:
    cur.execute("INSERT INTO docs (content, embedding) VALUES (%s, %s)",
                (doc.content, str(doc.embedding)))

# CORRECT: batch insert with execute_values (10-50x faster)
data = [
    (doc.content, str(doc.embedding))
    for doc in documents
]
execute_values(
    cur,
    "INSERT INTO docs (content, embedding) VALUES %s",
    data,
    page_size=500,  # rows per SQL batch
)
conn.commit()
    

from psycopg2 import pool

# Create a connection pool (at application startup)
connection_pool = pool.ThreadedConnectionPool(
    minconn=5,    # minimum connections
    maxconn=20,   # maximum connections
    host="localhost",
    database="vectordb",
    user="admin",
    password="secret_password",
)

# Use a connection from the pool
conn = connection_pool.getconn()
try:
    cur = conn.cursor()
    cur.execute("SELECT ... ORDER BY embedding <=> %s LIMIT 5", ...)
    results = cur.fetchall()
    cur.close()
finally:
    connection_pool.putconn(conn)  # return to pool
    

-- VACUUM to reclaim space after DELETE/UPDATE
VACUUM ANALYZE documents;

-- Rebuild IVFFlat index after massive inserts
REINDEX INDEX CONCURRENTLY idx_documents_embedding_ivf;
-- NOTE: HNSW does not require REINDEX for new inserts

-- Monitor index size
SELECT
    indexname,
    pg_size_pretty(pg_relation_size(indexname::regclass)) AS size
FROM pg_indexes
WHERE tablename = 'documents';

-- Verify that the index is being used
EXPLAIN ANALYZE
SELECT id, 1 - (embedding <=> '[0.1, 0.2, ...]') AS similarity
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'
LIMIT 5;
-- Look for "Index Scan using idx_..._hnsw" in the output