Documentation PostgreSQL 14.22 » Langage SQL » Conseils sur les performances » Remplir une base de données

Précédent | Niveau supérieur | Suivant
14.3. Contrôler le planificateur avec des clauses `JOIN` explicites | Sommaire | 14.5. Configuration avec une perte acceptée

## 14.4. Remplir une base de données

Vous pourriez avoir besoin d'insérer un grand nombre de données pour remplir une base de données tout au début. Cette section contient quelques suggestions pour réaliser cela de la façon la plus efficace.

### 14.4.1. Désactivez la validation automatique (autocommit)

Lors d'`INSERT` multiples, désactivez la validation automatique et faites une seule validation à la fin (en SQL, ceci signifie de lancer `BEGIN` au début et `COMMIT` à la fin. Quelques bibliothèques client pourraient le faire derrière votre dos, auquel cas vous devez vous assurer que la bibliothèque le fait quand vous le voulez). Si vous permettez à chaque insertion d'être validée séparément, PostgreSQL fait un gros travail pour chaque ligne ajoutée. Un bénéfice supplémentaire de réaliser toutes les insertions dans une seule transaction est que si l'insertion d'une ligne échoue alors les lignes insérées jusqu'à maintenant seront annulées. Vous ne serez donc pas bloqué avec des données partiellement chargées.

### 14.4.2. Utilisez `COPY`

Utilisez l'instruction `COPY` pour charger toutes les lignes en une seule commande, plutôt que d'utiliser une série de commandes `INSERT`. La commande `COPY` est optimisée pour charger un grand nombre de lignes ; elle est moins flexible que `INSERT`, mais introduit significativement moins de surcharge lors du chargement de grosses quantités de données. Comme `COPY` est une seule commande, il n'y a pas besoin de désactiver la validation automatique (autocommit) si vous utilisez cette méthode pour remplir une table.

Si vous ne pouvez pas utiliser `COPY`, utilisez `PREPARE` pour créer une instruction préparée `INSERT`, puis utilisez `EXECUTE` autant de fois que nécessaire. Ceci évite certaines surcharges lors d'une analyse et d'une planification répétées de commandes `INSERT`. Différentes interfaces fournissent cette fonctionnalité de plusieurs façons ; recherchez « instructions préparées » dans la documentation de l'interface.

Notez que charger un grand nombre de lignes en utilisant `COPY` est pratiquement toujours plus rapide que d'utiliser `INSERT`, même si `PREPARE ... INSERT` est utilisé lorsque de nombreuses insertions sont groupées en une seule transaction.

`COPY` est plus rapide quand il est utilisé dans la même transaction que la commande `CREATE TABLE` ou `TRUNCATE` précédente. Dans ce cas, les journaux de transactions ne sont pas impactés, car, en cas d'erreur, les fichiers contenant les données nouvellement chargées seront supprimés de toute façon. Néanmoins, cette considération ne s'applique que quand wal_level vaut `minimal` car toutes les commandes doivent écrire dans les journaux de transactions dans les autres cas.

### 14.4.3. Supprimez les index

Si vous chargez une table tout juste créée, la méthode la plus rapide est de créer la table, de charger en lot les données de cette table en utilisant `COPY`, puis de créer tous les index nécessaires pour la table. Créer un index sur des données déjà existantes est plus rapide que de mettre à jour de façon incrémentale à chaque ligne ajoutée.

Si vous ajoutez beaucoup de données à une table existante, il pourrait être avantageux de supprimer les index, de charger la table, puis de recréer les index. Bien sûr, les performances de la base de données pour les autres utilisateurs pourraient souffrir tout le temps où les index seront manquants. Vous devez aussi y penser à deux fois avant de supprimer des index uniques, car la vérification d'erreur apportée par la contrainte unique sera perdue tout le temps où l'index est manquant.

### 14.4.4. Suppression des contraintes de clés étrangères

Comme avec les index, une contrainte de clé étrangère peut être vérifiée « en gros volume » plus efficacement que ligne par ligne. Donc, il pourrait être utile de supprimer les contraintes de clés étrangères, de charger les données et de créer de nouveau les contraintes. De nouveau, il y a un compromis à faire entre la vitesse de chargement des données et la perte de la vérification des erreurs lorsque la contrainte manque.

De plus, quand vous chargez des données dans une table contenant des contraintes de clés étrangères, chaque nouvelle ligne requiert une entrée dans la liste des événements de déclencheur en attente(puisque c'est le lancement d'un déclencheur qui vérifie la contrainte de clé étrangère de la ligne). Le chargement de plusieurs millions de lignes peut amener la taille de la file d'attente des déclencheurs à dépasser la mémoire disponible, causant ainsi une mise en mémoire swap intolérable, voire l'échec de la commande. Dans ce cas, il peut être nécessaire, pas seulement souhaitable, de supprimer et recréer la clé étrangère lors de chargements de grandes quantités de données. Si la suppression temporaire de la contrainte n'est pas acceptable, le seul recours possible est de découper les opérations de chargement en de plus petites transactions.

### 14.4.5. Augmentez `maintenance_work_mem`

Augmenter temporairement la variable maintenance_work_mem lors du chargement de grosses quantités de données peut amener une amélioration des performances. Ceci aidera à l'accélération des commandes `CREATE INDEX` et `ALTER TABLE ADD FOREIGN KEY`. Cela ne changera pas grand-chose pour la commande `COPY`. Donc, ce conseil est seulement utile quand vous utilisez une des deux ou les deux techniques ci-dessus.

### 14.4.6. Augmenter `max_wal_size`

Augmenter temporairement la variable de configuration max_wal_size peut aussi aider à un chargement rapide de grosses quantités de données. Ceci est dû au fait que charger une grosse quantité de données dans PostgreSQL causera la venue très fréquente de checkpoints (la fréquence de ces checkpoints est spécifiée par la variable de configuration `checkpoint_timeout`). Quand survient un checkpoint, toutes les pages modifiées sont écrites sur disque. En augmentant `max_wal_size` temporairement lors du chargement des données, le nombre de checkpoints requis peut être significativement diminué.

### 14.4.7. Désactiver l'archivage des journaux de transactions et la réplication en flux

Lors du chargement de grosse quantité de données dans une instance qui utilise l'archivage des journaux de transactions ou la réplication en flux, il pourrait être plus rapide de prendre une nouvelle sauvegarde de base après que le chargement ait terminé, plutôt que de traiter une grosse quantité de données incrémentales dans les journaux de transactions. Pour empêcher un accroissement de la journalisation des transactions lors du chargement, vous pouvez désactiver l'archivage et la réplication en flux lors du chargement en configurant wal_level à `minimal`, archive_mode à `off` et max_wal_senders à zéro). Mais notez que le changement de ces paramètres requiert un redémarrage du serveur, et rends toute sauvegarde de base réalisée auparavant inutilisable pour la restauration par archives et pour créer un serveur secondaire, ce qui pourrait amener à des pertes de données.

En dehors d'éviter le temps de traitement des données des journaux de transactions par l'archiveur ou l'émetteur des journaux de transactions, ce paramétrage accélérera certaines commandes où la sous-transaction (ou transaction de plus haut niveau) courante crée ou tronque une table ou un index : elles n'écriront pas du tout dans les journaux de transactions si `wal_level` vaut `minimal`. (Elles peuvent garantir la sûreté des données de façon moins coûteuse en exécutant un `fsync` à la fin plutôt qu'en écrivant les journaux de transactions.)

### 14.4.8. Lancez `ANALYZE` après

Quand vous avez changé significativement la distribution des données à l'intérieur d'une table, exécuter `ANALYZE` est fortement recommandé. Ceci inclut le chargement de grosses quantités de données dans la table. Exécuter `ANALYZE` (ou `VACUUM ANALYZE`) vous assure que le planificateur dispose de statistiques à jour sur la table. Sans statistiques ou avec des statistiques obsolètes, le planificateur pourrait prendre de mauvaises décisions lors de la planification de la requête, amenant des performances pauvres sur toutes les tables sans statistiques ou avec des statistiques inexactes. Notez que si le démon autovacuum est activé, il pourrait exécuter `ANALYZE` automatiquement ; voir Section 25.1.3 et Section 25.1.6 pour plus d'informations.

### 14.4.9. Quelques notes sur pg_dump

Les scripts de sauvegarde générés par pg_dump appliquent automatiquement plusieurs des indications ci-dessus, mais pas toutes. Pour recharger une sauvegarde pg_dump aussi rapidement que possible, vous avez besoin de faire quelques étapes supplémentaires manuellement (notez que ces points s'appliquent lors de la restauration d'une sauvegarde, et non pas lors de sa création. Les mêmes points s'appliquent soit lors de la restauration d'une sauvegarde texte avec psql soit lors de l'utilisation de pg_restore pour charger un fichier de sauvegarde pg_dump).

Par défaut, pg_dump utilise `COPY` et, lorsqu'il génère une sauvegarde complexe, schéma et données, il est préférable de charger les données avant de créer les index et les clés étrangères. Donc, dans ce cas, plusieurs lignes de conduite sont gérées automatiquement. Ce qui vous reste à faire est de :

- Configurer des valeurs appropriées (c'est-à-dire plus importantes que la normale) pour `maintenance_work_mem` et `max_wal_size`.

- Si vous utilisez l'archivage des journaux de transactions ou la réplication en flux, considérez leur désactivation lors de la restauration. Pour faire cela, configurez `archive_mode` à `off`, `wal_level` à `minimal` et `max_wal_senders` à zéro avant de charger le script de sauvegarde. Après coup, remettez les anciennes valeurs et effectuez une nouvelle sauvegarde de base.

- Tester le mode parallélisé de la sauvegarde et de la restauration des outils pg_dump et pg_restore, et trouver le nombre optimal de tâches parallélisées à utiliser. La sauvegarde et la restauration en parallèle avec l'option `-j` devraient vous donner de meilleures performances.

- Se demander si la sauvegarde complète doit être restaurée dans une seule transaction. Pour cela, passez l'option `-1` ou `--single-transaction` à psql ou pg_restore. Lors de l'utilisation de ce mode, même les erreurs les plus petites annuleront la restauration complète, peut-être en annulant des heures de traitements. Suivant à quel point les données sont en relation, il peut être préférable de faire un nettoyage manuel. Les commandes `COPY` s'exécuteront plus rapidement si vous utilisez une transaction simple et que vous avez désactivé l'archivage des journaux de transaction.

- Si plusieurs processeurs sont disponibles sur le serveur, penser à utiliser l'option `--jobs` de pg_restore. Cela permet la parallélisation du chargement des données et de la création des index.

- Exécuter `ANALYZE` après coup.

Une sauvegarde des données seules utilise toujours `COPY`, mais elle ne supprime ni ne recrée les index et elle ne touche généralement pas les clés étrangères. [14] Donc, lorsque vous chargez une sauvegarde ne contenant que les données, c'est à vous de supprimer et recréer les index et clés étrangères si vous souhaitez utiliser ces techniques. Il est toujours utile d'augmenter `max_wal_size` lors du chargement des données, mais ne vous embêtez pas à augmenter `maintenance_work_mem` ; en fait, vous le ferez lors d'une nouvelle création manuelle des index et des clés étrangères. Et n'oubliez pas `ANALYZE` une fois que vous avez terminé ; voir Section 25.1.3 et Section 25.1.6 pour plus d'informations.

[14] Vous pouvez obtenir l'effet de désactivation des clés étrangères en utilisant l'option `--disable-triggers` -- mais réalisez que cela élimine, plutôt que repousse, la validation des clés étrangères et qu'il est du coup possible d'insérer des données mauvaises si vous l'utilisez.