Ajuste Fino de la Configuración de Memoria de PostgreSQL para un Rendimiento Óptimo

Últimamente he estado investigando la configuración de memoria de PostgreSQL, buscando formas de ajustarla y mejorar el rendimiento. Después de revisar varios recursos y realizar algunas pruebas, encontré algunos ajustes clave que pueden hacer una gran diferencia.

Si estás utilizando PostgreSQL y quieres optimizar cómo usa la memoria, aquí hay algunos ajustes importantes a los que debes prestar atención.

Ajustes Clave de Memoria para Afinar

Desglosaremos los ajustes de memoria más importantes de PostgreSQL que deberías considerar ajustar.

1. shared_buffers

Piensa en shared_buffers como el almacén personal de memoria de PostgreSQL donde guarda los datos a los que se accede con frecuencia. La idea es almacenar la mayor cantidad de datos posible en memoria para reducir esas costosas lecturas de disco.

Ajuste Recomendado:

La mayoría de los expertos sugieren establecer esto en aproximadamente 25% de la RAM total de tu sistema.

Por ejemplo, si tienes 16GB de RAM, establecerlo en alrededor de 4GB es un buen punto de partida.

2. work_mem

Cada vez que PostgreSQL procesa consultas que implican ordenar, unir o agrupar datos, utiliza work_mem. Este ajuste controla cuánta memoria recibe cada operación antes de que comience a escribir archivos temporales en disco.

Ejemplo:

• Si work_mem = 50MB y tu consulta tiene 5 tareas de ordenación, podría usar 50MB x 5 = 250MB de memoria.
• Ahora imagina 10 usuarios ejecutando consultas similares – eso es 250MB x 10 = 2.5GB de memoria utilizada.

Consejo:

• Si lo estableces demasiado bajo, las consultas pueden volverse más lentas debido al uso de disco.
• Si lo estableces demasiado alto, podrías quedarte sin memoria rápidamente, especialmente con muchos usuarios.

A veces, valores más grandes de work_mem pueden ralentizar las cosas porque PostgreSQL tiene que gestionar grandes bloques de memoria.

3. effective_cache_size

Este ajuste es un poco diferente: no asigna memoria realmente, sino que le dice a PostgreSQL cuánta memoria cree que puede usar del caché del sistema operativo. PostgreSQL utiliza este valor para decidir si debe usar índices o hacer escaneos secuenciales.

Establecerlo incorrectamente:

• ¿Demasiado bajo? PostgreSQL podría evitar índices, pensando que la memoria es escasa.
• ¿Demasiado alto? PostgreSQL podría volverse demasiado optimista y tomar malas decisiones.

Una buena regla general: Establece esto en 50-75% de tu RAM total, especialmente si PostgreSQL es la carga principal en la máquina.

4. maintenance_work_mem

Este ajuste se trata de cuánta memoria puede usar PostgreSQL para tareas como VACUUM, CREATE INDEX y otras tareas de mantenimiento. Establecer este valor más alto puede acelerar estas operaciones sin ralentizar la actividad normal de la base de datos.

Ajuste Sugerido:

Si tienes espacio, súbelo a 1GB o más para que las tareas de mantenimiento se realicen rápidamente.

Formas de Modificar los Ajustes de Memoria

PostgreSQL permite modificar los ajustes de memoria de varias maneras, dependiendo de tus requisitos y tolerancia al riesgo. Aquí están los enfoques más efectivos:

1. Modificar Ajustes de Memoria Globalmente

Para cambios permanentes en todas las sesiones y consultas, modifica el archivo de configuración de PostgreSQL (postgresql.conf) o usa ALTER SYSTEM:

ALTER SYSTEM SET work_mem = '500MB';
ALTER SYSTEM SET shared_buffers = '8GB';
ALTER SYSTEM SET effective_cache_size = '20GB';
SELECT pg_reload_conf();  -- Aplicar cambios sin reiniciar

2. Cambiar Ajustes por Sesión

Para ajustes temporales que afectan solo la conexión actual:

SET work_mem = '100MB';
SET effective_cache_size = '16GB';

Mi Ejemplo Real de Ajuste de Memoria de PostgreSQL

Escenario ##o

Una base de datos PostgreSQL 10 que se ejecuta en un servidor de 30GB de RAM, con a menudo 15GB de RAM disponible, estaba experimentando un rendimiento lento en las consultas de exportación de CSV al procesar transacciones financieras. Una consulta compleja que unía cuatro tablas grandes estaba tardando 33 segundos en producir resultados:

SELECT * FROM finance_transaction ft
INNER JOIN billing_record br ON br.id = ft.billing_record
INNER JOIN transaction_log tl ON ft.transaction_log = tl.id
INNER JOIN transaction_audit_history tah ON tah.finance_transaction = ft.id;

Tamaños de las tablas:

• finance_transaction (~1 millón de filas)
• transaction_log (~1 millón de filas)
• transaction_audit_history (~4 millones de filas)
• billing_record (~1 millón de filas)

Ajustes de memoria predeterminados:

Plan de Ejecución de Consultas (EXPLAIN ANALYZE)

Gather  (cost=1071962.33..3055242.09 rows=6022483 width=4782) (actual time=26603.645..33465.648 rows=4847517 loops=1)
  Workers Planned: 4
  Workers Launched: 4
  ->  Hash Join  (cost=1070962.33..2451993.79 rows=1505621 width=4782) (actual time=26817.703..29038.177 rows=969503 loops=5)
        Hash Cond: (ft.id = tah.finance_transaction)
        ->  Hash Join  (cost=716707.62..1254298.44 rows=403544 width=4591) (actual time=18471.362..19081.998 rows=259931 loops=5)
              Hash Cond: (br.id = ft.billing_record)
              ->  Parallel Seq Scan on billing_record br  (cost=0.00..39488.92 rows=335492 width=3598) (actual time=0.005..42.297 rows=268393 loops=5)
              ->  Hash  (cost=501594.96..501594.96 rows=1564453 width=993) (actual time=18468.587..18468.589 rows=1299656 loops=5)
                    Buckets: 4096  Batches: 512  Memory Usage: 708kB
                    ->  Hash Join  (cost=103452.20..501594.96 rows=1564453 width=993) (actual time=9094.933..15042.241 rows=1300948 loops=5)
                          Hash Cond: (tl.id = ft.transaction_log)
                          ->  Seq Scan on transaction_log tl  (cost=0.00..33484.53 rows=1564453 width=766) (actual time=0.019..133.507 rows=1564453 loops=5)
                          ->  Hash  (cost=46549.31..46549.31 rows=1300631 width=227) (actual time=9092.767..9092.768 rows=1300948 loops=5)
                                Buckets: 16384  Batches: 128  Memory Usage: 2199kB
                                ->  Seq Scan on finance_transaction ft  (cost=0.00..46549.31 rows=1300631 width=227) (actual time=0.005..143.568 rows=1300948 loops=5)
        ->  Hash  (cost=165645.54..165645.54 rows=4852654 width=191) (actual time=8267.360..8267.360 rows=4852654 loops=5)
              Buckets: 32768  Batches: 512  Memory Usage: 2059kB
              ->  Seq Scan on transaction_audit_history tah  (cost=0.00..165645.54 rows=4852654 width=191) (actual time=0.020..789.242 rows=4852654 loops=5)
Planning time: 0.346 ms
Execution time: 33586.541 ms

Analiza el plan de ejecución de la consulta:

Después de aumentar los ajustes de memoria, este es el resultado de EXPLAIN ANALYZE:

Gather  (cost=1071962.33..3055242.09 rows=6022483 width=4782) (actual time=16228.246..23884.145 rows=4847517 loops=1)
  Workers Planned: 4
  Workers Launched: 4
  ->  Hash Join  (cost=1070962.33..2451993.79 rows=1505621 width=4782) (actual time=17226.338..20072.376 rows=969503 loops=5)
        Hash Cond: (ft.id = tah.finance_transaction)
        ->  Hash Join  (cost=716707.62..1254298.44 rows=403544 width=4591) (actual time=14289.482..15561.150 rows=259931 loops=5)
              Hash Cond: (br.id = ft.billing_record)
              ->  Parallel Seq Scan on billing_record br  (cost=0.00..39488.92 rows=335492 width=3598) (actual time=0.005..37.096 rows=268393 loops=5)
              ->  Hash  (cost=501594.96..501594.96 rows=1564453 width=993) (actual time=14287.210..14287.212 rows=1299656 loops=5)
                    Buckets: 524288  Batches: 8  Memory Usage: 46914kB
                    ->  Hash Join  (cost=103452.20..501594.96 rows=1564453 width=993) (actual time=3728.706..6986.704 rows=1300948 loops=5)
                          Hash Cond: (tl.id = ft.transaction_log)
                          ->  Seq Scan on transaction_log tl  (cost=0.00..33484.53 rows=1564453 width=766) (actual time=0.018..123.814 rows=1564453 loops=5)
                          ->  Hash  (cost=46549.31..46549.31 rows=1300631 width=227) (actual time=3704.309..3704.309 rows=1300948 loops=5)
                                Buckets: 2097152  Batches: 2  Memory Usage: 151889kB
                                ->  Seq Scan on finance_transaction ft  (cost=0.00..46549.31 rows=1300631 width=227) (actual time=0.018..139.211 rows=1300948 loops=5)
        ->  Hash  (cost=165645.54..165645.54 rows=4852654 width=191) (actual time=2927.466..2927.466 rows=4852654 loops=5)
              Buckets: 2097152  Batches: 4  Memory Usage: 246002kB
              ->  Seq Scan on transaction_audit_history tah  (cost=0.00..165645.54 rows=4852654 width=191) (actual time=0.025..630.852 rows=4852654 loops=5)
Planning time: 0.341 ms
Execution time: 24013.565 ms

Comparación de Rendimiento: Antes vs Después de Aumentar los Ajustes de Memoria

Elegir los Ajustes de Memoria Correctos: Un Enfoque Caso por Caso

Ajustar los ajustes de memoria de PostgreSQL no es una solución única para todos. Depende del tamaño de tu base de datos, usuarios concurrentes y patrones de consulta. Encontrar el work_mem, shared_buffers y effective_cache_size correctos.

• Si tu servidor tiene mucha RAM libre, puedes asignar más memoria a PostgreSQL.
• Si tienes muchos usuarios concurrentes, asignar demasiada memoria por consulta puede causar inestabilidad en el sistema.
• Ejecuta EXPLAIN ANALYZE en consultas lentas para identificar desbordamientos de disco e ineficiencias en los joins.

Si quieres probar cambios de forma segura sin afectar todo el sistema, aplica ajustes por consulta en lugar de globalmente:

✅ Cambia los ajustes para una sola consulta pesada:

SET LOCAL work_mem = '300MB';
SELECT * FROM finance_transaction ft
INNER JOIN billing_record br ON br.id = ft.billing_record
INNER JOIN transaction_log tl ON ft.transaction_log = tl.id
INNER JOIN transaction_audit_history tah ON tah.finance_transaction = ft.id;

Conclusión

Ajustar los ajustes de memoria de PostgreSQL puede hacer una gran diferencia en el rendimiento, ayudando a que tus consultas se ejecuten más rápido y reduciendo el I/O de disco innecesario. La clave es encontrar el equilibrio correcto basado en la carga de tu base de datos, la RAM disponible y cómo se comportan tus consultas.

Puntos Clave:

• Conoce tu carga de trabajo – No aumentes ciegamente los ajustes de memoria; primero analiza cómo se están ejecutando tus consultas.
• Usa EXPLAIN ANALYZE – Esto te ayudará a detectar problemas como desbordamientos de disco y joins ineficientes.
• Ajusta work_mem con cuidado – Un valor más alto puede hacer que los joins y la ordenación sean más rápidos, pero establecerlo demasiado alto puede consumir toda tu RAM.
• Cambia los ajustes en el nivel correcto – Usa ajustes globales para mejoras generales, ajustes por sesión para cambios temporales y ajustes por consulta para operaciones realmente pesadas.
• Monitorea el impacto – Revisa los registros, rastrea el rendimiento y ajusta según sea necesario.

Al final del día, el ajuste de PostgreSQL es parte ciencia, parte experimentación. Comienza pequeño, rastrea los resultados y ajusta a medida que avanzas. Con los ajustes correctos, tu base de datos funcionará más suave, más rápido y de manera mucho más eficiente. 🚀