2.4 Bonnes pratiques

C'est fourni en bonus, pas le temps de l'aborder pendant la formation :)

2.4.1 Bonnes pratiques pour la gestion de BDD dans PostgreSQL/PostGIS

1. Installer et configurer correctement le serveur PostgreSQL

Au moment de l'installation de PostgreSQL, la configuration de base proposée est adaptée à de "petites" installations. Il ne faut donc pas hésiter à modifier certains fichiers de configuration pour une utilisation optimisée à vos besoins.

Deux fichiers de configuration clé dans …/num_version/data/, aussi accessibles via pgAdmin dans l'onglet Fichier, sont à modifier en fonction des besoins :

  • postgresql.conf
    Paramètres de connexion
    port = 5432 (n° de port sur lequel le serveur écoute)
    listen_addresses = * (adresse(s) IP sur laquelle le serveur écoute)
    Encodage des caractères
    client_encoding = latin1 (si nécessaire, sinon laisser utf-8 par défaut)
    Gestions des erreurs (logs)
    Mémoire
    Nettoyage (vacuum automatique)
    autovacuum = on (activé)
    track_counts = on (activé pour que le démon autovacuum fonctionne)
    En cas de modification il est nécessaire de redémarrer PostgreSQL.

  • pg_hba.conf
    Gestion de la sécurité des accès aux données des bases de données . Peut interdire les connexions distantes, limiter les accès aux différentes bases selon les utilisateurs…

2. Recommandations d’optimisation du serveur PostgreSQL-PostGIS

Adapter la configuration de PostgreSQL aux capacités du serveur : modifier postgresql.conf

Configuration de base = usage pour de "petites" BD personnelles
Utiliser PGTune pour réécrire postgresql.conf en fonction des caractéristiques de la machine http://www.portailsig.org/content/optimiser-postgresql-avec-pgtune
Mettre un autre port que 5432
Créer un tablespace sur une autre partition que la partition système
Activer la fonction AUTOVACCUUM pour libérer l’espace disque
Autres : http://www.postgis.fr/chrome/site/docs/workshop-foss4g/doc/tuning.html

Mettre en place de bonnes pratiques de sécurité

Contrôler les accès à la BD pour assurer la protection et l’intégrité des données : modifier pg_hba.conf
Vérifier les « ouvertures » vers l’extérieur
Gérer les rôles (utilisateurs) et leur affecter des droits adaptés (privilèges)
Faire des sauvegardes (dumps) réguliers

3. Gestion des rôles / utilisateurs

La gestion des rôles est très importante dans PostgreSQL. Un rôle est une entité qui peut posséder des objets de la BDD et avoir des droits sur la base et ses objets.

Le superutilisateur postgres (créé par défaut) ne doit servir qu'à l'administration des BDD, pensez à changer son mot de passe choisi par défaut.

Il faut créer des utilisateurs en ajustant leurs droits en fonction de leurs rôles (connexion, ajout/modif./suppr. de données, création bases, etc.).

En savoir plus sur la création de rôles - site postgresql.org.

4. Optimiser la BDD

Séparer les données « métier » des données système dans des schémas différents : il est recommandé de stocker les données thématiques dans un ou des schémas différents du schéma public (créé par défaut) pour une base en production. Ceci permet de séparer le cœur de PostgreSQL (tables systèmes, fonctions...) des données elles-mêmes. L'organisation en schémas multiples permet de simplifier la gestion des droits d'utilisation et de faciliter les mises à jour ainsi que la restauration d'éléments indépendants les uns des autres.

Les noms des BDD, schémas, tables, vues, ..., champs dans PostGIS doivent être en minuscule et sans caractères spéciaux, ni espace, ni accent pour faciliter/optimiser leur utilisation. S'il y a des majuscules dans un nom, il faudra alors obligatoirement l'encadrer par des guillemets doubles (ex : "MaTable"."MonChamp").

Gérer les contraintes des tables
Clés primaires et étrangères
Eviter les erreurs de saisie en gérant les types et contraintes

Optimiser les vitesses d’exécution des requêtes
Utiliser les index spatiaux ou non
Utiliser les fonctions VACCUUM et ANALYZE
Prévoir une réindexation régulière des tables dynamiques

2.4.2 Optimiser les requêtes

1. Eviter les erreurs de syntaxe SQL

Pour éviter toute ambiguïté, il est souvent nécessaire voire obligatoire d’écrire nom_table.nom_champ dans les requêtes SQL. Par défaut PostgreSQL cherche dans le schéma public si le nom du schéma n'est pas précisé.

Si le schéma public n'est pas utilisé, alors il faudra également impérativement mettre dans le FROM nom_schema.nom_table.

Pour assurer la liaison PostGIS-QGIS et l’intégrité des données de la BDD :

  • Vérifier que la vue ou la table possède un identifiant unique et que la colonne géométrie indique un type (point, ligne, polygon,...) et un SRID (code EPSG du système de référence spatiale).

  • Ajouter un index spatial au(x) champ(s) de géométrie qui le nécessite(nt).

2. Optimiser les requêtes

Placer les conditions WHERE les plus « rapides » en premier
Privilégier l’usage des sous-requêtes
Evaluer l’efficacité de la requête via l’instruction EXPLAIN ANALYZE

2.4.3 Travailler avec des vues matérialisées

Une vue matérialisée est un ensemble de données précalculé dérivé d’une spécification de requête (SELECT dans la définition de la vue) et stocké pour une utilisation ultérieure. Les données étant précalculées, l’interrogation d’une vue matérialisée est plus rapide que l’exécution de la requête d’origine. Cette différence de performances peut être significative lorsqu’une requête est exécutée fréquemment ou est suffisamment complexe.

Quelle est la différence entre une vue et une vue matérialisée ?

  • Vue : toujours construite au moment de l’exécution, la vue appelle les tables et effectue des regroupements à chaque appel. Une vue est également utile pour masquer des colonnes de données « sensibles ».
  • Vue matérialisée : a une existence concrète, car les tables sont pré-jointes, les agrégations sont faites et les résultats de la requête sont stockés sur le disque. Elles peuvent être "redondantes" et copiées à partir des tables de base. Elles ont besoin obligatoirement d’un mécanisme de rafraîchissement.

Astuce

Les vues matérialisées sont conçues pour améliorer les performances d’interrogation de charges de travail composées de modèles d’interrogation communs et répétés. Cependant, la matérialisation des résultats intermédiaires entraîne des coûts supplémentaires. En tant que tel, avant de créer des vues matérialisées, vous devez déterminer si les coûts sont compensés par les économies générées par la réutilisation assez fréquente de ces résultats (source : https://docs.snowflake.com/fr/user-guide/views-materialized.html).

Autres ressources et liens utiles

http://rbdd.cnrs.fr/spip.php?article338

http://www.postgis.fr/chrome/site/docs/workshop-foss4g/doc/tuning.html

https://sql.sh/cours

https://www.postgresqltutorial.com