Posts Tagged - postgresql

Tune PostgreSQL for pgvector

(leer este post muy por encima; saber más o menos para que son, pero esto es ya mucho detalle; doc og)

pgvector compute and memory requirements

Los cálculos de distancias vectoriales son muy diferentes a una BBDD tradicional. Para una búsqueda vectorial de proximidad con 1536 dimensiones cada búsqueda conlleva 1536 operaciones con floats. Buscar en 1.000.000 de vectores sin un índice, requiere de 1.5 billion operaciones por query.

Esto también depende de la función de distancia que escojamos:

  • L2 (Euclidean) distance <-> - es la más costosa computacionalmente hablando
  • Cosine distance <=> - relación coste/performance media. Recomendada a usar para recommendation engines y semantic search ya que procesa vectores de diferentes magnitudes de manera consistente
  • Inner product <#> - es la más rápida pero necesita vectores pre-normalizados. A utilizar si tenemos vectores normalizados ya que requiere menos computación (muchos modelos de OpenAI ya devuelven los vectores normalizados)

IMPORTANTE

Los vectores consumen más almacenamiento que otras columnas de una BBDD relacional. Un vector de 1536 dimensiones guardado como float4 con 1.000.000 de productos necesita 6 GB solo para la columna de los vectores. PostgreSQL carga estos vectores en memoria cuando se hacen operaciones con ellos.

Vectores con más dimensiones ofrecen mejor resolución semántica, pero también aumentan dramaticamente los requisitos de memoria y computacionales. Un vector de 3072 dimensiones requiere x4 el trabajo para cálculos de distancia y x2 de espacio de almacenamiento respecto a vectores con 1536 dimensiones.

/IMPORTANTE

Memory config for vector workloads

La memoria del server de PostgreSQL afecta en gran medida a la vector query performance. Con un buen tuning los indices vectoriales y los datos frecuentemente leidos, se mantienen en memoria.

param shared_buffers

IMPORTANTE

El param shared_buffers controla la shared memory cache donde se guardan estos datos. Para trabajos vectoriales esta cache debería ser lo suficientemente grande como para almacenar todos tus indices vectoriales y hot data. Un cache hit ratio por debajo de 99% indica que shared_buffers puede ser muy pequeño.
Se ajusta automáticamente según tu tier Postgres en Azure, pero se puede ajustar dentro de los limites del tier.
Para apps de búsquedas vectoriales, comenzar con un 25% de la memoria disponible, monitorizar y aumentar / reducir

/IMPORTANTE

-- Check current setting
SHOW shared_buffers;

-- View buffer cache hit ratio
SELECT
    sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS cache_hit_ratio
FROM pg_statio_user_tables;

param work_mem

El param work_mem controla la memoria disponible para query ops individuales como sorts y hash joins. Se usa especialmente en vector similarity queries, especialmente las que combinan vector search con filtering y ordering.

El default (4 MB) es muy pequeño para vector ops que deben ordenar resultados por similarity.

-- con cuidado
SET work_mem = '256MB'

Usarlo con cuidado ya que esto aplica a por operacion y por conexión. Se puede configurar por session level para queries específicas y no globalmente

param effective_cache_size

El param effective_cache_size le dice al query planner cuanta memoria hay disponible para caching. Settearla al 75% del total system memory. Valores más altos animan al planner a usar indices, lo cual es beneficioso para vector search.

El query planner toma decisiones sobre como ejecutar queries basandose en cost estimates. Tunearlos para SSD storage modernos mejora el vector query planning

*(Azure PostgreSQL usa SSDs, por esto es todo este toquetear params).

param random_page_cost

El param random_page_cost estima el coste de leer una random disk page relativa a una page secuencial. Tiene un default de 4.0. Bajarlo a 1,1-1,5 anima al planner a usar index scans.

SET random_page_cost = 1.1;

param effective_io_concurrency

El param effective_io_concurrency dice cuantas I/O ops concurrentes puede manejar el sistema. Settearla a 200 para mejorar performance para queries que combinan vector similarity con metadata filtering

param parallel_tuple_cost

Los params parallel_tuple_cost y parallel_setup_cost controlan la ejecución paralela de queries. Las Vector ops se benefician de ella en escaneos secuenciales de tablas grandes.

SHOW parallel_tuple_cost;
SHOW parallel_setup_cost;
SHOW max_parallel_workers_per_gather;

Configure pgvector-specific parameters

IVFFlat params

Al usar indices IVFFlat el param ivfflat.probes controla cuantas particiones (listas) se buscan por query. Aumentarla sube el recall value por queries pero las hace más lentas. Es un balanceo complicado.

-- Configure IVFFlat search depth
SET ivfflat.probes = 10;

-- Execute vector search
SELECT id, name, embedding <=> $1 AS distance
FROM products
ORDER BY embedding <=> $1
LIMIT 10;

HNSW params

El param hnsw.ef_search controla el tamaño de la dynamic candidate list durante la búsqueda. Valores más grandes exploran más partes del grafo a costa de velocidad.

Monitor and measure performance

NUNCA HACER TUNING SIN MEDIR ANTES Y DESPUÉS.

Usar el comando EXPLAIN ANALYZE para ver como PostgreSQL ejecuta una query y ver sus timings. Para queries vectoriales, esto indica si se usan los indices y donde se ocupa el tiempo.

Si ves scans secuenciales y esperas indices, comprobar los operadores y que se usen.

Hay veces que PostgreSQL decide no usar indices disponibles. Esto puede ocurrir en queries que devuelven una gran parte de la tabla (index overhead excede el sequential scan, outdated statistics o distance op distinto del que tiene el indice)

Best practices for pgvector tuning

  • Establish baselines first: Measure antes de hacer cambios, measure después y comparar
  • Change only one param at a time and measure again
  • Test with production-like data
  • Monitor for regressions: con el tiempo la performance se puede degradar
  • Document your settings: documentar que cambias, por que y que efecto ha tenido

Read More

Scale for high-volume workloads (PostgreSQL)

Vertical scaling on Azure

Azure Database for PostgreSQL ofrece 3 compute tiers cada uno con diferentes recursos

Tier vCores Memoria por vCore Ideal para
Burstable 1-20 2 GB DEV; low-traffic
General purpose 2-96 4 GB Balanced PROD
Memory optimized 2-96 8 GB Large working sets; vector workloads

Escalar verticalmente hasta que encuentres hard limits o el coste sea prohibitivo. Entonces escalar horizontalmente.

Read replicas for query distribution

Read replicas mantiene copias de tu BBDD primaria que pueden encargarse de read queries de manera independiente.

El server primario se encarga de read y write, mientras que las réplicas se encargan solo de read. Puedes tener hasta 5 replicas por primaria y las réplicas se pueden localizar en diferentes Azure regions. Cada replica tiene su propio connection endpoint, por lo que la App debe gestionar las queries correctamente.

No todas las vector search se benefician por igual del cacheo. Los mejores candidatos tienen alta frecuencia, low change rates y espacios vectoriales similares.

Ejemplos de poor caching candidates:

  • arbitrary vector similarity queries
  • rapidly changing data
  • queries with many filter combinations

se recomienda cachear embedding lookups y recomendaciones pre-computadas

import redis
import json

redis_client = redis.Redis(host='your-cache.redis.cache.windows.net',
                          port=6380, ssl=True, password='your-key')

def get_product_embedding(product_id):
    cache_key = f"embedding:{product_id}"
    cached = redis_client.get(cache_key)

    if cached:
        return json.loads(cached)

    # Fetch from database
    embedding = fetch_embedding_from_db(product_id)

    # Cache for 1 hour
    redis_client.setex(cache_key, 3600, json.dumps(embedding))
    return embedding

Monitor capacity

Se puede hacer track de database-level metrics con Azure Monitor

  • CPU percentage
  • memory percentage
  • storage IO percentage
  • active connections
  • query metrics incluing P95/P99 latency
  • etc

Read More

Optimize data layout (PostgreSQL)

Vector storage considerations

Las dimensiones de los modelos afectan al espacio que necesitamos para almacenamiento.

Dimensiones 1M vectors size
384 1.5 GB
768 3 GB
1536 6 GB
3072 12 GB

Para un product catalog de 1M de vectores usando 3072 necesitamos 12 GB de almacenamiento solo para vectores. Si además usamos un HNSW index esto se incrementa por un 50%.

IMPORTANTE

A menores dimensiones menos requisitos de almacenamiento y computacion. Un consejo es especificar las dimensiones en la definición de la columna, ya que esto aporta validación. Si intentar insertar vectores de diferentes dimensiones, falla con un error en vez de subtle bug de mismatched embedding models.

Si tenemos items con varias columnas de embeddings (por ejemplo titulo e imagen), cada columna necesita su propio index.

NO SE PUEDE CREAR UN SINGLE INDEX QUE CUBRA VARIAS VECTOR COLUMNS

CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    title_embedding vector(768),      -- Text embedding model
    image_embedding vector(512),       -- Image embedding model
    category_id INTEGER,
    price NUMERIC(10,2)
);

-- Create separate indexes for each embedding type
CREATE INDEX ON products USING hnsw (title_embedding vector_cosine_ops);
CREATE INDEX ON products USING hnsw (image_embedding vector_cosine_ops);

/IMPORTANTE

Metadata data types: structured columns vs JSONB

Usa columnas estructuradas (INTEGER, TIMESTAMP, etc.) cuando los atributos sean conocidos en tiempo de diseño, filtres u ordenes frecuentemente por atributos específicos o la query performance sea crítica

CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    embedding vector(1536),
    category_id INTEGER NOT NULL,
    price NUMERIC(10,2) NOT NULL,
    in_stock BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    brand TEXT,
    rating NUMERIC(2,1)
);

JSONB guarda datos semi-estructurados como JSON Binario, lo que ofrece flexibilidad y atributos dinámicos. JSONB provee de schema flexibility, easy evolution y nested structure, PERO tiene query overhead, límites para indices y las estadísticas son menos precisas.

JSONB se usa para contenido dinámico o que raramente se filtra u ordena donde valoramos más esa flexibilidad en el schema.

 CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    embedding vector(1536),
    -- Structured columns for common filters
    category_id INTEGER NOT NULL,
    price NUMERIC(10,2) NOT NULL,
    in_stock BOOLEAN DEFAULT true,
    -- JSONB for dynamic attributes
    attributes JSONB DEFAULT '{}'
);

Metadata indexes for filtered searches

Los indices de metadatos complementar a los indices vectoriales acelerando la fase de filtrado de queries. Sin un indice apropiado de metadatos, PostgreSQL puede necesitar escanear todas las rows para aplicar filtros antes de la búsqueda vectorial.

Un single-column index se encarga de matches exactos Un composite index gestiona filter combinations. Son los más efectivos pero tienen sus limites.

Un índice (category_id, price) se activará si buscamos por WHERE category_id = 5 y también con WHERE category_id = 5 AND price < 100 pero no se activará con WHERE price < 100

-- Single-column index for exact matches
CREATE INDEX idx_products_category ON products (category_id);

-- Composite index for common filter combinations
CREATE INDEX idx_products_category_price ON products (category_id, price);

Indices parciales

Podemos crear indices parciales. Esto son indices que un tamaño reducido pero que igualmente mejoran la performance para queries filtradas.

Por ejemplo si tenemos una app donde la mayoría de queries se ejecutan con WHERE in_stick = true podemos crear un indice parcial cuyo target sea justo esas qeuries

CREATE INDEX idx_products_instock ON products (category_id) WHERE in_stock = true

Indices JSONB

(se puede, pero no me voy a meter en este cristo para la cert)

Tablas particionadas (large datasets)

Las tablas grandes (recomendado >10.000.000 rows) se pueden particional en tablas más pequeñas y manejables. Para vector workloads particionar puede mejorar la performance y simplificar el mantenimiento.

Cada partición tiene sus propios indices. Si creamos un indice en la tabla padre, automaticamente se crea en todas las particiones.

-- Create partitioned table
CREATE TABLE user_interactions (
    id BIGSERIAL,
    user_id BIGINT NOT NULL,
    embedding vector(768),
    created_at TIMESTAMP NOT NULL,
    interaction_type TEXT
) PARTITION BY RANGE (created_at);

-- Create partitions for each month
CREATE TABLE user_interactions_2025_01
    PARTITION OF user_interactions
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

Read More

Connection Optimization (PostgreSQL)

(de momento lo dejo aqui; continuar)

Cada conexión consume server memory, requiere auth overhead y cuenta contra el server limit.

Connection overhead

El crear una nueva conexión a PostgreSQL conlleva 50-200 ~ ms. Para una app que maneje miles de requests / s, esto puede ser muy costoso.

Azure PostgreSQL limita las conexiones según el tier (burstable permite 50 conexiones, la más cara 5.000)

Connection pooling with PgBouncer

PgBouncer es un connection pooler en el puerto 6432 que está entre tu app y PostgreSQL. Mantiene una pool de DDBB connections a la que se conectan los clientes para las peticiones, lo que reduce el número de conexiones requeridas.

La connection string usa el formato postgresql://user:password@myserver.postgres.database.azure.com:6432/mydb

IMPORTANTE

Burstable tier NO soporta PgBounder

/IMPORTANTE

az postgres flexible-server parameter set \
	--resource-group myResourceGroup \
	--server-name myserver \
	--name pgbouncer.enabled \ 
	--value true

PgBounder soporta varios modos de pooling

  • Sesion mode - un cliente mantiene la server connection para toda la sesión. Esto provee una minimal connection reduction
  • Transaction mode - el cliente mantiene la conexión solo durante la transacción. Entre transacciones (commit o rollback) la connection vuelve a la pool. Este modo funciona bien para la mayoría de aplicaicones y reduce los requerimientos de conexiones
  • Statement mode - un cliente obtiene una conexion solo para statements individuales. Es el mejor para vector search workloads.

PgBounder ofrece varios params

  • pgbouncer.default_pool_size 20-50 dependiendo de concurrency needs
  • pgbouncer.max_client_conn +5000 para high-traffic apps
  • pgbouncer.pool_mode (transaction)
  • pgbouncer.query_wait_timeout (30-120s)

Application-level connection pooling

Aparte de PgBouncer tu app puede hacer manage de la connection pool directamente con psycopg_pool. Esto da más finer control y se integra mejor, aparte te permite revisar connection lifecycle, idle timeout y health checks.

Combinado con PgBouncer app pools gestionan local connection management mientras que PgBouncer se encarga del server-side.

from psycopg_pool import ConnectionPool

# Create a connection pool
pool = ConnectionPool(
    conninfo="postgresql://user:password@myserver.postgres.database.azure.com:6432/mydb",
    min_size=5,      # Minimum connections to maintain
    max_size=20,     # Maximum connections allowed
    max_idle=300,    # Close idle connections after 5 minutes
    max_lifetime=3600  # Recycle connections after 1 hour
)

# Use connections from the pool
def search_similar_products(query_embedding, limit=10):
    with pool.connection() as conn:
        with conn.cursor() as cur:
            cur.execute("""
                SELECT id, name, embedding <=> %s AS distance
                FROM products
                ORDER BY embedding <=> %s
                LIMIT %s
            """, (query_embedding, query_embedding, limit))
            return cur.fetchall()

Session management for AI workloads

Efficient SDK usage patterns

Connection resilience

Read More

Choose and configure vector indexes (PostgreSQL)

Los indices vectoriales permiten busquedas rapidas de similaridad organizando vectores en data structures que evitan la necesidad de comparar vector a vector con cada query.

Vector index fundamentals (NN vs ANN)

Sin un índice, las búsquedas necesitan un scan secuencial que compare vector a vector. Estos scans secuenciales calculan el nearest neighbour (NN), garantizando un true closest vector a costa de performance y query time que escala linearmente con el tamaño de la tabla.

En vez de NN search, podemos hacer indices de approximate nearest neighbour (ANN) lo cual cambia esa perfect accuracy por queries dramáticamente más rápidas. ANN usa data structures para encontrar vectores que son probablemente (pero no garantizado) de los más próximos. Con ANN el query time crece logarítmicamente o sublinear con el tamaño de la tabla.

La precisión de ANN se mide como recall. Si pides los 10 resultados más próximos y te entrega 8, se dice que tiene un recall de un 80%. Para AI apps un 95-99% es aceptable.

cuando es necesario implementar índices

Table size Index benefit
<10.000 rows limitado; secuential scans pueden ser suficientemente rápidos
10.000 - 100.000 moderado; los índices reducen la latencia bastante
100.000 - 1.000.000 significante; se requieren índices para uso interactivo
> 1.000.000 obligatorio; sin índices las queries son realmente lentas

IVFFlat (Inverted File Flat) index

Los IVFFlat index particionan vectores en clusters y entonces buscan solo los clusters relevantes para una query. Este approach reduce el search space, manteniendo una precisión razonable.

Durante la index creation, pgvector clusteriza tus vectores en un número especifico de listas, donde cada lista contiene vectores que son similares entre ellos. Al hacer una query, pgvector identifica las listas cuyos centroids son más próximos al query vector, y entonces calcula la distancia solo con los vectores de esas listas. Si tu query calcula distancia sobre 10 listas, habiendo un total de 1000, has reducido efectivamente el search space en un 99%.

Estos índices ofrecen fast build times, lower memory usage y runtime tunability, PERO requieren training data. Con pocos datos no funcionan, tienen un lower recall ceilling y necesitan hacer rebuilding cuando los datos cambian significativamente.

IMPORTANTE

Al crear el índice especificamos el numero de listas y el operador a usar. Hay que usar un operador de distancia en las queries que haga match con el puesto en el índice.

caso clase operador
cosine distance vector_cosine_ops <=>
euclidean distance vector_l2_ops <->
negative inner product vector_ip_ops <#>

/IMPORTANTE

-- Create IVFFlat index for cosine distance
CREATE INDEX ON products
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 1000);
numero de productos numero de listas a crear
<100.000 rows 100
100.000 - 1.000.000 1.000
1.000.000 - 10.000.000 4.000 - 10.000

El param ivfflat.probes controla cuantas listas se buscan por query. Valores más altos pueden mejorar el recall pero también incrementar el query time.

HNSW (Hierarchical Navigable Small World) index

Los HNSW index organizan vectores en una multi-layer graph structure que permite una navegación eficiente desde cualquier starting point al NN.

HNSW construye un grafo donde cada vector es un nodo conectado a su NN. El grafo tiene multiples capas, donde las capas mas altas contienen menos nodos y sirven como express lanes para navegar. Cuando haces una query, HNSW comienza con un punto fijo de entrada en la top layer, se mueve hacia el query vector usando las capas superiores y luego refina los resultados con las capas más densas.

Estos índices ofrecen un higher recall rate (+99%), no training dependency y performance consistente, PERO requieren más uso de memoria, longer build times y fixed parameters (cambiar m o ef_construction requiere reconstruir el índice).

Al crear un HNSW index especificamos:

  • el número de conexiones que cada nodo mantiene con otros nodos m. Valores más altos crean un grafo más denso con mejor recall pero más consumo de memoria y longer build times.
  • el construction search width ef_construction controla la search width durante la construccion del indice. Valores más altos resultan en un indice de mejor calidad, pero aumentan el build time. Este param solo afecta al build time y calidad del grafo resultante.

Aplican las mismas reglas de operadores que para IVFFlat:

caso clase operador
cosine distance vector_cosine_ops <=>
euclidean distance vector_l2_ops <->
negative inner product vector_ip_ops <#>
-- Create HNSW index for cosine distance
CREATE INDEX ON products
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

El param hnsw.ef_search controla el search width en query time (similar a probes para IVFFlat). Este valor debería ser por lo menos tan grande como el número de resultados de tus queries (LIMIT clause)

Choose an index type

IVFFlat

Requisitos

  • memory budget limitado
  • build time tolerance low
  • 90-95% recall es aceptable
  • data update frequency incluye frequent bulk updates

Ejemplo de IVFFlat: app con large catalogs con updates frecuentes donde añadimos o hacemos update de muchos productos en batch, o si tenemos una memoria limitada.

HNSW

Requisitos

  • tenemos mucha memoria
  • podemos tolerar longer build times
  • necesitamos 99% recall
  • el workload son mayormente reads con pocos updates

Ejemplo de HNSW: recommendation engine. Necesitamos high recall para que los usuarios vean todos los productos y sub-10ms query latency para mucho concurrencia.

Create and maintain indexes

Index building bloquea la tabla a writes, por defecto. Para PROD usar CONCURRENTLY para permitir writes durante index building. Tomará más tiempo y requiere más recursos, pero evita downtime.

-- Build index without blocking writes
CREATE INDEX CONCURRENTLY idx_products_embedding
ON products USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

Los indices se degradan con el tiempo por lo que hay que monitorizar síntomas como la latencia regularmente y estar atento a cuando haga falta reconstruirlos.

REINDEX INDEX CONCURRENTLY idx_products_embedding

Read More

Run vector similarity search for semantic retrieval (PostgreSQL)

Effective semantic retrieval combina vector similarity con metadata filtering, distance thresholds y query patterns.

Combine vector search con metadata filters

Raramente deberiamos ejecutar búsquedas sobre el corpus de documentos completo. Por lo general queremos resultados filtrados por categoria, fechas, autor, etc.

El resto es combinar vector similarity con filtros, manteniendo una query performance aceptable

Podemos añadir un WHERE para filtrar documentos. Primero se filtra y luego se calcula la distancia solo para los docs filtrados.

SELECT id, title, embedding <=> $1 AS distance
FROM documents
WHERE category = 'contracts'
  AND practice_area = 'corporate'
  AND created_at > '2024-01-01'
ORDER BY embedding <=> $1
LIMIT 10;

“To maintain performance with filtered queries, create B-tree indexes on columns you frequently filter”

Para queries que siempre combinan columnas específicas se recomienda usar composite indexes, ya que pueden ser más eficientes que multiple single-column indexes.

Implement distance thresholds

Usar LIMIT devuelve los top N most similar docs independientemente de su similitud real. Usar distance thresholds asegura un minimum quality filtrando resultados que caigan por debajo del límite.

Podemos filtrar por distancia con un WHERE . Esta query devuelve como máximo N docs, pero solo si cumplen con el límite. Si no hay docs cercanos, devuelve vacío.

SELECT id, title, embedding <=> $1 AS distance
FROM documents
WHERE embedding <=> $1 < 0.4
ORDER BY embedding <=> $1
LIMIT 10;

El distance thresholds necesita entender los datos. Primero deberiamos analizar los datos y calcular distancias y queries para ver resultados relevantes.

Por ejemplo para cosine distance los thresholds caen entre 0.3 (estricto; alta precisión) y 0.6 (lenient; high recall)

Retrieve documents with content for context

Si necesitamos sacar datos y metadatos de varias tablas, es mejor hacerlo todo de una operación en un single round trip

SELECT
    d.id,
    d.title,
    d.content,
    d.embedding <=> $1 AS distance,
    a.name AS author_name
FROM documents d
JOIN authors a ON d.author_id = a.id
WHERE d.embedding <=> $1 < 0.5
ORDER BY d.embedding <=> $1
LIMIT 5;

Handle multi-vector queries

Algunos escenarios requieren encontrar documentos similares en varias columnas de vectores. En esos casos podemos hacer un average de los vectores para producir un embedding combinado que representa “el centro” de esos embeddings.

busca documentos similares en ambos aspectos y los rankea por su proximidad combinada en ambas queries

WITH
merger_results AS (
    SELECT id, embedding <=> $1 AS distance FROM documents ORDER BY embedding <=> $1 LIMIT 20
),
environmental_results AS (
    SELECT id, embedding <=> $2 AS distance FROM documents ORDER BY embedding <=> $2 LIMIT 20
)
SELECT d.id, d.title,
       (COALESCE(mr.distance, 1) + COALESCE(er.distance, 1)) / 2 AS combined_score
FROM documents d
LEFT JOIN merger_results mr ON d.id = mr.id
LEFT JOIN environmental_results er ON d.id = er.id
WHERE mr.id IS NOT NULL OR er.id IS NOT NULL
ORDER BY combined_score
LIMIT 10;

La busqueda hibrida combina vector similarity con keyword-based full-text search para capturar ambos contenido semántico y relevancia léxica.

PostgreSQL tiene ts_rank for scoring keyword matches. Podemos combinar esto con vector distance (convertida a similarity score) para hybrid ranking. El weight determina cuanto de cada señal contribuye al resultado final

SELECT
    id,
    title,
    (1 - (embedding <=> $1)) * 0.7 +
        ts_rank(to_tsvector('english', content), plainto_tsquery('english', $2)) * 0.3 AS hybrid_score
FROM documents
WHERE to_tsvector('english', content) @@ plainto_tsquery('english', $2)
   OR embedding <=> $1 < 0.5
ORDER BY hybrid_score DESC
LIMIT 10;

“To speed up keyword matching, create a GIN index on the full-text search vector. For frequently searched columns, storing the tsvector in a generated column avoids recomputing it for every query”

Read More

pgvector - store and query embeddings (PostgreSQL)

Enable pgvector extension

La extensión pgvector añade el vector data type y similarity search operators a PostgreSQL. Antes de poder usar embeddings, hay que activar pgvector en la BBDD

comando para activar pgvector

CREATE EXTENSION IF NOT EXISTS vector;

verificar que se ha instalado

SELECT * FROM pg_extension WHERE extname = 'vector'

Design schemas for vector storage

Un buen diseño combina embeddings con los metadatos de la app necesarios para filtrar y mostrar.

Cuando defines una columna de vector, tienes que especificar el número de dimensiones vector(n)

ejemplo de creación que guarda el embedding en la misma tabla que los metadatos

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT,
    category TEXT,
    practice_area TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    embedding vector(1536)
);

si tenemos múltiples embeddings (titulo vs contenido) podemos guardarlos en la misma tabla

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT,
    title_embedding vector(384),
    content_embedding vector(1536)
);

Insert and update embeddings

Para apps con cambios frecuentes quizás es mejor regenerar los embeddings en batches durante off-peak hours y no actualizarlos en tiempo real.

ejemplo de single insert vectores

INSERT INTO documents (title, content, category, practice_area, embedding)
VALUES (
    'Corporate Merger Agreement Template',
    'This agreement outlines the terms and conditions for the merger of...',
    'contracts',
    'corporate',
    '[0.0123, -0.0456, 0.0789, -0.0321, ...]'::vector
);

ejemplo de batch insert vectores

INSERT INTO documents (title, content, category, embedding)
VALUES
    ('Document 1', 'Content...', 'legal', '[...]'::vector),
    ('Document 2', 'Content...', 'legal', '[...]'::vector),
    ('Document 3', 'Content...', 'legal', '[...]'::vector);

ejemplo de update con vectores

UPDATE documents
SET content = 'Updated document content...',
    embedding = '[0.0234, -0.0567, ...]'::vector,
    updated_at = NOW()
WHERE id = 42;

Query vectors with distance ops

pgvector provee de tres distance operators para medir similitud entre vectores. Cada uno calcula un tipo diferente.

L2 distance (Euclidean distance)

L2 distance (distancia euclideana) mide la línea recta entre dos vectores en el espacio vectorial. Es el operador <->. Cuando más pequeños los valores más similares son los vectores.

Funciona muy bien cuando la magnitud de los vectores conlleva significado.

SELECT id, title, embedding <-> '[0.0123, -0.0456, ...]'::vector AS distance
FROM documents
ORDER BY distance
LIMIT 10;

Cosine distance

Cosine distance mide el ángulo entre dos vectores, ignorando sus magnitudes. Es el operador <=>. Los valores van del rango 0 (dirección idéntica) a 2 (direcciones opuestas).

Cosine distance es la opción más común para text embeddings porque se centra en la dirección del significado y no en la longitud del vector.

SELECT id, title, embedding <=> '[0.0123, -0.0456, ...]'::vector AS distance
FROM documents
ORDER BY distance
LIMIT 10;

Negative inner product

Calcula el dot product de dos vectores y lo hace negativo. Es el operador <#>.

Negative inner product es útil para maximum inner product search, donde larger dot products indica vectores más similares. La negación lo convierte a una distancia donde valores más pequeños son mejores.

SELECT id, title, embedding <#> '[0.0123, -0.0456, ...]'::vector AS distance
FROM documents
ORDER BY distance
LIMIT 10;

Vector data types and precision

pgvector ofrece tres data types para guardar vectores, cada uno con un storage y performance diferente.

Para la mayoría de casos es mejor usar el standard vector type. Si necesitamos ahorrar espacio, y solo después de comprobar que la precisión es suficiente, podemos usar halfvec

vector type

vector type almacena elementos como single-precision (32-bit) floats. Es el standard para la mayoría de casos y da buen balance de precisión y almacenamiento.

halfvec

halfvec type almacena elementos como half-precision (16-bit) flaots. Reduce el almacenamiento a la mitad comparado con vector, manteniendo suficiente precisión para al mayoría de similarity search tasks.

Usa halfvec cuando el almacenamiento sea un problema y hayas verificado que la precisión reducida no impacta tus búsquedas

CREATE TABLE documents_compact (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    embedding halfvec(1536)
);

sparsevec

sparsevec type almacena sparse vectors donde la mayoría de elementos son cero. En vez de guardar todas las dimensiones, solo guarda los nonzero values y sus posiciones.

Este tipo es útil para modelos que producen sparse embeddings.

CREATE TABLE sparse_documents (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    embedding sparsevec(10000)
);

Read More

Manage index lifecycle and embedding updates (PostgreSQL)

Los índices vectoriales y los embeddings necesitan ongoing maintenance a medida que tus datos van evolucionando.

Monitor index health and performance

PostgreSQL tiene system views y funciones que revelan si los indices se están usando, cuanto espacio consumen y si las queries se ejecutan como se espera.

La view pg_stat_user_indexes muestra usage patterns para los índices. Si un index muestra cero scans, puede que las queries no la estén usando. Un high scan count confirma que el índice funciona como deberia.

SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE indexrelname LIKE '%embedding%'
ORDER BY idx_scan DESC;

Más allá de las usage statistics, tenemos EXPLAIN ANALYZE para ejecutarlo periodicamente sobre queries representativas, donde debemos hacer un seguimiento del execution time a lo largo del tiempo. Si una query sube de tiempo y los datos no han cambiado, puede que el indice se haya degradado.

Podemos monitorizar el build de un index con pg_stat_progress_create_index, ya que este puede llevar horas.

Rebuild indexes when data distribution change

ANN indexes optimizan su estructura interna basandose en data distribution en el momento que se crea. Cuando se añade un 20-30% de datos nuevos, nuevos dominios o caracteristicas diferentes, el indice ya no particiona los vectores de manera optima. Esta degradacion se presenta como una mayor query latency y/o peores resultados.

Para PROD donde no podemos tener downtime, usamos CREATE INDEX CONCURRENTLY para crear un replacement index sin bloquear queries. Toma más tiempo y recursos, pero la app continua sirviendo requests.

  1. Crear el indice con un nombre temporal
  2. Comprobar que funciona con EXPLAIN ANALYZE
  3. Borrar el índice viejo
  4. Hacer un rename al nuevo para que lo sustituya
-- Create new index concurrently (doesn't block queries)
CREATE INDEX CONCURRENTLY documents_embedding_new_idx
ON documents USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- Verify the new index works, then swap
DROP INDEX documents_embedding_idx;
ALTER INDEX documents_embedding_new_idx RENAME TO documents_embedding_idx;

Para sistemas que pueden tolerar interrupciones tenemos REINDEX INDEX documents_embedding_idx es más simple pero bloquean queries durante el rebuild

Update embeddings efficiently

Cuando el source content cambia, los embeddings asociados ya no representan el contenido, por lo que hay que sincronizarlos.

Para updates ocasionales, podemos updatear los embeddings en la misma transacción que el contenido para mantener todo consistente. Es más simple y garantiza consistencia, pero añade latencia a cada update.

Para Apps con cambios muy frecuentes de contenido podemos usar batches. Se marcan los documentos que necesitan un update de embeddings usando un flag y luego los actualizamos todos en batches. Esto reduce los costes y mejora la latencia del sistema, pero durante un tiempo el contenido puede estar desactualizado y mostrar resultados desfasados.

Para algunas Apps puede ser necesario hacer una regeneración periódica y no solo cuando el contenido cambie. Implementamos esto como scheduled jobs.

-- Add a column to track embedding status
ALTER TABLE documents ADD COLUMN embedding_stale BOOLEAN DEFAULT FALSE;

-- When content changes, mark for embedding update (fast)
UPDATE documents
SET content = 'New content...', embedding_stale = TRUE, updated_at = NOW()
WHERE id = 42;

-- Background job processes stale embeddings in batches
SELECT id, content FROM documents WHERE embedding_stale = TRUE LIMIT 100;

-- After generating embeddings externally, update in bulk
UPDATE documents
SET embedding = data.embedding, embedding_stale = FALSE
FROM (VALUES (1, '[...]'::vector), (2, '[...]'::vector)) AS data(id, embedding)
WHERE documents.id = data.id;

Handle embedding model changes

Si hacemos un upgrade a un nuevo modelo de embeddings tenemos que regenerar todos los embeddings, ya que modelos diferentes producen vectores diferentes (y puede que con un numero diferente de dimensiones).

No se pueden mezclar embeddings de modelos diferentes en una misma columna

Migration strategy

Migrar a un nuevo embedding model requiere planificarlo bien. No puedes hacer un overwrite de los embeddings y ya porque la app devolvería resultados inconsistentes mientras la migración esté en progreso.

El método más seguro es crear una nueva columna paralela donde metemos los nuevos embeddings que se van generando hasta que este listo y validado y podamos cambiar una columna por otra.

  1. Creamos la nueva columna
  2. Creamos el indice para la columna nueva
  3. Llenamos la columna con embeddings
  4. Update de las app queries para usar la nueva columna
  5. Borramos la columna y el indice viejos

Manage storage and cleanup

Los vectores consumen mucho espacio. Un vector de 1536 dimensiones con 1.000.000 de documentos consume 6 GB solo en vector data, más el espacio de los índices.

Reclaim space after updates

Al hacer update o borrado de rows, PostgreSQL no reclama inmediatamente el espacio en disco. Para vector tables con actualizaciones periodicas de vectores esto se puede agravar bastante. Este espacio muerto se puede reclamar con:

-- Standard vacuum (runs concurrently)
VACUUM documents;

-- Full vacuum (reclaims more space but locks the table)
VACUUM FULL documents;

Con tablas con heavy update actividy lo podemos configurar automáticamente

autovacuum cuando el 5% de las filas son modificadas

ALTER TABLE documents SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_analyze_scale_factor = 0.02
);

Read More

Implement retrieval patterns for RAG pipelines (PostgreSQL)

(muchisimo detalle - quizas para una segunda vuelta segun vea los examenes de prueba)

Retrieval augmented generation (RAG) mejora las respuestas del modelo, cogiendo contexto relevante de tus datos. El retriever escanea tu knowledge base y devuelve documentos que el LLM usa para generar grounded content.

RAG architecture and retriever role

RAG sigue un patron de 3 pasos:

  1. Query embedding: convertimos la pregunta del user en vectores usando un embedding model
  2. Retrieval: buscamos en la BBDD vectorial documentos similares al query embedding
  3. Generation: Pasamos los documentos obtenidos al LLM como contexto para generar una respuesta

En esta arquitectura, PostgreSQL es el retriever. Si el retriever saca documentos irrelevantes, al LLM le falta contexto para responder y puede generar información correcta. Si en cambio no encuentra documentos, la respuesta puede estar incompleta.

Design schemas for RAG workloads

RAG apps funcionan con chunks (no con documentos enteros).

Separate documents and chunks

Usamos dos tablas. Una para source documents y otra para chunks con embeddings

  • Flexible retrieval: puedes sacar chunks individuales o reconstruir documentos
  • Source tracking: Cada chunk linkea a su documento entero, para citaciones
  • Efficient storage: Document metadata se almacena una unica vez, no duplicada por chunk
  • Easy updates: Reemplazar un doc significa borrar todos sus chunks
CREATE TABLE source_documents (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    source_url TEXT,
    document_type TEXT,
    ingested_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE document_chunks (
    id SERIAL PRIMARY KEY,
    document_id INTEGER REFERENCES source_documents(id) ON DELETE CASCADE,
    chunk_index INTEGER NOT NULL,
    content TEXT NOT NULL,
    embedding vector(1536),
    token_count INTEGER,
    start_char INTEGER,
    end_char INTEGER,
    UNIQUE (document_id, chunk_index)
);

Add metadata for filtering and context

Incluir metadata para poder filtrar

CREATE TABLE document_chunks (
    id SERIAL PRIMARY KEY,
    document_id INTEGER REFERENCES source_documents(id) ON DELETE CASCADE,
    chunk_index INTEGER NOT NULL,
    content TEXT NOT NULL,
    embedding vector(1536),
    token_count INTEGER,
    section_title TEXT,
    page_number INTEGER,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Crear indices para RAG query patterns

Vector similarity indexes (IVFFlat o HNSW) permite fast embedding lookups. RAG workloads también se benefician de B-tree indexes.

Estos indexes ayudan al hacer retrieve de chunks, al unir de vuelta los source documents para citaciones, y al filtrar u ordenar dentro de un documento por chunk position. Sin estos indices se hace un escaneo de todos los chunks para cada query

-- B-tree index for document lookups (speeds up JOINs to source_documents)
CREATE INDEX document_chunks_document_id_idx
ON document_chunks (document_id);

-- Composite index for chunk ordering (supports context window queries)
CREATE INDEX document_chunks_doc_chunk_idx
ON document_chunks (document_id, chunk_index);

Implement chunk retrieval for context building

diseñar un RAG es un balanceo entre:

  • obtener suficiente contexto relevante
  • mantenerte dentro de los token limits
  • dar suficiente contexto al LLM para entender cada chunk

Read More

Fast vector similarity search (PostgreSQL)

A medida que los vector datasets crecen, las similarity queries que escaneen cada fila pueden volverse demasiado lentas. Esto se soluciona mediante vector indexes.

IMPORTANTE

Sin un index, pgvector ejecuta exact nearest-neighbor search. La BBDD calcula la distancia entre el query vector y cada vector de la tabla, entonces devuelve las filas con las distancias más pequeñas. Esto garantiza encontrar el nearest neighbor verdadero, pero el coste computacional crece con el número de filas.

/IMPORTANTE

Para tablas pequeñas (<10.000 rows) sirve el exact nearest-neighbour pero lo que a una tabla con 100.000 vectores le puede llevar 500 ms, a una tabla con 1.000.000 de vectores le puede llevar 5s.

Approximate nearest-neightbour (ANN) search intercambia precisión perfecta por mejora en velocidad. ANN organiza vectores en estructuras que permiten a la BBDD examinar solo un subset de vectores durante las queries. Los resultados pueden fallar a veces y dejarse algunos true nearest neighbours, pero las queries se completan en milisegundos independientemente del tamaño de la tabla.

Choose IVFFlat when:

  • memory usage is a concern
  • faster index builds for frequently changing data
  • trade query performance for lower resource usage
  • slightly lower recall

Choose HNSW when:

  • query performance is your primary concern
  • high recall (99%) with low latency
  • your dataset doesn’t change frequently
  • memory constraints aren’t severe

Inverted File Flat (IVFFlat) indexes

IVFFlat particiona vectores en clusters durante la creación del index. Cuando haces una query, la BBDD identifica los clusters más cercanos a tu query vector y busca solo dentro de esos clusters, en vez de escanear la tabla entera.

Cada cluster tiene un centroid. Esto es la posición media de todos los vectores del cluster. Cuando ejecutas una query, pgvector calcula distancias desde tu query a cada centroid, identifica los clusters más cercanos y busca solo en ellos.

el param lists controla el número de clusters. luego el param vector_cosine_ops tiene que ser el mismo que el distance operator que usemos para las queries <=>, etc

CREATE INDEX documents_embedding_idx ON documents
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);
Operator class Distance opwerator Use case
vector_12_ops <-> L2 Euclidean distance
vector_cosine_ops <=> Cosine distance
vector_ip_ops <#> Negative inner product

IVFFlat indexes requiere que existan datos para entrenar al clustering algorithm. Crear un index en una tabla vacía produce un index que no se puede usar para queries - primero cargar los datos, luego crear el índice. Si la distribución de datos cambia significativamente después de añadir datos puede ser necesario hacer un rebuild del índice

Hierarchical Navigable Small World (HNSW) indexes

HNSW indexes usan una graph structure multi-capa para similarity search. Tiene una mejro performance que IVFFlat, aunque consume más memoria y tiene longer build times.

HNSW construye una graph estructure navegable, en vez de particionar vectores en clusters (como have IVFFlat). HNSW construye un grafo donde cada vector es un nodo conectado a su nearest neighbours, organizados en múltiples capas.
La top layer contiene la menor cantidad de nodos espaciados lejos unos de otros. Cada capa a un nivel más bajo contiene más nodos con conexiones más densas, y la última capa contiene todos los nodos.

Cuando haces una query, el algoritmo comienza en la top layer y navega por el grafo, moviéndose a lower layers a medida que se acerca al query vector. Es un coarse-to-fine approach which narrows down the search space.

el param m es el número máximo de conexiones por nodo en el grafo. el param ef_construction controla el width de la búsqueda durante index building - valores más altos crean indices de mayor calidad pero son más lentos de construir. para PROD usar valores 100-200

CREATE INDEX documents_embedding_idx ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

A diferencia de IVFFlat, los indices HNSW se pueden crear en tablas vacías - se hace update de la graph structure a medida que se insertan nuevas filas

Read More

Query data (PostgreSQL)

PostgreSQL-specific filtering

PostgreSQL extiende el SQL estandard con más operadores.

El operador ILIKE es como LIKE pero ya case-insentive sin tener que hacer un LOWER.

También permite controlar cómo aparecen ordenados los null values en los resultados con NULLS FIRST o NULL LAST

Con COALESCE devuelve el primer valor no null de sus args. Se usa para proveer de default values en los resultados o para handlear nullable columns COALESCE(title, 'Untitled')

Query JSONB data

PostgreSQL’s JSONB type almacena datos estructurados que no se pueden meter en un schema fijo. AI apps usan JSONB normalmente para metadatos, config, model params o estructuras de respuesta variable.

PostgreSQL provee de operadores especializados para JSON content:

  • -> extrae un JSON element como JSON
  • ->> extrae un JSON element como texto (para comparisons y display)
  • #> devuelve JSON para nested paths
  • #>> devuelve texto para nested paths

Ejemplos:

  • metadata->>'status' extrae el status field como texto
  • checkpoint_data#>>'{results,0,score}' navega un nested path para obtener el valor específico

Existence and containment operators enable efficient filtering

  • ? comprueba key existence
  • @> tests containment

Ejemplos:

  • WHERE metadata ? 'priority'
  • WHERE checkpoint_data @> '{"status": "completed"}'

Cuando JSONB contiene arrays, podemos usar jsonb_array_elements para expandirlas y filtrar o agregar

-- Find conversations tagged with 'support'
SELECT DISTINCT c.*
FROM conversations c,
     jsonb_array_elements_text(c.metadata->'tags') AS tag
WHERE tag = 'support';

Efficient pagination with keyset pagination

Una paginación tradicional con OFFSET se vuelve lenta en tablas grandes porque PostgreSQL debe escanear y descartar todas las rows que salta. Cargar la página 1000 con 20 rows por página requiere escanear 20.000 rows y descartar 19.980.

Keyset pagination (also calle cursor-based pagination) usa WHERE para saltar rows, lo cual tiene una performance consistente independientemente de como de profundo pagines.

En vez de tracklear page numbers, trackeas el último valor que has visto y filtras desde ahí. Este approach requiere un unique, sortable column (o combinacion de ellas). Tu página debe almacenar los sort values de la última fila y pasarlos a la siguiente query.

(incluir id en ambos ORDER BY y WHERE gestiona los empates cuando múltiples filas tienen el mismo timestamp)

-- First page: get the 20 most recent messages
SELECT id, conversation_id, content, created_at
FROM messages
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Next page: filter by the last seen timestamp and id
SELECT id, conversation_id, content, created_at
FROM messages
WHERE (created_at, id) < ('2024-06-15 10:30:00', 12345)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Common Table Expressions (CTEs)

INVESTIGAR Y PROBAR ESTO. VER SI ES SQL native o de Postgre. Me puede ayudar mucho en mi dia a dia

CTEs definen named temporary result sets que existen solo durante la ejecución de una query. Mejoran la legibilidad y te permiten crear queries complejas paso a paso.

WITH recent_conversations AS (
    SELECT id, user_id, started_at
    FROM conversations
    WHERE started_at > CURRENT_DATE - INTERVAL '7 days'
),
message_stats AS (
    SELECT conversation_id, COUNT(*) AS message_count, MAX(created_at) AS last_message_at
    FROM messages
    GROUP BY conversation_id
)
SELECT rc.user_id, rc.started_at, COALESCE(ms.message_count, 0) AS message_count
FROM recent_conversations rc
LEFT JOIN message_stats ms ON rc.id = ms.conversation_id;

Recursive CTEs query tree structures like task hierarchies, org chats o threaded conversations. Consisten de un base case (anchor) y un recursive case que referencia al CTE en si mismo. Incluir siempre un depth limit u otra condicion de finalizacion para prevenir infinite loops

WITH RECURSIVE task_tree AS (
    -- Base case: start with the parent task
    SELECT id, parent_id, title, 1 AS depth
    FROM tasks WHERE id = 1
    UNION ALL
    -- Recursive case: find children of current level
    SELECT t.id, t.parent_id, t.title, tt.depth + 1
    FROM tasks t
    INNER JOIN task_tree tt ON t.parent_id = tt.id
    WHERE tt.depth < 10
)
SELECT * FROM task_tree ORDER BY depth, id;

AI apps a menudo necesita hacer retrieve de conversation threads donde los mensajes referencian parent messages

WITH RECURSIVE thread AS (
    -- Start with the root message
    SELECT id, parent_id, content, role, 0 AS depth
    FROM messages
    WHERE id = :root_message_id

    UNION ALL

    -- Get all replies
    SELECT m.id, m.parent_id, m.content, m.role, t.depth + 1
    FROM messages m
    INNER JOIN thread t ON m.parent_id = t.id
    WHERE t.depth < 50
)
SELECT * FROM thread ORDER BY depth, id;

INSERT with RETURNING

RETURNING hace retrieve de valores de filas insertadas, updateadas o borradas en un single round trip. Es esencial para obtener IDs autogenerados, timestamps o computed defaults sin una query separada

-- Get the generated ID after inserting a conversation
INSERT INTO conversations (user_id, session_id)
VALUES ('user123', 'sess_abc')
RETURNING id;

-- Get multiple generated values
INSERT INTO messages (conversation_id, role, content)
VALUES (1, 'user', 'Hello')
RETURNING id, created_at;

-- Use RETURNING with UPDATE
UPDATE tasks SET status = 'completed', completed_at = CURRENT_TIMESTAMP
WHERE id = 5
RETURNING id, status, completed_at;

Upserts with ON CONFLICT

INSERT ... ON CONFLICT se encarga de gestionar UNIQUE contraint violations, permitiendo al upsert insertar nuevas filas o actualizar las ya existentes.

Cuando hay un conflicto en una UNIQUE constraint, puedes actualizar la existing row con nuevos valores usando DO UPDATE

Con DO NOTHING hacemos skip de rows que violarian constraints

A DO UPDATE se le puede añadir WHERE para filtrar

INSERT INTO user_preferences (user_id, preference_key, preference_value)
VALUES ('user123', 'theme', 'dark')
ON CONFLICT (user_id, preference_key)
DO UPDATE SET
    preference_value = EXCLUDED.preference_value,
    updated_at = CURRENT_TIMESTAMP;

Practical patterns for AI apps

Retrieve conversation history with metadata for context building

WITH conversation_context AS (
    SELECT c.id, c.session_id, c.metadata->>'model' AS model
    FROM conversations c
    WHERE c.session_id = :session_id AND c.ended_at IS NULL
)
SELECT cc.session_id, cc.model, m.role, m.content, m.created_at
FROM conversation_context cc
INNER JOIN messages m ON cc.id = m.conversation_id
ORDER BY m.created_at
LIMIT 50;

Show recording task checkpoints with state management using upsert

INSERT INTO task_checkpoints (task_id, step_number, checkpoint_data)
VALUES (:task_id, :step_number, :checkpoint_json::jsonb)
ON CONFLICT (task_id, step_number)
DO UPDATE SET checkpoint_data = EXCLUDED.checkpoint_data, updated_at = CURRENT_TIMESTAMP
RETURNING id, created_at, (xmax = 0) AS is_new;

Show a paginated search with JSONB filters

SELECT c.id, c.session_id, c.started_at, c.metadata->>'status' AS status
FROM conversations c
WHERE c.user_id = :user_id
  AND c.metadata @> :filter_json::jsonb
  AND (c.started_at, c.id) < (:last_started_at, :last_id)
ORDER BY c.started_at DESC, c.id DESC
LIMIT 20;

Read More

Integrate SDKs and Apps (PostgreSQL)

Python integration con psycopg

psycopg es la libreria recomendada de PostgreSQL para Python. Se conecta mediante connection strings o params individuales.

import psycopg

# Connection string format
conn = psycopg.connect("postgresql://user:password@myserver.postgres.database.azure.com/mydb?sslmode=require")

# Individual parameters
conn = psycopg.connect(host="myserver.postgres.database.azure.com", dbname="mydb",
                       user="myuser", password="mypassword", sslmode="require")

Context managers se aseguran que las conexiones están correctamente cerradas, aún cuando ocurren exceptions.

Las queries parametrizadas en python se usan mediante %s

el outer with hace management de la conexión, el inner with hace management de cursor

with psycopg.connect(connection_string) as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM conversations WHERE id = %s", (conversation_id,))
        row = cur.fetchone()

Usar fetchone() cuando esperas una single row. Usar fetchall() para result sets que iterar

Connection management best practices

IMPORTANTE

  • set appropriate timeouts to prevent the app hanging when the database is unreachable or queries run longer than expected
    • connection timeouts control how long the client waits to establish a connection
    • statement timeouts limit query execution time
  • implement retry logic with exponential backoff
    • catch OperationalError for connectiosn and timeouts
    • don’t retry on constraint violations or syntax errors - those require code changes, not retries
  • always close connections when you’re done with them

/IMPORTANTE

conn = psycopg.connect(
    connection_string,
    connect_timeout=10,
    options="-c statement_timeout=30000"  # milliseconds
)

Error handling strategies

IMPORTANTE

Unique contraints, FK y check constraints dan errores específicos cuando se violan. Capturar estos errores UniqueViolation, ForeignKeyViolation y CheckViolation para dar feedback. Siempre hacer un rollback de una transacción después de una contraint violation.

Los deadlocks ocurren cuando dos transacciones se esperan la una a la otra. PostgreSQL los detecta y los elimina, lo que devuelve DeadlockDetedted. Hacer rollback y retry.
Manejar LockNotAvailable de forma similar cuando hay un timeout esperando a un lock que no llega.

/IMPORTANTE

Performance considerations

No insertar rows solas si se puede. Hacer batches. Usar executemany para insertar unos cientos de miles de rows. Para datasets grandes (+10.000 rows) usar el COPY command.

with cur.copy("COPY messages (conversation_id, role, content) FROM STDIN") as copy:
    for record in records:
        copy.write_row(record)

Prepared statements pueden mejorar performance para queries que se ejecutan con diferentes params.

Crear database connections es caro en performance. Usar connection pools para utilizar conexiones reutilizables

from psycopg_pool import ConnectionPool

pool = ConnectionPool(connection_string, min_size=1, max_size=10)

with pool.connection() as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM messages WHERE conversation_id = %s", (id,))

Read More

Create and manage schemas (PostgreSQL)

Database and schema organization

PostgreSQL organiza los objetos en una jerarquía:

  • el server instance contiene una o múltiples BBDD
  • las BBDD contienen schemas
  • los schemas contienen tablas y otros objetos

Un schema provee de un namespace para poder organizar objetos. El schema por defecto es public, que es el que se asigna cuando no especificas uno diferente.

IMPORTANTE

Usar databases separadas cuando queremos aislamiento total y completo entre datasets, cuando diferentes apps compartan server pero no quieres que accedan a los datos de la otra, o cuando quieres backups independientes.

Usar schemas separados dentro de una misma database cuando objetos relacionados necesitan referenciarse a través de FK, cuando quieras ejecutar queries con JOIN a través de namespaces, o cuando quieras una separación lógica sin tener un aislamiento total

/IMPORTANTE

Crear tablas

En este ejemplo id define una PK con BIGSERIAL PRIMARY KEY, la cual es autom-incremental

CREATE TABLE conversations (
    id BIGSERIAL PRIMARY KEY,
    session_id UUID NOT NULL,
    user_id VARCHAR(255) NOT NULL,
    started_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    ended_at TIMESTAMP WITH TIME ZONE,
    metadata JSONB DEFAULT '{}'::jsonb
);

Data types relevantes para AI apps

IMPORTANTE

  • JSONB: almacena JSON data en un formation binario que soporta indexing y querying eficiente. Usar JSONB cuando la estructura de datos varía entre records, necesites guardar nested objects o arrays, o necesites schema flexibility
  • TEXT and VARCHAR: ambos son para texto. TEXT no tiene límite y VARCHAR(n) enfuerza un máximo. No hay diferencia de performance.
  • TIMESTAMP WITH TIME ZONE: Usar siempre TIMESTAMPTZ para datos de tiempo que operen a través de time zones.
  • BYTEA: almacena binary data como byte array. Se usa para small binary objects que necesitan ser almacenados junto a datos relacionales. Para large binary files usar Azure Blob Storage con una referencia en la BBDD
  • SERIAL and BIGSERIAL: pseudo-types que crean ints auto-incrementales. PostgreSQL crea automáticamente una sequencia y pone un valor default. Usar BIGSERIAL par tablas que excedan de 2 billion rows.

/IMPORTANTE

Define relationships

IMPORTANTE

FK establecen relaciones entre tablas y fuerzan integridad referencial. Cuando defines una FK, PostgreSQL enfuerza que existen los valores en la columna referida.

One-to-many: conecta una single row de una tabla con multiple rows en otra. Ejemplos: Una conversación tiene múltiples mensajes; Un user tiene múltiples sesiones;

CREATE TABLE messages (
    id BIGSERIAL PRIMARY KEY,
    conversation_id BIGINT NOT NULL REFERENCES conversations(id),
    role VARCHAR(50) NOT NULL CHECK (role IN ('user', 'assistant', 'system')),
    content TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

Many-to-many: requieren de una junction table que referencia ambas tablas relacionadas. Ejemploss: las conversaciones pueden tener múltiples tags, y los tags pueden aplicarse a múltiples conversaciones

la composite PK fuerza que cada combinación aparezca una única vez

CREATE TABLE conversation_tags (
    conversation_id BIGINT REFERENCES conversations(id) ON DELETE CASCADE,
    tag_id BIGINT REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (conversation_id, tag_id)
);

Las acciones referenciales definen lo que pasa cuando haces update o delete de una fila referenciada

  • RESTRICT (default): previene la operación si la referencia existe
  • CASCADE: borra o hace update de las referencias
  • SET NULL: pone la FK a null
  • SET DEFAULT: pone la FK con el default
  • ON DELETE CASCADE: usarla con cuidado - si borramos una conversación borraremos también automáticamente todos los mensajes, lo que puede borrar muchos más datos de los que hubiéramos querido si las relaciones son complejas

/IMPORTANTE

Index basics

Los indices son estructuras que aceleran data retrieval con el coste de más almacenamiento y writes más lentos. PostgreSQL crea automáticamente indices para PKs y UNIQUE contraints.

el orden de las columnas en un indice compuesto importa, ya que se aplica a queries que filtren solo por los primeros items, pero no a las que solo filtren por los últimos

CREATE INDEX idx_messages_conversation_id ON messages(conversation_id);
CREATE INDEX idx_messages_created_at ON messages(created_at);
CREATE INDEX idx_messages_conversation_created ON messages(conversation_id, created_at);

Schema management commands

si algo va mal, puedes usar ROLLBACK para no cambiar el schema, ya que las operaciones son transaccionales

BEGIN;
ALTER TABLE conversations ADD COLUMN category VARCHAR(100);
CREATE INDEX idx_conversations_category ON conversations(category);
COMMIT;

Read More

Connect to PostgreSQL

Una conexión requiere de:

  • host (server FQDN <server-name>.postgres.database.azure.com)
  • puerto (5432 conexiones directas; 6432 PgBouncer)
  • database name
  • username (username@servername para Entra auth)
  • pwd (static o Entra token)

Auth methods

Azure Database for PostgreSQL soporta dos tipos de autenticación:

  • Microsoft Entra (OAuth 2.0 tokens)
  • PostgreSQL native auth (user & pwd)

Microsoft Entra

Para conectar las Entra identities necesitamos obtener un token de https://ossrdbms-aad.database.windows.net

El proceso es:

  • La app pide un token de Entra ID
  • Entra valida la identidad y devuelve un token
  • La app se conecta a PostgreSQL usando el token como pwd
  • PostgreSQL valida el token contra el Entra administrator configurado

en python se usa azure-identity library para obtener tokens. DefaultAzureCredential prueba automáticamente multiples auth methods en orden (Entra ID, Azure CLI credentials, etc)

from azure.identity import DefaultAzureCredential

credential = DefaultAzureCredential()
token = credential.get_token("https://ossrdbms-aad.database.windows.net/.default")
# Use token.token as the password in your connection string

TLS/SSL config

PostgreSQL clients usa sslmode param para controlar la encriptación y validar certificados. Tiene los siguientes modos:

  • disable: Azure rechaza conexiones con este modo
  • allow: Encripta si el server lo requiere, pero no valida certificados
  • prefer: Encripta si el server lo soporta, pero no valida certificados
  • require: Fuerza la encriptación, pero no valida certificados
  • verify-ca: Fuerza la encriptación y valida el server certificate
  • verify-full: Fuerza la encriptación, valida el ser certificate, y confirma que el certificate hostname hace match con el server

Para PROD usar verify-full para verificar que el server es genuino y real

Connection pooling con PgBouncer

PgBouncer solo está disponible para General Purpose y Memory optimized compute tiers. PgBouncer usa el puerto 6432

PgBouncer es una herramienta con un pool de conexión que reduce el overhead de crear conexiones constantemente, manteniendo una pool de conexiones reusables a través de las cuales enruta a los clientes. Esto reduce el overhead para apps que usan short-lived connections

activar pgBouncer

az postgres flexible-server parameter set \
	--resource-group myResourceGroup \
	--server-name myserver \
	--name pgbounder.enabled \
	--value true

tras activarlo, debemos usar el puerto 6432

postgresql://user@myserver.postgres.database.azure.com:6432/mydb?sslmode=require

Network access considerations

IMPORTANTE

Azure Database for PostgreSQL soporta dos modelos de networking:

  • public access (firewall rules): el server tiene una IP pública y las firewall rules controlan que clientes se pueden conectar
  • private access (VNet integration): el server solo tiene una IP privada dentro de una red virtual de Azure. Solo te puedes conectar desde recursos dentreo de la misma red, peered networks, o a través de VPN/ExpressRoute. Se usa para PROD envs.

/IMPORTANTE

Connection string examples

# Basic connection with SSL required
postgresql://myuser:mypassword@myserver.postgres.database.azure.com/mydb?sslmode=require

# Connection with certificate verification
postgresql://myuser:mypassword@myserver.postgres.database.azure.com/mydb?sslmode=verify-full&sslrootcert=/etc/ssl/certs/ca-certificates.crt

# Connection through PgBouncer (note port 6432)
postgresql://myuser:mypassword@myserver.postgres.database.azure.com:6432/mydb?sslmode=require

Read More

Explore Azure Database (PostgreSQL)

Azure Database for PostgreSQL es una BBDD relacional administrada por Azure. Microsoft se encarga de administrar hardware, parches, backups y high availability.

Con AI apps se usa para las que necesitan guardar datos relacionales junto a operaciones vectoriales.

Arquitectura y deployment options

Azure Database for PostgreSQL separa compute y storage en componentes independientes. La database engine corre en una Linux VM, mientras que los datos residen en Azure managed storage. Esto permite un escalado independiente y ofrece durabilidad a través de replicas redundantes.

Hay 3 tiers:

  • Burstable: baseline CPU performance con la habilidad de bursts por encima cuando se necesita. Se usa para dev envs, apps pequeñas o workloads que no necesitan una CPU a siempre a tope. B-series VM
  • General Purpose: balance de compute y memory resources para PROD workloads. Para apps que requieren response times predecibles. D-series VM
  • Memory Optimized: da un ratio más alto de memoria-to-CPU para datasets que necesiten mucha memoria. E-series VM

Los tiers si se pueden cambiar más adelante para adaptarse a tus necesidades

Managed service capabilities

El servicio se encarga de crear automáticamente los backups y los guarda en zone-redundant storage en otras regiones. Default retention es de 7 días, que se puede extender hasta 35.

Point-in-time restore te permite recuperar la BBDD a cualquier momento donde haya backups. Esto crea un nuevo server con todos los datos ercuperados.

También incluye built-in PgBouncer, un pool de conexión que reduce el overhead de crear conexiones constantemente, manteniendo una pool de conexiones reusables.

PgBouncer NO está disponible en Burstable tier

Supported PostgreSQL extensions

Algunas de las extensiones soportadas que son útiles para AI apps:

  • pgvector: activa los vector data types y similarity search ops. Permite guardar embeddings junto a los datos relacionales y hacer búsquedas al nearest neighbor.
  • pg_trgm: provee trigram-based text similarity functions. Se usa para fuzzy text matching, autocomplete features y encontrar strings similares sin matches exactos
  • hstore: añade key-value data type para guardar sets de pares key-value dentro de un único PostgreSQL value. Útil para semi-structured data que no requiere toda la flexibilidad de JSONB.

Puedes activar otras extensiones disponibles con CREATE EXTENSIONS

Read More