Concepto clave

El diseño de estrategia de indexing en PostgreSQL para aplicaciones de alta concurrencia no se trata solo de agregar indices, sino de crear un ecosistema de indices que equilibre velocidad de lectura, costo de escritura y mantenimiento del sistema. Piensa en esto como el sistema de transporte de una gran ciudad: no basta con tener muchas calles (indices), necesitas avenidas principales (indices compuestos), rutas alternativas (indices parciales) y regulacion del trafico (monitoreo de uso).

En entornos de alta concurrencia, cada indice adicional representa un compromiso: acelera las consultas SELECT pero ralentiza las operaciones INSERT, UPDATE y DELETE, ya que PostgreSQL debe mantener todos los indices sincronizados. La clave esta en identificar los patrones de acceso mas frecuentes y diseñar indices que maximicen el rendimiento para el 80% de las consultas criticas, aceptando que el 20% restante puede ser menos optimo.

Como funciona en la practica

Imagina una plataforma de e-commerce con 10 millones de usuarios y 100 transacciones por segundo. Tu tabla principal orders tiene esta estructura inicial:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    status VARCHAR(20) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

El problema: las consultas mas frecuentes buscan ordenes por usuario y estado, pero tambien necesitas reportes por fecha. Sin indices especificos, PostgreSQL realiza secuencias completas de la tabla, causando bloqueos y lentitud.

Paso 1: Analiza las consultas con EXPLAIN ANALYZE. Paso 2: Identifica que el 70% de las consultas son SELECT * FROM orders WHERE user_id = ? AND status = ? ORDER BY created_at DESC. Paso 3: Crea un indice compuesto que cubra exactamente este patron. Paso 4: Monitorea el impacto en las escrituras.

Codigo en accion

Antes: Solo el indice primario en id, consultas lentas:

-- Consulta tipica sin indice optimo
EXPLAIN ANALYZE 
SELECT * FROM orders 
WHERE user_id = 12345 
AND status = 'completed' 
ORDER BY created_at DESC 
LIMIT 10;

-- Resultado: Seq Scan en orders, costo: 250000

Despues: Estrategia de indexing multicapa:

-- 1. Indice compuesto para el patron principal
CREATE INDEX idx_orders_user_status_created 
ON orders(user_id, status, created_at DESC);

-- 2. Indice parcial para estados activos (solo 5% de las filas)
CREATE INDEX idx_orders_active 
ON orders(user_id, created_at) 
WHERE status IN ('pending', 'processing');

-- 3. Indice para reportes administrativos
CREATE INDEX idx_orders_daily_report 
ON orders(date(created_at), status, amount);

-- Ahora la misma consulta
EXPLAIN ANALYZE 
SELECT * FROM orders 
WHERE user_id = 12345 
AND status = 'completed' 
ORDER BY created_at DESC 
LIMIT 10;

-- Resultado: Index Scan usando idx_orders_user_status_created, costo: 4.2

Errores comunes

Sobre-indexar tablas de alta escritura: Agregar indices a tablas con mas de 100 escrituras/segundo sin evaluar el impacto en INSERT/UPDATE. Solucion: Usa pg_stat_user_indexes para monitorear el uso real.
Ignorar el orden de columnas en indices compuestos: Crear (status, user_id) cuando las consultas usan WHERE user_id = ? AND status = ?. PostgreSQL no puede usar eficientemente la segunda columna si la primera no esta en el WHERE. Solucion: Ordena las columnas por cardinalidad (de menor a mayor).
No considerar indices parciales: Indexar toda la tabla cuando solo necesitas un subconjunto (ej: ordenes activas). Esto desperdicia espacio y ralentiza mantenimiento. Solucion: Usa WHERE en CREATE INDEX para filtrar filas irrelevantes.
Olvidar el mantenimiento periodico: Los indices se fragmentan con el tiempo en tablas con muchas actualizaciones. Solucion: Programa REINDEX o VACUUM en horas de baja actividad.
Indexar columnas con baja selectividad: Como "gender" con solo 2 valores distintos. El optimizador puede ignorar estos indices. Solucion: Solo indexa columnas con alta selectividad (> 10% valores unicos).

Checklist de dominio

He identificado las 5-10 consultas mas frecuentes en mi aplicacion usando pg_stat_statements
He ejecutado EXPLAIN ANALYZE en cada consulta critica para entender el plan de ejecucion actual
He creado indices compuestos que cubren exactamente los patrones WHERE y ORDER BY juntos
He implementado al menos un indice parcial para filtrar subconjuntos de datos relevantes
He monitoreado el tamaño de indices vs datos con \di+ y pg_relation_size
He verificado que los nuevos indices no aumenten el tiempo de escritura en mas del 15%
Tengo un proceso automatizado para revisar y eliminar indices no utilizados mensualmente

Optimizacion de Indices para Sistema de Mensajeria en Tiempo Real

Eres el ingeniero de backend de una aplicacion de mensajeria con 5 millones de usuarios activos. La tabla messages tiene problemas de rendimiento durante horas pico.

Estructura actual:

CREATE TABLE messages (
    id BIGSERIAL PRIMARY KEY,
    sender_id INTEGER NOT NULL,
    receiver_id INTEGER NOT NULL,
    conversation_id INTEGER NOT NULL,
    content TEXT NOT NULL,
    is_read BOOLEAN DEFAULT false,
    created_at TIMESTAMP DEFAULT NOW()
);

Patrones de consulta identificados:

Obtener mensajes no leidos de una conversacion especifica (80% de las consultas):
SELECT * FROM messages WHERE conversation_id = ? AND is_read = false ORDER BY created_at DESC
Buscar mensajes entre dos usuarios en un rango de fechas (15%):
SELECT * FROM messages WHERE (sender_id = ? AND receiver_id = ?) OR (sender_id = ? AND receiver_id = ?) AND created_at BETWEEN ? AND ?
Reporte administrativo: conteo de mensajes por dia (5%)

Tarea:

Analiza los patrones y propone una estrategia de indexing con al menos 3 indices diferentes
Escribe los comandos CREATE INDEX completos con sintaxis correcta
Justifica cada indice explicando que patron cubre y por que es optimo
Incluye un indice parcial para optimizar espacio
Muestra como verificarias el rendimiento con EXPLAIN ANALYZE

Pistas

Considera que is_read = false solo aplica a una fraccion de los mensajes (indice parcial)
Para la busqueda entre dos usuarios, piensa en como PostgreSQL usa indices con condiciones OR
Los reportes administrativos pueden usar indices sobre funciones como date(created_at)

Evalua tu comprension

Completa el quiz interactivo de arriba para ganar XP.

Práctica: Diseño de Estrategia de Indexing