Limpando dados antigos de um banco de dados multi-tenant
Tags: sql, database, orm
Published: 2023-01-06 20:17:18

Se você está prestes a iniciar uma aplicação, pense bem antes de escolher um banco de dados multi-tenant. Se essa for sua escolha, considere colocar uma coluna em TODAS as tabelas indicando a qual tenant o registro pertence.

Caso você já tenha uma aplicação há anos funcionando no modelo multi-tenant e chegou a hora de apagar dados de ex-clientes antigos, saiba que esse processo pode ser demorado e complexo.

## Um breve histórico
A primeira tentativa de apagar os registros de clientes antigos foi usar um script em Python usando o ORM do Django. Essa solução se mostrou muito lenta devido ao overhead do ORM. Verificamos que essa tentativa iria demorar muito tempo, pois a cada dia entravam mais registros no sistema do que conseguíamos apagar.

A segunda tentativa foi usar PL/SQL para realizar uma espécie de delete cascade. Esse script é de domínio público e tem vários exemplos na internet. No entanto, esse script não é muito eficiente para fazer a deleção de grande volume de dados porque ele tem que montar os joins toda vez que vai deletar um registro (para poder encontrar o registro folha), além de deletar registro por registro. Verificamos que essa tentativa demoraria 50 anos para terminar de executar.

## Solução
A solução que encontramos para resolver esse problema foi também usar PL/SQL, porém em vez de montar o relacionamento a cada registro que seria deletado, faríamos o cache de todos os registros que precisavam ser apagados e depois apagariamos em lote baseado no cache. Isso funcionaria desde que novos registros não estejam sendo criados como dependentes dos registros que serão apagados.

Como haviam muitas tabelas, optamos por proceder com uma solução data driven. Isso quer dizer criar uma tabela de controle com todas as tabelas que precisam ser apagadas. Assim, o script do PL/SQL usa essa tabela para controlar a ordem do cache e a ordem da deleção e também serve para saber em qual status está o processo em cada tabela.

## Estrutura de Dados
O primeiro desafio foi montar corretamente essa tabela de controle. Inicialmente, pensamos que precisávamos mapear apenas as tabelas e marcar qual era a relação de dependência entre elas.

Analisando o problema mais profundamente, começou a ficar claro que uma mesma tabela poderia aparecer mais de uma vez, já que ela pode estar relacionada mais de uma vez. Portanto, o que realmente precisávamos relacionar nessa tabela de controle eram as contraints de chave estrangeira, com as respectivas informações sobre as tabelas que fazem parte dessa chave.

Apesar de a tabela de controle ser formalmente uma tabela, devido a termos uma coluna que relaciona a chave estrangeira com a chave estrangeira mãe, virtualmente estamos montando um estrutura de "árvore". Não é exatamente uma árvore por dois motivos: a) podemos ter dependência circular; b) podemos ter vários galhos repetidos.

O problema de dependência circular não é possível de solucionar, portanto é necessário detectar quando ocorre dependência circular e ignorar. Esses casos foram corrigidos manualmente com UPDATE para remover a referência que pudesse causar uma dependência circular.

O problema de ter vários galhos repetidos não prejudica a execução da deleção. Ele ocorre porque nossa árvore na verdade é uma DAG.

## Algoritmo
Para fazer o cacheamento dos dados, temos que começar pela "raiz" da "árvore" e ir em direção às "folhas". Isso é necessário porque vamos usar a tabela mãe para encontrar os registros da tabela filha.

Para fazer a deleção, a ordem tem que ser inversa. Temos que começar pelas folhas e ir em direção a raiz. Isso é necessário porque se deletarmos os registros pais antes dos filhos, vamos ter erro de constraint de chave estrangeira. Para evitar outros problemas, foi bastante útil criar uma coluna dizendo qual era a "altura" do nó na "árvore", para decidir qual filho começar.

## Considerações práticas
Para desenvolver e acompanhar a execução do script no ambiente de produção, foi muito importante criar um log. Como estamos em um banco de dados, o log foi criado em uma tabela.

Como o script iria rodar todos os dias no horário da madrugada, foi necessário criar uma janela de execução controlada. Por exemplo, em vez de executar a deleção de todos os registros que estavam na tabela de cache, se limitava apenas aos registros que estavam em um determinado intervalo e depois esse intervalo iria sendo convolucionado.

ATENÇÃO: essa técnica de convolução é chamada de watermark e deve ser utilizada com muita cautela. Ela só funciona em registros que são antigos (que era o objetivo do script), pois registros recentes podem ainda não ter sido salvos no banco de dados, mas já terem consumido a sequence.

Como o script iria apagar dados de produção, foi necessário também fazer um sistema de backup. Como já tínhamos um cache para acelerar o processo, modificamos o próprio cache para armazenar todas as colunas da tabela.

Também foi necessário criar um script de rollback, que deveria desfazer o que o script de deleção fazia.

## Resultado
Com esse algorítmo de primeiro planejar tudo que tem que ser excluído e depois finalmente fazer a exclusão conseguimos baixar o tempo de execução de 50 anos para 80 dias a exclusão de 4 bilhões de registros em mais de 100 tabelas. O tempo de desenvolvimento e testes do algoritmo em PL/SQL foi de 5 meses.