Consulta de conciliación lenta, deadlocks nocturnos y una retención que infla la tabla

Diagnóstico. La consulta pide ORDER BY fecha_operacion DESC LIMIT 100. El planificador ve en (fecha_operacion, id_comercio) un índice que ya entrega el orden y evita el sort, así que lo elige y recorre hacia atrás el rango de siete días aplicando id_comercio = ? como filtro, no como condición de búsqueda. Con 650 mil filas/día, la ventana es de ~4.5 millones de entradas de índice; si el comercio es de bajo volumen, el recorrido debe avanzar casi toda la ventana antes de juntar 100 filas. De ahí los 18 s: no es falta de índice, es un índice cuyo primer nivel no es selectivo para este predicado.

Solución. Crear (id_comercio, fecha_operacion DESC) INCLUDE (monto, estado) -igualdad primero, rango después- de modo que el mismo índice sirva para filtrar y para ordenar, y permita un index-only scan. Selectividad esperada: con ~5,000 comercios, un comercio aporta ~130 filas/día y ~910 en la semana; el recorrido se reduce de millones a cientos de entradas.

Complementos: eliminar el índice redundante por id_comercio (el nuevo prefijo lo cubre), verificar con EXPLAIN (ANALYZE, BUFFERS) que desaparecen las lecturas de heap innecesarias, y revisar estadísticas con ANALYZE. Añadir (fecha_operacion, id_comercio, estado) no ayuda: repite el error de poner la columna de rango al frente.

Causa. Es un ciclo de espera clásico por orden de bloqueo inconsistente. El proceso A bloquea la fila 1 (orden por id_pago) y luego pide la fila 2; el proceso B bloquea la fila 2 (orden por id_comercio) y pide la fila 1. PostgreSQL detecta el ciclo tras deadlock_timeout (1 s por defecto) y aborta una de las transacciones con 40P01. No es un problema de rendimiento ni de índices: es de protocolo de acceso.

Por qué subir deadlock_timeout es mala idea: solo retrasa la detección; las transacciones permanecen bloqueadas más tiempo, se retienen locks y versiones antiguas de filas, y el vacuum no puede limpiar. El deadlock seguirá ocurriendo.

Corrección correcta:

1. Imponer un orden global de bloqueo: ambos procesos deben ordenar por la misma clave antes de actualizar (SELECT ... WHERE id_pago = ANY(...) ORDER BY id_pago FOR UPDATE), o usar UPDATE ... FROM (SELECT ... ORDER BY id_pago) s.
2. Acortar transacciones: no mezclar cálculo y actualización; commit lo antes posible.
3. Si el conflicto es por comercio, serializar por comercio con pg_advisory_xact_lock(hashtext(id_comercio)) en lugar de bloquear filas dispersas.
4. Añadir reintento con backoff exponencial ante 40P01 y 40001, porque el reintento es seguro y barato.

Verificación: monitorear pg_stat_database.deadlocks y los logs; el objetivo es cero, no «menos que antes».

Lectura de los números. Un DELETE de 30 millones de filas mensuales no libera espacio: marca tuplas muertas que autovacuum debe recorrer. Con 22% de tuplas muertas, el autovacuum por defecto (scale_factor = 0.2, cost_limit conservador) va justo por detrás del ritmo de borrado en una tabla de 480 millones de filas; el resultado es bloat de tabla e índices, más páginas leídas por consulta y vacuum más lento, en un círculo vicioso.

Cambio de diseño: particionar por rango sobre fecha_operacion, con particiones mensuales. La retención pasa a ser DETACH PARTITION + DROP, que es O(1) en metadatos y no genera bloat; además, la consulta de siete días toca una o dos particiones y mejora el pruning. La clave de partición ya está en el predicado, requisito para el partition pruning.

Ajustes inmediatos mientras se migra: parámetros de autovacuum por tabla (autovacuum_vacuum_scale_factor = 0.02, autovacuum_vacuum_cost_limit más alto), borrados por lotes con commit intermedio en lugar de un DELETE masivo, y monitorizar n_dead_tup y last_autovacuum. Tras el DROP de particiones conviene reconstruir o reindexar para recuperar espacio. Orden de ejecución: índice correcto (impacto inmediato en latencia) → orden de bloqueo (elimina errores) → particionado (sostenibilidad).

Prioridad por impacto y reversibilidad.

1. Orden de bloqueo y reintentos: cambio de código, reversible con un despliegue, elimina errores visibles al usuario. Va primero, sin ventana especial.
2. Índice nuevo: CREATE INDEX CONCURRENTLY evita bloquear escrituras; se puede crear en horario de baja actividad y validar con EXPLAIN antes de retirar el índice viejo. Nunca crear y borrar el mismo día: se necesita evidencia de que el plan mejora.
3. Particionado: es migratorio, requiere tabla nueva, copia por lotes, doble escritura o ventana de corte, y validación de conteos. No va un viernes ni junto con lo demás.

Por qué no todo a la vez: si algo falla, no se sabría qué cambio lo causó, y el rollback de una migración de particiones no es un git revert. Un cambio por ventana, con métrica de éxito definida (latencia p95 de la consulta <500 ms, deadlocks = 0, tuplas muertas <5%) y un plan de reversa escrito.

Riesgo específico del índice concurrente: puede fallar por transacciones largas y dejar un índice inválido; hay que verificar pg_index.indisvalid y limpiar. Riesgo del particionado: consultas que no incluyan la clave de partición recorrerán todas las particiones, así que hay que auditar el resto del tráfico antes de cortar.