Guía Completa de Optimización de PostgreSQL
Optimiza PostgreSQL para alto rendimiento: configuración, índices, planes de consulta, pool de conexiones, particionado, vacuum y monitorización.
Introducción
PostgreSQL viene con valores por defecto conservadores — shared_buffers en 128MB, work_mem en 4MB — dimensionados para arrancar en casi cualquier hardware. Eso sirve para un portátil, no para producción. Una instancia bien ajustada suele implicar: más memoria para caché, checkpoints que no frenen las escrituras, índices que respondan a las consultas reales, un pool delante de las conexiones limitadas y un autovacuum que aguante tu ritmo de escritura.
Esta guía cubre la optimización del servidor y la operación: configuración, índices, planes de consulta, pooling, particionado, vacuum y monitorización. Para reescritura de consultas y análisis profundo de EXPLAIN, consulta la Guía Completa de Optimización de Consultas SQL — esta guía trata del motor, no del SQL.
Cuándo Optimizar Qué
Relaciona el síntoma con la palanca antes de tocar la configuración:
| Síntoma | Primera palanca |
|---|---|
| Consultas lentas en tablas grandes | Índices + EXPLAIN (ANALYZE, BUFFERS) |
| CPU alta, planner elige malos planes | Estadísticas (ANALYZE), random_page_cost, jit |
| Cache hit ratio < 95% | shared_buffers, effective_cache_size |
| Escrituras se atascan en ráfagas | Ajuste de checkpoints (max_wal_size, completion target) |
Cientos de conexiones, too many clients | PgBouncer + menor max_connections |
| Tablas >10M filas que lo ralentizan todo | Particionado |
| Bloat, dead tuples acumulándose | Autovacuum por tabla |
| I/O saturado en SSD | random_page_cost, effective_io_concurrency |
Regla general: mide primero con pg_stat_statements y EXPLAIN, cambia una cosa, vuelve a medir. Optimizar sin línea base es adivinar.
Cuándo la optimización no es la herramienta: si una consulta va lenta porque está mal escrita, ningún parámetro la arregla — reescríbela. Si la carga supera a un nodo, la respuesta son réplicas de lectura o sharding, no más RAM.
Establece una Línea Base
Antes de cambiar nada, captura los números con los que compararás:
# Benchmark rápido con pgbench (incluido en PostgreSQL)
pgbench -i -s 50 mydb # inicializa, factor de escala 50
pgbench -c 20 -j 4 -T 60 mydb # 20 clientes, 4 hilos, 60s
# → informa tps y latencia; guárdalo
-- Activa pg_stat_statements (requiere restart tras habilitarlo)
-- postgresql.conf: shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- Consultas por tiempo total — tus objetivos de optimización
SELECT
calls,
ROUND(total_exec_time::numeric, 0) AS total_ms,
ROUND(mean_exec_time::numeric, 2) AS mean_ms,
LEFT(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
Ajuste de Configuración
Parámetros de memoria
# postgresql.conf — los valores asumen un servidor dedicado de 8GB
# Shared buffers — ~25% de la RAM
shared_buffers = 2GB
# Effective cache size — ~75% de la RAM (pista para el planner, no una reserva)
effective_cache_size = 6GB
# Work mem — por sort/hash, por conexión
# peor caso ≈ work_mem * max_connections * sorts_por_consulta
work_mem = 64MB
# Maintenance work mem — VACUUM, CREATE INDEX, ALTER TABLE
maintenance_work_mem = 512MB
# WAL buffers — auto (-1) usa 1/32 de shared_buffers; ajústalo en cargas de escritura alta
wal_buffers = 16MB
Conexiones y paralelismo
# Mantén este valor BAJO — PostgreSQL maneja bien ~100-300 conexiones
# activas, no 1000. Pon un pool delante en lugar de subirlo.
max_connections = 200
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4
# JIT — ayuda en consultas analíticas grandes, perjudica a las OLTP cortas.
# Si tu carga es sobre todo de consultas cortas, considera jit = off.
jit = on
Ajuste de checkpoints e I/O
# Reparte los checkpoints para evitar frenazos de escritura
checkpoint_timeout = 15min
max_wal_size = 4GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9
# En SSD/NVMe — los valores por defecto asumen discos mecánicos
random_page_cost = 1.1 # el 4 por defecto penaliza index scans en SSD
effective_io_concurrency = 200 # peticiones de I/O paralelas que el SO maneja bien
Huge pages y nivel de SO
# postgresql.conf
huge_pages = try # usa THP si el SO lo proporciona
# /etc/sysctl.d/99-postgresql.conf — hosts Linux
vm.swappiness = 10 # prefiere datos en RAM frente a swap
vm.overcommit_memory = 2 # evita que el OOM killer mate postgres
# Transparent HugePages: 'madvise' funciona mejor que 'always' para postgres
echo madvise > /sys/kernel/mm/transparent_hugepage/enabled
Ajuste de autovacuum
autovacuum = on
autovacuum_max_workers = 6 # el 3 por defecto suele quedarse corto en escritura alta
autovacuum_naptime = 30s
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.05 # vacuum cuando el 5% de la tabla está muerta
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.02
En tablas calientes, ajusta por tabla en lugar de globalmente — una tabla de 500M filas al 5% acumula 25M de dead tuples antes de vacuum:
ALTER TABLE orders SET (
autovacuum_vacuum_scale_factor = 0.01,
autovacuum_vacuum_cost_delay = 2
);
Perfiles de Ajuste por Tamaño de Servidor
Los valores de configuración escalan con el hardware. Puntos de partida para un host dedicado a PostgreSQL — ajusta tras el benchmark:
| Parámetro | 4 GB | 16 GB | 64 GB |
|---|---|---|---|
shared_buffers | 1GB | 4GB | 16GB |
effective_cache_size | 3GB | 12GB | 48GB |
work_mem | 16MB | 32–64MB | 64–128MB |
maintenance_work_mem | 256MB | 512MB | 2GB |
wal_buffers | 16MB | 16MB | 32–64MB |
max_connections | 100 | 200 | 300 |
max_parallel_workers_per_gather | 2 | 4 | 8 |
Dos advertencias. work_mem se multiplica por las conexiones activas y por los nodos de sort/hash por consulta, así que en cargas con muchas conexiones mantenlo bajo y súbelo por sesión para informes (SET work_mem = '256MB'). Y max_connections se mantiene modesto a cualquier tamaño — un pool gestiona a la multitud mejor que backends planificados por el kernel.
Un repositorio complementario incluye postgresql.conf listos para estos perfiles, una configuración de PgBouncer y las consultas de monitorización de esta guía.
Estrategias de Indexación
B-tree (por defecto)
-- Índice de columna única
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
-- Índice compuesto (el orden importa — igualdad primero, luego rango)
CREATE INDEX idx_orders_status_created ON orders(status, created_at);
-- Índice parcial — indexa solo las filas que importan
CREATE INDEX idx_orders_pending ON orders(customer_id)
WHERE status = 'pending';
Índice covering (INCLUDE)
-- Las columnas extra viajan en el índice y la consulta no toca el heap
CREATE INDEX idx_orders_covering ON orders(customer_id, status)
INCLUDE (total, created_at);
-- Esta consulta se resuelve index-only:
SELECT total, created_at FROM orders
WHERE customer_id = 42 AND status = 'pending';
Índice por expresión
CREATE INDEX idx_orders_lower_email ON orders(LOWER(email));
-- La consulta debe coincidir exactamente con la expresión
SELECT * FROM orders WHERE LOWER(email) = 'alice@example.com';
Índice GIN (JSONB, arrays, trigramas)
CREATE INDEX idx_events_data ON events USING GIN (data);
CREATE INDEX idx_tags ON articles USING GIN (tags);
CREATE INDEX idx_articles_search ON articles USING GIN (to_tsvector('english', body));
Índice BRIN (tablas grandes ordenadas)
-- Block Range INdex — kilobytes en lugar de megabytes, ideal para datos
-- ordenados por tiempo donde el orden físico coincide con la columna
CREATE INDEX idx_logs_brin ON logs USING BRIN (created_at);
Cuándo NO indexar
- Tablas pequeñas (~1000 filas o menos) — el sequential scan gana
- Columnas por las que casi nunca se filtra
- Columnas de mucha escritura y poca lectura — cada índice penaliza cada escritura
- Columnas de baja cardinalidad (booleano, status con 3 valores) — índice parcial en su lugar
- Crea siempre con
CONCURRENTLYen tablas en vivo:CREATE INDEX CONCURRENTLY idx_x ON t(col)— evita el bloqueo de escritura a costa de una construcción más lenta
Optimización de Consultas
EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 20;
-- Qué buscar:
-- Seq Scan en tabla grande → probablemente falta un índice
-- Rows estimado 1, real 400000 → stats desactualizadas, ejecuta ANALYZE
-- Sort vertiendo a disco (external merge) → sube work_mem para esa consulta
-- Nested Loop con millones de iteraciones → índice faltante en el lado interno
Antipatrones habituales
-- MAL: OR impide usar índices
SELECT * FROM orders WHERE customer_id = 1 OR status = 'pending';
-- BIEN: divide en UNION ALL
SELECT * FROM orders WHERE customer_id = 1
UNION ALL
SELECT * FROM orders WHERE status = 'pending' AND customer_id != 1;
-- MAL: wildcard inicial anula el B-tree
SELECT * FROM customers WHERE name LIKE '%alice%';
-- BIEN: índice de trigramas
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_customers_name_trgm ON customers USING GIN (name gin_trgm_ops);
-- MAL: función sobre la columna indexada
SELECT * FROM orders WHERE EXTRACT(YEAR FROM created_at) = 2024;
-- BIEN: rango sobre la columna en crudo
SELECT * FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
-- MAL: SELECT * lo trae todo e impide index-only scans
-- BIEN: selecciona solo lo que necesitas
SELECT id, total, status FROM orders WHERE customer_id = 42;
Pool de Conexiones
PostgreSQL crea un proceso por conexión — cada una cuesta ~5-10MB más overhead de cambio de contexto. Pasadas unas cientas conexiones activas, el rendimiento se desploma.
PgBouncer
# pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256 # md5 aún funciona; scram es el valor seguro hoy
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 300
| Modo | Descripción | Mejor para |
|---|---|---|
| session | Un servidor por cliente | Conexiones persistentes, prepared statements, temp tables |
| transaction | Servidor por transacción | La mayoría de apps — mejor aprovechamiento; ojo con el estado de sesión |
| statement | Servidor por sentencia | Solo consultas simples de una sola sentencia |
El modo transaction rompe SET, prepared statements, LISTEN/NOTIFY y temp tables de sesión — revisa cómo tu ORM usa la sesión antes de activarlo.
Particionado
Particionado por rango (series temporales)
CREATE TABLE events (
id BIGSERIAL,
created_at TIMESTAMPTZ NOT NULL,
data JSONB
) PARTITION BY RANGE (created_at);
CREATE TABLE events_2024_01 PARTITION OF events
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE events_2024_02 PARTITION OF events
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
CREATE TABLE events_default PARTITION OF events DEFAULT;
-- Automatiza las particiones mensuales
CREATE EXTENSION pg_partman;
SELECT partman.create_parent('public.events', 'created_at', 'native', 'monthly');
Particionado por lista (por categoría)
CREATE TABLE orders_by_region (
id BIGSERIAL,
region TEXT NOT NULL,
total NUMERIC
) PARTITION BY LIST (region);
CREATE TABLE orders_us PARTITION OF orders_by_region FOR VALUES IN ('US');
CREATE TABLE orders_eu PARTITION OF orders_by_region FOR VALUES IN ('EU', 'UK');
CREATE TABLE orders_other PARTITION OF orders_by_region DEFAULT;
Particionado por hash (distribución uniforme)
CREATE TABLE users_hashed (
id BIGSERIAL,
email TEXT,
data JSONB
) PARTITION BY HASH (id);
CREATE TABLE users_hash_0 PARTITION OF users_hashed FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE users_hash_1 PARTITION OF users_hashed FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE users_hash_2 PARTITION OF users_hashed FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE users_hash_3 PARTITION OF users_hashed FOR VALUES WITH (MODULUS 4, REMAINDER 3);
El partition pruning solo funciona cuando las consultas filtran por la clave de partición — una tabla particionada por created_at consultada por customer_id no gana nada y paga overhead de planificación en cada consulta.
Gestión de Vacuum
-- Bloat: dead tuples como % de filas vivas
SELECT
schemaname,
tablename,
pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS size,
n_live_tup,
n_dead_tup,
ROUND(n_dead_tup::FLOAT / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;
-- Vacuum manual con actualización de estadísticas
VACUUM (ANALYZE, VERBOSE) orders;
-- VACUUM FULL reescribe la tabla y mantiene un bloqueo exclusivo — planifícalo
VACUUM FULL orders;
-- ¿Está autovacuum corriendo ahora y sobre qué?
SELECT pid, phase, heap_blks_total, heap_blks_scanned
FROM pg_stat_progress_vacuum;
Dead tuples por encima del ~10-20% en tablas calientes significan que autovacuum no da abasto — ajusta autovacuum_vacuum_scale_factor por tabla antes de recurrir a VACUUM FULL.
Monitorización
La optimización es un ciclo, no un evento. Vigila estas señales en el tiempo — no solo tras los cambios, sino como telemetría de base: tiempos de consulta vía pg_stat_statements, conexiones en pg_stat_activity, proporción de dead tuples, cache hit ratio y lag de replicación si usas standbys. Una deriva lenta en cualquiera de ellas te indica la siguiente palanca antes de que se convierta en incidente.
-- Consultas lentas en vivo: log_min_duration_statement = 100 en postgresql.conf
-- Qué está corriendo ahora mismo
SELECT pid, now() - query_start AS duration, query, state
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;
-- Uso de índices — scans cercanos a cero son candidatos a eliminación
SELECT
schemaname,
tablename,
indexname,
idx_scan AS scans
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC
LIMIT 20;
-- Cache hit ratio por tabla (objetivo >95%)
SELECT
relname,
ROUND(heap_blks_hit::FLOAT / NULLIF(heap_blks_hit + heap_blks_read, 0) * 100, 2) AS hit_ratio
FROM pg_statio_user_tables
ORDER BY hit_ratio ASC
LIMIT 20;
-- Cache hit ratio a nivel de base de datos
SELECT
datname,
ROUND(blks_hit::FLOAT / NULLIF(blks_hit + blks_read, 0) * 100, 2) AS cache_hit_ratio
FROM pg_stat_database;
Resolución de Problemas
| Síntoma | Causa probable | Solución |
|---|---|---|
| Consulta lenta de repente tras un deploy | Stats desactualizadas o plan nuevo | ANALYZE a las tablas; compara planes antes/después con EXPLAIN |
FATAL: too many connections | Sin pool, o pool agotado | PgBouncer en modo transaction; audita apps con conexiones idle (pg_stat_activity) |
| Picos de latencia de escritura cada ~5min | Ráfaga de I/O del checkpoint | checkpoint_completion_target = 0.9, sube max_wal_size |
| Autovacuum siempre atrasado | Pocos workers o scale factor alto | autovacuum_max_workers = 6, scale factor 0.01 por tabla |
| El índice no se usa | Baja selectividad, stats viejas o función en la columna | ANALYZE; revisa costes en EXPLAIN; reescribe el predicado |
work_mem se desborda (temp files en logs) | Sorts/hashes que superan work_mem | Sube work_mem para esa carga, o arregla la consulta |
| Disco lleno de WAL | max_wal_size mal leído o archive_command fallando | Revisa fallos en pg_stat_archiver; arregla el archivado antes de que el WAL llene el disco |
Buenas Prácticas
shared_buffersal ~25% de la RAM — el ajuste con más impacto en un servidor dedicado- Crea índices antes de cargar datos — o usa
CREATE INDEX CONCURRENTLYen tablas en vivo - Ejecuta
ANALYZEtras cargas masivas — el planner trabaja con estadísticas frescas - Usa un pool de conexiones — PostgreSQL maneja bien ~100-300 conexiones activas, no miles
- Vigila el cache hit ratio — objetivo >95%; por debajo, añade RAM o arregla consultas
- Particiona tablas de más de ~10M filas — pero solo si las consultas filtran la clave de partición
- Usa
pg_stat_statements— optimiza lo que realmente se ejecuta, no lo que crees que se ejecuta - Ajusta autovacuum por tabla caliente — los scale factors globales quedan cortos en tablas grandes
- Usa
EXPLAIN (ANALYZE, BUFFERS)— nunca adivines qué hace el planner - Cambia un parámetro cada vez — benchmark tras cada cambio o no sabrás qué ayudó
Errores Comunes
- Producción con los valores por defecto —
shared_buffers = 128MBapenas cachea nada - Sobre-indexar — cada índice penaliza cada escritura; elimina los no usados
- Saltarse
ANALYZEtras cargas masivas — el planner apuesta con stats viejas VACUUM FULLen horas pico — bloquea la tabla entera- Subir
max_connectionsa 500+ sin pool — el cambio de contexto se come la CPU - Indexar columnas de baja cardinalidad — un índice sobre un booleano es casi inútil
work_memalto globalmente —work_mem × conexiones × sortspuede agotar la RAM- Ignorar el bloat — los dead tuples se acumulan hasta que los sequential scans se arrastran
- Optimizar sin línea base — sin datos de
pgbench/pg_stat_statementsno hay prueba de que ayudó - Tratar
effective_cache_sizecomo una reserva — es una pista para el planner, no memoria asignada
Ver También
Preguntas frecuentes
¿Cuánta RAM debo asignar a PostgreSQL?
Asigna ~25% a shared_buffers y avisa al planner del ~75% con effective_cache_size. El resto es para la page cache del SO y el work_mem por conexión. Para un servidor dedicado de 16GB: shared_buffers = 4GB, effective_cache_size = 12GB, work_mem entre 32-64MB según max_connections.
¿Cuándo usar particionado frente a indexación?
Índices para consultas que filtran columnas concretas y devuelven pocos resultados. Particionado para tablas de más de ~10M filas donde las consultas filtran la clave de partición (normalmente tiempo). Son complementarios — las tablas particionadas siguen necesitando índices dentro de cada partición. Para ver cómo otro motor aborda el mismo problema, consulta Guía Completa de Indexación en MongoDB.
¿Cómo encuentro índices sin uso?
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_total_relation_size(schemaname || '.' || indexname) DESC;
Cero scans desde el último reset de estadísticas lo convierte en candidato a eliminar — pero revisa cuándo se ejecutó pg_stat_reset() para no borrar un índice que solo se usa en los informes de fin de mes. Elimina con DROP INDEX CONCURRENTLY.
¿Por qué mi cache hit ratio es bajo aunque subí shared_buffers?
shared_buffers no es toda la historia — PostgreSQL también depende de la page cache del SO, y una caché fría tras un reinicio lee de disco. Si el working set simplemente excede la RAM, ningún parámetro lo arregla: reduce el working set (índices, particionado, archivado) o añade memoria. Consulta pg_statio_user_tables para ver qué tablas fallan realmente a la caché.
¿Qué controla realmente work_mem?
Memoria por operación de sort o hash, por nodo de consulta, por conexión — no un pool global. work_mem = 64MB con 200 conexiones ejecutando consultas con varios sorts puede pedir gigabytes. Si aparecen temp file en los logs o EXPLAIN muestra external merge, súbelo para esa carga; con muchas conexiones, súbelo con cautela.
Recursos Relacionados
Desnormalización: espacio por velocidad de lectura
Guía práctica de desnormalización de bases de datos: cuándo cambiar almacenamiento por rendimiento de lectura y mantener datos consistentes.
GuideCTEs en SQL: Expresiones de Tablas Comunes Explicadas
Guía práctica de CTEs en SQL: expresiones no recursivas y recursivas, legibilidad, rendimiento y cuándo usarlas sobre subconsultas.
GuideMigración de Datos: Estrategias Zero-Downtime que Funcionan
Planifica, duplica escrituras, rellena históricos y valida una migración de datos sin interrupción — con evolución de esquemas y rollback.
RecipeSembrar Base de Datos
Cómo sembrar bases de datos con datos realistas para desarrollo, testing y staging usando scripts de seed, migraciones y factories en PostgreSQL, MongoDB y Prisma.
GuideBúsqueda de Texto Completo: Implementa Búsqueda que Funciona
Guía práctica de búsqueda de texto completo: tsvector de PostgreSQL, indexación en Elasticsearch, diseño de consultas, ajuste de relevancia y construcción de búsqueda confiable con autocompletado, faceting y tolerancia a errores.
GuideGuía Completa de Optimización de Consultas SQL
Detecta y corrige consultas SQL lentas: lee planes EXPLAIN, indexa bien, elimina consultas N+1 y aplica particionamiento, pooling y caché en PostgreSQL y MySQL.