Na documentação do PostgreSQL sobre preenchimento de bancos de dados, eles mencionam como podemos melhorar as operações de carregamento em massa desabilitando restrições e índices, mas vendo como INSERT ... ON CONFLICT (upsert) requer o uso de uma restrição exclusiva para funcionar, e isso faz todo o sentido, Eu estive me perguntando o quão boa é a seguinte estratégia:
Crie várias conexões para carregar dados em lotes em paralelo em uma tabela de teste não registrada
Crie restrições adiadas para fazer validação de integridade nos dados da tabela
Faça o upsert dos dados de preparo em uma grande tabela de destino (5 GB+), que também precisaria ter um PK, índice exclusivo ou restrição exclusiva para possibilitar o upsert.
A abordagem de inserção em conflito é conhecida por ser melhor do que criar manualmente funções SQL ou scripts para unir as duas tabelas para descobrir os "novos registros" a serem inseridos e os "registros comuns" a serem atualizados. Esse é o motivo pelo qual INSERT ... ON CONFLICT existe.
No entanto, ainda estou me perguntando as implicações de desempenho para ler os dados após carregá-los.
Perguntas:
Em termos de inchaço do índice, um upsert seria pior do que simples inserções de cópia em massa + atualização para uma tabela sem restrições? O upsert é ruim para a manutenção do índice?
Se for esse o caso, acredito que carregamentos mais rápidos também implicariam em um desempenho de consulta ruim após o carregamento da tabela. Devo recriar os índices após INSERT ... ON CONFLICT?
Comparado com INSERT ... ON CONFLICT, a forma de função/script de fazer isso, embora com menos desempenho para cargas, seria melhor para manutenção de índice, pois a tabela de destino não precisa ter um índice para fazer o upserting?
Não acho que seja esse o caso. Eu acho que um ponto muito importante foi uma conveniência para que as pessoas não precisem implementar um loop catch and retry para violações de restrição exclusivas em inserções de linha única. Não reverter inserções em massa inteiras devido a algumas violações também é uma melhoria muito boa, mas não acho que seja o ponto principal . E se você vai ter a tabela offline para outros usos de qualquer maneira, acho que na verdade não é o objetivo de ON CONFLICT.
ON CONFLICT paga um preço alto para lidar com a concorrência em um nível refinado. Se você pode garantir que apenas este procedimento está inserindo novos registros, então uma consulta para fazer a inserção de novas chaves e uma segunda consulta para fazer a atualização das antigas quase certamente seriam mais eficientes do que usar ON CONFLICT. Se o seu ponto 3 significa que a tabela grande teve todas as restrições e índices descartados, exceto o necessário para suportar o ON CONFLICT, então parece provável que você esteja em uma janela de manutenção e, portanto, provavelmente pode garantir que não haja inserções simultâneas.
Eu não esperaria que esse fosse o caso em geral, se você mantiver a restrição exclusiva de qualquer maneira. Mas isso dependeria de coisas como se os valores de chave exclusivos das linhas que estão sendo inseridas estão ocorrendo principalmente em ordem ou aleatórios. Além disso, bloat dependeria se as linhas que seguem o caminho UPDATE têm espaço para a linha atualizada no mesmo bloco em que a versão antiga residia originalmente (portanto, o fator de preenchimento da tabela), mas isso também seria verdade se o UPDATE fosse uma instrução separada . Você teria que simular algo semelhante às suas condições reais e testá-lo para obter respostas claras.
Se você colocar a tabela offline para uso normal e descartar todas as restrições e índices, ela deverá ter mais desempenho e levar a índices menos inchados (embora talvez apenas um pouco menos inchados) depois de reconstruí-los. Esse é o caso se você comparar com o uso do ON CONFLICT online ou com a eliminação de todas as restrições, exceto uma, e a execução do ON CONFLICT offline.