Quando são usados vários comandos INSERT, é melhor desativar o AUTOCOMMIT (efetivação automática), e fazer o COMMIT apenas no final. (Em puro SQL, isto significa executar o comando BEGIN no início e o comando COMMIT no final da transação. Algumas bibliotecas cliente podem fazer isto sem que seja visto; neste caso, é necessário garantir que a biblioteca possa fazer isto quando quiser.) Se for permitido que cada inserção seja efetivada em separado, o PostgreSQL fará muito trabalho para cada linha adicionada. Um benefício adicional de fazer todas as inserções em uma única transação é que, se a inserção de uma linha falhar, a inserção de todas as linhas inseridas até este ponto será desfeita, para que não se fique preso a dados parcialmente carregados.

Deve ser usado o COPY para carregar todas as linhas em um único comando, em vez de usar uma série de comandos INSERT. O comando COPY é otimizado para carregar um grande número de linhas; embora seja menos flexível do que o comando INSERT, produz uma sobrecarga muito menor para grandes cargas de dados. Como COPY é um comando único, não há necessidade de desativar o AUTOCOMMIT se for usado este comando para carregar a tabela.

Se não for possível usar o comando COPY, pode ser benéfico usar PREPARE para criar uma instrução INSERT preparada e, em seguida, usar EXECUTE quantas vezes forem necessárias. Isto evita parte da sobrecarga de analisar e planejar repetidamente o comando INSERT. Diferentes interfaces fornecem esta facilidade de maneiras diferentes; procure por “instruções preparadas” na documentação da interface.

Note que carregar um grande número de linhas usando o comando COPY é quase sempre mais rápido do que usar o comando INSERT, mesmo se for usado PREPARE, e várias inserções forem agrupadas em uma única transação.

O comando COPY é mais rápido quando usado na mesma transação que um comando anterior CREATE TABLE ou TRUNCATE. Nestes casos, nada precisa ser escrito no WAL, porque em caso de erro, os arquivos contendo os dados recém-carregados serão removidos de qualquer maneira. Entretanto, esta consideração só se aplica quando a variável de configuração wal_level for minimal, caso contrário todos os comandos devem escrever no WAL.

Se estivermos carregando uma tabela recém-criada, o método mais rápido é criar a tabela, carregar em massa os dados da tabela usando COPY, e criar os índices necessários para a tabela. Criar um índice em dados pré-existentes é mais rápido do que atualizá-lo incrementalmente à medida que cada linha é carregada.

Se estivermos adicionando uma grande quantidade de dados a uma tabela existente, pode ser um ganho eliminar os índices, carregar a tabela e, em seguida, recriar os índices. Obviamente, o desempenho do banco de dados para os demais usuários pode sofrer durante o tempo em que os índices estão ausentes. Deve-se pensar duas vezes antes de remover um índice exclusivo, porque a verificação de erros proporcionada pela restrição de unicidade estará perdida enquanto o índice estiver ausente.

Assim como nos índices, uma restrição de chave estrangeira pode ser verificada “em massa” com mais eficiência do que linha por linha. Portanto, pode ser útil eliminar as restrições de chave estrangeira, carregar os dados, e recriar as restrições. Novamente, deve haver um equilíbrio entre a velocidade de carga dos dados e a perda de verificação de erros enquanto a restrição estiver ausente.

Além disso, quando se carrega dados em uma tabela com restrições de chave estrangeira existentes, cada nova linha requer uma entrada na lista de eventos de gatilho pendentes do servidor (já que é o disparo de um gatilho que verifica a restrição de chave estrangeira da linha). Carregar muitos milhões de linhas pode fazer com que a fila de eventos de gatilho estoure a memória disponível, levando a um swap intolerável, ou até mesmo a uma falha total do comando. Portanto, pode ser necessário, e não apenas desejável, remover e reaplicar as chaves estrangeiras ao carregar uma grande quantidade de dados. Se a remoção temporária da restrição não for aceitável, o único outro recurso pode ser dividir a operação de carga em transações menores.

O aumento temporário da variável de configuração maintenance_work_mem, enquanto se carrega uma grande quantidade de dados, pode melhorar o desempenho. Isto ajuda a acelerar os comandos CREATE INDEX e os comandos ALTER TABLE ADD FOREIGN KEY. Este aumento não fará muito pelo comando COPY em si, então este conselho só é útil quando se está usando uma das técnicas acima, ou as duas.

O aumento temporário da variável de configuração max_wal_size também pode tornar as grandes cargas de dados mais rápidas. Isto ocorre, porque carregar uma grande quantidade de dados no PostgreSQL faz com que os pontos de verificação ocorram com uma frequência acima da normal (especificado pela variável de configuração checkpoint_timeout). Sempre que ocorre um ponto de verificação, todas as páginas sujas devem ser descarregadas para o disco. Ao aumentar a variável de configuração max_wal_size temporariamente durante a carga de dados em massa, o número de pontos de verificação necessários pode ser reduzido.

Ao carregar grandes quantidades de dados em uma instância que usa arquivamento do WAL ou replicação por fluxo, pode ser mais rápido fazer uma nova cópia de segurança base após a conclusão do carregamento do que processar uma grande quantidade de dados incrementais do WAL. Para evitar o arquivamento incremental do WAL durante o carregamento, deve ser desativado o arquivamento e a replicação por fluxo definindo wal_level como minimal, archive_mode como off e max_wal_senders como zero. Observe, porém, que a alteração destas configurações exige a reinicialização do servidor e torna qualquer cópia de segurança base feita anteriormente inútil para recuperação de arquivamento e para criação de um servidor secundário, o que pode levar à perda de dados.

Além de evitar o tempo para processar os dados do WAL pelo arquivador ou remetente do WAL, esta configuração torna certos comandos mais rápidos, porque não escrevem no WAL se wal_level for igual a minimal, e a subtransação corrente (ou transação de nível superior) criou ou truncou a tabela ou índice que estes comandos alteram. (Pode ser garantida uma segurança contra falhas mais econômica executando a função fsync no final, do que escrevendo no WAL.)

Sempre que se altera muito a distribuição dos dados de uma tabela, é altamente recomendável executar o comando ANALYZE. Isto inclui a carga em massa de uma grande quantidade de dados na tabela. A execução do comando ANALYZE (ou VACUUM ANALYZE) garante que o planejador tenha estatísticas atualizadas sobre a tabela. Sem estatísticas, ou com estatísticas obsoletas, o planejador pode tomar decisões ruins durante o planejamento de consultas, levando a um desempenho ruim em qualquer tabela com estatísticas imprecisas ou inexistentes. Note que se o processo autovacuum estiver ativado, então poderá executar o comando ANALYZE automaticamente; veja Atualização das estatísticas do planejador e a Autovacuum para obter mais informações.

Os scripts de cópia de segurança gerados pela aplicação pg_dump aplicam automaticamente várias, mas não todas, as diretrizes acima. Para recuperar uma cópia de segurança feita pelo pg_dump o mais rápido possível, é necessário fazer algumas coisas extras manualmente. (Note que estes pontos se aplicam ao recuperar a cópia de segurança, e não ao criar a cópia de segurança. Os mesmos pontos se aplicam ao carregar uma cópia de segurança de texto usando o psql, ou usando o pg_restore para carregar um arquivo criado pelo pg_dump.)

Por padrão, a aplicação pg_dump usa o comando COPY e, quando está gerando uma cópia de segurança completa de esquema e dados, é cuidadoso ao carregar os dados antes de criar os índices e chaves estrangeiras. Portanto, neste caso, várias diretrizes são tratadas automaticamente. O que resta a você fazer é:

Uma cópia de segurança somente de dados ainda usa o comando COPY, mas não exclui nem recria índices, e normalmente não toca em chaves estrangeiras ^[106]. Portanto, ao carregar uma cópia de segurança somente de dados, cabe a você excluir e recriar índices e chaves estrangeiras se desejar usar estas técnicas. Ainda é útil aumentar a variável de configuração max_wal_size enquanto carrega os dados, mas não se preocupe em aumentar a variável de configuração maintenance_work_mem; em vez disso, você faria isto ao recriar manualmente índices e chaves estrangeiras posteriormente. E não se esqueça de executar o comando ANALYZE quando terminar; veja Atualização das estatísticas do planejador e a Autovacuum para obter mais informações.