Share via

Melhores práticas para carregar dados em massa no Banco de Dados do Azure para PostgreSQL ─ Servidor Flexível

  • Artigo

APLICA-SE A: Banco de Dados do Azure para PostgreSQL – Servidor Flexível

Este artigo discute vários métodos para carregar dados em massa no Banco de Dados do Azure para servidor flexível PostgreSQL, juntamente com as práticas recomendadas para cargas de dados iniciais em bancos de dados vazios e cargas de dados incrementais.

Métodos de carregamento

Os seguintes métodos de carregamento de dados são organizados em ordem do mais demorado para o menos:

  • Execute um comando INSERT de registro único.
  • Faça o carregamento em lote de 100 a 1.000 linhas por commit. É possível usar um bloco de transação para agrupar diversos registros por commit.
  • Execute INSERT com diversos valores de linha.
  • Execute o comando COPY.

O método preferencial para carregar dados em um banco de dados é usar o comando COPY. Se não for possível usar o comando COPY, o próximo melhor método é usar o lote INSERT. O método ideal para carregar dados em massa é o multithreading com um comando COPY.

Práticas recomendadas para carregamentos de dados iniciais

Remover índices

Antes de fazer um carregamento de dados inicial, elimine todos os índices nas tabelas. É sempre mais eficiente criar os índices após o carregamento dos dados.

Remover restrições

Veja a descrição das seguintes principais restrições de remoção:

  • Restrições exclusivas da chave

    Para obter um alto desempenho, recomenda-se remover as restrições de chaves exclusivas antes de um carregamento de dados inicial e recria-las após a operação. No entanto, descartar restrições de chave exclusivas cancela as proteções contra dados duplicados.

  • Restrições de chave estrangeira

    Recomenda-se remover as restrições de chave estrangeira antes do carregamento de dados inicial e recria-las após a operação.

    Alterar o parâmetro session_replication_role para replica também desabilita todas as verificações de chave estrangeira. No entanto, lembre-se de que essa alteração poderá deixar os dados em um estado inconsistente se não for usada corretamente.

Tabelas não registradas

Considere os prós e os contras do uso de tabelas não registradas antes de usá-las nos carregamentos de dados iniciais.

O uso de tabelas não registradas torna o carregamento de dados mais rápido. Os dados gravados em tabelas não registradas não são gravados no log write-ahead.

Veja as seguintes desvantagens do uso de tabelas não registradas:

  • Não são seguras contra falhas. Uma tabela não registrada é automaticamente truncada após uma falha ou um desligamento não limpo.
  • Os dados de tabelas não registradas não podem ser replicados para servidores em espera.

Para criar uma tabela não registrada ou alterar uma tabela existente para esse tipo, use as seguintes opções:

  • Crie uma nova tabela não registrada usando a sintaxe a seguir:

    CREATE UNLOGGED TABLE <tablename>;

  • Converta uma tabela registrada existente em uma tabela não registrada usando a seguinte sintaxe:

    ALTER TABLE <tablename> SET UNLOGGED;

Ajuste de parâmetro do servidor

  • autovacuum: durante o carregamento de dados inicial, é melhor desligar autovacuum. Após a conclusão do carregamento inicial, recomenda-se executar um VACUUM ANALYZE manual em todas as tabelas do banco de dados e, em seguida, ativar autovacuum.

Observação

Siga as recomendações somente se houver memória e espaço em disco suficientes.

  • maintenance_work_mem: pode ser definido como um máximo de 2 gigabytes (GB) em uma instância de servidor flexível do Banco de Dados do Azure para PostgreSQL. maintenance_work_mem ajuda a acelerar a criação automática de chaves estrangeiras, índices e vácuo automático.

  • checkpoint_timeout: Em uma instância de servidor flexível do Banco de Dados do Azure para PostgreSQL, o checkpoint_timeout valor pode ser aumentado para um máximo de 24 horas a partir da configuração padrão de 5 minutos. Recomendamos que você aumente o valor para 1 hora antes de carregar dados inicialmente na instância de servidor flexível do Banco de Dados do Azure para PostgreSQL.

  • checkpoint_completion_target: recomenda-se um valor de 0,9.

  • max_wal_size: pode ser definido como o valor máximo permitido em uma instância de servidor flexível do Banco de Dados do Azure para PostgreSQL, que é de 64 GB enquanto você faz o carregamento de dados inicial.

  • wal_compression: pode ser ativado. A ativação desse parâmetro pode gerar algum custo extra de CPU na compactação durante o WAL (log write-ahead) e na descompactação durante a reprodução do WAL.

Recomendações de servidor flexível do Banco de Dados do Azure para PostgreSQL

Antes de iniciar uma carga de dados inicial na instância de servidor flexível do Banco de Dados do Azure para PostgreSQL, recomendamos que você:

  • Desabilitar a alta disponibilidade no servidor. É possível habilitar após a conclusão do carregamento inicial no primário.
  • Criar réplicas de leitura após o carregamento inicial de dados.
  • Tornar o registro mínimo ou desativá-lo completamente durante os carregamentos iniciais de dados (por exemplo: desabilitar pgaudit, pg_stat_statements, o Repositório de Consultas).

Recriar índices e adicionar restrições

Supondo que os índices e as restrições foram removidos antes do carregamento inicial, recomenda-se usar valores altos em maintenance_work_mem (conforme mencionado anteriormente) para criar índices e adicionar restrições. Além disso, a partir do PostgreSQL 11, os seguintes parâmetros podem ser modificados para uma criação de índice paralelo mais rápida após o carregamento inicial de dados:

  • max_parallel_workers: define o número máximo de trabalhos que o sistema pode suportar para consultas paralelas.

  • max_parallel_maintenance_workers: controla o número máximo de processos de trabalho que podem ser usados em CREATE INDEX.

Também é possível criar índices realizando as configurações recomendadas no nível da sessão. Veja o seguinte exemplo de como fazer isso:

SET maintenance_work_mem = '2GB';
SET max_parallel_workers = 16;
SET max_parallel_maintenance_workers = 8;
CREATE INDEX test_index ON test_table (test_column);

Práticas recomendadas para cargas de dados incrementais

Tabelas de partição

É sempre recomendado particionar tabelas grandes. Algumas vantagens do particionamento, especialmente durante cargas incrementais, incluem o seguinte:

  • A criação de partições com base em novos deltas torna mais eficiente a adição de dados à tabela.
  • A manutenção de tabelas torna-se mais fácil. É possível descartar uma partição durante um carregamento incremental de dados para evitar exclusões demoradas em tabelas grandes.
  • O Autovacuum é disparado somente em partições que foram alteradas ou adicionadas durante carregamentos incrementais, o que facilita a manutenção de estatísticas na tabela.

Manter estatísticas atualizadas da tabela

O monitoramento e a manutenção de estatísticas de tabela são importantes para o desempenho da consulta no banco de dados. Isso também inclui cenários em que você tem cargas incrementais. O PostgreSQL usa o processo de daemon de vácuo automático para limpar tuplas mortas e analisar as tabelas para manter as estatísticas atualizadas. Para saber mais, confira Monitoramento e ajuste do Autovacuum.

Criar índices em restrições de chave estrangeira

A criação de índices em chaves estrangeiras nas tabelas filhas pode ser benéfica nos seguintes cenários:

  • Atualizações de dados ou exclusões na tabela pai. Quando os dados são atualizados ou excluídos na tabela pai, as pesquisas são executadas na tabela filho. Para fazer pesquisas mais rapidamente, você pode indexar chaves estrangeiras na tabela filho.
  • Consultas, nas quais é possível ver a junção de tabelas pai e filho em colunas de chave.

Identificar índices não utilizados

Identifique índices não utilizados no banco de dados e remova-os. Índices são uma sobrecarga em cargas de dados. Quanto menos índices houver em uma tabela, melhor será o desempenho durante a ingestão de dados.

É possível identificar índices não utilizados de duas maneiras: por Repositório de Consultas e por uma consulta de uso de índice.

Repositório de Consultas

O recurso Repositório de Consultas ajuda a identificar os índices que podem ser removidos com base nos padrões de uso de consultas no banco de dados. Para obter uma orientação passo a passo, consulte Repositório de Consultas.

Depois de habilitar o Repositório de Consultas no servidor, é possível usar a consulta a seguir para identificar índices que podem ser removidos por meio da conexão com o banco de dados azure_sys.

SELECT * FROM IntelligentPerformance.DropIndexRecommendations;

Uso do índice

Também é possível usar a seguinte consulta para identificar índices não utilizados:

SELECT 
    t.schemaname, 
    t.tablename, 
    c.reltuples::bigint                            AS num_rows, 
    pg_size_pretty(pg_relation_size(c.oid))        AS table_size, 
    psai.indexrelname                              AS index_name, 
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size, 
    CASE WHEN i.indisunique THEN 'Y' ELSE 'N' END AS "unique", 
    psai.idx_scan                                  AS number_of_scans, 
    psai.idx_tup_read                              AS tuples_read, 
    psai.idx_tup_fetch                             AS tuples_fetched 
FROM 
    pg_tables t 
    LEFT JOIN pg_class c ON t.tablename = c.relname 
    LEFT JOIN pg_index i ON c.oid = i.indrelid 
    LEFT JOIN pg_stat_all_indexes psai ON i.indexrelid = psai.indexrelid 
WHERE 
    t.schemaname NOT IN ('pg_catalog', 'information_schema') 
ORDER BY 1, 2;

As colunas number_of_scans, tuples_read e tuples_fetched indicam o valor da coluna usage.number_of_scans do índice de zero pontos como um índice que não está sendo usado.

Ajuste de parâmetro do servidor

Observação

Siga as recomendações nos parâmetros a seguir somente se houver memória e espaço em disco suficientes.

  • maintenance_work_mem: Esse parâmetro pode ser definido como um máximo de 2 GB na instância de servidor flexível do Banco de Dados do Azure para PostgreSQL. maintenance_work_mem ajuda a acelerar a criação de índice e adições de chave estrangeira.

  • checkpoint_timeout: Na instância do servidor flexível do Banco de Dados do Azure para PostgreSQL, o checkpoint_timeout valor pode ser aumentado para 10 ou 15 minutos a partir da configuração padrão de 5 minutos. Aumentar checkpoint_timeout para um valor maior, como 15 minutos, pode reduzir a carga de E/S, mas a desvantagem é que leva mais tempo para a recuperação em caso de falhas. Recomenda-se considerar cuidadosamente antes de realizar uma alteração.

  • checkpoint_completion_target: recomenda-se um valor de 0,9.

  • max_wal_size: esse valor depende da SKU, do armazenamento e da carga de trabalho. Uma maneira de chegar ao valor correto para max_wal_size é mostrada no exemplo a seguir.

    Durante o horário comercial de pico, chegue a um valor fazendo o seguinte:

    a. Obtenha o LSN (número de sequência de log) do WAL atual executando a seguinte consulta:

    SELECT pg_current_wal_lsn ();

    b. Aguarde o número de checkpoint_timeout segundos. Execute a seguinte consulta para obter o LSN atual do WAL:

    SELECT pg_current_wal_lsn ();

    c. Use os dois resultados para verificar a diferença, em GB:

    SELECT round (pg_wal_lsn_diff('LSN value when run second time','LSN value when run first time')/1024/1024/1024,2) WAL_CHANGE_GB;

  • wal_compression: pode ser ativado. A ativação desse parâmetro pode gerar algum custo extra de CPU na compactação durante o registro do WAL e na descompactação durante a reprodução do WAL.

Próximas etapas