Fala dataholics, no post de hoje falaremos sobre a performance de um DELETE SEM WHERE vs TRUNCATE TABLE, qual é o mais rápido na sua opinião?
Bom para quem vem do mundo de banco de dados talvez já tenha visto algum tema sobre isso em algum momento, o TRUNCATE table é considerado um comando DDL (Data Definition Language) e o DELETE é um comando do tipo DML(Data Manipulation Language) na maioria das engines de SGBDs tradicionais como SQL Server e Oracle o Truncate Table é muito mais eficiente internamente, pois, as engines, em geral, desalocam todo o bloco de dados baseados em suas páginas de controle ao invés de deletar linha a linha e gerar log de tudo, igual acontece em um comando DML.
Se você é do Team SQL Server escrevi sobre como funciona as paginas de controle há muito tempo:
Obs: Sim, blog do Jamal era eu rs.
Bom, mas se nos SGBDs tradicionais o TRUNCATE é mais rápido, será que no Databricks com DELTA LAKE é o mesmo?
Vamos ao cenário.
Vamos usar uma tabela com 53 mil arquivos de dados (Parquets) e 7707593536192 bytes.
E quanto é esse número? Sim, são 7 Terabytes de dados em uma tabela, não é a maior que já atuei, mas é um tamanho relevante.
Para efeitos de curiosidade essa tabela tem seus míseros 127 Bilhões de registros.
E agora vamos às comparações:
DELETE SEM WHERE 19 segundos, bom se você comparar com qualquer banco de dados isso é extremamente rápido (muito mesmo)! Em um SQL Server ou Oracle da vida isso possivelmente nem rodaria pela quantidade de log que seria gerado, no mínimo seu disco de log iria pro espaço.
E o TRUNCATE TABLE? Também 20 segundos. WTF!
Reginaldo, por tudo que estudei e sabia, o Truncate table deveria ser mais rápido!
Sim, resumindo é a mesma coisa, a mesma performance entre um TRUNCATE TABLE vs DELETE SEM WHERE em uma tabela DELTA.
WHY? Ou como diriam os mineiros, UAI?
Resumirei para não estender tanto, olha só o log das duas alterações, praticamente o mesmo tamanho!
Tomou seu café já? Olha o SIZE desse log, são 23 MB de logs.
Qual motivo de um log tão grande? Vamos comparar, olhando o log do DELETE.
Linha 6 - Marcando operação de DELETE
Linha 7/8 - Mostrando o predicado do DELETE SEM WHERE, tudo com TRUE que é igual 1 = 1.
Linha 18 - numRemovedFiles 53 mil arquivos removidos, eis o motivo do tempo de execução e do tamanho do arquivo de log, esse arquivo tem um bloco de REMOVE (veja abaixo) para cada arquivo, ou seja, 53 mil blocos.
Obs: Alguns dados foram mascarados.
Esse é um bloco de REMOVE do arquivo, informando que aquele arquivo não faz mais parte da tabela e agora pode ser visualizado com TIME TRAVEL, nesse arquivo temos 53 mil blocos igual a esse, 1 para cada arquivo PARQUET da tabela.
E o TRUNCATE TABLE?
Linha 6 - Marcando operação de TRUNCATE
Linha 16 - numRemovedFiles 53 mil arquivos removidos.
Ou seja, ambos comandos fazem a mesma coisa, descartam todos os arquivos da versão atual da tabela e inserem uma informação que aquele arquivo não é mais válido, o maior trabalho dessa operação é registrar ela no LOG, pois, nesse caso não há necessidade escrever novos arquivos PARQUETS.
Olhando como ficou o describe detail, 0 arquivos, mas lembrando que os 53 mil arquivos ficam disponível para TIME TRAVEL ate ocorrer um VACUUM, isso significa que seu storage ainda tem 7 TB de dados.
Bom é isso, espero que tenha ficado claro, no Databricks não tem diferença de performance entre DELETE SEM WHERE vs TRUNCATE e claro, o truncate não tem opção de filtro, é sempre tudo ou nada, o delete é geralmente usado pontualmente e em poucos registros.
Fique bem e até a próxima.
Comentarios