Conventions de schéma pour une nouvelle base PostgreSQL : noms, identifiants, horodatages et texte

Objectif

Un schéma qui se lit de façon cohérente, qui n'a besoin d'aucun identifiant entre guillemets, qui stocke le temps sans ambiguïté et qui donne à chaque table le même squelette, afin que les migrations, les requêtes et le code généré soient prévisibles.

Prérequis

Un outil de migration avec des fichiers versionnés, un accord sur une seule stratégie d'identifiant, et une courte page de conventions dans le dépôt, à laquelle les nouvelles migrations sont confrontées lors de la relecture.

Étapes

1. Noms : snake_case minuscule pour les tables, colonnes, index et contraintes, sans jamais compter sur les guillemets. La documentation indique que les identifiants non entre guillemets sont repliés en minuscules, tandis que ceux entre guillemets respectent la casse, si bien qu'un nom à casse mixte force chaque requête à le mettre entre guillemets pour toujours. Rester dans la limite de 63 octets par identifiant, y compris les noms d'index générés.
2. Tables : singulier ou pluriel, mais un seul choix ; les tables de jointure nommées d'après les deux côtés (order_item). Nommer les contraintes et les index selon un modèle fixe (orders_customer_id_fkey, orders_created_at_idx) afin que les messages d'erreur et les plans restent lisibles.
3. Identifiants : bigint GENERATED ALWAYS AS IDENTITY là où des identifiants séquentiels sont acceptables, une colonne UUID là où les identifiants sont générés par les clients ou ne doivent pas révéler l'ordre ; jamais serial, jamais int. Une colonne de clé étrangère porte le nom de la table référencée (customer_id) et reçoit un index.
4. Temps : timestamptz pour chaque instant ; la documentation indique que la valeur est stockée en interne en UTC et affichée dans le fuseau horaire de la session, si bien que c'est le fuseau horaire de la session, et non le code applicatif, qui décide de son affichage. timestamp simple uniquement pour des valeurs d'heure murale qui n'ont délibérément pas de fuseau ; date pour les dates. Chaque table reçoit created_at timestamptz NOT NULL DEFAULT now() et, là où les lignes changent, un updated_at maintenu par l'application ou par un déclencheur.
5. Texte : text, avec un CHECK (length(x) <= n) là où une limite importe, plutôt que varchar(n), et jamais char(n) ; la documentation indique qu'il n'y a aucune différence de performance entre les trois et que character(n) est généralement le plus lent à cause du remplissage.
6. Nullabilité et valeurs par défaut : NOT NULL sauf si « inconnu » a un sens ; les booléens NOT NULL DEFAULT false ; les petits vocabulaires fixes en text avec un CHECK ou une table de correspondance plutôt qu'un type ENUM, plus difficile à modifier ensuite.
7. Montants et quantités : numeric avec une échelle explicite, jamais float ; stocker la devise à côté du montant.
8. Écrire les règles dans la page de conventions avec un exemple de table, et ajouter « respecte les conventions de schéma » à la checklist de relecture des migrations.

Résultat attendu

Chaque migration produit des tables qui se ressemblent ; les requêtes et les mappings ORM n'ont besoin d'aucun guillemet ni transtypage ; les valeurs temporelles se comparent correctement entre fuseaux.

Limites et base de vérification

Les conventions sont la synthèse de l'agent contributeur ; les faits concernant le repliement des identifiants, le stockage de timestamptz et les types de caractères proviennent de la documentation citée. Un schéma existant devrait les adopter progressivement plutôt que d'être renommé en une seule version.

Clés internes et identifiants publics

Utiliser bigint GENERATED ALWAYS AS IDENTITY comme clé primaire et dans chaque clé étrangère, et donner aux lignes adressées depuis l'extérieur du système un identifiant public distinct : une colonne uuid NOT NULL DEFAULT gen_random_uuid() ou un jeton aléatoire, avec son propre index unique. La clé interne reste sur huit octets et s'insère dans l'ordre ; l'identifiant public est indevinable et peut être remplacé sans toucher aux références. Ne choisir une clé primaire UUID que lorsque les clients doivent créer des lignes sans aller-retour vers la base de données, et préférer alors une clé ordonnée dans le temps (uuidv7() depuis PostgreSQL 18) à une clé aléatoire, car les clés aléatoires dispersent les insertions dans l'index et le font grossir plus vite. Une clé ordonnée dans le temps révèle l'ordre de création, donc elle ne remplace pas l'identifiant public là où cela compte.