Optimización de carga masiva de datos

Imagina que tienes que cargar un millón de filas de datos. Si lo haces lento, tu servidor estará ocupado mucho tiempo, los usuarios pueden notar que la base de datos va más lenta, y lo peor — ¡tu café puede enfriarse antes de que termine el proceso! Optimizar te ayuda a evitar sobrecargar el servidor, reducir el tiempo de espera y minimizar el riesgo de errores durante la carga.

Vamos a empezar con pasos sencillos y luego pasamos a trucos más avanzados y curiosos.

Desactivar índices y triggers

Los índices y triggers son cosas geniales que hacen nuestras bases de datos inteligentes y rápidas. Pero durante la carga masiva pueden ralentizar mucho el proceso, porque el servidor intentará actualizar los índices y ejecutar los triggers para cada fila que subas.

Para quitarle ese peso al sistema temporalmente, puedes desactivarlos.

Ejemplo de cómo desactivar índices y triggers:

-- Desactivamos triggers para la tabla
ALTER TABLE students DISABLE TRIGGER ALL;

-- Cargamos los datos
COPY students FROM '/path/to/students.csv' DELIMITER ',' CSV HEADER;

-- Activamos los triggers de nuevo
ALTER TABLE students ENABLE TRIGGER ALL;

¿Cómo funciona esto?

1. Desactivamos todos los triggers temporalmente con el comando DISABLE TRIGGER ALL.
2. Después de cargar los datos, volvemos a activar los triggers con ENABLE TRIGGER ALL.

Error típico: si olvidas volver a activar los triggers, algunos procesos automáticos (por ejemplo, actualizar campos por defecto) pueden no funcionar bien. Así que no olvides dejar todo como estaba — es como salir del modo "avión" en tu móvil.

Uso de transacciones

Las transacciones te permiten cargar todos los datos como si fuera una sola operación gigante. Si algo sale mal, puedes hacer rollback y tu base de datos no se convertirá en un lío de datos incompletos.

Ejemplo de uso de transacción:

-- Empezamos la transacción
BEGIN;

-- Cargamos los datos
COPY courses FROM '/path/to/courses.csv' DELIMITER ',' CSV HEADER;

-- Confirmamos los cambios
COMMIT;

¿Por qué esto va más rápido?

Si cargas datos sin transacción, el servidor confirma los cambios después de cada fila. Con transacción, el servidor lo hace solo una vez al final, ahorrando mucho tiempo.

Desactivar la comprobación de integridad

Si no necesitas comprobar claves foráneas o restricciones de unicidad durante la carga, desactívalas. Si no, la base de datos revisará cada fila y eso la ralentiza.

Ejemplo de cómo desactivar la comprobación de integridad:

SET session_replication_role = 'replica';

-- Cargamos los datos
COPY enrollments FROM '/path/to/enrollments.csv' DELIMITER ',' CSV HEADER;

SET session_replication_role = 'origin';

session_replication_role = 'replica' desactiva la comprobación de integridad de datos (por ejemplo, unicidad y restricciones FOREIGN KEY).

Aumentar la memoria para la ejecución

Ajustar la memoria de PostgreSQL puede mejorar el rendimiento de la carga de datos. Los parámetros clave son work_mem y maintenance_work_mem.

Ejemplo de cómo aumentar la memoria:

-- Aumentamos la memoria
SET work_mem = '64MB';
SET maintenance_work_mem = '256MB';

-- Cargamos los datos
COPY teachers FROM '/path/to/teachers.csv' DELIMITER ',' CSV HEADER;

¿Qué conseguimos con esto?

• work_mem se usa para operaciones intermedias como ordenaciones o hash.
• maintenance_work_mem afecta a operaciones relacionadas con índices, como su reconstrucción.

Consejo: Ten cuidado al aumentar la memoria, sobre todo en sistemas con pocos recursos.

Preparar los datos antes de la carga

Preparar los datos puede reducir mucho el tiempo de carga. Por ejemplo, si tienes filas duplicadas, elimínalas antes para que PostgreSQL no pierda tiempo procesando datos innecesarios.

Ejemplo de limpieza de datos:

Si tienes un archivo con filas duplicadas, puedes usar Python para quitarlas.

import pandas as pd

# Cargamos el archivo CSV
data = pd.read_csv('students.csv')

# Eliminamos duplicados
data = data.drop_duplicates()

# Guardamos el CSV limpio
data.to_csv('students_clean.csv', index=False)

Particionar los datos

Si tienes un archivo enorme, divídelo en varios archivos más pequeños. Así PostgreSQL podrá procesar los datos de forma más eficiente.

Ejemplo:

Divide el archivo large_data.csv en trozos de 1000 filas usando Linux:

split -l 1000 large_data.csv chunk_

Luego cárgalos uno a uno:

COPY students FROM 'chunk_aa' DELIMITER ',' CSV HEADER;
COPY students FROM 'chunk_ab' DELIMITER ',' CSV HEADER;
-- Y así sucesivamente

Cargar datos en segundo plano

Si puedes, usa procesos en segundo plano para cargar datos y así no sobrecargar tu base de datos principal.

Herramientas como pg_cron te ayudan a programar tareas.

Ejemplo: configurar carga en segundo plano con pg_cron:

CREATE EXTENSION pg_cron;

SELECT cron.schedule('*/5 * * * *', $$COPY students FROM '/path/to/data.csv' DELIMITER ',' CSV HEADER$$);

Cada 5 minutos los datos del archivo se cargarán en la tabla.

¡Esto es solo un ejemplo, en la vida real no lo hagas así! Solo quería mostrarte que PostgreSQL es muy flexible y puedes controlar la carga de datos directamente desde scripts SQL.

Piedras en el camino

Algunas cosas a tener en cuenta:

• Si desactivas índices y triggers, ¡no olvides volver a activarlos! Si se te pasa, tendrás que arreglar errores después de la carga.
• Al aumentar los parámetros de memoria, vigila los recursos del servidor: una consulta glotona puede fundirse toda la RAM.
• Si usas transacciones, asegúrate de que el archivo de datos no tenga errores graves. Un solo error puede hacer rollback de toda la carga.

Recomendaciones para el futuro

Ahora ya sabes cómo optimizar la carga masiva de datos — desde desactivar índices hasta usar transacciones. Estas habilidades te ayudarán no solo a cargar datos más rápido, sino también a ahorrar recursos del servidor, tus nervios, tu café y a dejar a los usuarios contentos.

¡La próxima vez que tengas que trabajar con archivos de gigabytes, estarás listo!