Tuning de parámetros de PostgreSQL

PostgreSQL viene con configuración conservadora: los defaults asumen un servidor pequeño y una carga modesta. En máquinas modernas con decenas de GB de RAM, dejar los valores originales desperdicia recursos y degrada el rendimiento. Esta lección recorre los parámetros con mayor impacto y muestra cómo aplicarlos sin causar incidentes.

Memoria: los cuatro parámetros clave

La gestión de memoria en PostgreSQL se reparte entre cuatro parámetros principales que conviene calibrar conjuntamente:

• shared_buffers: caché compartida de bloques de tabla e índice. Recomendación: 25 % de la RAM. Valor demasiado bajo provoca lecturas constantes a disco; valor demasiado alto deja sin memoria al sistema y a work_mem.

• effective_cache_size: estimación de la caché disponible (PostgreSQL + sistema operativo). No reserva memoria, solo informa al planificador. Recomendación: 50-75 % de la RAM.

• work_mem: memoria por operación de sort y hash. Por conexión, no global. Cada query puede usar varios work_mem si tiene varios sorts. Recomendación inicial: 4 a 16 MB.

• maintenance_work_mem: memoria para VACUUM, CREATE INDEX, REINDEX. Recomendación: 5-10 % de la RAM, hasta 1-2 GB.

ALTER SYSTEM SET shared_buffers = '8GB';
ALTER SYSTEM SET effective_cache_size = '24GB';
ALTER SYSTEM SET work_mem = '16MB';
ALTER SYSTEM SET maintenance_work_mem = '1GB';

SELECT pg_reload_conf();

shared_buffers requiere reinicio. Los demás se aplican con pg_reload_conf(). Si arrancas un cluster nuevo, programa los valores antes de conectar usuarios.

Cálculo de work_mem por conexión

El cálculo correcto de work_mem depende del número de conexiones simultáneas. Si tu servidor tiene 100 conexiones activas y cada una puede consumir hasta 4 sorts de work_mem, el peor caso es:

work_mem * 4 sorts/query * 100 conexiones = consumo maximo

Con work_mem = 16MB, el peor caso son 6.4 GB. Si el servidor tiene 32 GB y shared_buffers = 8GB, queda margen suficiente. Si subes work_mem = 256MB con esas mismas conexiones, el peor caso son 102 GB y el OOM killer se llevará el postmaster.

En entornos con muchas conexiones, conectar mediante PgBouncer o similar reduce drásticamente la presión sobre work_mem. Una pool de 50 conexiones reales con 1000 conexiones de cliente es preferible a 1000 conexiones reales.

Detectar derrames a disco

Cuando una operación supera work_mem, PostgreSQL derrama a disco temporal. Esto puede ralentizar la query 10-100 veces. Detectarlos es esencial:

-- Activar log de archivos temporales
ALTER SYSTEM SET log_temp_files = '0';
SELECT pg_reload_conf();

Con log_temp_files = 0, cualquier archivo temporal se registra en logs. También se puede ver en EXPLAIN ANALYZE:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM pedidos ORDER BY total DESC;

--    Sort Method: external merge  Disk: 50000kB
--   ^^^ derrame a disco, work_mem insuficiente

Si esa query se ejecuta a menudo, conviene subir work_mem solo para esa sesión:

SET work_mem = '256MB';
SELECT * FROM pedidos ORDER BY total DESC;
RESET work_mem;

Paralelismo

PostgreSQL puede paralelizar sequential scans, joins, agregaciones y CREATE INDEX. Los parámetros principales:

ALTER SYSTEM SET max_worker_processes = 16;            -- limite global
ALTER SYSTEM SET max_parallel_workers = 8;             -- para queries
ALTER SYSTEM SET max_parallel_workers_per_gather = 4;  -- por query
ALTER SYSTEM SET max_parallel_maintenance_workers = 4; -- CREATE INDEX

max_parallel_workers_per_gather es el límite por query: una query analítica puede usar 4 workers + el proceso principal = 5 procesos. En un servidor con 16 vCPUs y 50 conexiones, 4 workers por gather es razonable.

El paralelismo solo se activa si el coste estimado de la query supera min_parallel_table_scan_size (8MB por defecto) y parallel_setup_cost (1000) más parallel_tuple_cost (0.1) por tupla.

JIT compilation

PostgreSQL 11+ incorpora JIT (Just-In-Time compilation) basado en LLVM. Compila expresiones complejas a código nativo, acelerando queries analíticas pesadas. Activado por defecto desde la versión 12:

SHOW jit;                          -- on por defecto
SHOW jit_above_cost;               -- 100000 por defecto
SHOW jit_inline_above_cost;        -- 500000
SHOW jit_optimize_above_cost;      -- 500000

JIT añade overhead inicial. Para queries OLTP rápidas (< 100 ms) conviene desactivarlo:

ALTER SYSTEM SET jit = 'off';

Para entornos analíticos (data warehouses, reporting), JIT acelera consultas largas. Medir antes de cambiar.

Checkpoints y WAL

Los checkpoints sincronizan los buffers modificados a disco. Frecuencia y tamaño afectan al rendimiento:

ALTER SYSTEM SET checkpoint_timeout = '15min';
ALTER SYSTEM SET max_wal_size = '8GB';
ALTER SYSTEM SET min_wal_size = '2GB';
ALTER SYSTEM SET checkpoint_completion_target = 0.9;
ALTER SYSTEM SET wal_compression = 'on';

Checkpoints muy frecuentes saturan el disco con escrituras. Checkpoints muy espaciados generan muchos WAL pendientes, retrasan recovery y aumentan el riesgo de pérdida tras crash.

wal_compression = on reduce el tamaño del WAL en 2-3x con un coste de CPU mínimo. Suele compensar siempre.

Logging útil para tuning

Logs bien configurados son la base del tuning empírico:

ALTER SYSTEM SET log_min_duration_statement = '500ms';   -- queries > 500ms
ALTER SYSTEM SET log_checkpoints = 'on';
ALTER SYSTEM SET log_connections = 'on';
ALTER SYSTEM SET log_disconnections = 'on';
ALTER SYSTEM SET log_lock_waits = 'on';
ALTER SYSTEM SET log_temp_files = '0';
ALTER SYSTEM SET log_autovacuum_min_duration = '1s';

Combinado con pg_stat_statements, ofrece una imagen completa del comportamiento real.

Validación con pgbench

pgbench es la herramienta oficial para benchmarking. Antes y después de cualquier cambio importante, mide el efecto:

# Inicializar dataset escalado (factor 100 = ~1.5 GB)
pgbench -i -s 100 -d produccion

# Test de 5 minutos con 10 clientes y 4 threads
pgbench -c 10 -j 4 -T 300 -P 30 -d produccion

# transaction type: <builtin: TPC-B (sort of)>
# scaling factor: 100
# number of clients: 10
# number of threads: 4
# duration: 300 s
# tps = 4523.123456 (excluding connections establishing)

Compara TPS antes y después del cambio. Las mejoras reales suelen ser del 10-30 % por iteración; cambios "espectaculares" suelen indicar que el original estaba muy mal configurado.

Generadores de configuración

Hay herramientas que sugieren valores razonables a partir de RAM y tipo de carga. PGTune y pgconfig.org son las más usadas. Generan recomendaciones para:

• Tipo de carga: OLTP, Data Warehouse, Mixto, Desktop.
• Tamaño total de RAM.
• Conexiones máximas.
• Número de CPUs.
• Tipo de almacenamiento (HDD o SSD).

Estas herramientas son un buen punto de partida. El tuning fino requiere medir con tu carga real y ajustar iterativamente. Nunca apliques sus sugerencias sin reiniciar en horario controlado.

Connection pooling

Es uno de los ajustes con mayor impacto y a menudo se descuida. Cada conexión PostgreSQL consume memoria del proceso backend (típicamente 5-15 MB) más work_mem cuando ejecuta queries. Mil conexiones cuesta varios GB solo de overhead.

PgBouncer o pgcat mantienen un pool reducido (50-100 conexiones reales) y multiplexan miles de conexiones de cliente. Beneficios:

• Reduce memoria total.
• Permite subir work_mem con seguridad.
• Acelera la apertura percibida de conexión (no hay fork).
• Aísla picos de carga.

# pgbouncer.ini
[databases]
produccion = host=localhost dbname=produccion

[pgbouncer]
pool_mode = transaction
default_pool_size = 50
reserve_pool_size = 10
max_client_conn = 1000

Lista de chequeo antes de subir a producción

Antes de declarar un cluster listo para producción, verifica:

• shared_buffers entre 15 y 25 % de RAM.
• work_mem calculado para tu número máximo de conexiones reales.
• effective_cache_size estimado correctamente.
• max_wal_size suficiente para evitar checkpoints muy frecuentes bajo carga.
• autovacuum habilitado y con thresholds ajustados por tabla caliente.
• PgBouncer configurado si la aplicación abre muchas conexiones.
• log_min_duration_statement activo para detectar slow queries.
• pg_stat_statements instalado y consultado regularmente.
• shared_preload_libraries incluyendo extensiones necesarias (auto_explain, pg_stat_statements, timescaledb si aplica).

flowchart LR
    A[RAM 32GB] --> B[shared_buffers 8GB]
    A --> C[work_mem 16MB x conn]
    A --> D[effective_cache_size 24GB]
    A --> E[maintenance_work_mem 1GB]
    F[100 conn maxima] --> G[PgBouncer pool 50]
    H[Carga real] --> I[pgbench y pg_stat_statements]
    I --> J[Iteracion ajustes]

El tuning no es magia. Es el resultado de medir, cambiar un parámetro, medir de nuevo y documentar. Los valores que funcionan en tu cluster pueden ser distintos de los del cluster vecino. Lo único universal es la metodología.