PostgreSQL + pgvector: El Stack Subestimado para IA

El Problema con Vector Databases Especializadas

Pinecone, Weaviate y similares ofrecen una propuesta atractiva: "drop-in vector search". Pero vienen con costos ocultos:

• Pricing impredecible: Pinecone Standard: $70/mes base + $0.40/1M queries. A 10M queries/mes: $170/mes
• Vendor lock-in severo: APIs propietarias, migración compleja
• Fragmentación de datos: Embeddings en Pinecone, metadata en PostgreSQL = 2 sistemas a mantener
• Latencia de red: API calls externos añaden 50-200ms vs queries locales
• Sin SQL joins: Imposible combinar búsquedas vectoriales con queries relacionales complejas

pgvector: Extensión PostgreSQL para Vector Search

pgvector es una extensión open-source que añade soporte de vectores a PostgreSQL. Instalación: 1 línea SQL:

CREATE EXTENSION vector;

Una vez instalada, puedes almacenar embeddings como columnas tipo vector:

CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  content TEXT,
  embedding vector(1536),  -- OpenAI ada-002 dimension
  metadata JSONB,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Index para búsquedas rápidas
CREATE INDEX ON documents
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

Operaciones de Búsqueda

Semantic search con SQL estándar:

-- Búsqueda por similitud coseno
SELECT id, content, metadata,
       1 - (embedding <=> '[0.1, 0.2, ...]') as similarity
FROM documents
WHERE metadata->>'category' = 'technical'
ORDER BY embedding <=> '[0.1, 0.2, ...]'
LIMIT 10;

Nota clave: Combinas búsqueda vectorial + filtros SQL en una sola query.

Ventajas de PostgreSQL + pgvector

Performance: pgvector vs Pinecone

Benchmarks con 1M embeddings (OpenAI ada-002, 1536 dimensions):

Conclusión: pgvector es 15% más lento en latencia pura, pero elimina 50-100ms de network overhead de API calls. Recall equivalente. Para la mayoría de aplicaciones, diferencia imperceptible.

Cuándo pgvector NO es suficiente

• 100M+ embeddings con queries <10ms requeridas
• Multi-tenancy extremo (1000+ tenants con aislamiento estricto)
• Real-time updates a escala massive (>10K writes/sec)

Para 95% de aplicaciones B2B (incluyendo las que tienen "millions of users"), pgvector performance es más que suficiente.

Arquitectura Real: Sistema AI con pgvector

Caso de estudio SeducSer (500K usuarios activos):

Schema PostgreSQL

-- Usuarios y contexto
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email TEXT UNIQUE,
  metadata JSONB
);

-- Embeddings de conversaciones
CREATE TABLE conversation_embeddings (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  content TEXT,
  embedding vector(1536),
  conversation_id TEXT,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Index para búsquedas
CREATE INDEX ON conversation_embeddings
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

-- Knowledge base
CREATE TABLE knowledge_base (
  id SERIAL PRIMARY KEY,
  category TEXT,
  title TEXT,
  content TEXT,
  embedding vector(1536),
  metadata JSONB
);

CREATE INDEX ON knowledge_base
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 50);

Workflow Típico

1. User query arrives via n8n webhook
2. Generate embedding con OpenAI API (ada-002)
3. Semantic search en knowledge_base + conversation history:

   SELECT kb.title, kb.content, kb.metadata,
          1 - (kb.embedding <=> $1) as similarity
   FROM knowledge_base kb
   WHERE kb.category = 'faq'
     AND kb.metadata->>'active' = 'true'
   ORDER BY kb.embedding <=> $1
   LIMIT 5;

4. Context assembly con resultados + user history (JOIN)
5. LLM call (Claude/GPT-4) con contexto enriquecido
6. Store interaction con nuevo embedding para future context

Setup: PostgreSQL + pgvector en Producción

Deployment production-ready toma 1-2 horas:

Opción 1: Docker (Desarrollo/Small Scale)

docker run -d \
  --name postgres-pgvector \
  -e POSTGRES_PASSWORD=secreto \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql/data \
  pgvector/pgvector:pg17

Opción 2: VPS Managed (Producción)

Hetzner/DigitalOcean VPS con PostgreSQL 17:

# Install pgvector
sudo apt install postgresql-17-pgvector

# Enable extension
sudo -u postgres psql -d mydb -c "CREATE EXTENSION vector;"

# Configure for vector workloads
# postgresql.conf:
shared_buffers = 2GB
effective_cache_size = 6GB
maintenance_work_mem = 512MB
max_parallel_workers_per_gather = 4

Indexing Strategy

Para tablas >100K embeddings, usa IVFFlat index:

-- Lists = sqrt(total_rows) es buen starting point
CREATE INDEX ON embeddings_table
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 316);  -- para ~100K rows

-- Para tablas grandes (>1M rows), considera HNSW (PostgreSQL 17+)
CREATE INDEX ON embeddings_table
USING hnsw (embedding vector_cosine_ops);

Monitoring

-- Query performance
EXPLAIN ANALYZE
SELECT * FROM documents
ORDER BY embedding <=> '[...]'
LIMIT 10;

-- Index usage stats
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE indexname LIKE '%embedding%';

Casos de Uso Reales

1. RAG (Retrieval Augmented Generation)

Use case más común: enriquecer prompts LLM con contexto relevante. pgvector almacena knowledge base, queries buscan top-k documentos similares.

2. Semantic Search en SaaS

Reemplazar full-text search tradicional con búsqueda semántica. Ejemplo: "find similar support tickets" sin keyword matching.

3. Recommendation Systems

User embeddings + item embeddings = recommendations via cosine similarity. SQL JOINs permiten filtros complejos (price range, category, etc.)

4. Duplicate Detection

Detectar contenido duplicado/similar: embeddings de documentos, buscar pares con similarity > threshold.

Migración: Pinecone → pgvector

He migrado 2 proyectos de Pinecone a pgvector. Proceso estándar:

1. Export from Pinecone (API batch fetch, 10K vectors/call)
2. Schema design en PostgreSQL con metadata columns apropiadas
3. Bulk insert con COPY command (1M embeddings: ~5 min)
4. Create indexes (IVFFlat, ~10 min para 1M rows)
5. Update application code (SQL queries vs Pinecone SDK)
6. Testing paralelo validar recall equivalente
7. Cutover apagar Pinecone, monitorear performance

Timeline total: 2-3 días para 1M+ embeddings.ROI inmediato: $70-240/mes ahorrados.