StackPractices
advanced Por Mathias Paulenko

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íntomaPrimera palanca
Consultas lentas en tablas grandesÍndices + EXPLAIN (ANALYZE, BUFFERS)
CPU alta, planner elige malos planesEstadísticas (ANALYZE), random_page_cost, jit
Cache hit ratio < 95%shared_buffers, effective_cache_size
Escrituras se atascan en ráfagasAjuste de checkpoints (max_wal_size, completion target)
Cientos de conexiones, too many clientsPgBouncer + menor max_connections
Tablas >10M filas que lo ralentizan todoParticionado
Bloat, dead tuples acumulándoseAutovacuum por tabla
I/O saturado en SSDrandom_page_cost, effective_io_concurrency
Mermaid flowchart LR diagram

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ámetro4 GB16 GB64 GB
shared_buffers1GB4GB16GB
effective_cache_size3GB12GB48GB
work_mem16MB32–64MB64–128MB
maintenance_work_mem256MB512MB2GB
wal_buffers16MB16MB32–64MB
max_connections100200300
max_parallel_workers_per_gather248

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 CONCURRENTLY en 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
ModoDescripciónMejor para
sessionUn servidor por clienteConexiones persistentes, prepared statements, temp tables
transactionServidor por transacciónLa mayoría de apps — mejor aprovechamiento; ojo con el estado de sesión
statementServidor por sentenciaSolo 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íntomaCausa probableSolución
Consulta lenta de repente tras un deployStats desactualizadas o plan nuevoANALYZE a las tablas; compara planes antes/después con EXPLAIN
FATAL: too many connectionsSin pool, o pool agotadoPgBouncer en modo transaction; audita apps con conexiones idle (pg_stat_activity)
Picos de latencia de escritura cada ~5minRáfaga de I/O del checkpointcheckpoint_completion_target = 0.9, sube max_wal_size
Autovacuum siempre atrasadoPocos workers o scale factor altoautovacuum_max_workers = 6, scale factor 0.01 por tabla
El índice no se usaBaja selectividad, stats viejas o función en la columnaANALYZE; revisa costes en EXPLAIN; reescribe el predicado
work_mem se desborda (temp files en logs)Sorts/hashes que superan work_memSube work_mem para esa carga, o arregla la consulta
Disco lleno de WALmax_wal_size mal leído o archive_command fallandoRevisa fallos en pg_stat_archiver; arregla el archivado antes de que el WAL llene el disco

Buenas Prácticas

  • shared_buffers al ~25% de la RAM — el ajuste con más impacto en un servidor dedicado
  • Crea índices antes de cargar datos — o usa CREATE INDEX CONCURRENTLY en tablas en vivo
  • Ejecuta ANALYZE tras 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 = 128MB apenas cachea nada
  • Sobre-indexar — cada índice penaliza cada escritura; elimina los no usados
  • Saltarse ANALYZE tras cargas masivas — el planner apuesta con stats viejas
  • VACUUM FULL en horas pico — bloquea la tabla entera
  • Subir max_connections a 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_mem alto globalmente — work_mem × conexiones × sorts puede 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_statements no hay prueba de que ayudó
  • Tratar effective_cache_size como 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.