🚀 RDS Tuning en PostgreSQL - Notas de Formación
1. SQL y el Plan de Ejecución
SQL es un lenguaje declarativo: especificamos qué queremos obtener, pero no cómo hacerlo. El motor de PostgreSQL determina el mejor método de ejecución a través del plan de ejecución, evaluando:
✅ Volumen de datos en las tablas
✅ Índices disponibles
✅ Tablas de estadísticas (ANALYZE
)
2. Plan de Ejecución en PostgreSQL y su Caché
PostgreSQL calcula el plan de ejecución al ejecutar una consulta por primera vez en una sesión.
Dicho plan se cachea y reutiliza en las siguientes ejecuciones dentro de la misma sesión.
Problema: Si los datos cambian drásticamente, el plan puede volverse ineficiente.
Solución:
✔ Usar un connection pool (PgBouncer
, RDS Proxy
) para evitar sesiones largas con planes desactualizados.
✔ Actualizar las estadísticas periódicamente:
ANALYZE my_table;
✔ Forzar reoptimización de planes:
DISCARD PLANS;
3. Mantenimiento de PostgreSQL: Auto-Vacuum y Estadísticas
PostgreSQL cuenta con un Auto-Vacuum que se ejecuta regularmente para:
🔹 Actualizar estadísticas (ANALYZE
) y mejorar los planes de ejecución.
🔹 Liberar espacio (VACUUM
) y reducir fragmentación.
🔹 Manejar visibilidad de datos (MVCC
).
Optimización del Auto-Vacuum
Si hay mucha escritura, el auto-vacuum podría no ejecutarse lo suficientemente rápido. Opciones:
✔ Reducir el umbral de activación:
ALTER TABLE my_table SET (autovacuum_vacuum_scale_factor = 0.01);
✔ Ejecutar VACUUM ANALYZE
en horarios estratégicos.
✔ Usar pg_repack
para reorganizar tablas sin bloquearlas.
4. Índices y Caché en PostgreSQL
PostgreSQL almacena en memoria los datos más accedidos usando:
✅ shared_buffers
(caché de páginas de datos).
✅ work_mem
(memoria para ordenamientos y joins).
Tipos de Índices en PostgreSQL
📌 B-Tree (default, ideal para búsquedas exactas y ordenaciones).
📌 GIN (para búsquedas en arrays y full-text search).
📌 GiST (datos espaciales con PostGIS).
📌 BRIN (para tablas grandes con datos correlacionados).
Problemas Comunes en Índices:
⚠ Alto consumo de IOPS → Puede indicar falta de índices o una mala estrategia de caché.
⚠ Consultas que usan Seq Scan en lugar de Index Scan → Revisar con EXPLAIN ANALYZE
.
5. Optimización de IOPS y Patrón de Escritura/Lectura
Si ves un alto consumo de IOPS en RDS, revisa:
✔ Falta de memoria (shared_buffers
).
✔ Uso incorrecto de índices (EXPLAIN ANALYZE
).
✔ Exceso de escrituras (VACUUM
, fillfactor
).
Distribución Ideal de IOPS
📊 En bases de datos transaccionales (OLTP), un 70% lecturas / 30% escrituras suele ser óptimo.
📊 Bases de datos analíticas (OLAP) pueden requerir 90% lecturas.
6. Monitoreo en CloudWatch
Para un análisis preciso: ver la última hora y ajustar el período a 1 minuto para evitar gráficas suavizadas.
🔹 CPUUtilization: Mantener por debajo del 25%.
🔹 ReadLatency: Si es alto, revisar índices y memoria.
🔹 WriteLatency: Si es alto, evaluar la estrategia de escrituras y VACUUM
.
🔹 TotalIOPS: Identificar picos de consumo.
🔹 EBSIOBalance%: Revisar si la instancia ha alcanzado su límite de IOPS.
7. Comandos Útiles para Tuning
Ver el plan de ejecución de una consulta
EXPLAIN ANALYZE SELECT * FROM my_table WHERE column = 'value';
Actualizar estadísticas y optimizar planes
VACUUM ANALYZE my_table;
Monitorear consultas más costosas
SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;
Query para detectar locks en las bd de postgres
Query para detectar locks en las bd de postgres
SELECT blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;