Hoje, vi uma dúvida na lista do PostgreSQL-Br sobre como fazer um DELETE ficar mais rápido. Traduzi um artigo do Sr. Josh Berkus que trás algumas dicas de performance que podem ajudar muito. Além disso, você deve, é claro, dar uma olhada no plano de consulta do seu comando. Ver se os índices estão sendo utilizados corretamente, etc. No entanto, e se eu estiver com uma tabela realmente grande e precisar excluir uma porcentagem significativa de registros de uma tabela realmente grande (com pelo menos alguns milhões de de registros)? Um índice não resolverá o problema. Ocorre que a operação de DELETE é lenta quando utilizada para muitos registros por vários motivos:
- Precisa atualizar todos os índices da tabela;
- Precisa checar todos as chaves estrangeiras (FKs) e gatilhos apontadas para esta tabela;
- Precisa atualizar todo o mapeamento de espaço livre no disco, para permitir a alocação deste espaço novamente;
- Precisa atualizar as estatísticas da tabela.
Os mecanismos de alocação física em disco variam muito de SGDB para SGDB. O PostgreSQL utiliza o vacuum para reclamar os espaço vazio entre os dados, o Oracle utiliza uma complexa estrutura de extensões e cabeçalhos de arquivos para manter as informações sobre a alocação destas extensões. Seja como for, gerenciar os índices e o espaço em disco é um trabalho que custa caro para ser realizado.
Se você quer simplesmente zerar todos os registros de uma tabela, a solução é simples, basta um TRUNCATE. Você também pode utilizar tabelas temporárias para armazenar valores intermediários de cálculos longos e complexos. As tabelas temporárias podem durar uma sessão ou apenas uma transação. Isto elimina a necessidade de ter que excluir registros ou dar um TRUNCATE. Mas estes são casos bastante específicos.
O Fato é que as pessoas não costumam criar tabelas que se esvaziam completamente. Em rotinas de arquivamento isso é comum. Mas nem sempre podemos excluir TODOS os registros da tabela. Se estamos falando de bases com tabelas grandes (milhões de registros), a primeira solução que deve estar sempre em mente é particionar a tabela. O particionamento pode lhe permitir excluir apenas uma partição da tabela, deixando as demais intactas. Assim, o nosso amigo TRUNCATE pode ser utilizado para excluir uma parte dos registros rapidamente, mantendo os demais registros intactos.
É claro que particionar tabelas exige um campo que se possa ser utilizado para dividir a tabela em várias partições. Nem sempre o critério de particionamento vai corresponder ao critério de exclusão de registros. Você pode precisar excluir uma grande quantidade de registros que compõe uma fração de uma ou mais partições da tabela. Assim, temos que recorrer a outro expediente para contornar o problema da lentidão no DELETE. Uma opção utilizada em ambientes de grande porte é trocar o DELETE pelo INSERT. Ocorre que inserir registros é muito mais rápido que apaga-los. De maneira geral temos que:
TRUNCATE < INSERT < DELETE
Vejamos uma forma de resolver problema de excluir muitos registros utilizando o INSERT:
- Crie uma tabela auxiliar com a mesma estrutura da tabela em que os dados serão excluídos. Não crie índices, chaves primarias (PKs), estrangeiras (FKs) ou qualquer outro tipo de rescrição neste momento. Apenas os campos e seus tipos de dados. Utilize um tablespace que comporte o volume de dados que serão inseridos no próximo passo. O espaço em disco extra necessário é uma desvantagem deste tipo de procedimento, mas quem disse que performance não tem preço?
- Insira os registros que você NÃO quer apagar na tabela auxiliar. É aqui que o INSERT aparece na história. Você poderia utilizar um CREATE TABLE … AS SELECT … WHERE … para inserir apenas os registros que você não deseja excluir e ainda criar a tabela auxiliar numa tacada só. Em grande escala isto nem sempre é interessante. Apesar da tabela criada ser apropriada para a operação, realizar um INSERT em uma única tacada pode ser inadequado. Veja algumas observações no final do texto.
- Agora que você já tem os dados que você pretende preservar em outro lugar, você não precisa mais de nenhuma informação na tabela original. Você pode se livrar de todos os dados da tabela original. Você poderia dar um TRUNCATE na tabela original e depois carregar os dados de volta para ela. Mas neste caso você precisaria dar um novo INSERT, o que poderia lhe custar muito caro. Além disso, precisaríamos excluir ou desabilitar todos os índices e restrições (FKs, PKs, CHECKs, gatilhos, etc) para que o INSERT ocorra de forma mais rápida. Na verdade qualquer operação de carga, seja INSERT ou UPDATE, deve desabilitar todos os índices e restrições antes da carga. Depois da carga feita, você pode reconstruí-los ou habilita-los, assim como é feito na carga de um backup lógico. Desta forma, o que vamos fazer aqui é excluir todas as dependências externas a tabela original, como gatilhos e FKs. Você pode fazer uma varredura no dicionário de dados para lhe auxiliar nesta tarefa de forma mais cuidadosa e dinâmica. Há um exemplo aqui que mostra como fazer isso no Oracle. Com um pouco de reengenharia você pode adaptar o exemplo para a sua situação.
- Uma vez que todas as dependências para a tabela originas foram removidas, você pode enfim exclui-la com um simples DROP TABLE.
- Agora é hora de você renomear a tabela auxiliar com um ALTER TABLE para o nome da tabela original, uma vez que ela vai substituí-la.
- Com o nome correto, você pode recriar os índices, PKs, FKs, gatilhos, etc.
Deu trabalho? Quem disse que trabalhar com grandes bancos de dados seria fácil? Mesmo com todos estes passos, o processo será muito mais rápido do que realizar um simples DELETE. É claro que com um número pequeno de registros o custo de todas as operações de DDL podem superar o custo do DELETE. O limite de onde é vantajoso trocar um DELETE por todo este procedimento com INSERT deve variar para cada ambiente, mas quando você começa a trabalhar com alguns milhões de registros isto faz muito sentido. Um erro comum entre os desenvolvedores é não imaginar o sistema em produção depois de alguns anos… durante a implantação inicial o sistema se comporta com ótima velocidade. Depois que as tabelas alcançam um certo volume, os problemas de desempenho surgem e procedimentos como este mostram sua eficiência. Pense grande!
Um detalhe importante é que o INSERT também tem suas limitações. Você não deve realizar o INSERT de milhões de linhas numa única transação. Também não deve realizar um COMMIT por linha obrigando a gravação constante no log de transações. Agrupe o INSERT em transações alguns milhares registros. Um pouco de PL deve resolver seu problema. Outra questão, que fica para outro artigo, é que alguns SGDBs possuem outras formas de otimizar o INSERT como:
- Múltiplos registros por INSERT em INSERT … VALUES (…, …, …) , (…, …, …) , …
- Uso de procedimentos preparados como PREPARE … EXECUTE
- Uso do COPY ou outros comandos que substituem o INSERT em carga de dados.
