Eu tenho uma tabela de transações armazenando grande quantidade de dados de transações. Apenas os dados mais recentes de um mês precisam estar disponÃveis para uso transacional em tempo real. Dados menos recentes só podem ser usados ​​para geração de relatórios ou análise offline.
Qual é a melhor prática para lidar com esses dados de transação?
Devo criar uma tabela de histórico com as mesmas colunas e mover dados com idade superior a um mês da tabela de transações para a tabela de histórico com um trabalho em lote? Devo criar uma tabela de histórico por ano? Ou apenas usar uma única tabela de histórico para hospedar todos os dados anteriores?
Ou devo usar o particionamento de banco de dados em vez de tabelas de histórico?
Ou devo criar um data warehouse e mover os dados para ele?
Você deve especificar qual sistema de banco de dados e versão você está usando, pois isso afetará suas escolhas reais disponÃveis para como você estrutura e gerencia os dados.
Você mencionou que grande em seu contexto significa 100.000 novos registros por dia, então vamos planejar algo uma ordem de magnitude maior, 1 milhão de registros por dia. Em um mês são 30 milhões de novos registros, em um ano são ~360 milhões de registros. Embora 360 milhões de registros estejam começando a ficar um pouco pesados, não é de forma alguma algo incontrolável ou que precise de tratamento especial na maioria dos sistemas de banco de dados modernos.
Vai depender das consultas reais que você está executando, a simultaneidade de seu servidor com gravações versus leituras e o tempo de execução aceitável para relatar os dados, mas 360 milhões de registros em um ano não devem assustá-lo com o RDBMS moderno . Eu gerenciei tabelas com 10 bilhões de registros nelas e relatei diretamente delas, geralmente consultando cerca de 1 milhão de registros por vez em menos de 10 segundos em hardware bastante modesto.
Se você deseja reduzir a simultaneidade de relatórios, pode procurar arquivar os dados mais antigos (ou seja, qualquer coisa mais antiga que o último mês de dados) ou migrá-los para um data warehouse. Dependendo do tipo de consulta que está sendo feita e do sistema de banco de dados que você está usando, você pode ter recursos disponÃveis como indexação columnstore e Ãndices filtrados que podem ajudá-lo a melhorar os relatórios diretamente da própria tabela sem a necessidade de arquivar.
Arquitetar corretamente seu esquema e indexar seus dados será o caminho mais longo para melhorar seus recursos de relatório.
Finalmente, como Jonathan Fite mencionou, o particionamento não melhorará diretamente seu desempenho de consulta, mas pode ser usado para melhorar seu gerenciamento da tabela, pois você pode dividir seus dados de maneira inteligente em partições que permitem gerenciar os dados menos ativos enquanto os mais ativos partições estão em uso. Você verá melhorias na simultaneidade reduzida quando precisar fazer coisas como arquivar seus dados (por meio de troca de partição) ou manutenção de Ãndice.
Se sua estimativa de 100.000 linhas por dia estiver correta e você não prever nenhum crescimento significativo nos próximos anos na realidade, então você está olhando apenas para 36 milhões de linhas por ano. Eu nem pensaria em particionar ou arquivar nesse ponto. A indexação regular em um esquema bem arquitetado deve fornecer tempos de execução de consulta muito rápidos. Mas, pessoalmente, gosto de planejar uma magnitude maior de dados do que realmente espero, como acima.