Condividi tramite

Procedure consigliate per il caricamento bulk dei dati in Database di Azure per PostgreSQL

Questo articolo illustra vari metodi per caricare i dati in blocco in un'istanza del server flessibile di Database di Azure per PostgreSQL, insieme alle procedure consigliate per i caricamenti iniziali dei dati in database vuoti e caricamenti incrementali dei dati.

Metodi di caricamento

I seguenti metodi di caricamento dei dati sono disposti in ordine da quello che richiede più tempo a quello che richiede meno tempo:

  • Eseguire un comando INSERT a record singolo.
  • Eseguire in batch da 100 a 1.000 righe per commit. È possibile usare un blocco di transazioni per eseguire il wrapping di più record per ogni commit.
  • Eseguire INSERT con più valori di riga.
  • Eseguire il comando COPY.

Il metodo preferito per il caricamento dei dati in un database è il comando COPY. Se il comando COPY non è possibile, INSERT in batch è il secondo metodo migliore. Il multithreading con un comando COPY è ottimale per caricare i dati in blocco.

Passaggi per caricare i dati in blocco

Di seguito sono riportati i passaggi per caricare i dati in blocco nell'istanza del server flessibile di Database di Azure per PostgreSQL.

Passaggio 1: Preparare i dati

Verificare che i dati siano puliti e formattati correttamente per il database.

Passaggio 2: Scegliere il metodo di caricamento

Selezionare il metodo di caricamento corretto in base alle dimensioni e alla complessità dei dati.

Passaggio 3: Eseguire il metodo di caricamento

Eseguire il metodo di caricamento scelto per caricare i dati nel database.

Passaggio 4: Verificare i dati

Dopo il caricamento, verificare che i dati siano stati caricati correttamente nel database.

Procedure consigliate per i caricamenti iniziali dei dati

Di seguito sono riportate le procedure consigliate per i caricamenti iniziali dei dati.

Eliminare gli indici

Prima di eseguire un caricamento iniziale dei dati, è consigliabile eliminare tutti gli indici nelle tabelle. Creare gli indici dopo il caricamento dei dati è sempre più efficiente.

Vincoli di rilascio

I vincoli di rilascio principali sono descritti di seguito:

  • Vincoli di chiave univoca

Per ottenere prestazioni elevate, è consigliabile eliminare i vincoli di chiave univoca prima del caricamento iniziale dei dati e ricrearli al termine del caricamento. Tuttavia, l'eliminazione di vincoli di chiave univoca annulla le misure di sicurezza contro i dati duplicati.

  • Vincoli di chiavi esterne

È consigliabile eliminare i vincoli di chiave esterna prima del caricamento iniziale dei dati e ricrearli dopo il completamento del caricamento dei dati.

La modifica del parametro session_replication_role in replica disabilita anche tutti i controlli di chiave esterna. Tuttavia, se la modifica non viene usata correttamente, possono verificarsi problemi di incoerenza dei dati.

Tabelle non registrate

Considerare i vantaggi e gli svantaggi dell'uso di tabelle non registrate prima di usarle nei caricamenti iniziali dei dati.

L'uso di tabelle non registrate velocizza il caricamento dei dati. I dati scritti in tabelle non registrate non vengono scritti nel log write-ahead.

Gli svantaggi dell'uso di tabelle non registrate sono i seguenti:

  • Non sono sicure per gli arresti anomali. Una tabella non registrata viene troncata automaticamente dopo un arresto anomalo o in seguito a un arresto non corretto.
  • I dati delle tabelle non registrate non possono essere replicati nei server di standby.

Per creare una tabella non registrata o modificare una tabella esistente in una tabella non registrata, utilizzare le opzioni seguenti:

  • Creare una nuova tabella non registrata con la sintassi seguente:

    CREATE UNLOGGED TABLE <tablename>;

  • Convertire una tabella registrata esistente in una tabella non registrata con la sintassi seguente:

    ALTER TABLE <tablename> SET UNLOGGED;

Regolazione del parametro server

  • auto vacuum': It's best to turn off autovacuum' durante il caricamento iniziale dei dati. Al termine del caricamento iniziale, è consigliabile eseguire un VACUUM ANALYZE manuale in tutte le tabelle del database e quindi attivare auto vacuum.

Nota

Seguire le indicazioni riportate qui solo se sono disponibili memoria e spazio su disco sufficienti.

  • maintenance_work_mem: può essere impostato su un massimo di 2 gigabyte (GB) in un'istanza del server flessibile di Database di Azure per PostgreSQL. maintenance_work_mem consente di velocizzare la creazione automatica di autovacuum, indici e chiavi esterne.

  • checkpoint_timeout: in un'istanza del server flessibile di Database di Azure per PostgreSQL il valore checkpoint_timeout può essere aumentato fino a un massimo di 24 ore dall'impostazione predefinita di 5 minuti. È consigliabile aumentare il valore a 1 ora prima di caricare inizialmente i dati nell'istanza del server flessibile di Database di Azure per PostgreSQL.

  • checkpoint_completion_target: è consigliabile usare il valore 0,9.

  • max_wal_size: può essere impostato sul valore massimo consentito in un'istanza del server flessibile di Database di Azure per PostgreSQL, ovvero 64 GB durante l'esecuzione del caricamento iniziale dei dati.

  • wal_compression: può essere attivato. L'abilitazione di questo parametro può comportare un costo aggiuntivo della CPU per la compressione durante la registrazione del log write-ahead (WAL) e per la decompressione durante la riproduzione WAL.

Consigli

Prima di avviare un caricamento iniziale dei dati nell'istanza del server flessibile di Database di Azure per PostgreSQL, è consigliabile:

  • Disabilitare la disponibilità elevata nel server. È possibile abilitarla dopo il completamento del caricamento iniziale nel server primario.
  • Creare repliche di lettura al termine del caricamento iniziale dei dati.
  • Rendere minima o disabilitare del tutto la registrazione durante i caricamenti iniziali dei dati (ad esempio: disabilitare pgaudit, pg_stat_statements, Query Store).

Ricreare gli indici e aggiungere vincoli

Supponendo di aver eliminato gli indici e i vincoli prima del caricamento iniziale, è consigliabile usare valori elevati in maintenance_work_mem (come indicato in precedenza) per creare indici e aggiungere vincoli. Inoltre, a partire dalla versione 11 di PostgreSQL, è possibile modificare i parametri seguenti per una creazione più rapida dell'indice parallelo dopo il caricamento iniziale dei dati:

  • max_parallel_workers: imposta il numero massimo di ruoli di lavoro che il sistema può supportare per le query parallele.

  • max_parallel_maintenance_workers: controlla il numero massimo di processi di lavoro che possono essere usati in CREATE INDEX.

È anche possibile creare gli indici rendendo le impostazioni consigliate a livello di sessione. Ecco un esempio di come eseguire questa operazione:

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);

Procedure consigliate per i caricamenti incrementali dei dati

Le procedure consigliate per i caricamenti incrementali dei dati sono descritte di seguito:

Partizionare le tabelle

È sempre consigliabile partizionare tabelle di grandi dimensioni. Alcuni vantaggi del partizionamento, in particolare durante i caricamenti incrementali, includono:

  • La creazione di nuove partizioni in base ai nuovi delta rende più efficiente l'aggiunta di nuovi dati alla tabella.
  • La gestione delle tabelle diventa più semplice. È possibile eliminare una partizione durante un caricamento incrementale dei dati per evitare eliminazioni che richiedono molto tempo in tabelle di grandi dimensioni.
  • Autovacuum viene attivato solo nelle partizioni modificate o aggiunte durante i caricamenti incrementali, semplificando la gestione delle statistiche nella tabella.

Mantenere statistiche aggiornate nella tabella

Il monitoraggio e la gestione delle statistiche delle tabelle sono importanti per le prestazioni delle query nel database. Sono inclusi anche scenari in cui sono presenti carichi incrementali. PostgreSQL usa il processo del daemon autovacuum per pulire le tuple inattive e analizzare le tabelle per mantenere aggiornate le statistiche. Per altre informazioni, vedere Monitoraggio e ottimizzazione di Autovacuum.

Creare indici su vincoli di chiave esterna

La creazione di indici in chiavi esterne nelle tabelle figlio può essere utile negli scenari seguenti:

  • Aggiornamenti o eliminazioni dei dati nella tabella padre. Quando i dati vengono aggiornati o eliminati nella tabella padre, le ricerche vengono eseguite nella tabella figlio. È possibile indicizzare le chiavi esterne nella tabella figlio per velocizzare le ricerche.
  • Query, in cui è possibile visualizzare le tabelle padre e figlio nelle colonne chiave.

Identificare gli indici inutilizzati

Identificare gli indici inutilizzati nel database e rilasciarli. Gli indici sono un sovraccarico sui caricamenti dei dati. Minore è il numero di indici in una tabella, migliori sono le prestazioni durante l'inserimento dei dati.

È possibile identificare gli indici inutilizzati in due modi: da Query Store e da una query sull'utilizzo dell'indice.

Archivio query

La funzionalità Query Store consente di identificare gli indici che possono essere eliminati in base ai modelli di utilizzo delle query nel database. Per istruzioni dettagliate, vedere Query Store.

Dopo aver abilitato Query Store nel server, è possibile usare la query seguente per identificare gli indici che possono essere eliminati connettendosi al database azure_sys.

SELECT * FROM IntelligentPerformance.DropIndexRecommendations;

Utilizzo indice

È anche possibile usare la query seguente per identificare gli indici inutilizzati:

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;

Le colonne number_of_scans, tuples_read e tuples_fetched indicano il valore di colonna usage.number_of_scans di zero punti come indice non utilizzato.

Regolazione del parametro server

Nota

Seguire le indicazioni nei parametri seguenti solo se sono disponibili memoria e spazio su disco sufficienti.

  • maintenance_work_mem: questo parametro può essere impostato su un massimo di 2 GB nell'istanza del server flessibile di Database di Azure per PostgreSQL. maintenance_work_mem consente di velocizzare la creazione dell'indice e le aggiunte di chiavi esterne.

  • checkpoint_timeout: in un'istanza del server flessibile di Database di Azure per PostgreSQL il valore checkpoint_timeout può essere aumentato fino a 10-15 minuti dall'impostazione predefinita di 5 minuti. L'aumento di checkpoint_timeout a un valore più significativo, come 15 minuti, può ridurre il carico di I/O, ma lo svantaggio è che il ripristino richiede più tempo in caso di arresto anomalo. È consigliabile prestare attenzione prima di apportare la modifica.

  • checkpoint_completion_target: è consigliabile usare il valore 0,9.

  • max_wal_size: questo valore dipende da SKU, archiviazione e carico di lavoro. L'esempio seguente mostra un modo per arrivare al valore corretto per max_wal_size.

Durante le ore lavorative di punta, arrivare a un valore eseguendo le operazioni seguenti:

a) Accettare il numero di sequenza di log (LSN) corrente eseguendo la query seguente:

SELECT pg_current_wal_lsn ();

b. Attendere il numero di secondi di checkpoint_timeout. Accettare l'LSN WAL corrente eseguendo la query seguente:

SELECT pg_current_wal_lsn ();

c. Usare i due risultati per verificare la differenza in 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: può essere attivato. L'abilitazione di questo parametro può comportare un costo aggiuntivo della CPU per la compressione durante la registrazione WAL e per la decompressione durante la riproduzione WAL.

Contenuti correlati

  • Last updated on