PostgreSQL: 10 optimizaciones que transformarán el rendimiento de tu app

PostgreSQL es la base de datos relacional de código abierto más avanzada del mundo, y la que recomendamos por defecto en Alternetica para la gran mayoría de proyectos. Pero tener PostgreSQL no garantiza tener rendimiento. Hemos auditado docenas de aplicaciones en LATAM con tiempos de respuesta de 5-10 segundos que después de optimizaciones se reducen a milisegundos. Estas son las 10 técnicas que más impacto tienen.

1. Usa EXPLAIN ANALYZE antes de optimizar

Antes de cualquier optimización, necesitas entender qué está pasando realmente. EXPLAIN ANALYZE ejecuta la consulta y muestra el plan de ejecución con tiempos reales.

EXPLAIN ANALYZE
SELECT u.nombre, COUNT(o.id) as total_ordenes
FROM usuarios u
LEFT JOIN ordenes o ON u.id = o.usuario_id
WHERE u.activo = true
GROUP BY u.id, u.nombre
ORDER BY total_ordenes DESC
LIMIT 20;

Las líneas más importantes a buscar:

• Seq Scan: Escaneo secuencial, señal de que falta un índice
• Hash Join vs Nested Loop: Hash Join es generalmente mejor para tablas grandes
• cost=X..Y rows=Z: La estimación del planificador
• actual time=X..Y rows=Z: El tiempo real

Si el "rows" estimado difiere mucho del "rows" real, las estadísticas están desactualizadas. Ejecuta ANALYZE tabla para actualizarlas.

2. Índices correctos para las consultas frecuentes

El índice correcto puede convertir un Seq Scan de 30 segundos en un Index Scan de 1ms.

-- Consulta frecuente: buscar órdenes por usuario y estado
SELECT * FROM ordenes
WHERE usuario_id = 123 AND estado = 'pendiente'
ORDER BY fecha_creacion DESC;

-- Índice compuesto en el orden correcto (columna más selectiva primero)
CREATE INDEX idx_ordenes_usuario_estado_fecha
ON ordenes (usuario_id, estado, fecha_creacion DESC);

Regla de oro: El orden de las columnas en el índice importa. Las columnas que aparecen en condiciones de igualdad van primero, las de rango o sort van al final.

3. Índices de cobertura (Covering Indexes)

Un índice de cobertura incluye todas las columnas que la consulta necesita, eliminando la necesidad de acceder a la tabla principal.

-- Sin índice de cobertura: PostgreSQL hace Index Scan + Heap Fetch
SELECT nombre, email FROM usuarios WHERE empresa_id = 5;

-- Con índice de cobertura: solo Index-Only Scan
CREATE INDEX idx_usuarios_empresa_covering
ON usuarios (empresa_id) INCLUDE (nombre, email);

La diferencia de rendimiento puede ser de 3-10x en tablas grandes porque evita accesos adicionales al disco.

4. Índices parciales para subconjuntos de datos

Si frecuentemente consultas solo una fracción de los datos, un índice parcial es más eficiente.

-- El 95% de las consultas son sobre órdenes activas
-- Crear índice solo para ese subconjunto
CREATE INDEX idx_ordenes_activas_fecha
ON ordenes (fecha_creacion DESC)
WHERE estado IN ('pendiente', 'procesando');

-- Este índice es mucho más pequeño que uno sobre toda la tabla
-- y más rápido para las consultas frecuentes

5. Eliminar el problema N+1 con JOIN o subconsultas

El problema N+1 es el asesino silencioso del rendimiento: cargas 100 registros y luego haces 100 consultas adicionales para obtener información relacionada.

-- MAL: N+1 en la aplicación
-- Primero: SELECT * FROM usuarios WHERE empresa_id = 5  (1 consulta)
-- Luego: SELECT * FROM ordenes WHERE usuario_id = ? x100  (100 consultas)

-- BIEN: Todo en una consulta con JOIN
SELECT
  u.id,
  u.nombre,
  u.email,
  COUNT(o.id) FILTER (WHERE o.estado = 'completada') AS ordenes_completadas,
  SUM(o.total) FILTER (WHERE o.estado = 'completada') AS total_vendido
FROM usuarios u
LEFT JOIN ordenes o ON u.id = o.usuario_id
WHERE u.empresa_id = 5
GROUP BY u.id, u.nombre, u.email;

En ORMs como Prisma o Sequelize, busca las opciones de include/eager loading para evitar N+1.

6. Connection Pooling con PgBouncer

Cada conexión a PostgreSQL consume memoria (típicamente 5-10MB). Si tienes 100 instancias de tu aplicación cada una abriendo 10 conexiones, son 1,000 conexiones y potencialmente 10GB de RAM solo en conexiones.

PgBouncer actúa como proxy, manteniendo un pool de conexiones real a PostgreSQL y multiplexando las conexiones de la aplicación.

# pgbouncer.ini básico
[databases]
mi_app = host=localhost dbname=mi_app

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5

Con pool_mode = transaction, una conexión real se comparte entre múltiples conexiones de la aplicación, compartiendo el pool entre transacciones. Para la mayoría de aplicaciones web, 20-30 conexiones reales pueden servir a cientos de conexiones de la aplicación.

7. Particionamiento de tablas grandes

Para tablas que crecen constantemente (logs, eventos, transacciones), el particionamiento permite que las consultas accedan solo a las particiones relevantes.

-- Tabla de eventos particionada por mes
CREATE TABLE eventos (
    id BIGSERIAL,
    usuario_id INT NOT NULL,
    tipo VARCHAR(100) NOT NULL,
    datos JSONB,
    creado_en TIMESTAMP NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (creado_en);

-- Crear particiones (automatizable con pg_partman)
CREATE TABLE eventos_2025_01
PARTITION OF eventos
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE eventos_2025_02
PARTITION OF eventos
FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

-- Una consulta que filtra por fecha solo escanea la partición relevante
SELECT * FROM eventos
WHERE creado_en >= '2025-01-01' AND creado_en < '2025-02-01'
AND usuario_id = 456;

8. JSONB correctamente indexado

JSONB es una de las features más poderosas de PostgreSQL, pero sin índices apropiados puede ser un cuello de botella.

-- Índice GIN para búsquedas dentro del JSONB
CREATE INDEX idx_metadata_gin ON productos USING GIN (metadata);

-- Índice específico para un campo frecuentemente consultado
CREATE INDEX idx_metadata_categoria
ON productos ((metadata->>'categoria'));

-- Consulta eficiente
SELECT nombre, precio
FROM productos
WHERE metadata->>'categoria' = 'electronica'
AND metadata @> '{"activo": true}';

Evita usar JSONB como sustituto de columnas tipadas para datos que siempre necesitarás filtrar. El overhead de JSON vs tipos nativos es real para columnas de alta cardinalidad.

9. Estrategia de VACUUM y AUTOVACUUM

PostgreSQL usa MVCC (Multi-Version Concurrency Control): cuando actualizas un registro, el registro viejo no se elimina inmediatamente. VACUUM limpia esos registros muertos y previene el "table bloat".

-- Ver el estado de autovacuum en tus tablas
SELECT
  schemaname,
  tablename,
  n_dead_tup,
  n_live_tup,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Para tablas con mucha actividad de escritura, ajustar autovacuum
ALTER TABLE ordenes SET (
  autovacuum_vacuum_scale_factor = 0.01,  -- vaciar al 1% de filas muertas
  autovacuum_analyze_scale_factor = 0.005
);

Una tabla con millones de registros muertos tiene rendimiento degradado incluso con índices perfectos.

10. pg_stat_statements para monitoreo continuo

pg_stat_statements registra estadísticas de todas las consultas ejecutadas. Es la herramienta más valiosa para identificar consultas problemáticas en producción.

-- Habilitar la extensión (requiere restart de PostgreSQL)
-- En postgresql.conf: shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Las 10 consultas que más tiempo total consumen
SELECT
  round(mean_exec_time::numeric, 2) AS promedio_ms,
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  substring(query, 1, 100) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Esta consulta te mostrará exactamente dónde está el tiempo real de tu base de datos en producción. Las sorpresas son frecuentes.

Herramientas complementarias

• pganalyze: Monitoreo continuo con alertas de regresión de rendimiento
• PGHERO: Dashboard visual para estadísticas de PostgreSQL
• pg_partman: Automatización de particionamiento
• pgBadger: Análisis de logs de PostgreSQL

Conclusión: el rendimiento es un proceso, no una tarea

Optimizar PostgreSQL no es algo que se hace una vez. Las consultas cambian, el volumen de datos crece, los patrones de acceso evolucionan. Lo que importa es tener los instrumentos de observabilidad (pg_stat_statements, logging de queries lentas) para detectar problemas antes de que afecten a los usuarios.

Si tu aplicación tiene problemas de rendimiento en base de datos que no has podido resolver, en Alternetica hacemos auditorías de rendimiento de PostgreSQL. Contáctanos y analizamos tu caso específico.