Respuesta.

La archivación de datos es una de las tareas más importantes para sistemas OLTP de alta carga con tablas grandes. Históricamente, los primeros intentos de implementarlo fueron extremadamente simples: los datos se eliminaban o se copiaban en tablas separadas manualmente a través de scripts o aplicaciones. Más tarde, surgieron enfoques más sistemáticos que consideran la integridad transaccional y el mínimo impacto en el funcionamiento principal de la base de datos.

El problema aquí no solo radica en la transferencia física de la información, sino también en mantener la coherencia, minimizar bloqueos y asegurar un alto rendimiento. Los errores durante la archivación pueden llevar a la pérdida de datos, bloqueo de usuarios o un aumento significativo de la carga.

La solución radica en usar operaciones por lotes con control transaccional, así como en la formación de tablas de archivo especiales con una estructura idéntica, o la automatización a través de un programador de tareas y procedimientos.

Ejemplo de código:

-- Transferimos 5000 registros que son más antiguos de un año a la tabla de archivo
INSERT INTO archive_orders SELECT * FROM orders WHERE order_date < DATEADD(year, -1, GETDATE())
    AND id IN (SELECT TOP 5000 id FROM orders WHERE order_date < DATEADD(year, -1, GETDATE()) ORDER BY id);
DELETE FROM orders WHERE id IN (SELECT TOP 5000 id FROM orders WHERE order_date < DATEADD(year, -1, GETDATE()) ORDER BY id);

Características clave:

Transferir datos antiguos por lotes para reducir la carga.
Sincronización de las operaciones de inserción y eliminación mediante transacciones.
Programación de la automatización a través de un cronograma de trabajos y procedimientos.

Preguntas capciosas.

¿Cuál es el riesgo de realizar un DELETE masivo de registros antiguos y cómo evitarlo?

Un DELETE masivo puede conducir a la escalada de bloqueos y a un lentitud en el funcionamiento de toda la base. Esto se puede evitar realizando eliminaciones en pequeñas porciones dentro de un ciclo o usando LIMIT/TOP, si lo admite la base de datos.

WHILE 1=1
BEGIN
  DELETE TOP (1000) FROM orders WHERE order_date < '2023-01-01';
  IF @@ROWCOUNT = 0 BREAK;
END

¿Se puede utilizar TRUNCATE para eliminar datos archivados?

TRUNCATE elimina todas las filas en la tabla y no es adecuado para la limpieza condicional de ciertas filas. No activa desencadenadores, no admite WHERE y solo se utiliza para la limpieza completa, no para la archivación selectiva.

¿Cómo garantizar que la transferencia se haya realizado correctamente si la eliminación y la inserción se realizan en diferentes transacciones?

Es mejor realizar la transferencia de filas en una sola transacción: primero copiamos al archivo, luego eliminamos de la principal. De lo contrario, se puede obtener incoherencia en caso de falla entre las operaciones.

BEGIN TRANSACTION
INSERT INTO archive_orders SELECT * FROM orders WHERE ...
DELETE FROM orders WHERE ...
COMMIT

Errores comunes y anti-patrones

Eliminar enormes volúmenes de datos con una sola consulta, provocando bloqueos.
Archivación sin verificar que todas las filas se hayan transferido realmente.
Uso de TRUNCATE en lugar de DELETE, lo que lleva a una pérdida de todos los datos de la tabla.

Ejemplo de la vida real

Caso negativo

Un ingeniero ejecuta un script en un millón de registros DELETE FROM logs WHERE event_date < '2022-01-01' durante el horario laboral.

Ventajas:

El script es simple.

Desventajas:

Se bloquea toda la tabla, los usuarios no pueden trabajar, el proceso dura horas, no se puede revertir sin una copia de seguridad.

Caso positivo

Se planifica la transferencia de 5000 filas a través de un procedimiento almacenado y un trabajo programado durante la noche, registrando el éxito de cada porción.