Ajuste del rendimiento de PostgreSQL: Cómo optimizar su servidor de base de datos

El ajuste de las consultas SQL es sólo una parte de la historia del rendimiento.

Para sacar el máximo partido de PostgreSQL, también es necesario ajustar el servidor de bases de datos y el sistema operativo.

En este artículo, exploraremos los aspectos clave del ajuste del rendimiento, desde las consideraciones de hardware hasta los parámetros de configuración de PostgreSQL.

Por qué es importante ajustar el rendimiento

Incluso las consultas bien escritas pueden sufrir si:

El servidor está mal configurado.
Los recursos de hardware no están optimizados.
Demasiadas conexiones saturan el sistema.
Los puntos de control y la configuración de la WAL están mal ajustados.

👉 El ajuste del rendimiento asegura que PostgreSQL utiliza su hardware de manera eficiente y escala sin problemas.

Paso 1: Empezar por el sistema operativo

Antes de ajustar PostgreSQL, compruebe que el sistema operativo no es el cuello de botella:

Memoria: Asegúrese de que hay suficiente RAM disponible para evitar el intercambio.
CPU: Supervise los tiempos de carga y espera.
E/S de disco: Comprueba si hay picos de latencia.

Buenas prácticas:

Coloque los registros de transacciones (pg_wal) en un disco dedicado.
Utilice RAID 10 (0+1) para mayor velocidad y redundancia.
Evita los sistemas de archivos remotos para los directorios de datos.
Desactivar la sobrecarga innecesaria del sistema de archivos (por ejemplo, montar con noatime).

Paso 2: Ajuste de los parámetros PostgreSQL

La configuración por defecto de PostgreSQL es conservadora.

Ajústelos en postgresql.conf:

Ajustes de conexión

max_conexiones: Por defecto suele ser demasiado alto. Utilice un agrupador de conexiones (PgBouncer/pgpool-II) en lugar de aumentarlo a ciegas.

Ajustes de memoria

buffers_compartidos: Normalmente se ajusta a 25-40% de RAM disponible.
memoria_de_trabajo: Memoria por operación de consulta (ordenaciones, hashes). Ajústalo con cuidado para evitar un uso excesivo de memoria.
mantenimiento_trabajo_mem: Se utiliza para VACIAR, CREAR ÍNDICE. Aumentar para un mantenimiento más rápido.
tamaño_efectivo_de_cache: Ayuda al planificador a estimar la caché disponible (normalmente 50-75% de RAM).

WAL y puntos de control

wal_level: Utilice réplica para la replicación, lógico para la descodificación lógica.
wal_buffers: Autoajustado, pero puede aumentarse para sistemas con mucha escritura.
tamaño_máximo_wal: Aumentar para reducir la frecuencia de los puntos de control.
checkpoint_timeout: Por defecto 5 minutos. Aumentar reduce la presión del punto de control.

Paso 3: Utilizar consultas paralelas

PostgreSQL soporta ejecución paralela para:

Exploraciones secuenciales
Exploraciones sólo de índices
Uniones (hash, merge, bucle anidado)
Agregaciones

Parámetros clave:

max_trabajadores_paralelos
max_trabajadores_paralelos_por_reunion
coste_instalación_paralelo
coste_tupla_paralela

👉 El paralelismo puede mejorar significativamente el rendimiento de las cargas de trabajo analíticas.

Paso 4: Cargar datos de forma eficiente

Para cargas masivas de datos:

Utilice COPIA en lugar de múltiples INSERT.
Envolver inserciones en una transacción (COMENZAR ... COMPROMETERSE).
Desactivar temporalmente el registro de WAL con UNLOGGED mesas o conjunto wal_level = mínimo durante la carga.
Elimine y vuelva a crear índices después de cargar tablas grandes.

Paso 5: Considerar los ajustes no duraderos (con precaución)

Para entornos de prueba o casos en los que la durabilidad es menos crítica:

fsync = desactivado - Escrituras más rápidas, pero riesgo de corrupción.
synchronous_commit = off - Se salta la descarga de WAL en cada confirmación.
full_page_writes = off - Reduce el tamaño de la WAL pero aumenta el riesgo durante los choques.

⚠️ Utilizar sólo cuando la pérdida de datos sea aceptable.

Paso 6: Utilizar herramientas de supervisión

Aproveche Postgres Enterprise Manager (PEM) o herramientas de código abierto como pgBadger y pg_stat_statements:

El Asistente de Ajuste PEM sugiere cambios en la configuración.
El Gestor de Capacidades proyecta el crecimiento del almacenamiento.
Los paneles de rendimiento muestran el uso de memoria, CPU y E/S en tiempo real.

Lista de comprobación rápida para el ajuste del rendimiento

✅ Optimice el hardware (discos, RAM, CPU).
✅ Pon WAL en discos separados.
✅ Ajuste la configuración de memoria (shared_buffers, work_mem, maintenance_work_mem).
✅ Ajustar parámetros de WAL y checkpoint.
✅ Habilitar consultas paralelas.
✅ Cargar datos con las estrategias COPY y bulk.
✅ Monitorizar continuamente con PEM o pgBadger.

Reflexiones finales

PostgreSQL proporciona a los administradores de bases de datos la flexibilidad necesaria para ajustar el rendimiento a una amplia variedad de cargas de trabajo.

Empiece por el sistema operativo, pase a los parámetros de PostgreSQL y utilice herramientas de supervisión para validar las mejoras.