Por que o DELETE em massa no PostgreSQL é tão lento é quando usar DROP TABLE

O que é o problema do DELETE em massa no Postgres

Toda vez que você roda um DELETE grande no PostgreSQL, o banco não apaga as linhas de verdade. Ele apenas marca cada uma como morta. Quem decide o destino real dessas linhas é o autovacuum, que passa depois recolhendo o lixo.

Isso parece detalhe interno, mas tem consequencia direta: um DELETE de dez milhoes de linhas gera dez milhoes de marcacoes, dez milhoes de entradas no WAL é uma fila enorme para o vacuum digerir. Enquanto isso, a tabela continua gigante no disco é os índices ficam inchados.

Engenheiros do PlanetScale resumiram bem em um post recente: o único DELETE que escala de verdade no Postgres é o DROP TABLE. Provocacao? Sim. Mas com fundo serio. Operações destrutivas em massa precisam de outra abordagem, não do verbo SQL mais obvio.

Como funciona o MVCC é por que ele complica DELETE

O Postgres usa MVCC, sigla para Multi Version Concurrency Control. Toda linha tem duas marcas internas: a transação que criou é a transação que apagou. Quando você roda DELETE, o banco apenas escreve a marca de apagamento. A linha continua la, ocupando espaco.

Esse modelo evita travas pesadas em leitura. Outras conexões podem continuar lendo a versão antiga da linha enquanto sua transação decide se vai commitar ou não. É o que permite o Postgres rodar OLTP com milhares de conexões sem virar um show de deadlocks.

O preco vem depois. Linhas mortas precisam ser limpas pelo autovacuum, que é um processo em segundo plano. Se você gera mais lixo do que ele consegue varrer, a tabela cresce, os índices crescem, o planejador comeca a errar estimativas é tudo desacelera.

Principais sintomas de DELETE em massa mal feito

O time de banco geralmente percebe o estrago quando já está em chamas. Os sinais clássicos sao bem conhecidos.

• Tabela inchada: tamanho em disco continua igual ou maior depois do DELETE, porque o espaco só volta após vacuum full ou repack.
• Índices bloated: consultas que usavam índice ficam mais lentas, mesmo com volume menor de dados validos.
• WAL explode: a replicação para replicas fica atrasada é o disco do primario enche.
• Autovacuum em loop: o worker fica horas em uma única tabela é bloqueia outros workers de tabelas igualmente cheias.
• Locks longos: outras transações esperam, timeouts comecam a estourar na aplicação.

Se você já viu uma dessas em produção, já sabe que não basta rodar VACUUM é torcer. Precisa de plano.

Como comecar: alternativas ao DELETE direto

Antes de pensar em DROP TABLE, vale conhecer as opções em ordem crescente de agressividade. Escolha conforme o quanto você pode pausar a tabela.

Passo 1. Avalie se da para usar TRUNCATE. Esse comando esvazia a tabela inteira em milissegundos, sem gerar linhas mortas. Ele exige lock exclusivo, mas a operação em si é quase instantanea.

Passo 2. Se precisa manter parte dos dados, considere DELETE em lotes. Apague de mil em mil ou de dez mil em dez mil, com pausa entre os lotes para o autovacuum acompanhar.

Passo 3. Para volumes muito grandes, use particionamento. Tabelas particionadas por data permitem trocar DELETE por DROP de uma particao inteira, que é barato.

Passo 4. Em último caso, copie o que precisa manter para uma tabela nova, faca DROP da antiga é renomeie a nova. É o famoso swap, é é exatamente o que o post do PlanetScale defende.

Exemplo prático: limpando uma tabela de logs

Imagine uma tabela events com cem milhoes de linhas é você quer manter apenas os últimos trinta dias. Rodar DELETE direto vai travar a aplicação por horas. O caminho mais seguro tem tres etapas.

Primeiro, crie uma tabela espelho com a mesma estrutura: CREATE TABLE events_new (LIKE events INCLUDING ALL);. Depois, copie apenas o que importa: INSERT INTO events_new SELECT * FROM events WHERE created_at > now() - interval '30 days';. Por fim, faca o swap dentro de uma transação curta: BEGIN; ALTER TABLE events RENAME TO events_old; ALTER TABLE events_new RENAME TO events; COMMIT; é em seguida DROP TABLE events_old;.

O DROP final é instantaneo. O Postgres apenas remove o arquivo do disco. Sem MVCC, sem vacuum, sem WAL gigantesco. É o ganho real que o titulo do artigo provoca.

Comparacao com alternativas

Cada estrategia tem seu lugar. A escolha depende do quanto você pode bloquear a tabela é do volume de dados a remover.

• DELETE direto: bom para poucas linhas, até alguns milhares. Não escala alem disso.
• DELETE em lotes com commit: aceitavel para alguns milhoes, mas leva horas é gera muito WAL.
• TRUNCATE: ideal quando você esvazia tudo. Reseta sequências se usar RESTART IDENTITY.
• Particionamento + DROP de particao: melhor opção para dados temporais. Operação trivial é barata.
• Swap de tabelas (DROP TABLE): melhor opção quando você quer manter uma fracao pequena dos dados.

O diferencial do DROP é que ele não escreve linha morta nenhuma. O Postgres simplesmente esquece a tabela. Por isso ela escala onde DELETE não escala.

Pontos positivos é limitacoes

A estrategia de DROP TABLE para limpeza em massa tem virtudes claras. Ela é rapida, previsivel é não deixa lixo para o autovacuum. Em sistemas com janela de manutenção, ela resolve em segundos o que DELETE faria em horas.

Mas tem custos. Você perde permissões, índices customizados, triggers é foreign keys da tabela original, a menos que recrie tudo. Em sistemas com muitas dependências, esse swap exige cuidado.

Particionamento também tem seu preco. Adicionar particionamento em tabela existente não é trivial, é algumas consultas precisam ser reescritas para usar o predicado correto. Vale o esforco apenas se a tabela cresce sem parar.

Casos de uso reais

Veja onde cada abordagem brilha.

Time de observabilidade com tabela de logs que cresce gigabytes por dia: use particionamento por dia ou semana. Limpeza vira um DROP por particao antiga, agendado por cron.

SaaS com tenants que vez ou outra encerra contas é quer apagar tudo daquele cliente: particione por tenant ou faca soft delete é archive periodico para outra tabela.

Sistema legado com tabela inchada é janela de manutenção curta: faca swap. Crie nova, copie o que importa, renomeie, dropa antiga.

Job batch noturno que limpa carrinhos abandonados: DELETE em lotes pequenos com sleep entre eles, dentro da janela de baixo trafego. Solução pragmatica.

Dicas é boas práticas

Quem opera Postgres em produção aprende cedo alguns macetes que evitam dor.

• Monitore bloat: use extensões como pgstattuple ou ferramentas como pgwatch para ver inchacao real.
• Ajuste autovacuum: em tabelas grandes, baixe o autovacuum_vacuum_scale_factor para 0.05 ou menos, é suba o autovacuum_vacuum_cost_limit.
• Use pg_repack: para tabelas já inchadas sem janela de manutenção, essa extensão reescreve sem lock exclusivo prolongado.
• Cuidado com FOREIGN KEY: DELETE em tabela pai com cascade pode disparar DELETE gigante em tabela filha sem você perceber.
• Teste em copia: antes de rodar qualquer estrategia em produção, faca em uma copia da tabela é meca tempo, WAL gerado é impacto em índices.

O erro mais comum de quem comeca é achar que DELETE é barato. Não é. Cada linha apagada é uma divida técnica que o autovacuum vai cobrar mais tarde.

Vale a pena repensar seus DELETE em massa?

Se você mantem um sistema com tabelas que crescem o tempo todo, sim. Vale repensar agora, antes de virar incidente. DELETE em massa direto é bomba relogio em qualquer banco MVCC, não apenas no Postgres.

Comece mapeando quais tabelas tem retencao definida, ou seja, dados que vao ser apagados em algum momento. Para essas, particionamento por data é quase sempre o melhor caminho. Para tabelas que você limpa inteiras de tempos em tempos, TRUNCATE resolve. Para limpezas pontuais grandes, o swap com DROP TABLE entrega o resultado mais rapido.

O próximo passo é simples: pegue sua maior tabela é descubra quantas linhas estao mortas hoje. Se o número assustar, você já sabe por onde comecar a otimização.