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 conjuntowal_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.