13.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 au tout début. Cette section contient
quelques suggestions pour réaliser cela de la façon la plus efficace.
13.4.1. Désactivez la validation automatique (autocommit)
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.
Utilisez 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
, utiliser PREPARE pourrait vous
aider à 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
.
Notez que charger un grand nombre de lignes en utilisant
COPY
est pratiquement
toujours plus rapide que d'utiliser
INSERT
, même si
PREPARE
est utilisé lorsque de
nombreuses insertions sont groupées en une seule transaction.
13.4.3. Supprimez les index
Si vous chargez une table tout juste créée, la façon 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 l'index, de charger la table,
puis de recréer l'index. Bien sûr, les performances de la base de
données pour les autres utilisateurs pourraient être sévèrement
affectées tout le temps où l'index sera manquant. 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.
13.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 entre la vitesse de chargement des données et la perte
de la vérification des erreurs lorsque la contrainte manque.
13.4.5. Augmentez maintenance_work_mem
Augmentez 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.
13.4.6. Augmentez checkpoint_segments
Augmenter temporairement la variable de configuration checkpoint_segments
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 trop fréquente de points de vérification (la
fréquence de ces points de vérification est spécifiée par la
variable de configuration checkpoint_timeout). Quand survient un point de
vérification, toutes les pages modifiées sont écrites sur le
disque. En augmentant checkpoint_segments
temporairement lors du chargement des données, le nombre de points
de vérification requis peut être diminué.
13.4.7. Lancez ANALYZE après
Quand vous avez changé significativement la distribution des
données à l'intérieur d'une table, lancer ANALYZE est fortement
recommandée. Ceci inclut le chargement de grosses quantités de
données dans la table. Lancer
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.
13.4.8. 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
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, les premières lignes de
conduite sont gérées automatiquement. Ce qui vous reste à faire est
d'initialiser les valeurs appropriées (c'est-à-dire plus
importantes que les valeurs habituelles) pour maintenance_work_mem et checkpoint_segments avant de charger le script de
sauvegarde puis d'exécuter
ANALYZE
.
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. [] 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
checkpoint_segments 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é.
Contrôler le planificateur avec des clauses
JOIN explicites
