Temos um banco de dados com pouco mais de 200 gb de tamanho, com cerca de 430 milhões de linhas, uma chave primária e um índice espacial. A cada poucas semanas, recebemos uma atualização que envolve cerca de 3 milhões de linhas, excluindo alguns recursos antigos e atualizando novos. Isso é feito off-line. Também temos uma tabela de histórico, que armazena as exclusões acumuladas de cada atualização. Esta tabela está atualmente em torno de 20 gb e 40 tem milhões de linhas. Essencialmente, primeiro inserimos na tabela de histórico qualquer coisa que esteja marcada como deletar na tabela de alteração, então excluímos tudo da tabela principal, seja para deletar ou novo, e então inserimos na nova tabela todos os registros alterados (todos off-line, para ser claro).
Se eu executar isso em um único procedimento/função, uma reescrita de tabela parece ser acionada, pois voltei depois de algumas horas e descobri que falhou devido ao uso do espaço em disco disponível, cerca de 100 gb. Se eu executá-lo linha por linha em um console psql, não haverá esse problema. Como não há instruções DDL e o número total de linhas alteradas é muito menor do que o tamanho total do banco de dados, estou confuso sobre o que pode estar causando essa quantidade de uso de disco temporário, pois o espaço total necessário para registrar toda a alteração como um transação parece ser muito menor.
Existe alguma regra prática para isso ou uma consulta de diagnóstico simples que posso executar para ver o que está acontecendo?
Eu diria que a diferença é que seu procedimento PL/PgSQL é executado em uma única transação.
Se você executar linha por linha em
psql, a menos que explicitamenteBEGINeCOMMIT, estará executando em transações individuais. Isso pode ser muito mais lento, mas também significa que o autoVACUUMpode vir para liberar e reutilizar as linhas excluídas, para que osUPDATEs subsequentes possam gravar novas versões de linha nesses espaços liberados.Se você estiver em uma única transação, o sistema terá que manter as versões de linha antigas por perto porque precisa delas no caso de você efetuar
ROLLBACKa transação ou encontrar um erro.Portanto, você não pode realmente fazer o que deseja apenas no PL/pgSQL, a menos que esteja disposto a usar hacks como
dblink. Você precisa agrupar seu trabalho em uma série de transações menores e, como o PostgreSQL ainda não suporta confirmação autônoma de uma função PL/PgSQL, isso significa dblink ou um cliente externo.