Technologie - PostgreSQL : concevoir, exploiter et faire évoluer vos bases de données

Qu'est-ce que PostgreSQL ?

PostgreSQL est une base de données relationnelle, libre et open source, développée depuis près de quarante ans. Les données sont rangées dans des tables reliées entre elles, et on les interroge en SQL.

Pour une application métier, trois choses comptent surtout :

• les transactions : PostgreSQL respecte les propriétés ACID depuis 2001. Quand une commande enregistre un paiement et retire un article du stock, soit les deux écritures passent, soit aucune ;
• les contraintes : clés primaires et étrangères, unicité, champs obligatoires, et contraintes d'exclusion. La base refuse d'elle-même une ligne qui enfreint une règle, que l'erreur vienne de l'application, d'un script de reprise ou d'une requête lancée à la main ;
• les extensions : on ajoute à la base des types, des index et des fonctions. PostGIS gère les données géographiques, pg_trgm compare des textes qui se ressemblent.

Notre base par défaut avec Django

Tous nos nouveaux projets partent sur PostgreSQL, avec Django pour l'application.

Le module django.contrib.postgres de Django donne accès aux types, aux index et aux contraintes propres à PostgreSQL, et les migrations Django installent les extensions et posent les contraintes comme le reste du schéma. Quand on déploie une nouvelle version de l'application, la base change avec elle, sans manipulation à part.

Ce que nous en faisons sur nos projets

Retrouver un patient malgré les accents et les fautes

Dans Cobra, l'e-CRF d'une cohorte de patients asthmatiques et BPCO, on cherche un patient par son nom. Il arrive qu'on tape « Lefevre » alors que le dossier est enregistré sous « Lefèvre », ou qu'une lettre manque. L'extension unaccent retire les accents avant la comparaison, et pg_trgm mesure la ressemblance entre deux textes à partir de leurs groupes de trois caractères. Le bon patient sort quand même dans les résultats.

Empêcher deux tarifs de se chevaucher

Sur Simplauto, chaque centre de contrôle technique a ses grilles tarifaires et ses ventes flash. Si deux grilles couvrent les mêmes dates pour un même centre, on ne sait plus quel prix afficher. Nous avons écrit cette règle dans la base : avec l'extension btree_gist, une contrainte d'exclusion refuse d'enregistrer une grille dont la période recouvre celle d'une autre grille du même centre. Les ventes flash suivent la même règle.

La recherche géographique

Simplauto utilise aussi PostGIS pour sa recherche géographique. PostGIS ajoute à PostgreSQL des types pour stocker des points, des lignes et des polygones, avec des index et des fonctions de distance : une recherche « autour de ce lieu » s'écrit en SQL et s'appuie sur un index.

Des triggers

Un trigger est une fonction que PostgreSQL exécute lui-même quand une ligne est écrite. Dans Cobra, c'est un trigger qui attribue leur numéro aux patients.

Dans Colibri, l'outil de suivi des patients en pneumologie, l'historique des données est tenu en base par des triggers, posés avec la bibliothèque Django pghistory. Comme c'est la base qui écrit l'historique, une modification y laisse sa trace quel que soit le chemin par lequel elle arrive.

En production

Cloud SQL et PgBouncer

La base de Simplauto tourne sur Cloud SQL, le service PostgreSQL managé de Google Cloud. Devant elle, PgBouncer regroupe les connexions : les processus de l'application se partagent un petit nombre de connexions ouvertes au lieu d'en ouvrir chacun une, ce qui évite de saturer la base quand le trafic monte.

Les sauvegardes

À heure fixe, pg_dump fait une copie logique de la base, c'est-à-dire un fichier qui contient de quoi la recréer, et l'envoie vers du stockage objet. Si un export échoue, une alerte nous prévient. Restaurer cette copie ramène la base à l'heure de l'export, pas à un instant choisi ; pour revenir à une minute précise, il faut l'archivage continu des journaux de PostgreSQL (les WAL), que les services managés comme Cloud SQL proposent.

Une copie anonymisée pour la recette

Chaque nuit, la base de production de Simplauto est copiée vers l'environnement de recette, et les données personnelles sont anonymisées pendant la copie avec datanymizer. On teste ainsi les nouvelles fonctionnalités sur des données qui ont le volume et les cas particuliers de la production, sans y exposer les données des clients.

Ce que Naeka peut faire avec PostgreSQL

Modéliser et contraindre au niveau de la base

Nous partons de vos règles métier : ce qui doit être unique, ce qui ne doit jamais se chevaucher, ce qui doit toujours être renseigné. Celles qui peuvent l'être deviennent des contraintes dans la base, et une donnée incohérente ne peut plus y entrer, même par un import ou un script.

Monter de version

Chaque version majeure de PostgreSQL est maintenue pendant cinq ans, puis ne reçoit plus de correctifs. Nous testons la nouvelle version sur une copie de votre base, avec l'application et ses extensions, puis nous planifions la bascule avec vous.

Sauvegarder et savoir restaurer

Nous mettons en place les sauvegardes et nous surveillons qu'elles réussissent. Nous pouvons aussi organiser des tests de restauration : on recharge une sauvegarde sur une base à part pour vérifier qu'elle est utilisable et mesurer le temps que prend une restauration.

Des copies anonymisées pour les environnements de test

Copier telles quelles les données de production en recette, c'est montrer des données personnelles à tous ceux qui ont accès à cet environnement. Pour respecter le RGPD, nous mettons en place des copies anonymisées, rafraîchies automatiquement.

L'hébergement managé

Cloud SQL chez Google Cloud, ou les bases de données managées d'OVH : nous choisissons l'offre avec vous, nous configurons la base, ses accès et ses sauvegardes, puis nous l'exploitons.

Un projet avec PostgreSQL ?

Une base à concevoir, une version à monter, des sauvegardes à reprendre en main : contactez-nous. On part de votre schéma et de votre hébergement actuels pour décider par quoi commencer. Pour le reste de notre stack, voyez notre page Python.