DELETE muito lento no PostgreSQL, solução alternativa?

Eu tive um problema parecido. Acontece que esses ON DELETE CASCADEgatilhos estavam desacelerando um pouco as coisas, porque essas exclusões em cascata eram terrivelmente lentas.

Resolvi o problema criando índices nos campos de chave estrangeira nas tabelas de referência e passei de algumas horas para a exclusão para alguns segundos.

Você tem poucas opções. A melhor opção é executar uma exclusão em lote para que os gatilhos não sejam atingidos. Desative os gatilhos antes de excluir e, em seguida, reative-os. Isso economiza uma quantidade muito grande de tempo. Por exemplo:

ALTER TABLE tablename DISABLE TRIGGER ALL; 
DELETE ...; 
ALTER TABLE tablename ENABLE TRIGGER ALL;

Uma chave importante aqui é que você deseja minimizar a profundidade das subconsultas. Nesse caso, convém configurar tabelas temporárias para armazenar informações relevantes para evitar subconsultas profundas em sua exclusão.

O método mais fácil de resolver o problema é consultar o tempo detalhado do PostgreSQL: EXPLAIN. Para isso, você precisa encontrar, no mínimo, uma única consulta que seja concluída, mas que demore mais do que o esperado. Digamos que a consulta lenta se pareça com

delete from mydata where id='897b4dde-6a0d-4159-91e6-88e84519e6b6';

Em vez de realmente executar essa consulta, você pode fazer

begin;
explain (analyze,buffers,timing)
delete from mydata where id='897b4dde-6a0d-4159-91e6-88e84519e6b6';
rollback;

O rollbackno final permite executar isso sem realmente modificar o banco de dados. Você ainda obtém o tempo detalhado do que levou quanto. Depois de executar isso, você pode encontrar na saída que algum gatilho causa grandes atrasos:

...
Trigger for constraint XYZ123: time=12311.292 calls=1
...

O timeestá em ms (milissegundos), portanto, verificar essa restrição levou cerca de 12,3 segundos. Você precisa adicionar um novo INDEXsobre as colunas necessárias para que esse gatilho possa ser calculado de forma eficaz. Para referências de chave estrangeira, a coluna que faz referência a outra tabela deve ser indexada (ou seja, a coluna de origem, não a coluna de destino). O PostgreSQL não cria automaticamente esses índices para você e DELETEé a única consulta comum em que você realmente precisa desse índice. Como resultado, você pode ter acumulado anos de dados até atingir o caso em que DELETEestá muito lento devido à falta de um índice.

A razão pela qual a coluna de origem precisa do índice é que quando você tem tabelas Xe Y, com Y.rreferência de chave estrangeira a X.id, a exclusão de qualquer linha da tabela Xrequer a verificação se existe uma linha Y.rapontando para essa linha na tabela . XSem um índice sobre o Y.rPostgreSQL, será necessário varrer toda a tabela Ypara verificar isso. Com o índice a verificação será rápida porque o índice pode dizer diretamente se tal valor existe em Y.r.

Depois de corrigir o desempenho dessa restrição (ou alguma outra coisa que levou muito tempo), repita a consulta em begin/ rollbackbloco para poder comparar o novo tempo de execução com o tempo de execução anterior. Continue até ficar satisfeito com o tempo de resposta de exclusão de uma única linha (consegui que uma consulta fosse de 25,6 segundos para 15 ms ou cerca de 1700x mais rápido simplesmente adicionando índices diferentes). Então você pode prosseguir para concluir sua exclusão completa sem nenhum hack.

Observe que, se você adicionar um novo índice e isso não melhorar o desempenho, pode ser uma boa ideia remover esse índice. Manter quaisquer índices atualizados quando novas linhas são adicionadas e removidas causará alguma perda de desempenho, portanto, você não deve ter índices sem uma necessidade real.

(Observe que EXPLAINprecisa de uma consulta que possa ser concluída com êxito. Certa vez, tive um problema em que o PostgreSQL demorou muito para descobrir que uma exclusão violaria uma restrição de chave estrangeira e, nesse caso EXPLAIN, não pode ser usada porque não emitirá tempo para falhas consultas. Não conheço nenhuma maneira fácil de depurar problemas de desempenho nesse caso.)

Desabilitar gatilhos pode ser uma ameaça à integridade do banco de dados e não pode ser recomendado; no entanto, se você tiver certeza de que sua operação é à prova de falhas, poderá desabilitar os gatilhos com o seguinte:

SET session_replication_role = replica;

Corre DELETEaqui.

Para restaurar acionadores, execute:

SET session_replication_role = DEFAULT;

Fonte aqui.

Se você tiver gatilhos ON DELETE CASCADE, esperamos que eles estejam lá por um motivo e, portanto, não devem ser desativados. Outro truque (ainda adicione seus índices) que funciona para mim é criar uma função de exclusão que exclui manualmente os dados começando com as tabelas no final da cascata e funciona em direção à tabela principal. (Isso é o mesmo que você teria que fazer se tivesse um gatilho ON DELETE RESTRICT)

CREATE TABLE tablea (
    tablea_uid integer
);

CREATE TABLE tableb (
    tableb_uid integer,
    tablea_rid integer REFERENCES tablea(tablea_uid)
);

CREATE TABLE tablec (
    tablec_uid integer,
    tableb_rid integer REFERENCES tableb(tableb_uid)
);

Nesse caso, exclua os dados em tablec, em seguida, em tableb, em seguida, em tablea

CREATE OR REPLACE FUNCTION delete_in_order()
 RETURNS void AS $$

    DELETE FROM tablec;
    DELETE FROM tableb;
    DELETE FROM tablea;

$$ LANGUAGE SQL;

Para mim, o truque foi descartar a restrição fk de outra tabela de referência . Esta tabela de referência era enorme. Mas cuidado, eu sabia que aquela restrição para os registros que eu tinha que deletar não era relevante. Portanto, eu poderia descartar temporariamente a restrição para adicioná-la posteriormente (durante a qual eu tinha certeza de que não havia outra atividade de banco de dados).