A veces, es necesario importar en las bases de datos PostgreSQL grandes cantidades de datos a través de una única operación o de un número limitado de ellas. Este proceso, conocido comúnmente como importación masiva de datos (en el cual la fuente de datos suele ser uno o más archivos de mayor tamaño) en ocasiones puede resultar inaceptablemente lento.
Existen muchas razones que explican un rendimiento tan limitado: los índices, los triggers, las claves foráneas, las claves primarias de GUID o incluso el registro WAL (Write Ahead Log), todos pueden causar retrasos.
En este artículo, analizaremos algunos consejos basados en las mejores prácticas para la importación masiva de datos en PostgreSQL. Sin embargo, pueden existir situaciones en las que ninguno de estos consejos constituya una solución efectiva. Recomendamos a los lectores que, antes de aplicar cualquier método, consideren sus ventajas y desventajas.
Consejo 1: Convertir la tabla de destino al modo UNLOGGED
En PostgreSQL 9.5 y superiores, la tabla de destino puede ser convertida primero en UNLOGGED (no registrada), y luego de nuevo en LOGGED (registrada) tras la carga de los datos:
ALTER TABLE <target table> SET UNLOGGED
<bulk data insert operations…>
ALTER TABLE <target table> LOGGED
El modo UNLOGGED impide que PostgreSQL envíe al WAL las operaciones de escritura en la tabla. Esto hace que el proceso de carga sea significativamente más rápido. Sin embargo, dado que las operaciones no son registradas, los datos no podrán ser recuperados al producirse una caída o un apagado incorrecto del servidor durante la carga. Al reiniciarse, PostgreSQL truncará automáticamente cualquier tabla UNLOGGED.
Además, puesto que las tablas no registradas no se replican en los servidores standby, las replicaciones existentes deben ser eliminadas antes de la carga y recreadas después de la misma. Dependiendo del volumen de datos en el nodo primario, así como del número de standbys, el tiempo para recrear la replicación puede ser inaceptablemente elevado por los requisitos de alta disponibilidad.
Recomendamos las siguientes prácticas para la inserción masiva de datos en las tablas no registradas:
- Realizar una copia de seguridad de la tabla y de sus datos antes de convertirla en no registrada
- Recrear cualquier replicación en los servidores standby tras la carga de los datos
- Utilizar inserciones masivas no registradas para las tablas que pueden ser fácilmente repobladas (por ejemplo, tablas de búsqueda de gran tamaño o tablas de dimensiones
Consejo 2: Eliminar y recrear los índices
Los índices existentes pueden causar retrasos considerables durante la inserción masiva de datos. Esto se debe a la necesidad de actualizar la entrada del índice correspondiente a medida que se añaden nuevas filas.
Siempre que sea posible, recomendamos eliminar los índices en la tabla de destino antes de iniciar la inserción masiva, para luego recrearlos tras la finalización de la carga. Una vez más, la creación de índices en las tablas de gran tamaño puede requerir mucho tiempo, aunque generalmente resultará más rápida que la actualización de los índices durante la carga.
DROP INDEX <index_name1>, <index_name2> … <index_name_n>
<bulk data insert operations…>
CREATE INDEX <index_name> ON <target_table>(column1, …,column n)
Puede valer la pena, justo antes de crear los índices, incrementar temporalmente el parámetro de configuración maintenance_work_mem. Aumentar la memoria de trabajo acelerará la creación de los índices.
Otra opción prudente es realizar en la misma base de datos una copia de la tabla de destino que contenga los datos y los índices existentes. La tabla recién copiada podrá luego ser probada con la inserción masiva en ambos escenarios: eliminación y recreación de los índices, o actualización dinámica de los mismos. El método que produzca un mejor rendimiento podrá luego ser utilizado para la tabla activa.
Consejo 3: Eliminar y recrear las claves foráneas
Al igual que los índices, las restricciones de las claves foráneas también pueden afectar al rendimiento de la carga masiva. Esto se debe a que, por cada clave foránea en cada fila introducida, tiene que ser comprobada la existencia de una clave primaria correspondiente. Para realizar la comprobación, PostgreSQL utiliza un trigger que funciona en segundo plano. Al cargar un gran número de filas aumentará la sobrecarga, puesto que el trigger será disparado para cada una de ellas.
A menos que las reglas de la empresa lo impidan, recomendamos que se eliminen todas las claves foráneas de la tabla de destino, que se carguen los datos en una sola transacción y que, tras ella, se vuelvan a crear las claves foráneas.
ALTER TABLE <target_table>
DROP CONSTRAINT <foreign_key_constraint>
BEGIN TRANSACTION
<bulk data insert operations…>
COMMIT
ALTER TABLE <target_table>
ADD CONSTRAINT <foreign key constraint>
FOREIGN KEY (<foreign_key_field>)
REFERENCES <parent_table>(<primary key field>)...
Nuevamente, incrementar el parámetro de configuración maintenance_work_mem puede mejorar el rendimiento a la hora de recrear las restricciones de claves foráneas.
Consejo 4: Desactivar los triggers
Si el proceso de carga implica la eliminación de registros en la tabla de destino, el uso de INSERT y DELETE para añadir o eliminar triggers también puede causar retrasos en la carga masiva de datos. La razón es que cada trigger tendrá una lógica que deberá comprobarse y operaciones que deberán completarse tras la inserción o eliminación de cada fila.
Recomendamos desactivar todos los triggers en la tabla de destino antes de realizar la carga masiva de datos y volver a activarlos una vez terminada la misma. La desactivación de todos (ALL) los triggers incluye también los triggers del sistema que realizan las comprobaciones de restricciones de claves foráneas.
ALTER TABLE <target table> DISABLE TRIGGER ALL
<bulk data insert operations…>
ALTER TABLE <target table> ENABLE TRIGGER ALL
Consejo 5: Uso del comando COPY
Recomendamos usar el comando COPY de PostgreSQL para cargar datos desde uno o más archivos. Puesto que está optimizado para cargas masivas de datos, COPY resulta más efectivo que la ejecución de un gran número de instrucciones INSERT o incluso INSERT multivalor.
COPY <target table> [( column1>, … , <column_n>)]
FROM '<file_name_and_path>'
WITH (<option1>, <option2>, … , <option_n>)
Otros beneficios del uso de COPY incluyen:
- Soporta la importación de archivos de texto y binarios
- Es de carácter transaccional
- Permite especificar la estructura de los archivos de entrada
- Puede cargar los datos de manera condicional usando la cláusula WHERE
Consejo 6: Utilizar INSERT multivalor
Ejecutar varios miles o varios cientos de miles de declaraciones INSERT puede ser una mala elección para la carga masiva de datos. Eso se debe a que cada uno de los comandos INSERT tiene que ser analizado y preparado por el optimizador de consultas, someterse a todas las comprobaciones de restricción, ejecutarse como transacción separada y ser registrado en el WAL. El uso de una sola instrucción INSERT multivalor puede evitar esta sobrecarga.
INSERT INTO <target_table> (<column1>, <column2>, …, <column_n>)
VALUES
(<value a>, <value b>, …, <value x>),
(<value 1>, <value 2>, …, <value n>),
(<value A>, <value B>, …, <value Z>),
(<value i>, <value ii>, …, <value L>),
...
Puesto que el rendimiento de los INSERT multivalor se ve afectado por los índices existentes, recomendamos eliminar estos índices antes de ejecutar el comando, y luego volver a crearlos
Otro aspecto que hay que tener en cuenta es la cantidad de memoria disponible en PostgreSQL para ejecutar los INSERT multivalor. Cuando se ejecuta un INSERT multivalor, tiene que caber en la RAM un gran número de valores de entrada, y a menos que se disponga de suficiente memoria, el proceso puede fallar.
Recomendamos configurar el parámetro effective_cache_size al 50% de la RAM total de la máquina, y el parámetro shared_buffer al 25%. Para estar seguros, deberá ejecutarse una serie de INSERT multivalor en los que cada sentencia contenga valores para 1000 filas.
Consejo 7: Ejecutar ANALYZE
Recomendamos vivamente la ejecución del comando ANALYZE en la tabla de destino inmediatamente después de una importación masiva de datos, aunque esto no supone una mejora en el rendimiento de la misma. Un gran número de nuevas filas alterará significativamente la distribución de los datos en las columnas y producirá la desactualización de las estadísticas existentes en la tabla. Si el optimizador de consultas utiliza estadísticas obsoletas, el rendimiento de las consultas puede ser inaceptablemente bajo. Ejecutar el comando ANALYZE garantizará que todas las estadísticas existentes estén actualizadas.
Consideraciones finales
Aunque quizás no se produzca todos los días una importación masiva en una base de datos, cuando se realiza influirá en el rendimiento de las consultas. Por eso es necesario reducir al mínimo el tiempo de carga. Algo que los DBAs pueden hacer para evitar imprevistos es probar las optimizaciones de carga en un entorno de desarrollo o de preproducción. Lo harán utilizando especificaciones de servidor y configuraciones PostgreSQL parecidas a dichos entornos. Puesto que cada escenario de carga de datos es diferente, lo ideal es probar diferentes métodos para encontrar el más adecuado.