Optimisation de la gestion de la base de données

1) Vérifier la validité de la géométrie

Une géométrie non valide est la cause principale de l'échec des requêtes spatiales.

La notion de validité de la géométrie et les méthodes pour corriger les géométries non valides sont exposées dans le module 4 Compléments SQL

Un rapport plus général a été publié par le PNE Progiciels géomatiques.

2) Répartir les tables volumineuses sur plusieurs tables

Le partitionnement consiste à diviser une grande table en plusieurs tables plus petites.

Le partitionnement peut offrir plusieurs avantages :

  • Les performances des requêtes peuvent être améliorées de façon spectaculaire dans certaines situations (meilleure efficience des index).

  • Le partitionnement devrait être appliqué dès que la taille de la table est supérieure à la mémoire physique du serveur de base de données

Pour plus de précisions, consulter la page : http://docs.postgresql.fr/9.3/ddl-partitioning.html

3) Bien gérer les contraintes (clés primaires et étrangères)

Le module 2 Rappels et concepts de base détaille les notions de clé primaire et étrangère.

4) Utiliser les index (spatiaux ou non) pour accélérer les requêtes

L'utilisation d'index est une façon habituelle d'améliorer les performances d'une base de données.

Un index permet au serveur de bases de données de retrouver une ligne spécifique bien plus rapidement qu'en parcourant séquentiellement la table.

Les index peuvent aussi bénéficier aux commandes UPDATE et DELETE comportant des conditions de recherche.

De plus, les index peuvent être utilisés dans les jointures. Ainsi, un index défini sur une colonne qui fait partie d'une condition de jointure peut aussi accélérer significativement les requêtes avec jointures.

Mais les index ajoutent aussi une surcharge au système de base de données dans son ensemble, si bien qu'ils doivent être utilisés avec discernement.

Après la création d'un index, le système doit le maintenir synchronisé avec la table. Cela rend plus lourdes les opérations de manipulation de données.

C'est pourquoi les index qui sont peu, voire jamais, utilisés doivent être supprimés.

Attention : la présence d'index ralentit très fortement l'insertion donc il ne faut pas en mettre lorsque cela n'est pas nécessaire.

Source documentaire : http://docs.postgresql.fr/9.3/indexes.html

5) Autres actions de maintenance de la base

  • Effectuer régulièrement des VACUUM et ANALYSE (auto-vacuum par exemple)

  • Effectuer une réindexation régulière

  • Utiliser la commande CLUSTER pour les tables géographiques

Ces commandes sont expliquées dans le module 2 Administration