Operações em lote em vez de loops linha a linha: round trips, transações e COPY

O que é

Um loop linha a linha envia uma instrução por linha e espera a resposta de cada uma. Uma operação em lote envia o conjunto inteiro de uma vez: um INSERT ... VALUES (...), (...) com várias linhas, um único UPDATE ... FROM (VALUES ...), uma leitura com WHERE id = ANY(...) ou um stream COPY. O guia do PostgreSQL sobre como popular um banco de dados recomenda desativar o autocommit e fazer commit uma única vez no final quando se usam muitos inserts; usar COPY para carregar todas as linhas em um único comando, porque ele é otimizado para grandes quantidades de linhas e gera significativamente menos overhead do que uma série de INSERTs; usar prepared statements quando COPY não for possível; e criar os índices depois de carregar uma tabela nova, porque indexar dados já existentes é mais rápido do que atualizar um índice a cada linha. O executemany do psycopg executa o mesmo comando sobre uma sequência de parâmetros; a documentação o descreve como mais eficiente do que consultas separadas e, ao mesmo tempo, sugere copy() para muitos inserts.

Por que importa

O custo por linha é a latência do round trip, mais o parse e o planejamento, mais, com autocommit, um commit que precisa ser tornado durável. Com um round trip de 1 ms, um milhão de linhas gasta pelo menos 1.000 s (cerca de 17 minutos) só esperando pela rede (cálculo aritmético), antes de o banco de dados fazer qualquer trabalho. As operações em lote eliminam essa parcela e permitem que o banco planeje uma única vez.

Como aplicar

• Leituras: uma única consulta com id = ANY(%s) ou um join, em vez de um loop de buscas pontuais; pagine resultados grandes com cursores keyset.
• Escritas: INSERT com várias linhas, executemany ou COPY para cargas; UPDATE ... FROM (VALUES ...) ou UPDATE ... WHERE id = ANY(...) para atualizações em massa; DELETE ... WHERE id = ANY(...) para exclusões.
• Agrupe os lotes: uma transação por alguns milhares de linhas mantém os locks curtos e o write-ahead log limitado, sem perder a diluição do custo do commit.
• Para uma tabela nova: carregue os dados, depois crie índices e constraints e, por fim, execute ANALYZE, como o guia recomenda.
• Use streaming na entrada em vez de materializar uma lista de um milhão de dicionários; executemany aceita um iterável de conjuntos de parâmetros e copy() escreve as linhas uma a uma a partir de qualquer iterador.

Armadilhas

Uma instrução em lote falha como um todo: uma única linha inválida aborta o lote, por isso decida de antemão se, para o lote que falhou, será feito um fallback linha a linha ou se ele será rejeitado. Uma transação gigantesca mantém locks durante todo o seu tempo de vida e incha o write-ahead log. Triggers e constraints por linha continuam sendo disparados linha a linha. Exclusões em massa em tabelas grandes bloqueiam outros escritores; exclua em lotes, por faixa de chave primária. Os métodos de bulk dos ORMs costumam ignorar validação do modelo, signals e save hooks; leia as ressalvas deles antes de depender desses métodos.