Operaciones masivas en lugar de bucles fila por fila: viajes de ida y vuelta, transacciones y COPY

Qué es

Un bucle fila por fila envía una sentencia por cada fila y espera la respuesta de cada una. Una operación masiva envía todo el conjunto de una vez: un INSERT ... VALUES (...), (...) de varias filas, un único UPDATE ... FROM (VALUES ...), una lectura con WHERE id = ANY(...) o un flujo COPY. La guía de PostgreSQL sobre cómo poblar una base de datos indica que, al hacer muchas inserciones, hay que desactivar el autocommit y hacer un único commit al final; que hay que usar COPY para cargar todas las filas con un solo comando, porque está optimizado para grandes cantidades de filas y supone una sobrecarga notablemente menor que una serie de INSERT; que, cuando COPY no es posible, hay que usar sentencias preparadas; y que conviene crear los índices después de cargar una tabla nueva, porque indexar datos ya existentes es más rápido que actualizar un índice fila por fila. El método executemany de psycopg ejecuta el mismo comando sobre una secuencia de parámetros, y su documentación lo describe como más eficiente que consultas separadas, aunque sugiere copy() para muchas inserciones.

Por qué importa

El coste por fila es la latencia del viaje de ida y vuelta, más el análisis y la planificación de la sentencia y, con autocommit, un commit que debe quedar persistido de forma duradera. Con un viaje de ida y vuelta de 1 ms, un millón de filas pasan al menos 1000 s (unos 17 minutos) esperando solo a la red (cálculo aritmético), antes de que la base de datos haga ningún trabajo. Las operaciones masivas eliminan ese término y permiten que la base de datos planifique una sola vez.

Cómo aplicarlo

• Lecturas: una sola consulta con id = ANY(%s) o un join en lugar de un bucle de búsquedas puntuales; paginar los resultados grandes con cursores por clave (keyset).
• Escrituras: INSERT de varias filas, executemany o COPY para las cargas; UPDATE ... FROM (VALUES ...) o UPDATE ... WHERE id = ANY(...) para actualizaciones masivas; DELETE ... WHERE id = ANY(...) para borrados.
• Agrupar en lotes: una transacción por cada pocos miles de filas mantiene los bloqueos cortos y el write-ahead log acotado, y aun así amortiza el coste del commit.
• Para una tabla nueva: cargar, después crear índices y restricciones, y luego ejecutar ANALYZE, como recomienda la guía.
• Procesar la entrada como flujo en lugar de materializar una lista de un millón de diccionarios; executemany acepta un iterable de conjuntos de parámetros y copy() escribe las filas de una en una desde cualquier iterador.

Trampas

Una sentencia masiva falla como unidad: una sola fila errónea aborta todo el lote, así que conviene decidir de antemano si, para el lote fallido, se vuelve al procesamiento fila por fila o se rechaza. Una transacción enorme mantiene los bloqueos durante toda su duración e infla el write-ahead log. Los triggers y las restricciones por fila se siguen ejecutando fila por fila. Los borrados masivos en tablas grandes bloquean otras escrituras concurrentes; conviene borrar por rangos de clave primaria en lotes. Los métodos masivos de los ORM a menudo omiten la validación del modelo, las señales y los hooks de guardado; conviene leer sus advertencias antes de confiar en ellos.