Optimizar PostgreSQL y pgvector para cargas de IA en producción

radio_button_uncheckedshort_text1. Diagnosticar la carga vectorial antes de ajustarla

Imagina un recomendador que creció de 50.000 a dos millones de productos. Una consulta antes cercana a 30 ms supera un segundo y las campañas atraen decenas de miles de usuarios simultáneos. La meta es mantener búsquedas representativas por debajo de 100 ms sin perder exhaustividad útil ni rendimiento estable.

• Mide latencia, exhaustividad, QPS, aciertos de caché, CPU, memoria, E/S, conexiones y retraso de réplica.
• Ajusta memoria y planificador de PostgreSQL.
• Elige ANN por volumen, actualización, precisión y presupuesto de compilación.
• Modifica diseño, proceso, lecturas, caché y conexiones solo según la evidencia.

Resumen del tema

Parte de objetivos explícitos de latencia y exhaustividad y de una línea base parecida a producción para toda la ruta.

radio_button_uncheckedshort_text2. Comprender el costo de la distancia vectorial

Una comparación de 1.536 dimensiones recorre todos los elementos; un examen exacto de un millón de filas supera 1.500 millones de operaciones elementales. L2 (<->) añade una raíz cuadrada. Coseno (<=>) neutraliza la magnitud y es habitual en semántica. Producto interno negativo (<#>) suele ser más ligero, pero representa bien la similitud solo con vectores normalizados.

Duplicar dimensiones duplica almacenamiento y eleva mucho el cálculo. Prueba 768 o 1.024 antes de pagar 1.536 o 3.072. Dos millones de vectores de 1.536 dimensiones rondan 12 GB sin índices; un grafo HNSW puede sumar aproximadamente la mitad o más.

Resumen del tema

Métrica y dimensión determinan relevancia y también CPU, memoria y almacenamiento consumidos por la búsqueda.

radio_button_uncheckedshort_text3. Ajustar memoria sin multiplicar el riesgo

shared_buffers es la caché propia de PostgreSQL. Cerca del 25% de la memoria es solo un comienzo; la configuración de Azure y las mediciones mandan. En acceso vectorial, investiga una tasa de aciertos inferior a aproximadamente 99%. work_mem se asigna por ordenación o hash, posiblemente varias veces por conexión: 256 MB globales con cientos de sesiones pueden agotar RAM, así que usa SET LOCAL.

effective_cache_size no reserva memoria: indica al planificador la caché combinada probable. Cerca del 75% puede iniciar pruebas en un servidor dedicado, siempre adaptado al nivel real.

-- Inspect memory and planner settings
SHOW shared_buffers;
SHOW work_mem;
SHOW effective_cache_size;
SHOW random_page_cost;
SHOW effective_io_concurrency;

-- Change expensive settings only for the current transaction
BEGIN;
SET LOCAL work_mem = '256MB';
SET LOCAL hnsw.ef_search = 100;
-- run the vector query here
COMMIT;

SELECT
  sum(heap_blks_hit)::numeric /
  nullif(sum(heap_blks_hit + heap_blks_read), 0) AS cache_hit_ratio
FROM pg_statio_user_tables;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title
FROM products
WHERE category_id = $2
ORDER BY embedding <=> $1::vector
LIMIT 10;

Resumen del tema

Mantén caliente el conjunto activo, describe la memoria con realismo y limita work_mem alto a la transacción que lo necesita.

radio_button_uncheckedshort_text4. Alinear el planificador y la E/S SSD

random_page_cost 4,0 modela discos giratorios. En SSD administrado, prueba alrededor de 1,1–1,5 para no castigar el índice aleatorio. effective_io_concurrency cerca de 200 puede mejorar la lectura anticipada y Bitmap Heap Scan con filtros.

Sin ANN, más max_parallel_workers_per_gather y costos paralelos menores pueden favorecer el examen exacto paralelo. Comprueba EXPLAIN (ANALYZE, BUFFERS), filas estimadas/reales, Index Scan o Seq Scan, ejecuta ANALYZE tras grandes cargas y revisa Azure Monitor.

Resumen del tema

Los costos deben representar SSD; planes reales y métricas de plataforma validan la mejora.

radio_button_uncheckedshort_text5. Decidir cuándo compensa la indexación aproximada

ANN puede mejorar varios órdenes de magnitud, sacrificando algo de exhaustividad. Compara siempre con vecinos exactos. El examen exacto sigue siendo válido para tablas pequeñas, prefiltrado muy selectivo, precisión obligatoria o datos demasiado cambiantes.

Resumen del tema

Usa ANN cuando domina el costo de examen y conserva una referencia exacta para medir el intercambio entre exhaustividad y latencia.

radio_button_uncheckedshort_text6. Configurar IVFFlat para compilación rápida

IVFFlat agrupa vectores representativos en listas y consulta solo algunas. Compila rápido y consume menos memoria que HNSW: conviene con recursos limitados, meta de 90–95% de exhaustividad, desarrollo y renovaciones masivas. Necesita datos para entrenarse y puede requerir reconstrucción tras un cambio de distribución.

Las listas son heurísticas: unas 100 hasta 100.000 filas, 1.000 cerca de un millón y varios miles en conjuntos multimillonarios. Para dos millones, prueba 1.500–2.000 junto a valores más amplios. Inicia probes cerca de sqrt(lists), o 5–10% en pruebas orientadas a exhaustividad, y elige por medición.

-- IVFFlat: build after representative rows are loaded
CREATE INDEX CONCURRENTLY products_embedding_ivf_idx
ON products USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 2000);

SET LOCAL ivfflat.probes = 45;

-- HNSW: higher memory and build cost, usually stronger recall/latency
CREATE INDEX CONCURRENTLY products_embedding_hnsw_idx
ON products USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

SET LOCAL hnsw.ef_search = 100;

Resumen del tema

IVFFlat favorece compilación y memoria; lists forma grupos y probes gobierna trabajo y exhaustividad.

radio_button_uncheckedshort_text7. Configurar HNSW y evaluar DiskANN

HNSW recorre un grafo multicapa. Empieza m cerca de 16; 32 mejora conectividad con más memoria. ef_construction debe ser al menos 2 × m y suele partir de 4 × m. ef_search suele iniciar en 40: prueba 20 para latencia y 100–200 para alta exhaustividad, nunca menos que LIMIT.

HNSW encaja con mucha lectura, RAM suficiente, inserciones pequeñas y exhaustividad cercana a 99%. DiskANN es otra opción de Azure para gran escala, alta exhaustividad, cuantificación de producto y dimensiones muy superiores en versiones recientes. Comprueba versión y disponibilidad.

Resumen del tema

HNSW invierte memoria y compilación en exhaustividad/latencia; DiskANN amplía la elección para gran escala en Azure.

radio_button_uncheckedshort_text8. Compilar, verificar y mantener índices vectoriales

La clase debe coincidir con el operador: vector_cosine_ops con <=>, vector_l2_ops con <-> y vector_ip_ops con <#>. Una discordancia suele causar Seq Scan. Carga datos antes de IVFFlat, usa CONCURRENTLY en producción, actualiza estadísticas y demuestra el uso con EXPLAIN ANALYZE.

Minutos para IVFFlat sobre un millón y horas para HNSW sobre diez millones son ejemplos, no garantías: hardware, almacenamiento, paralelismo y parámetros dominan. Observa progreso, tamaño e idx_scan y reindexa concurrentemente ante deriva o bloat.

SELECT phase,
       round(100.0 * blocks_done / nullif(blocks_total, 0), 1) AS percent
FROM pg_stat_progress_create_index;

SELECT indexrelname, idx_scan, idx_tup_read,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'products';

REINDEX INDEX CONCURRENTLY products_embedding_hnsw_idx;

Resumen del tema

Crear un índice es una operación observable: operador, estadísticas, progreso, uso y política de reconstrucción importan.

radio_button_uncheckedshort_text9. Diseñar vectores y metadatos para las consultas

Declara dimensiones para rechazar embeddings incompatibles. Separa vectores de título, imagen y comportamiento e indexa cada espacio consultado. Coloca filtros estables y frecuentes en columnas tipadas; reserva JSONB para atributos dinámicos, anidados o raros.

B-tree acelera igualdad y rangos; índices compuestos siguen el prefijo izquierdo y los parciales ayudan a in_stock. GIN soporta contención y claves JSONB; un rango numérico extraído de JSONB necesita un índice de expresión con conversión numérica.

CREATE TABLE products (
  id BIGSERIAL PRIMARY KEY,
  category_id BIGINT NOT NULL,
  price NUMERIC(12,2) NOT NULL,
  in_stock BOOLEAN NOT NULL,
  attributes JSONB NOT NULL DEFAULT '{}',
  title_embedding vector(768),
  image_embedding vector(512)
);

CREATE INDEX products_category_price_idx
  ON products(category_id, price);
CREATE INDEX products_available_category_idx
  ON products(category_id) WHERE in_stock;
CREATE INDEX products_attributes_gin_idx
  ON products USING gin(attributes);
CREATE INDEX products_attribute_price_idx
  ON products (((attributes->>'price')::numeric));

Resumen del tema

Usa un espacio vectorial por propósito, columnas tipadas para predicados comunes y JSONB donde su flexibilidad compense.

radio_button_uncheckedshort_text10. Combinar filtros de metadatos y búsqueda vectorial

Una categoría que selecciona 5% de dos millones reduce el conjunto potencial a 100.000. PostgreSQL puede comenzar por B-tree si el filtro es selectivo o por el índice vectorial si es amplio; EXPLAIN ANALYZE revela la decisión.

Si el predicado se aplica después de ANN, recupera más candidatos que el resultado final. Dimensiona la sobreextracción según selectividad: poca cantidad omite vecinos; demasiada desperdicia la ganancia.

-- Let PostgreSQL combine a selective metadata filter with vector ordering
SELECT id, title
FROM products
WHERE category_id = $2 AND in_stock
ORDER BY embedding <=> $1::vector
LIMIT 10;

-- If filtering occurs after ANN retrieval, deliberately overfetch
WITH candidates AS (
  SELECT id, title, category_id, in_stock,
         embedding <=> $1::vector AS distance
  FROM products
  ORDER BY embedding <=> $1::vector
  LIMIT 100
)
SELECT * FROM candidates
WHERE category_id = $2 AND in_stock
ORDER BY distance LIMIT 10;

Resumen del tema

Filtros selectivos reducen cálculo; el posfiltrado necesita sobreextracción deliberada y plan comprobado.

radio_button_uncheckedshort_text11. Particionar solo si coincide con el acceso

Con decenas de millones de filas, particionar por fecha, inquilino o categoría facilita poda, eliminación de históricos y mantenimiento de índices pequeños. RANGE sirve al tiempo, LIST a pocas categorías y HASH a distribuir claves. Un índice en la tabla madre genera equivalentes por partición.

CREATE TABLE product_vectors (
  id BIGINT, created_at TIMESTAMPTZ NOT NULL, embedding vector(1536)
) PARTITION BY RANGE (created_at);

CREATE TABLE product_vectors_2026_08
PARTITION OF product_vectors
FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

CREATE INDEX ON product_vectors
USING hnsw (embedding vector_cosine_ops);

También añade consultas entre particiones, unicidad que incluye la clave y complejidad operativa. Úsalo solo si consultas importantes llevan esa clave y el plan muestra poda.

Resumen del tema

Particiona por un límite natural de acceso a gran escala, no para compensar un esquema deficiente.

radio_button_uncheckedshort_text12. Escalar proceso según conjunto activo y concurrencia

Como hipótesis, prueba De uso general con 4–8 vCores por debajo de un millón y Optimizada para memoria con 8–16 entre uno y diez millones. Cientos de búsquedas simultáneas pueden requerir 32 o más. Escala verticalmente si una consulta necesita CPU, RAM o E/S; distribuye lecturas si limita el QPS agregado. CPU sostenida sobre 70%, baja residencia o E/S saturada pesan más que el número de filas.

Resumen del tema

Elige nivel por conjunto activo, costo de consulta, concurrencia y saturación medida.

radio_button_uncheckedshort_text13. Distribuir lecturas y guardar resultados en caché

Las réplicas de lectura usan streaming físico asíncrono y tienen endpoint, región y tamaño propios. Sirven recomendaciones y búsquedas que toleran desfase; lectura tras escritura va al primario. Vigila el retraso, que aumenta con lotes, red o réplica pequeña. La documentación actual admite réplicas en De uso general y Optimizada para memoria, no en Ampliable.

Guarda embeddings populares, listas precalculadas, vectores estables de usuario y agregados. Vectores arbitrarios, valores volátiles e infinitas combinaciones de filtros son malos candidatos. Empieza TTL en 15–60 minutos e invalida por evento o actualiza en segundo plano cuando haga falta.

SELECT now() - pg_last_xact_replay_timestamp() AS replica_lag;

# Cache a bounded, repeatable recommendation result
SETEX recommendations:product:4281 1800 '{"ids":[18,77,304]}'

El módulo fuente menciona Azure Cache for Redis. Microsoft anunció su retirada y recomienda Azure Managed Redis para diseños nuevos y migraciones; se conserva el patrón de caché, pero se aplica el servicio actual.

Resumen del tema

Las réplicas aportan lectura con posible retraso; Redis elimina trabajo repetido cuando claves y frescura son controlables.

radio_button_uncheckedshort_text14. Supervisar capacidad, crecimiento y costo

Observa CPU, memoria, E/S, conexiones, P95/P99, QPS, exhaustividad, aciertos de caché y retraso. Umbrales ilustrativos podrían avisar CPU superior a 80% por cinco minutos y memoria por encima de 90%, pero la línea base y el SLO deciden.

1. Registra estado y pico.
2. Proyecta catálogo, dimensiones, actualización, QPS y concurrencia.
3. Prueba vectores y filtros representativos.
4. Documenta disparadores para nivel, réplica, caché, reconstrucción o partición.
5. Revisa tras cambios de modelo o tráfico.

Reduce costo si CPU permanece bajo 30% con margen excesivo. Evalúa reserva para carga estable, conserva elasticidad para picos, elimina índices sin uso, archiva vectores viejos, usa la menor precisión aprobada y reserva Ampliable para desarrollo.

Resumen del tema

Planificar capacidad une telemetría, previsión, disparadores y costo en un ciclo repetible.

radio_button_uncheckedshort_text15. Agrupar conexiones con PgBouncer

Abrir una conexión puede consumir 50–200 ms entre TCP, TLS, autenticación, backend e inicio de sesión. Los máximos cambian por nivel y tamaño; cifras como 859 en un servidor general pequeño o cerca de 5.000 en uno grande son ejemplos. Consulta los límites desplegados.

PgBouncer integrado funciona en De uso general y Optimizada para memoria por el puerto 6432. transaction devuelve la conexión al hacer commit/rollback y suele ser ideal para una API vectorial. session conserva funciones de sesión pero reutiliza menos; statement maximiza reutilización sin transacciones multiinstrucción.

# Built-in PgBouncer endpoint uses port 6432
postgresql://app@server:password@server.postgres.database.azure.com:6432/appdb

# Suggested starting posture; benchmark for the real workload
pool_mode = transaction
default_pool_size = 40
max_client_conn = 5000
query_wait_timeout = 60

Con transaction, SET normal no persiste: usa SET LOCAL o predeterminados. Prepared statements con nombre pueden ligarse al backend y LISTEN/NOTIFY no es compatible. Empieza alrededor de 20–50 conexiones por pool, deja margen y mide.

Resumen del tema

PgBouncer amortiza apertura y protege límites; transaction funciona cuando el código no depende del estado de sesión.

radio_button_uncheckedshort_text16. Combinar pools de SDK, lotes, asincronía y resiliencia

El pool de aplicación reduce churn antes de PgBouncer. Si 1.000 backends seguros se dividen entre diez instancias, 100 por instancia aún necesita margen. Un pool pequeño crea espera y uno enorme sobrecarga. Recicla tras 30–60 minutos y usa cadenas Npgsql idénticas para no fragmentar pools.

from psycopg_pool import AsyncConnectionPool

pool = AsyncConnectionPool(
    conninfo=DATABASE_URL,
    min_size=5,
    max_size=20,
    max_idle=300,
    max_lifetime=3600,
)

async with pool.connection() as conn:
    async with conn.transaction():
        await conn.execute("SET LOCAL hnsw.ef_search = 100")
        rows = await conn.execute(
            "SELECT id FROM products ORDER BY embedding <=> %s LIMIT 10",
            (query_vector,),
        )

Agrupa identificadores con ANY y usa COPY para ingestión grande. La asincronía mejora rendimiento esperando E/S sin superar el pool. Define connect_timeout y statement_timeout, trata PoolTimeout como saturación controlada y reintenta OperationalError transitorio pocas veces con espera exponencial y jitter.

Resumen del tema

Pool limitado, PgBouncer, SET LOCAL, lotes, asincronía, tiempos de espera y reintentos finitos forman una estrategia.

radio_button_uncheckedshort_text17. Laboratorio guiado: optimizar búsqueda vectorial

El ejercicio fuente reserva unos 30 minutos para una comparación controlada.

1. Prepara suscripción de Azure, Visual Studio Code, Azure CLI reciente y psql.
2. Descarga archivos iniciales y revisa la implementación.
3. Implementa Base de Datos de Azure para PostgreSQL con autenticación Microsoft Entra.
4. Genera datos de prueba con embeddings.
5. Registra línea exacta y plan.
6. Crea IVFFlat y HNSW y compara latencia/exhaustividad con el resultado exacto.
7. Ajusta lists/probes o m/ef una variable cada vez, documenta y elimina recursos desechables.

Resumen del tema

El laboratorio produce una referencia exacta, dos experimentos ANN y una decisión documentada de velocidad frente a exhaustividad.

radio_button_uncheckedshort_text18. Evaluación comentada y lista de producción

1. Con dos millones de vectores de 1.536 dimensiones y 85% de aciertos, prioriza shared_buffers y verifica el conjunto activo.
2. Para cinco millones renovados diariamente con compilación corta, IVFFlat con lists según volumen encaja mejor que HNSW.
3. Si category_id filtrado causa Seq Scan, confirma primero B-tree en esa columna; luego clase vectorial y estadísticas.
4. Con 500 búsquedas/s, usa PgBouncer transaction y pool repartido, no una conexión por solicitud.
5. Con CPU 75% en De uso general y P95 individual menor de 50 ms, prueba primero un nivel Optimizado para memoria mayor; réplica y caché no aceleran la consulta individual no almacenada.

• Mide línea base y exhaustividad.
• Mantén dimensión, métrica, operador y clase coherentes.
• Comprueba planes relacionales y ANN.
• Vigila compilación, uso, deriva, bloat y retraso.
• Aplica la guía actual de Redis y limita pools/reintentos.
• Documenta disparadores y reevalúa todo cambio material.

Referencias oficiales

1. Optimizar el rendimiento de pgvector en Base de Datos de Azure para PostgreSQL
2. Habilitar y usar DiskANN
3. Réplicas de lectura en Base de Datos de Azure para PostgreSQL
4. PgBouncer integrado en Base de Datos de Azure para PostgreSQL
5. Migrar a Azure Managed Redis

Resumen del tema

El rendimiento vectorial en producción procede de ajustes medidos en proceso, datos, índices, distribución, caché y conexiones.