01 - pgvector: Trasforma PostgreSQL in Vector DB

# Prerequisiti: PostgreSQL 14+ già installato
sudo apt-get update
sudo apt-get install -y postgresql-server-dev-16

# Clona e compila pgvector
git clone --branch v0.8.0 https://github.com/pgvector/pgvector.git
cd pgvector
make
sudo make install

# Abilita l'estensione nel tuo database
sudo -u postgres psql -d il_tuo_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: eseguito automaticamente al primo avvio
CREATE EXTENSION IF NOT EXISTS vector;
    

# Avvio rapido senza docker-compose
docker run -d \
  --name pgvector-db \
  -e POSTGRES_DB=vectordb \
  -e POSTGRES_PASSWORD=secret \
  -p 5432:5432 \
  pgvector/pgvector:pg17

# Connetti e abilita pgvector
docker exec -it pgvector-db psql -U postgres -d vectordb \
  -c "CREATE EXTENSION vector;"
    

-- Su Supabase, pgvector e già disponibile.
-- Basta abilitare l'estensione:
CREATE EXTENSION IF NOT EXISTS vector;

-- Verifica l'installazione
SELECT extversion FROM pg_extension WHERE extname = 'vector';
-- Risultato: 0.8.0
    

-- Abilita l'estensione (se non già fatto)
CREATE EXTENSION IF NOT EXISTS vector;

-- Tabella documenti per un sistema RAG
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()
);

-- Aggiungi un commento alla colonna per documentazione
COMMENT ON COLUMN documents.embedding IS
    'Embedding generato con OpenAI text-embedding-3-small (1536 dim)';
    

-- Inserimento singolo
INSERT INTO documents (title, content, source, embedding)
VALUES (
    'Introduzione a PostgreSQL',
    'PostgreSQL e un database relazionale open-source potente e affidabile...',
    'docs/postgres-intro.md',
    '[0.023, -0.045, 0.067, 0.089, -0.012, ...]'  -- vettore a 1536 dimensioni
);

-- Inserimento multiplo (batch)
INSERT INTO documents (title, content, source, category, embedding)
VALUES
    ('Capitolo 1', 'Contenuto cap 1...', 'libro.pdf', 'tutorial',
     '[0.11, -0.22, 0.33, ...]'),
    ('Capitolo 2', 'Contenuto cap 2...', 'libro.pdf', 'tutorial',
     '[0.44, -0.55, 0.66, ...]'),
    ('FAQ', 'Domande frequenti...', 'faq.md', 'supporto',
     '[-0.12, 0.34, -0.56, ...]');
    

-- COSINE DISTANCE <=> (il più usato per testo)
-- Risultato: 0 = identici, 2 = opposti
SELECT title, content,
       embedding <=> '[0.1, 0.2, ...]' AS cosine_distance
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]' ASC
LIMIT 5;

-- L2 (EUCLIDEA) <->
-- Risultato: 0 = identici, valori crescenti = più lontani
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 (NEGATO) <#>
-- Risultato: valori più negativi = più simili
SELECT title, content,
       (embedding <#> '[0.1, 0.2, ...]') * -1 AS similarity
FROM documents
ORDER BY embedding <#> '[0.1, 0.2, ...]' ASC
LIMIT 5;
    

-- Trova i 5 documenti più simili a un vettore query
SELECT
    id,
    title,
    content,
    1 - (embedding <=> $1) AS similarity  -- $1 = vettore query
FROM documents
ORDER BY embedding <=> $1
LIMIT 5;
    

-- Filtra per categoria e soglia di similarità
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 con altre tabelle: trova documenti simili con info autore
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;

-- Esclusione: trova simili ma NON il documento stesso
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;
    

-- Abilita iterative scan per HNSW
SET hnsw.iterative_scan = relaxed_order;  -- o strict_order

-- Imposta il limite massimo di tuple da scansionare
SET hnsw.max_scan_tuples = 20000;

-- Ora le query con filtri restrittivi funzionano correttamente
SELECT id, title,
       1 - (embedding <=> $1) AS similarity
FROM documents
WHERE category = 'raro'  -- categoria con pochi documenti
ORDER BY embedding <=> $1
LIMIT 10;
-- Senza iterative scan: potrebbe restituire 0-3 risultati
-- Con iterative scan: restituisce sempre fino a 10 risultati

-- Modalità disponibili:
-- off           = comportamento tradizionale (default)
-- strict_order  = mantiene l'ordine esatto delle distanze
-- relaxed_order = ordine approssimato (migliori performance)
    

import openai
import psycopg2
from psycopg2.extras import execute_values

# Configura il client OpenAI
client = openai.OpenAI(api_key="sk-...")

def get_embedding(text: str, model: str = "text-embedding-3-small") -> list[float]:
    """Genera un embedding per un testo usando OpenAI."""
    response = client.embeddings.create(
        input=text,
        model=model
    )
    return response.data[0].embedding  # lista di 1536 float

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

# Genera e inserisci embeddings
documents = [
    ("Introduzione a Python", "Python e un linguaggio di programmazione versatile..."),
    ("PostgreSQL Avanzato", "Le CTE ricorsive permettono di eseguire query gerarchiche..."),
    ("Docker per Sviluppatori", "I container Docker isolano le applicazioni..."),
]

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

# Carica il modello (scaricato automaticamente la prima volta)
model = SentenceTransformer('all-MiniLM-L6-v2')  # 384 dimensioni

# Genera embeddings per un batch di testi
texts = [
    "Python e un linguaggio di programmazione versatile",
    "Le CTE ricorsive permettono query gerarchiche in SQL",
    "I container Docker isolano le applicazioni",
]
embeddings = model.encode(texts)  # shape: (3, 384)

# Connetti e inserisci
conn = psycopg2.connect(
    host="localhost", database="vectordb",
    user="admin", password="secret_password"
)
cur = conn.cursor()

# NOTA: la colonna deve essere vector(384), non 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()
    

-- Indice HNSW con parametri di default
CREATE INDEX idx_documents_embedding_hnsw
ON documents
USING hnsw (embedding vector_cosine_ops);

-- Indice HNSW con parametri personalizzati
CREATE INDEX idx_documents_embedding_hnsw
ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (
    m = 24,                -- connessioni per nodo (default: 16, range: 2-100)
    ef_construction = 128  -- candidati durante costruzione (default: 64, range: 4-1000)
);

-- Regola ef_search per le query (default: 40)
SET hnsw.ef_search = 100;  -- più alto = più preciso ma più lento
    

-- IMPORTANTE: IVFFlat richiede dati nella tabella PRIMA di creare l'indice
-- (necessità di fare clustering k-means sui dati esistenti)

-- Indice IVFFlat
CREATE INDEX idx_documents_embedding_ivf
ON documents
USING ivfflat (embedding vector_cosine_ops)
WITH (
    lists = 100   -- numero di cluster (default: 100)
);

-- Regola il numero di cluster da esplorare durante la ricerca
SET ivfflat.probes = 10;  -- default: 1, consigliato: sqrt(lists)

-- Regola pratica per "lists":
-- rows < 1M:    lists = rows / 1000
-- rows >= 1M:   lists = sqrt(rows)
    

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

# === Configurazione ===

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


# === Funzioni di embedding ===

client = openai.OpenAI(api_key=OPENAI_API_KEY)

def get_embedding(text: str) -> list[float]:
    """Genera un embedding per un testo."""
    response = client.embeddings.create(
        input=text,
        model=EMBEDDING_MODEL,
    )
    return response.data[0].embedding


def get_embeddings_batch(texts: list[str]) -> list[list[float]]:
    """Genera embeddings per un batch di testi (più efficiente)."""
    response = client.embeddings.create(
        input=texts,
        model=EMBEDDING_MODEL,
    )
    return [item.embedding for item in response.data]


# === Setup del database ===

def setup_database() -> None:
    """Crea la tabella e gli indici."""
    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 completato.")


# === Inserimento documenti ===

def insert_documents(
    documents: list[dict],
    batch_size: int = 50,
) -> None:
    """Inserisce documenti con embeddings generati automaticamente."""
    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"Inseriti {min(i + batch_size, len(documents))}/{len(documents)} documenti")

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


# === Ricerca per similarità ===

def search(
    query: str,
    limit: int = 5,
    category: str | None = None,
    min_similarity: float = 0.3,
) -> list[SearchResult]:
    """Cerca documenti simili alla query."""
    query_embedding = get_embedding(query)

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

    # Abilita iterative scan per filtri restrittivi
    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. Inserisci documenti di esempio
    sample_docs = [
        {
            "title": "Introduzione a PostgreSQL",
            "content": "PostgreSQL e un RDBMS open-source con supporto ACID completo.",
            "category": "database",
        },
        {
            "title": "Indici in PostgreSQL",
            "content": "I B-tree sono il tipo di indice default. HNSW e usato per vettori.",
            "category": "database",
        },
        {
            "title": "Cos'è il RAG",
            "content": "RAG combina retrieval e generation per risposte basate su documenti.",
            "category": "ai",
        },
        {
            "title": "Docker per Sviluppatori",
            "content": "Docker isola le applicazioni in container leggeri e portabili.",
            "category": "devops",
        },
        {
            "title": "Embeddings e Vector Search",
            "content": "Gli embeddings trasformano testo in vettori per la similarity search.",
            "category": "ai",
        },
    ]
    insert_documents(sample_docs)

    # 3. Cerca documenti
    print("\n--- Ricerca: 'come funzionano i database vettoriali' ---")
    results = search("come funzionano i database vettoriali", limit=3)
    for r in results:
        print(f"  [{r.similarity:.3f}] {r.title} ({r.category})")

    print("\n--- Ricerca filtrata per categoria 'ai' ---")
    results = search("tutorial su PostgreSQL", 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

# Configura il modello di embedding
embeddings = OpenAIEmbeddings(
    model="text-embedding-3-small",
    openai_api_key="sk-...",
)

# NOTA: langchain-postgres usa psycopg3 (non psycopg2)
CONNECTION_STRING = "postgresql+psycopg://admin:secret@localhost:5432/vectordb"

# Crea il vector store
vector_store = PGVector(
    embeddings=embeddings,
    collection_name="langchain_docs",
    connection=CONNECTION_STRING,
    use_jsonb=True,  # metadati JSONB per filtri efficienti
)

# Aggiungi documenti
docs = [
    Document(
        page_content="pgvector trasforma PostgreSQL in un vector database",
        metadata={"source": "tutorial.md", "chapter": 1},
    ),
    Document(
        page_content="HNSW e l'indice consigliato per la maggior parte dei casi",
        metadata={"source": "tutorial.md", "chapter": 2},
    ),
    Document(
        page_content="LangChain semplifica la costruzione di sistemi RAG",
        metadata={"source": "guida-rag.md", "chapter": 1},
    ),
]

vector_store.add_documents(docs)

# Ricerca per similarità
results = vector_store.similarity_search(
    "come creare un indice vettoriale",
    k=3,
)
for doc in results:
    print(f"[{doc.metadata['source']}] {doc.page_content[:80]}...")

# Ricerca con score
results_with_scores = vector_store.similarity_search_with_score(
    "cos'è pgvector",
    k=3,
)
for doc, score in results_with_scores:
    print(f"[Score: {score:.4f}] {doc.page_content[:80]}...")

# Usa come retriever in una catena RAG
retriever = vector_store.as_retriever(
    search_type="similarity",
    search_kwargs={"k": 5},
)

# Il retriever può essere usato direttamente nelle chain LangChain
relevant_docs = retriever.invoke("installazione pgvector docker")
    

from psycopg2.extras import execute_values

# SBAGLIATO: un INSERT alla volta (lento)
for doc in documents:
    cur.execute("INSERT INTO docs (content, embedding) VALUES (%s, %s)",
                (doc.content, str(doc.embedding)))

# CORRETTO: batch insert con execute_values (10-50x più veloce)
data = [
    (doc.content, str(doc.embedding))
    for doc in documents
]
execute_values(
    cur,
    "INSERT INTO docs (content, embedding) VALUES %s",
    data,
    page_size=500,  # righe per batch SQL
)
conn.commit()
    

from psycopg2 import pool

# Crea un pool di connessioni (all'avvio dell'applicazione)
connection_pool = pool.ThreadedConnectionPool(
    minconn=5,    # connessioni minime
    maxconn=20,   # connessioni massime
    host="localhost",
    database="vectordb",
    user="admin",
    password="secret_password",
)

# Usa una connessione dal 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)  # restituisci al pool
    

-- VACUUM per recuperare spazio dopo DELETE/UPDATE
VACUUM ANALYZE documents;

-- Ricostruisci l'indice IVFFlat dopo inserimenti massicci
REINDEX INDEX CONCURRENTLY idx_documents_embedding_ivf;
-- NOTA: HNSW non richiede REINDEX per nuovi inserimenti

-- Monitora la dimensione dell'indice
SELECT
    indexname,
    pg_size_pretty(pg_relation_size(indexname::regclass)) AS size
FROM pg_indexes
WHERE tablename = 'documents';

-- Verifica che l'indice venga usato
EXPLAIN ANALYZE
SELECT id, 1 - (embedding <=> '[0.1, 0.2, ...]') AS similarity
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'
LIMIT 5;
-- Cerca "Index Scan using idx_..._hnsw" nell'output