- Un article qui rassemble des patterns pratiques pour utiliser Postgres de manière plus productive et plus sûre
- Chaque pattern est modeste en soi, mais leur accumulation finit par faire une grande différence
Utiliser des clés primaires UUID
- Les UUID étant aléatoires, ils ont des inconvénients en matière de tri et de performance des index
- Ils occupent plus d’espace que des ID numériques
- Mais ils offrent les avantages suivants
- Il est possible de générer un UUID sans se connecter à la base de données
- Ils peuvent être exposés à l’extérieur en toute sécurité
- On peut générer automatiquement un UUID comme clé primaire avec
gen_random_uuid()
Toujours ajouter les champs created_at et updated_at
- Lors du débogage, il est très utile de savoir quand un enregistrement a été créé et modifié
updated_at peut être configuré pour être mis à jour automatiquement via un trigger- La fonction n’a besoin d’être créée qu’une seule fois, mais le trigger doit être appliqué à chaque table
Définir on update/delete restrict sur les clés étrangères
- Lors de la définition des contraintes de clé étrangère, il faut impérativement utiliser
on update restrict on delete restrict
- Cela évite qu’une suppression de données ne déclenche accidentellement des suppressions en cascade
- Le stockage coûte peu cher, mais la récupération des données est très difficile ; il vaut donc mieux rester prudent
Recommander l’usage des schémas
- Le schéma par défaut est
public, mais à mesure que l’application grossit, il vaut mieux la séparer dans des schémas distincts
- Les schémas fonctionnent comme des espaces de noms, et il est possible de faire des jointures entre différents schémas
- Plus le nombre de tables augmente, plus l’usage des schémas améliore la lisibilité et la maintenabilité
Utiliser le pattern des tables d’enum
- Plutôt que le type enum de PostgreSQL ou une check constraint, utiliser des tables d’enum est plus flexible
- Si les valeurs d’enum sont gérées dans une table séparée, on peut ajouter des métadonnées ou étendre facilement les valeurs d’enum
- Les contraintes sont conservées en référant aux valeurs de la table d’enum via une clé étrangère
Nommer les tables au singulier
- Il est préférable de nommer les tables au singulier plutôt qu’au pluriel
- Lors de l’écriture des requêtes, le singulier est plus clair, tandis que le pluriel peut créer des ambiguïtés de sens ou de possession
Nommer les tables de jointure de manière mécanique
- Pour les relations many-to-many, il est plus sûr et plus clair de nommer les tables de jointure en concaténant les deux noms de table
- Exemple :
person_pet
- Ajouter un index unique sur la combinaison pour éviter les doublons
Utiliser le soft delete plutôt que la suppression
- Au lieu de supprimer réellement les données, il est préférable d’utiliser un champ timestamp comme
revoked_at pour indiquer le moment de la suppression
- Cela permet de savoir non seulement si l’élément a été supprimé, mais aussi quand il l’a été
- Un timestamp fournit plus d’informations qu’une valeur booléenne
Représenter les statuts avec une table de logs
- Au lieu de représenter un statut dans une seule colonne, stocker l’historique des changements de statut dans une table séparée
- Le moment où le statut survient est indiqué explicitement dans la colonne
valid_at
- Pour récupérer rapidement le statut le plus récent, on met en place un flag
latest ainsi qu’un index unique et un trigger
- C’est particulièrement avantageux dans le traitement d’événements asynchrones ou dans les situations où l’ordre peut être mélangé
Ajouter un system_id pour les lignes spéciales
- En plus des tables d’enum, il peut être nécessaire d’avoir certaines « lignes système »
- Ajouter un champ texte
system_id nullable et définir un index unique
system_id permet de retrouver clairement certaines lignes spécifiques
Utiliser les vues avec parcimonie
- Les vues sont utiles pour abstraire des requêtes complexes, mais elles sont difficiles à maintenir
- Si une colonne est supprimée, il faut recréer la vue
- Créer des vues sur d’autres vues pose des problèmes de performance et de lisibilité
- Il faut donc les utiliser avec discernement, uniquement lorsque c’est nécessaire
Tirer activement parti des requêtes JSON
- Postgres est très puissant non seulement pour stocker du JSON, mais aussi pour renvoyer des requêtes au format JSON
- Il peut renvoyer des relations imbriquées sous forme de JSON en une seule requête
- Cela permet de récupérer toutes les données nécessaires d’un coup, sans problème de N+1
- Inconvénients : perte des informations de type, et nécessité de charger l’ensemble des données en mémoire d’un seul coup
- Les avantages en termes de performance ou de structure sont toutefois plus importants
4 commentaires
> Nommer les tables de jointure de manière systématique
Je trouve que le simple fait d’avoir une règle de nommage comme celle-ci, c’est déjà une bonne chose~
Si l’on envisage UUID7, cela ne permettrait-il pas un tri chronologique ?
L’article sur l’utilisation de PostgreSQL avec des UUID comme clés primaires vaut aussi le détour.
La méthode consistant à ajouter un timestamp lors d’un soft delete est bonne. Si on utilise un UUID comme clé primaire, on ne peut pas trier par ordre chronologique, donc utiliser un snowflake id ou un ulid semble aussi être une bonne option. Dans ce cas, chaque serveur doit toutefois disposer d’un sequence number.