Índices y bloqueos en un reporte nocturno que degrada la operación
Un sistema de facturación con 40 millones de filas en la tabla facturas ejecuta cada noche un reporte que agrega ventas por cliente y mes. El reporte tarda 3 h y durante ese tiempo los usuarios reportan tiempos de espera al registrar pagos. La tabla tiene un índice primario por id y un índice por fecha. El reporte hace SELECT cliente_id, DATE_TRUNC('month', fecha), SUM(total) FROM facturas GROUP BY 1,2 y luego un UPDATE de un campo procesado en las mismas filas. El motor usa aislamiento READ COMMITTED con MVCC y los UPDATE del reporte toman bloqueos de fila.
1. Explica por qué el reporte ralentiza los pagos aunque las lecturas no bloqueen escrituras en MVCC.
Read the worked answer
En MVCC las lecturas no bloquean escrituras, pero el reporte no es solo lectura: hace un UPDATE masivo sobre las mismas filas que consultó. Esos UPDATE toman bloqueos de fila y se mantienen hasta el commit; si el reporte procesa millones de filas en una sola transacción, los pagos que intentan actualizar filas bloqueadas esperan o fallan por timeout.
Además, una transacción larga impide que el mecanismo de limpieza de versiones (vacuum) reclame tuplas antiguas, lo que infla la tabla y degrada los planes. El GROUP BY sobre 40 M de filas sin un índice adecuado (por ejemplo, compuesto por cliente_id, fecha) fuerza un escaneo completo y ordenamientos costosos, alargando la ventana de bloqueo.
El síntoma (esperas al pagar) se explica por contención de bloqueos y por el aumento de E/S, no por bloqueo de lecturas.
2. Propón una estrategia concreta para reducir el impacto sin cambiar el resultado del reporte.
Read the worked answer
Separar lectura y escritura y acortar transacciones:
- Índice compuesto
(cliente_id, fecha)o(fecha, cliente_id)según selectividad, para que la agregación use un index-only scan y no ordene 40 M de filas. - Reporte en lotes: calcular la agregación en una tabla/materialized view y actualizar
procesadopor rangos deiden transacciones pequeñas (p. ej., 10 000 filas) con commit entre lotes, reduciendo la ventana de bloqueo. - Réplica de lectura para la fase de agregación, dejando la escritura de
procesadoen el primario en lotes. - Programar el proceso en horario de baja demanda y monitorear bloqueos; evitar una única transacción de 3 h.
El resultado del reporte no cambia; solo cambia cómo se ejecuta. Conviene medir con EXPLAIN ANALYZE antes y después para confirmar la mejora.