Partager via

Bonnes pratiques pour charger en bloc des données dans Azure Database pour PostgreSQL

Cet article décrit différentes méthodes pour charger des données en bloc dans une instance de serveur flexible Azure Database pour PostgreSQL, ainsi que les meilleures pratiques pour les chargements de données initiales dans les bases de données vides et les chargements incrémentiels de données.

Méthodes de chargement

Les méthodes de chargement de données suivantes sont organisées de la plus longue à la moins longue :

  • Exécutez une commande INSERT à enregistrement unique.
  • Réalisez des lots compris entre 100 et 1 000 lignes par validation. Vous pouvez utiliser un bloc de transaction pour envelopper plusieurs enregistrements par validation.
  • Exécutez INSERT avec plusieurs valeurs de ligne.
  • Exécutez la commande COPY.

La méthode recommandée pour charger des données dans une base de données est la commande COPY. Si la commande COPY est impossible, le traitement INSERT est la deuxième meilleure méthode. Le multithreading avec une commande COPY est optimal pour les chargements en bloc de données.

Étapes pour charger des données en bloc

Voici les étapes à suivre pour charger en bloc des données dans une instance de serveur flexible Azure Database pour PostgreSQL.

Étape 1 : préparer vos données

Vérifiez que vos données sont propres et correctement mises en forme pour la base de données.

Étape 2 : Choisir la méthode de chargement

Sélectionnez la méthode de chargement appropriée en fonction de la taille et de la complexité de vos données.

Étape 3 : Exécuter la méthode de chargement

Exécutez la méthode de chargement choisie pour charger vos données dans la base de données.

Étape 4 : Vérifier les données

Après le chargement, vérifiez que les données ont été correctement chargées dans la base de données.

Bonnes pratiques pour les premiers chargements de données

Voici les bonnes pratiques pour les premiers chargements de données.

Supprimer les index

Avant d’effectuer un premier chargement de données, nous vous recommandons de supprimer tous les index des tables. La création d’index après le chargement des données est toujours plus efficace.

Supprimer les contraintes

Les principales contraintes à supprimer sont décrites ici :

  • Contraintes de clés uniques

Pour obtenir des performances solides, nous recommandons de supprimer les contraintes de clé unique avant un premier chargement de données et de les recréer une fois le chargement terminé. Toutefois, la suppression des contraintes de clé unique ne vous protège plus des données dupliquées.

  • Contraintes de clés étrangères

Nous recommandons de supprimer les contraintes de clés étrangères avant le premier chargement de données et de les recréer une fois le chargement de données terminé.

Le changement du paramètre session_replication_role en replica désactive également toutes les vérifications de clé étrangère. Cependant, si le changement n'est pas correctement utilisé, il peut entraîner des incohérences dans les données.

Tables non journalisées

Tenez compte des avantages et inconvénients des tables non journalisées avant de les utiliser dans les premiers chargements de données.

L’utilisation de tables non journalisées accélère le chargement des données. Les données écrites dans des tables non journalisées ne sont pas écrites dans le journal WAL (write-ahead log).

Les inconvénients liés à l’utilisation de tables non journalisées sont les suivants :

  • Elles ne sont pas protégées contre les incidents. Une table non journalisée est automatiquement tronquée après un incident ou un arrêt brutal.
  • Les données des tables non journalisées ne peuvent pas être répliquées sur des serveurs de secours.

Pour créer une table non journalisée ou changer une table existante en table non journalisée, utilisez les options suivantes :

  • Créez une nouvelle table non journalisée en utilisant la syntaxe suivante :

    CREATE UNLOGGED TABLE <tablename>;

  • Convertissez une table journalisée existante en table non journalisée en utilisant la syntaxe suivante :

    ALTER TABLE <tablename> SET UNLOGGED;

Réglage des paramètres de serveur

  • auto vacuum': It's best to turn off le nettoyage automatique pendant le premier chargement de données. Une fois le premier chargement terminé, nous vous recommandons d’exécuter une VACUUM ANALYZE manuelle sur toutes les tables de la base de données, puis d’activer auto vacuum.

Remarque

Suivez les recommandations indiquées ici seulement s’il y a suffisamment de mémoire et d’espace disque.

  • maintenance_work_mem : Peut être défini sur un maximum de 2 gigaoctets (Go) sur une instance de serveur flexible Azure Database pour PostgreSQL. maintenance_work_mem permet d’accélérer la création de clés étrangères, d’index et le nettoyage automatique.

  • checkpoint_timeout : Sur une instance de serveur flexible Azure Database pour PostgreSQL, la valeur checkpoint_timeout peut être augmentée à un maximum de 24 heures à partir du paramètre par défaut de 5 minutes. Nous vous recommandons d’augmenter la valeur à 1 heure avant de commencer à charger des données sur l’instance de serveur flexible Azure Database pour PostgreSQL.

  • checkpoint_completion_target : Nous recommandons une valeur de 0,9.

  • max_wal_size : Peut être défini sur la valeur maximale autorisée sur une instance de serveur flexible Azure Database pour PostgreSQL, qui est de 64 Go pendant que vous procédez au premier chargement de données.

  • wal_compression : Peut être activé. L’activation de ce paramètre peut entraîner des coûts supplémentaires de processeur pour la compression pendant la journalisation WAL (write-ahead log) et la décompression pendant la relecture WAL.

Recommandations

Avant de commencer un premier chargement de données sur l’instance de serveur flexible Azure Database pour PostgreSQL, nous vous recommandons de :

  • Désactiver la haute disponibilité sur le serveur. Vous pouvez l’activer une fois le premier chargement terminé sur le serveur principal.
  • Créer des réplicas en lecture une fois le premier chargement de données terminé.
  • Réduire la journalisation au minimum ou la désactiver complètement pendant les premiers chargements de données (par exemple, désactiver pgaudit, pg_stat_statements, magasin des requêtes).

Recréer des index et ajouter des contraintes

En supposant que vous avez supprimé les index et les contraintes avant le premier chargement, nous vous recommandons d’utiliser des valeurs élevées dans maintenance_work_mem (comme mentionné précédemment) pour créer des index et ajouter des contraintes. En outre, à compter de PostgreSQL version 11, les paramètres suivants peuvent être modifiés pour accélérer la création d’index parallèle après le premier chargement de données :

  • max_parallel_workers : Définit le nombre maximal de Workers que le système peut prendre en charge pour les requêtes parallèles.

  • max_parallel_maintenance_workers : Contrôle le nombre maximal de processus de Worker qui peuvent être utilisés dans CREATE INDEX.

Vous pouvez également créer les index en utilisant les paramètres recommandés au niveau de la session. Voici un exemple de la procédure à suivre :

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

Bonnes pratiques pour les chargements de données incrémentiels

Les bonnes pratiques pour les chargements de données incrémentiels sont décrites ici :

Partitionner des tables

Nous vous recommandons toujours de partitionner des tables volumineuses. Voici quelques avantages du partitionnement, en particulier pendant les chargements incrémentiels :

  • La création de nouvelles partitions basées sur les nouveaux deltas permet d’ajouter efficacement de nouvelles données à la table.
  • La gestion des tables devient plus facile. Vous pouvez supprimer une partition pendant un chargement incrémentiel de données pour éviter les suppressions qui prennent du temps dans les grandes tables.
  • Le nettoyage automatique est déclenché uniquement sur les partitions qui ont été modifiées ou ajoutées pendant les chargements de données incrémentiels, ce qui facilite la maintenance des statistiques sur la table.

Maintenir à jour les statistiques sur la table

La surveillance et la maintenance des statistiques sur la table sont importantes pour les performances des requêtes sur la base de données. Cela inclut également les scénarios dans lesquels vous avez des chargements de données incrémentielles. PostgreSQL utilise le processus de démon de nettoyage automatique pour nettoyer les tuples morts et analyser les tables pour maintenir les statistiques à jour. Pour plus d’informations, consultez Surveillance et réglage du nettoyage automatique.

Créer des index sur des contraintes de clé étrangère

La création d’index sur des clés étrangères dans les tables enfants peut être un avantage dans les scénarios suivants :

  • Mises à jour ou suppressions de données dans la table parent. Lorsque les données sont mises à jour ou supprimées dans la table parent, les recherches sont effectuées dans la table enfant. Pour accélérer les recherches, vous pouvez indexer les clés étrangères sur la table enfant.
  • Les requêtes, où vous pouvez voir la jointure de tables parentes et enfants sur des colonnes clés.

Identifier les index inutilisés

Identifiez les index inutilisés dans la base de données et supprimez-les. Les index constituent une surcharge pour les chargements de données. Moins il y a d’index sur une table, meilleures sont les performances pendant l’ingestion des données.

Vous pouvez identifier les index inutilisés de deux façons : via un Magasin des requêtes et dans une requête d’utilisation d’index.

Magasin des requêtes

La fonctionnalité Magasin des requêtes permet d’identifier les index qui peuvent être supprimés en fonction des schémas d’utilisation des requêtes sur la base de données. Pour des instructions détaillées, consultez Magasin des requêtes.

Une fois que vous avez activé la fonctionnalité Magasin des requêtes sur le serveur, vous pouvez utiliser la requête suivante pour identifier les index qui peuvent être supprimés en vous connectant à la base de données azure_sys.

SELECT * FROM IntelligentPerformance.DropIndexRecommendations;

Utilisation d’index

Vous pouvez également utiliser la requête suivante pour identifier les index inutilisés :

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;

Les colonnes number_of_scans, tuples_read et tuples_fetched indiquent la valeur de colonne usage.number_of_scans d’index de zéro point pour les index qui ne sont pas en cours d’utilisation.

Réglage des paramètres de serveur

Remarque

Suivez les recommandations dans les paramètres suivants seulement s’il y a suffisamment de mémoire et d’espace disque.

  • maintenance_work_mem : Ce paramètre peut être défini sur un maximum de 2 Go sur l’instance de serveur flexible Azure Database pour PostgreSQL. maintenance_work_mem accélère la création d’index et les ajouts de clés étrangères.

  • checkpoint_timeout : Sur l’instance de serveur flexible Azure Database pour PostgreSQL, la valeur checkpoint_timeout peut être augmentée à 10 ou 15 minutes à partir du paramètre par défaut de 5 minutes. L’augmentation du paramètre checkpoint_timeout pour le définir sur une valeur supérieure, telle que 15 minutes, peut réduire la charge d’E/S, mais l’inconvénient est que la récupération prend plus de temps en cas d’incident. Nous vous recommandons d’y réfléchir à deux fois avant d’apporter la modification.

  • checkpoint_completion_target : Nous recommandons une valeur de 0,9.

  • max_wal_size : Cette valeur dépend de la référence SKU, du stockage et de la charge de travail. L’exemple suivant montre un moyen d’arriver à la valeur appropriée pour max_wal_size.

Pendant les heures de pointe, arrivez à une valeur en procédant comme suit :

a) Prenez le numéro séquentiel dans le journal (LSN) WAL actuel en exécutant la requête suivante :

SELECT pg_current_wal_lsn ();

b. Attendez pendant le nombre de secondes de checkpoint_timeout. Prenez le LSN WAL actuel en exécutant la requête suivante :

SELECT pg_current_wal_lsn ();

c. Utilisez les deux résultats pour vérifier la différence en Go :

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 : Peut être activé. L’activation de ce paramètre peut engendrer un coût supplémentaire de processeur pour la compression pendant la journalisation WAL et la décompression pendant la relecture WAL.

Contenu connexe

  • Last updated on