Boas práticas para o carregamento em massa de dados para o Base de Dados do Azure para PostgreSQL flexible server

Este artigo discute vários métodos para carregar dados em massa em uma instância de servidor flexível do Banco de Dados do Azure para 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 demorado:

  • Execute um comando de registro INSERT único.
  • Agrupe entre 100 e 1.000 linhas por submissão. Você pode usar um bloco de transação para encapsular vários registros por confirmação.
  • Executar INSERT com múltiplos valores de linha.
  • Execute o comando COPY.

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

Etapas para carregar dados em massa

Aqui estão as etapas para carregar dados em massa em uma instância de servidor flexível do Banco de Dados do Azure para PostgreSQL.

Passo 1: Preparar os seus dados

Certifique-se de que seus dados estejam limpos e formatados corretamente para o banco de dados.

Passo 2: Escolha o método de carregamento

Selecione o método de carregamento apropriado com base no tamanho e na complexidade dos seus dados.

Etapa 3: Executar o método de carregamento

Execute o método de carregamento escolhido para carregar seus dados no banco de dados.

Etapa 4: Verificar os dados

Após o upload, verifique se os dados foram carregados corretamente no banco de dados.

Práticas recomendadas para cargas iniciais de dados

Aqui estão as práticas recomendadas para cargas iniciais de dados.

Eliminar índices

Antes de fazer uma carga inicial de dados, recomendamos descartar todos os índices nas tabelas. Criar os índices depois que os dados são carregados é sempre mais eficiente.

Eliminar restrições

As principais restrições de queda são descritas aqui:

  • Restrições de chave única

Para obter um desempenho forte, recomendamos eliminar restrições de chave exclusivas antes de uma carga de dados inicial e recriá-las depois que a carga de dados for concluída. No entanto, eliminar restrições de chave exclusivas cancela as proteções contra dados duplicados.

  • Restrições de chave estrangeira

Recomendamos eliminar as restrições de chave estrangeira antes da carga inicial de dados e recriá-las após a conclusão da carga de dados.

Alterar o session_replication_role parâmetro para replica também desativa todas as verificações de chave estrangeira. No entanto, se a alteração não for usada corretamente, pode deixar os dados inconsistentes.

Tabelas sem registo

Considere os prós e os contras das tabelas sem registo antes de as utilizar em cargas iniciais de dados.

O uso de tabelas não registradas acelera o carregamento de dados. Os dados gravados em tabelas não registradas não são gravados no log write-ahead.

As desvantagens de usar tabelas não registradas são:

  • Eles não são à prova de colisão. Uma tabela sem registo é automaticamente esvaziada após uma falha ou um encerramento incorreto.
  • Os dados de tabelas sem registo não podem ser replicados para servidores de standby.

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

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

    CREATE UNLOGGED TABLE <tablename>;

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

    ALTER TABLE <tablename> SET UNLOGGED;

Ajuste de parâmetros

  • auto vacuum': It's best to turn off auto vacuum' durante o carregamento inicial dos dados. Após a conclusão da carga inicial, recomendamos que execute manualmente um VACUUM ANALYZE em todas as tabelas da base de dados e, em seguida, ative o auto vacuum.

Observação

Siga as recomendações aqui apenas 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 o vácuo automático, o índice e a criação de chaves estrangeiras.

  • checkpoint_timeout: Numa instância de servidor flexível do Base de Dados do Azure para PostgreSQL, o valor checkpoint_timeout pode ser aumentado até um máximo de 24 horas, a partir do valor predefinido de 5 minutos. Recomendamos aumentar o valor para 1 hora antes de carregar inicialmente os dados na instância flexível do servidor do Banco de Dados do Azure para PostgreSQL.

  • checkpoint_completion_target: Recomendamos 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ê está fazendo o carregamento inicial de dados.

  • wal_compression: Isto pode ser ativado. Ativar este parâmetro pode implicar algum custo adicional de CPU com a compressão durante o registo no write-ahead log (WAL) e com a descompressão durante a repetição do WAL.

Recommendations

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

  • Desative a alta disponibilidade no servidor. Pode ativá-lo depois de o carregamento inicial estar concluído no principal.
  • Crie réplicas de leitura após a conclusão do carregamento inicial de dados.
  • Torne o registro mínimo ou desative-o todo junto durante as cargas iniciais de dados (por exemplo, desabilitar pgaudit, pg_stat_statements, query store).

Recriar índices e adicionar restrições

Supondo que você tenha descartado os índices e restrições antes da carga inicial, recomendamos o uso de valores altos em maintenance_work_mem (como mencionado anteriormente) para criar índices e adicionar restrições. Além disso, a partir do PostgreSQL versão 11, os seguintes parâmetros podem ser modificados para uma criação de índice paralelo mais rápida após a carga inicial de dados:

  • max_parallel_workers: Define o número máximo de trabalhadores 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 no CREATE INDEX.

Você também pode criar os índices fazendo as configurações recomendadas no nível da sessão. Aqui está um exemplo de como fazê-lo:

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

As práticas recomendadas para cargas de dados incrementais são descritas aqui:.

Tabelas de partição

Recomendamos sempre que particione tabelas grandes. Algumas vantagens do particionamento, especialmente durante cargas incrementais, incluem:

  • A criação de novas partições com base em novos deltas torna eficiente a adição de novos dados à tabela.
  • Manter tabelas fica mais fácil. Você pode soltar uma partição durante uma carga de dados incremental para evitar exclusões demoradas em tabelas grandes.
  • O Autovacuum seria acionado apenas em partições que foram alteradas ou adicionadas durante cargas incrementais, o que facilita a manutenção de estatísticas na tabela.

Manter as estatísticas da tabela atualizadas

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 obter mais informações, consulte Monitoramento e ajuste de vácuo automático.

Criar índices sobre restrições de chave estrangeira

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

  • Atualizações ou exclusões de dados na tabela pai. Quando os dados são atualizados ou excluídos na tabela pai, as pesquisas são realizadas na tabela filho. Você pode indexar chaves estrangeiras na tabela filho para fazer pesquisas mais rápidas.
  • Consultas, onde você pode ver tabelas pai e filho se unindo em colunas principais.

Identificar índices não utilizados

Identifique os índices não utilizados no banco de dados e solte-os. Os índices são uma sobrecarga nas cargas de dados. Quanto menos índices em uma tabela, melhor o desempenho durante a ingestão de dados.

Você pode identificar índices não utilizados de duas maneiras: pelo Repositório de Consultas e por uma consulta de uso de índice.

Loja de Consultas

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

Depois de habilitar o Repositório de Consultas no servidor, você pode usar a consulta a seguir para identificar índices que podem ser descartados conectando-se a azure_sys banco de dados.

SELECT * FROM IntelligentPerformance.DropIndexRecommendations;

Utilização do índice

Você também pode 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 indicariam que o valor zero na coluna usage.number_of_scans do índice corresponde a um índice que não está a ser utilizado.

Ajuste de parâmetros

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: Este parâmetro pode ser definido para 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 índices e adições de chaves estrangeiras.

  • checkpoint_timeout: Na instância flexível do servidor 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 mais significativo, como 15 minutos, pode reduzir a carga de E/S, mas a desvantagem é que leva mais tempo para se recuperar se houver uma falha. Recomendamos uma consideração cuidadosa antes de fazer a alteração.

  • checkpoint_completion_target: Recomendamos um valor de 0,9.

  • max_wal_size: Esse valor depende de SKU, armazenamento e carga de trabalho. O exemplo a seguir mostra uma maneira de chegar ao valor correto para max_wal_size.

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

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

SELECT pg_current_wal_lsn ();

b) Aguarde o checkpoint_timeout número de segundos. Obtenha o LSN atual do WAL executando a seguinte consulta:

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 running the second time','LSN value when run the first time')/1024/1024/1024,2) WAL_CHANGE_GB;
  • wal_compression: Isto pode ser ativado. Ativar este parâmetro pode implicar um custo adicional de CPU para a compressão durante o registo no WAL e a descompressão durante a reexecução do WAL.

Conteúdo relacionado

  • Last updated on