pgvector

# 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

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

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

-- 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
);

-- 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      (<#>)

-- 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;

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()

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)

-- 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;