La base de datos suele ser el mayor cuello de botella de rendimiento en los sistemas web corporativos. A medida que crece el volumen de registros en la base de datos, las consultas SQL mal optimizadas (llamadas Slow Queries) comienzan a bloquear las conexiones en el servidor, elevando el uso de la CPU al 100% y ralentizando la experiencia de los usuarios finales.
En este artículo, presentamos las principales técnicas de ingeniería de bases de datos para analizar y optimizar la velocidad de las consultas en PostgreSQL.
1. Localizando Slow Queries con pg_stat_statements
El primer paso es mapear qué consultas son las verdaderas causantes de la lentitud en la aplicación. Para ello, activamos la extensión nativa pg_stat_statements.
Activación:
En el archivo de configuración postgresql.conf, agregue:
shared_preload_libraries = 'pg_stat_statements'Reinicie PostgreSQL y cree la extensión en la base de la aplicación:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;La consulta de abajo lista las diez instrucciones que más tiempo acumulado consumieron. En PostgreSQL 13 o más nuevo las columnas son total_exec_time y mean_exec_time. En la versión 12, use total_time y mean_time.
SELECT
round(total_exec_time::numeric, 1) AS total_ms,
calls,
round(mean_exec_time::numeric, 1) AS mean_ms,
left(query, 120) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;Un total_ms alto con muchos calls es la consulta que merece un índice. Un mean_ms alto con pocos calls es un informe puntual, no el cuello de botella del día.
2. Descifrando el Plan de Ejecución con EXPLAIN ANALYZE
Con la consulta de esa lista, ejecute el plan real. BUFFERS muestra si la lectura vino del disco:
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM pedidos
WHERE cliente_id = 42
ORDER BY data_criacao DESC
LIMIT 20;PostgreSQL devuelve el plan, no las filas. Un Seq Scan en pedidos con shared read alto es la tabla entera en disco. Después del índice de la sección siguiente, el mismo comando muestra Index Scan.
Qué buscar en el informe:
- Seq Scan (Sequential Scan): Indica que la base de datos tuvo que leer toda la tabla, fila por fila, en el disco. En tablas con millones de registros, esto resulta catastrófico.
- Index Scan: Significa que la búsqueda utilizó un índice indexado en memoria para encontrar el registro instantáneamente, lo cual representa el escenario ideal.
3. Creando Índices Inteligentes (B-Tree e Índices Compuestos)
Para eliminar los Sequential Scans en las cláusulas WHERE, creamos índices en los campos más consultados.
Ejemplo:
Si el sistema realiza búsquedas frecuentes combinando cliente_id y data_criacao: CREATE INDEX idx_pedidos_cliente_data ON pedidos (cliente_id, data_criacao DESC);
Evite el exceso: Cada nuevo índice creado acelera las consultas de lectura (SELECT), pero ralentiza las operaciones de escritura (INSERT/UPDATE), ya que PostgreSQL necesita reconstruir el árbol de índices con cada cambio de registro.
4. Calibración de Memoria Compartida
La configuración predeterminada de PostgreSQL es conservadora para permitir su funcionamiento en hardware modesto. Para producción, calibre los parámetros fundamentales en su postgresql.conf:
shared_buffers: Cantidad de memoria RAM dedicada a la caché de datos. Configúrela entre el 25% y el 40% de la memoria total disponible en el servidor.work_mem: Memoria utilizada para operaciones de ordenación interna (ORDER BY, DISTINCT) por conexión. Elevarla de forma moderada evita la escritura temporal de datos en el disco durante las ordenaciones.
Conclusión
Mantener una base de datos saludable exige un monitoreo constante del plan de ejecución y de las métricas de memoria del servidor. Al aplicar índices compuestos planificados y ajustar las variables de buffers de PostgreSQL, su aplicación corporativa funcionará de manera mucho más fluida y económica.