Data archiving: movendo dados antigos sem perder acesso

1. Por que arquivar dados? Motivações e desafios

1.1. Performance em banco de produção

Tabelas que acumulam anos de registros tornam-se lentas. Índices crescem, o custo de manutenção (reindexação, atualização de estatísticas) aumenta, e consultas simples passam a exigir scans desnecessários. Um banco de produção com 500 GB de dados históricos pode ter 80% desse volume acessado raramente, mas ainda impactando o desempenho geral.

Leis como a LGPD, GDPR ou normas setoriais (ex: retenção de registros financeiros por 5 anos) exigem que os dados sejam mantidos, mas não necessariamente na tabela principal de produção. O desafio é cumprir a retenção mínima sem degradar a operação diária.

1.3. O dilema do acesso

Arquivar não pode significar "esconder". Usuários, relatórios e sistemas legados precisam eventualmente consultar dados antigos. A solução deve oferecer acesso transparente, sem exigir que o usuário saiba onde o dado está fisicamente armazenado.

2. Estratégias de particionamento como base do arquivamento

2.1. Range partitioning por data

O particionamento por intervalo de datas é a fundação do arquivamento moderno. Cada partição representa um período (mês, trimestre, ano) e pode ser gerenciada independentemente.

CREATE TABLE pedidos (
    id INT NOT NULL,
    data_pedido DATE NOT NULL,
    cliente_id INT,
    valor DECIMAL(10,2),
    PRIMARY KEY (id, data_pedido)
)
PARTITION BY RANGE (YEAR(data_pedido)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p_futuro VALUES LESS THAN MAXVALUE
);

2.2. Detach partition: movendo partições inteiras

Com o particionamento, é possível desanexar uma partição inteira e movê-la para um tablespace secundário ou até mesmo para outro banco.

-- Desanexa a partição de 2020
ALTER TABLE pedidos DETACH PARTITION p2020;

-- Move para tablespace de arquivo (se ainda no mesmo banco)
ALTER TABLE pedidos_2020 SET TABLESPACE tablespace_arquivo;

2.3. Partition pruning e consultas híbridas

O otimizador ignora automaticamente partições que não atendem ao filtro da consulta. Uma query com WHERE data_pedido >= '2023-01-01' acessará apenas as partições relevantes, ignorando as arquivadas.

3. Tabelas de arquivo separadas: o padrão "shadow table"

3.1. Criação de tabelas paralelas

Quando o particionamento não é viável (ex: banco legado), cria-se tabelas sombra com a mesma estrutura.

CREATE TABLE pedidos_2020 (LIKE pedidos INCLUDING ALL);
CREATE TABLE pedidos_2021 (LIKE pedidos INCLUDING ALL);

3.2. Migração controlada via INSERT...SELECT + DELETE em lote

A migração deve ser feita em lotes para evitar locks prolongados e estourar logs de transação.

DECLARE @batch_size INT = 1000;
DECLARE @rows_affected INT = 1;

WHILE @rows_affected > 0
BEGIN
    BEGIN TRANSACTION;

    INSERT INTO pedidos_2020 (id, data_pedido, cliente_id, valor)
    SELECT TOP (@batch_size) id, data_pedido, cliente_id, valor
    FROM pedidos
    WHERE data_pedido < '2021-01-01'
    ORDER BY id;

    SET @rows_affected = @@ROWCOUNT;

    DELETE FROM pedidos
    WHERE id IN (
        SELECT TOP (@batch_size) id
        FROM pedidos
        WHERE data_pedido < '2021-01-01'
        ORDER BY id
    );

    COMMIT TRANSACTION;

    WAITFOR DELAY '00:00:01'; -- Pausa para reduzir contenção
END;

3.3. União transparente via views

Uma view que combina produção + arquivo permite acesso unificado.

CREATE VIEW vw_pedidos AS
SELECT * FROM pedidos
UNION ALL
SELECT * FROM pedidos_2021
UNION ALL
SELECT * FROM pedidos_2020;

Para isolar completamente a carga, os dados podem ser movidos para um banco de arquivo dedicado. A comunicação entre bancos é feita via linked server (SQL Server) ou dblink (PostgreSQL/Oracle).

-- PostgreSQL: criando dblink
CREATE EXTENSION dblink;

SELECT * FROM dblink(
    'dbname=banco_arquivo host=servidor_arquivo user=archiver password=secret',
    'SELECT id, data_pedido, cliente_id, valor FROM pedidos_2020'
) AS arquivo(id INT, data_pedido DATE, cliente_id INT, valor DECIMAL(10,2));

4.2. Vantagens do isolamento

  • Backup granular: o banco de produção tem backups menores e mais rápidos.
  • Carga isolada: consultas pesadas em dados históricos não afetam o desempenho da produção.
  • Manutenção independente: reindexação e atualização de estatísticas no banco de arquivo não impactam o ambiente principal.

4.3. Cuidados necessários

Latência de rede pode tornar consultas lentas. Para dados muito acessados, considere cache local ou replicação parcial. Segurança deve ser reforçada com criptografia na conexão e autenticação forte entre servidores.

5. Políticas de retenção e janelas de arquivamento

5.1. Definindo critérios

A política deve considerar múltiplos fatores: data de criação, status do registro (ex: pedido finalizado vs. em aberto), última atualização ou combinações lógicas.

-- Critério composto: pedidos finalizados há mais de 2 anos
WHERE data_pedido < DATEADD(YEAR, -2, GETDATE())
  AND status = 'FINALIZADO'

5.2. Job agendado com checkpoint

Um job diário (via cron, SQL Agent ou pg_cron) executa o processo com logs detalhados e checkpoint para permitir retomada.

-- Pseudocódigo do job
DECLARE @data_corte DATE = '2021-01-01';
DECLARE @lote INT = 5000;
DECLARE @total_movido INT = 0;

WHILE 1=1
BEGIN
    EXEC sp_arquivar_pedidos @data_corte, @lote, @total_movido OUTPUT;
    IF @total_movido = 0 BREAK;
    INSERT INTO log_arquivamento (data_execucao, registros, status)
    VALUES (GETDATE(), @total_movido, 'SUCESSO');
END;

5.3. Estratégia de apagão

Para evitar inconsistências, as escritas na tabela de origem devem ser pausadas durante a migração ou usar replicação lógica que capture mudanças posteriores ao corte.

6. Mantendo a integridade referencial entre dados ativos e arquivados

6.1. Chaves estrangeiras quebradas

Registros arquivados podem referenciar dados que ainda estão na produção. A solução é manter chaves estrangeiras apenas dentro de cada domínio (ativo ou arquivo) e tratar a integridade via aplicação ou triggers.

6.2. Tabelas de lookup imutáveis

Tabelas de domínio pequenas (ex: categorias, status_pedido) devem ser replicadas no banco de arquivo, pois raramente mudam.

-- Replicação simples
INSERT INTO banco_arquivo.dbo.categorias
SELECT * FROM producao.dbo.categorias;

6.3. Triggers de auditoria

Cada movimentação deve ser registrada para rastreabilidade.

CREATE TRIGGER trg_audita_arquivamento
ON pedidos
AFTER DELETE
AS
INSERT INTO log_arquivamento (id_pedido, data_arquivamento, usuario)
SELECT deleted.id, GETDATE(), SYSTEM_USER
FROM deleted;

7. Acesso transparente: views, stored procedures e APIs unificadas

7.1. View federada com UNION ALL

A view mais simples combina todas as fontes. Para performance, use UNION ALL (sem remoção de duplicatas) e garanta índices nas colunas de filtro.

7.2. Stored procedure com parâmetro de controle

CREATE PROCEDURE sp_consulta_pedidos
    @cliente_id INT,
    @incluir_arquivados BIT = 0
AS
BEGIN
    SELECT * FROM pedidos WHERE cliente_id = @cliente_id
    UNION ALL
    SELECT * FROM pedidos_arquivo WHERE cliente_id = @cliente_id AND @incluir_arquivados = 1;
END;

7.3. Camada de aplicação inteligente

A aplicação pode implementar um roteamento: primeiro consulta a produção; se não encontrar, consulta o arquivo. Isso reduz a carga no banco de arquivo para consultas comuns.

8. Monitoramento, rollback e testes do processo de arquivamento

8.1. Métricas essenciais

  • Tempo de execução por lote
  • Volume movido (registros e MB)
  • Taxa de erro (deadlocks, timeouts)
  • Latência de consulta antes e depois do arquivamento

8.2. Período de quarentena

Nunca delete os dados originais imediatamente. Mantenha um período de quarentena (ex: 30 dias) onde os dados ainda existem na produção, mas marcados como "arquivados". Isso permite reversão rápida.

-- Marcar como arquivado em vez de deletar
UPDATE pedidos SET status_arquivo = 'ARQUIVADO', data_arquivamento = GETDATE()
WHERE data_pedido < '2021-01-01';

8.3. Testes em staging

Simule o arquivamento com uma massa de dados realista (pelo menos 10% do volume de produção). Valide:

  • Consultas que acessam dados ativos continuam rápidas
  • Consultas que acessam dados arquivados retornam resultados corretos
  • O processo de rollback funciona sem perda de dados

Referências