Melhor maneira de excluir milhões de linhas por ID

Eu preciso excluir cerca de 2 milhões de linhas do meu database PG. Eu tenho uma lista de IDs que preciso excluir. No entanto, qualquer maneira que eu tente fazer isso é levar dias.

Eu tentei colocá-los em uma tabela e fazê-lo em lotes de 100. 4 dias depois, isso ainda está sendo executado com apenas 297268 linhas excluídas. (Eu tive que selecionar 100 id’s de uma tabela de IDs, deletar onde NA lista, deletar da tabela ids os 100 que eu selecionei).

Eu tentei:

DELETE FROM tbl WHERE id IN (select * from ids) 

Isso está demorando para sempre também. Difícil avaliar por quanto tempo, já que não consigo ver o progresso até o final, mas a consulta ainda estava em andamento após dois dias.

Apenas procurando a maneira mais eficaz de excluir de uma tabela quando eu sei que a ID específica é excluída, e há milhões de IDs.

Tudo depende …

  • Exclua todos os índices (exceto aquele no ID que você precisa para a exclusão)
    Recrie-os depois (= muito mais rápido que atualizações incrementais de índices)

  • Verifique se você tem gatilhos que podem ser excluídos / desativados com segurança

  • As foreign keys fazem referência à sua mesa? Eles podem ser excluídos? Temporariamente excluído?

  • Dependendo das suas configurações de autovacuum, pode ajudar a executar o comando VACUUM ANALYZE antes da operação.

  • Supondo que não haja access de escrita concorrente a tabelas envolvidas, ou você pode ter que bloquear tabelas exclusivamente ou esta rota pode não ser para você.

  • Alguns dos pontos listados no capítulo relacionado do manual Preenchendo um database também podem ser úteis, dependendo da sua configuração.

  • Se você excluir grandes partes da tabela e o restante couber na RAM, a maneira mais rápida e fácil seria esta:

 SET temp_buffers = '1000MB'; -- or whatever you can spare temporarily CREATE TEMP TABLE tmp AS SELECT t.* FROM tbl t LEFT JOIN del_list d USING (id) WHERE d.id IS NULL; -- copy surviving rows into temporary table TRUNCATE tbl; -- empty table - truncate is very fast for big tables INSERT INTO tbl SELECT * FROM tmp; -- insert back surviving rows. 

Dessa forma, você não precisa recriar vistas, foreign keys ou outros objects dependentes. Leia sobre a configuração temp_buffers no manual . Esse método é rápido, desde que a tabela caiba na memory ou pelo menos na maior parte. Esteja ciente de que você pode perder dados se o seu servidor falhar no meio desta operação. Você pode envolvê-lo em uma transação para torná-lo mais seguro.

Execute ANALYZE depois. Ou VACUUM ANALYZE se você não tiver percorrido a rota truncada, ou VACUUM FULL ANALYZE se quiser colocá-lo no tamanho mínimo. Para tabelas grandes, considere as alternativas CLUSTER / pg_repack :

  • Otimizar o intervalo de consulta do registro de data e hora do Postgres

Para tabelas pequenas, um simples DELETE vez de TRUNCATE é geralmente mais rápido:

 DELETE FROM tbl t USING del_list d WHERE t.id = d.id; 

Leia a seção de annotations para TRUNCATE no manual . Em particular (como Pedro também apontou em seu comentário ):

TRUNCATE não pode ser usado em uma tabela que tenha referências de chave estrangeira de outras tabelas, a menos que todas essas tabelas também sejam truncadas no mesmo comando. […]

E:

TRUNCATE não triggersrá nenhum gatilho ON DELETE que possa existir para as tabelas.

Sabemos que o desempenho de atualização / exclusão do PostgreSQL não é tão poderoso quanto o Oracle. Quando precisamos excluir milhões ou dezenas de milhões de linhas, é realmente difícil e leva muito tempo.

No entanto, ainda podemos fazer isso em dbs de produção. A ideia a seguir é minha:

Primeiro, devemos criar uma tabela de log com 2 colunas – id & flag ( id refere-se ao id que você deseja excluir; flag pode ser Y ou null , com Y significando que o registro foi excluído com sucesso).

Mais tarde, criamos uma function. Fazemos a tarefa de exclusão a cada 10.000 linhas. Você pode ver mais detalhes no meu blog . Embora seja em chinês, você ainda pode obter as informações desejadas do código SQL.

Certifique-se de que a coluna id de ambas as tabelas seja indexada, pois ela será executada mais rapidamente.

Você pode tentar copiar todos os dados da tabela, exceto os IDs que deseja excluir em uma nova tabela, renomeando e depois trocando as tabelas (desde que você tenha resources suficientes para fazê-lo).

Este não é um conselho de especialistas.

Duas respostas possíveis:

  1. Sua tabela pode ter muitas restrições ou triggersdores anexados quando você tenta excluir um registro. Ele incorrerá em muitos ciclos do processador e na verificação de outras tabelas.

  2. Você pode precisar colocar essa declaração dentro de uma transação.

A maneira mais fácil de fazer isso seria eliminar todas as suas restrições e, em seguida, fazer a exclusão.

Primeiro, verifique se você tem um índice nos campos de ID, na tabela que deseja excluir e na tabela que está usando para IDs de exclusão.

100 de cada vez parece muito pequeno. Tente 1000 ou 10000.

Não há necessidade de excluir nada da tabela de IDs de exclusão. Adicione uma nova coluna para um número de lote e preencha-a com 1000 para o lote 1, 1000 para o lote 2, etc. e verifique se a consulta de exclusão inclui o número do lote.

Se a tabela da qual você está excluindo for referenciada por some_other_table (e você não quiser descartar as foreign keys mesmo que temporariamente), verifique se há um índice na coluna de referência em some_other_table !

Eu tive um problema semelhante e usei auto_explain com auto_explain.log_nested_statements = true , que revelou que a delete estava realmente fazendo seq_scans em some_other_table :

  Query Text: SELECT 1 FROM ONLY "public"."some_other_table" x WHERE $1 OPERATOR(pg_catalog.=) "id" FOR KEY SHARE OF x LockRows (cost=[...]) -> Seq Scan on some_other_table x (cost=[...]) Filter: ($1 = id) 

Aparentemente, ele está tentando bloquear as linhas de referência na outra tabela (que não deveria existir ou a exclusão falhará). Depois de criar índices nas tabelas de referência, a exclusão foi de magnitude mais rápida.

    Intereting Posts