PostgreSQL / PostGis : bonnes pratiques et astuces

Dans ce dossier, nous aborderons quelques bonnes pratiques et astuces pour le couple PostgreSQL / PostGis. Cet article est rédigé pour une utilisation sur un serveur linux. Néanmoins, la plupart des points sont totalement transposables sous windows.

Cet article n'étant pas un tutoriel mais un recueil non exhaustif de bonnes pratiques et d'astuces, les points sont abordés sommairement. Il est donc nécessaire de se référer à la documentation officielle pour les mettre en oeuvre.

1 - Installer correctement son serveur

• Mettre un autre port que le port par défaut. Il s'agit ici d'une mesure de sécurité basique. C'est surtout conseillé dans le cadre d'une ouverture vers le web. A modifier dans postgresql.conf
  
  
• Créer un tablespace sur une autre partition que la partition système. Le tablespace est un espace de stockage qui va permettre de stocker les données d'une base de données (tables, index, tables systèmes...). En le définissant sur une autre partition que la partition système, vous évitez d'encombrer celle-ci en l'attribuant aux nouvelles bases de données.
  Le modifier à postériori pour une base ne sera pris en compte que pour les nouveaux éléments (tables, index...) de la base. Pour changer le tablespace sur ceux-ci, il vous faut modifier le tablespace de chacun des éléments (table, index).
   
• Mettre toutes les données dans un ou plusieurs autres schemas que le schema par défaut "public" : cette manipulation à mettre en oeuvre dès la conception de la base est très importante. En effet, elle permet de séparer le coeur de PostGis (tables systèmes PostGis, fonctions...) des données elle-même. On peut créer plusieurs autres schémas soit par thématique soit selon d'autres organisations (référentiel / données métiers / données produites ou données tabulaires / données vectorielles par exemple). Cette organisation en schema multiple peut aussi simplifier la gestion des droits d'utilisation.
  Le principal avantage reste néanmoins de faciliter la mise à jour de PostGis ainsi que la restauration ou l'échange plus efficaces de données. L'import d'une sauvegarde contenant les fonctionnalités PostGis dans une autre installation peut en effet causer des conflits.
   
• Bien vérifier les "ouvertures" vers l'extérieur (postgresql.conf et pg_hba.conf). En effet, il est important de limiter l'accès à PostgreSQL au maximum.
  Le paramètre listen_addresses du fichier de configuration postgresql.conf permet de définir quelles IP sont "écoutées" par PostgreSQL (toutes, localhost). Par défaut, ce paramètre est à * c'est à dire qu'il écoute tout.
  Le paramètrage de pg_hba.conf va permettre de gérer plus finement les accès en configurant les IP ou plages d'IPs autorisées en précisant les bases autorisés et les modes d'accès (sans mot de passe, mot de passe cryptés...).
   
• Prendre le temps de configurer son serveur (postgresql.conf) en utilisant la doc pour bien comprendre l'impact des paramètres sur les performances. En effet, le paramétrage par défaut est plutôt "bas" ce qui veut dire qu'on ne va pas par défaut exploiter pleinement la puissance d'un serveur digne de ce nom. Ici, on ne peut pas donner de conseils particuliers, il faut bien lire la documentation et tester l'impact sur le serveur. 

2 - Bonnes pratiques de sécurité (sécurité contre l'intrusion et intégrité des données) :

• bien gérer ses users et ses groupes (ses rôles) : cela permet de bien sécuriser ses bases, tant contre l'intrusion que contre les erreurs de manipulation. Les accès peuvent se gérer à plusieurs niveaux (base de données, schéma, table, tablespace, function...). On peut autoriser à un utilisateur (à tous les niveaux) :
  
  • la lecture (select) : droit de consulter les données
  • l'écriture (insert) : droit de créer de nouvelles données
  • la modification (update) : droit de modifier des données existantes
  • la suppression (delete) : droit de supprimer des données
  • la création de clés primaires / étrangères (references) : droit de créer des clés primaires et étrangères.
  • la création de triggers (trigger) : droit de créer des triggers. Un trigger est une fonction qui se déclenche lors d'un évènement prédéfini.
  • .....

L'utilisation des rôles et des droits associées permet de gérer de manière très fine les accès et les utilisateurs, permettant d'assurer l'intégrité des données. Typiquement, les donnéees de références seront en lecture seule (sauf pour le ou les administreurs SIG) et les données métiers seront lisibles uniquement pour les services concernées.

La conception de ses rôles et droits d'accès n'est pas à négliger et est presque aussi importante que la conception de la base de données elle-même.

• comme dit précédemment, changer le port de PostgreSQL et bien gérer les accès "physiques" (postresql.conf et pg_hba.conf).
   
• faire des dumps (sauvegardes) réguliers au minimum voire mettre en place un système de réplication. Il est indispensable de mettre en place un système de sauvegarde des bases de données, ce système devant être pensé selon vos besoins.

Le plus simple est de faire des dumps réguliers des bases de données. Il est alors indispensable de stocker ces dumps sur une autre machine que celle du serveur postgreSQL pour garantir leur disponibilité en cas de problème.

Il est aussi possible de mettre en place un système de réplication de la base qu'il soit synchrone (en quasi temps-réel) ou asynchrone. On parle alors de serveurs maîtres et esclaves. Cette fonctionnalité est disponible par défaut dans PostgreSQL mais il existe aussi des extensions qui peuvent avoir certains avantages. Pour avoir un premier aperçu des diverses possibilités, voir cette page.

3 - Bien optimiser sa base :

• bien gérer les contraintes (clés primaires et étrangères).
   
• utiliser les index (spatiaux ou non) pour accélérer les requêtes. 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.
   
• vacuum et analyze réguliers (auto-vacuum par exemple). Le vacuum permet de libérer l'espace disque qui n'est plus utilisé par postgreSQL. L'analyze récolte des informations statistiques sur les tables à destination de l'analyseur de requêtes. Un analyze à jour permet donc à l'analyseur de requêtes de prendre les bonnes décisions (utilisation de l'index ou non...) et donc de répondre aux requêtes plus rapidement.

Par défaut, postgreSQL fait ces 2 opérations automatiquement : c'est l'auto-vacuum que l'on peut paramétrer plus finement dans le fichier de configuration (postgresql.conf).

• réindexation régulière surtout pour des tables qui "bougent" (suppressions, modifications fréquentes). L'utilisation de la commande SQL REINDEX permet de recalculer en entier un index. Cela permet un accès à l' index par les requêtes légèrement plus rapide. Dans les vieilles versions de PostgreSQL, le gain est énorme.
   
• pour les tables géographiques, l'utilisation de la commande CLUSTER sur l'index géométrique permet un léger gain en rapidité lors des requêtes spatiales. Cette commande permet d'ordonner physiquement la table selon l'index géométrique et permet donc un accès aux enregistrements "proches" plus rapide.

Attention,  cette opération est lourde puisqu'elle pause un verrou exclusif sur la table (aucune autre action possible sur la table durant ce temps).

Vu les contraintes que cela impose, je conseillerai de le faire sur les tables qui bougent peu ou même pas (peu ou pas d'insertions, de modifications).

• pour les tables et les vues géographiques, maintenir la table système geometry_columns à jour. Cela peut se faire "à la main" ou grâce à la fonction Populate_Geometry_Columns(). C'est indispensable pour l'utilisation des couches PostGis dans des logiciels SIG clients.

NB : pour toutes les taches régulières, penser à utiliser les taches automatiques (taches CRON sous linux).

4 - Optimiser ses requêtes

• mettre les conditions (where) les plus "rapides" en premier. L'analyseur de requêtes se charge lui-même d'adopter la stratégie d'éxécution de la requête théoriquement la plus rapide. Il détermine donc l'ordre d'éxécution des conditions. Dans certains cas où l'analyseur n'a pas assez d'éléments pour choisir, mettre en premier la condition la plus "rapide" ou la plus discriminante permet d'optimiser les requêtes.
   
• penser à utiliser les sous-requêtes. Dans certains cas, l'utilisation de sous requêtes est plus efficace que les multiples conditions et jointures. Il faut donc penser à tester l'utilisation de sous-requêtes, d'autant plus qu'elles permettent de décomposer un problème complexe en plusieurs problèmes plus simples.
   
• Utiliser les opérateurs spatiaux dans les requêtes spatiales ? Dans de nombreux tutoriels, on conseille d'utiliser les opérateurs spatiaux pour accélérer les fonctions spatiales. Néanmoins, ce n'est plus utile et même ralentit très légèrement les requêtes dans les versions modernes de PostGis puisque les opérateurs spatiaux sont inclus dans les fonctions spatiales. Par exmple, ST_intersects() inclut un préfiltrage avec l'opérateur spatial && (intersection des rectangles englobant) alors que l'ancienne fonction intersects() ne le faisait pas.
   
• Ne pas utiliser de fonctions dépréciées !
  
  
• En règle générale, ce n'est pas parce qu'une requête marche qu'il faut s'arrêter. Il faut tester différentes possibilités et évaluer leur rapidité.
   
• Utiliser l'instruction EXPLAIN qui permet d'obtenir des informations sur le déroulement de la requête pour évaluer son efficacité. Voir la documentation.

En vrac

• Récupérer l'id d'un enregistrement inséré ou modifié. La clause returning permet de retourner un ou plusieurs champs lors d'une insertion ou d'un update.

Par exemple, la requête "INSERT INTO client(nom, prenom) VALUES ("DURAND", "Jean-pierre") RETURNING id_client" va renvoyer le numéro de client (champ de numérotation automatique).

• Faire des statistiques en utilisant des conditions. Lorsqu'on utilise la clause GROUP BY dans une requête de sélection, on peut faire des statistiques grâce aux fonctions d'aggrégation (sum, count...). L'utilisation la plus connue est celle d'un champ : sum(population) pour faire la somme des populations par exemples.

Mais on peut faire des choses beaucoup plus compliquées en utilisant des expressions.

Rien ne vaut un exemple. Supposons donc une table des communes qui contient les champs code_insee, nom_commune, nom_canton, nom_departement, nom_region, population et statut_administratif.

La requête suivante,

SELECT nom_region, count(*) as nb_communes, count(DISTINCT nom_departement) AS nb_deps, count(DISTINCT nom_canton) as nb_cantons, sum(case when statut_administratif="Préfecture" then population else 0 end) as pop_prefectures  FROM communes GROUP BY nom_region

va renvoyer pour chaque région le nom de la région, le nombre de communes, le nombre de départements, le nombre de cantons et le total de la population des préfectures de la région.

On peut aussi imaginer faire des statistiques sur un champ contenant la date d'insertion (champ date_insertion) pour évaluer l'activité d'insertion dans une table.

SELECT count(case when date_insertion > CURRENT_DATE - interval '1 month' then 1 else NULL end) as insertions_mois, count(case when date_insertion > CURRENT_DATE - integer '7' then 1 else NULL end) as insertions_semaine, count(case when date_insertion = CURRENT_DATE then 1 else NULL end) as insertions_jour FROM ma_table

La requête ci dessus renverra donc le nombre d'enregistrements insérés dans la table durant le dernier mois, la dernière semaine et aujourd'hui.

Bref, utiliser ces conditions permet d'éviter de faire de multiples requêtes ou des requêtes imbriquées complexes.

• En cas d'insertion massive dans une table, il peut être performant de supprimer les index avant puis de les recréer après. Néanmoins, la rapidité offerte dans les requêtes de sélection par ces index disparaît pendant ce laps de temps. On peut aussi utiliser les instructions SQL BEGIN et COMMIT de part et d'autres des requêtes d'insertion pour que la validation ne se fasse pas pour chaque insertion mais après. On peut enfin créer une instruction préparée (instruction SQL PREPARE) qu'on utilisera ensuite avec la commande SQL EXECUTE.
  
  
• lister les tables, index... d'une base. A l'aide de requête sur les tables systèmes, on va pouvoir lister les index, les tables, les colonnes, les schémas... (tables pg_table, pg_class, pg_namespace...).

Conclusion

Si vous avez vos astuces ou vos bonnes pratiques, n'hésitez pas à les laisser en commentaire.