Conception de tables PostgreSQL

Quand utiliser

• Concevoir un nouveau schéma PostgreSQL, ou en examiner un avant sa mise en production.
• Choisir les types de colonnes, clés, contraintes ou index pour PostgreSQL spécifiquement.
• Décider si et comment partitionner une grande table, ou comment stocker des données semi-structurées.
• Planifier un changement de schéma sur une base de données en production sans interruption de service.

Les règles et points de décision pour un schéma PostgreSQL. Le catalogue complet des types de données, les patterns de charge (update-intensif, insert-intensif, upsert, évolution de schéma), les extensions, l'indexation JSONB et les exemples DDL détaillés se trouvent dans references/details.md ; consultez-le quand une section ci-dessous y renvoie.

Règles fondamentales

• Définir une PRIMARY KEY pour les tables de référence (users, orders, etc.). Pas toujours nécessaire pour les données time-series/événements/logs. Si utilisée, préférer BIGINT GENERATED ALWAYS AS IDENTITY ; utiliser UUID seulement quand l'unicité globale/l'opacité est requise.
• Normaliser d'abord (jusqu'à 3NF) pour éliminer la redondance de données et les anomalies de mise à jour ; dénormaliser uniquement pour des lectures à fort ROI mesurées où les problèmes de performance des jointures sont avérés.
• Ajouter NOT NULL partout où c'est sémantiquement requis ; utiliser des DEFAULTs pour les valeurs courantes.
• Créer des index pour les chemins d'accès que vous interrogez réellement : PK/unique (auto), colonnes FK (manuel !), filtres/tri fréquents et clés de jointure.
• Préférer TIMESTAMPTZ pour le temps d'événement ; NUMERIC pour l'argent ; TEXT pour les chaînes ; BIGINT pour les entiers ; DOUBLE PRECISION pour les flottants (ou NUMERIC pour l'arithmétique décimale exacte).

Pièges PostgreSQL

• Identifiants : non guillemets → minuscules. Éviter les noms guillemets/mixtes ; utiliser snake_case.
• Unique + NULL : UNIQUE permet plusieurs NULL. Utiliser UNIQUE NULLS NOT DISTINCT (...) (PG15+) pour restreindre à un NULL.
• Index FK : PostgreSQL n'indexe pas automatiquement les colonnes FK. Les ajouter.
• Pas de coercitions silencieuses : les débordements de longueur/précision génèrent des erreurs (pas de troncature). Insérer 999 dans NUMERIC(2,0) échoue, contrairement aux bases de données qui tronquent ou arrondissent silencieusement.
• Les séquences/identité ont des lacunes (normal ; ne pas « corriger »). Les rollbacks, crashes et transactions concurrentes laissent des lacunes (1, 2, 5, 6...).
• Stockage heap : pas de PK en cluster par défaut ; CLUSTER est une réorganisation ponctuelle, non maintenue lors des futures insertions.
• MVCC : les mises à jour/suppressions laissent des dead tuples ; vacuum les gère — concevoir pour éviter le churn de lignes larges actives.

Types de données

• IDs : BIGINT GENERATED ALWAYS AS IDENTITY ; UUID pour les IDs distribués ou opaques, générés avec uuidv7() (PG18+) ou gen_random_uuid().
• Nombres : BIGINT sauf si le stockage est critique ; DOUBLE PRECISION plutôt que REAL ; NUMERIC(p,s) pour l'argent et les décimales exactes.
• Chaînes : TEXT, avec CHECK (LENGTH(col) <= n) quand une limite est nécessaire ; BYTEA pour le binaire. Recherches insensibles à la casse : index d'expression sur LOWER(col), ou CITEXT quand une contrainte doit être insensible à la casse.
• Temps : TIMESTAMPTZ, DATE, INTERVAL. now() est le début de la transaction ; clock_timestamp() est l'horloge murale.
• Booléens : BOOLEAN NOT NULL sauf si un état tri-valué est requis.
• Enums : CREATE TYPE ... AS ENUM seulement pour les petits ensembles stables ; les valeurs métier évolutives reçoivent TEXT + CHECK ou une table de lookup.
• JSONB plutôt que JSON, indexé avec GIN, pour les attributs optionnels/semi-structurés uniquement.
• Les types tableaux, plages, réseau, géométrique, recherche textuelle, domaine, composite et vecteur, plus le stockage TOAST et le contrôle de collation : voir references/details.md.

Types à éviter

Contraintes

• PK : UNIQUE + NOT NULL implicite ; crée un index B-tree.
• FK : spécifier ON DELETE/UPDATE (CASCADE, RESTRICT, SET NULL, SET DEFAULT). Indexer la colonne de référence. Utiliser DEFERRABLE INITIALLY DEFERRED pour les dépendances circulaires vérifiées à la validation.
• UNIQUE : crée un index B-tree ; permet plusieurs NULL sauf NULLS NOT DISTINCT (PG15+). Préférer NULLS NOT DISTINCT sauf si les NULL dupliqués sont voulus.
• CHECK : au niveau ligne ; NULL passe (logique à trois valeurs). Combiner avec NOT NULL : price NUMERIC NOT NULL CHECK (price > 0).
• EXCLUDE : prévient les chevauchements avec opérateurs, ex. EXCLUDE USING gist (room_id WITH =, booking_period WITH &&) stop la double réservation. Nécessite un type compatible GiST.

Indexation

• B-tree : défaut pour égalité/plage (=, <, >, BETWEEN, ORDER BY).
• Composite : règle de préfixe le plus à gauche (WHERE a = ? AND b > ? utilise (a,b) ; WHERE b = ? non). Colonnes les plus sélectives d'abord.
• Couvrant : CREATE INDEX ON tbl (id) INCLUDE (name, email) pour les scans index-only.
• Partiel : sous-ensembles actifs, CREATE INDEX ON tbl (user_id) WHERE status = 'active'.
• Expression : CREATE INDEX ON tbl (LOWER(email)) ; la requête doit utiliser la même expression.
• GIN : contenance/existence JSONB, tableaux, recherche textuelle. GiST : plages, géométrie, contraintes d'exclusion.
• BRIN : données grandes et naturellement ordonnées (time-series) à coût de stockage minimal ; efficace quand l'ordre disque corrèle avec la colonne indexée.

Partitionnement

• Utiliser pour les grandes tables (>100M lignes) dont les requêtes filtrent systématiquement sur la clé de partitionnement, ou où la maintenance (pruning, remplacement en masse) suit une clé.
• RANGE pour time-series (PARTITION BY RANGE (created_at) ; TimescaleDB l'automatise avec rétention et compression), LIST pour les valeurs discrètes, HASH pour la distribution uniforme sans clé naturelle.
• Exclusion de contrainte : le planificateur élague les partitions via leurs contraintes CHECK ; le partitionnement déclaratif (PG10+) les crée pour vous.
• Préférer le partitionnement déclaratif ou les hypertables. Ne PAS utiliser l'héritage de tables.
• Limitations : pas de contraintes UNIQUE globales — inclure la clé de partitionnement dans PK/UNIQUE. FKs depuis des tables partitionnées nécessitent PG11+, FKs référençant une table partitionnée nécessitent PG12+ ; sur les anciennes versions, utiliser des triggers.

Exemples

CREATE TABLE users (
  user_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX ON users (LOWER(email));
CREATE INDEX ON users (created_at);

CREATE TABLE orders (
  order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(user_id),
  status TEXT NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','PAID','CANCELED')),
  total NUMERIC(10,2) NOT NULL CHECK (total > 0),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ON orders (user_id);
CREATE INDEX ON orders (created_at);

-- attributs JSONB avec un scalaire généré et indexable
CREATE TABLE profiles (
  user_id BIGINT PRIMARY KEY REFERENCES users(user_id),
  attrs JSONB NOT NULL DEFAULT '{}',
  theme TEXT GENERATED ALWAYS AS (attrs->>'theme') STORED
);
CREATE INDEX profiles_attrs_gin ON profiles USING GIN (attrs);

Aller plus loin

references/details.md contient la matière que ce fichier ne fait que nommer :

• Le catalogue complet des types de données : stockage TOAST, collations, tableaux, plages, réseau, géométrique, recherche textuelle, domaines, composites, vecteurs.
• Types de tables (TEMPORARY, UNLOGGED) et sécurité au niveau ligne.
• Notes sur les contraintes et index, et DDL de partitionnement pour RANGE, LIST et HASH.
• Patterns de charge : update-intensif, insert-intensif, design upsert, évolution de schéma sûre.
• Colonnes générées et extensions (pg_trgm, citext, timescaledb, postgis, pgvector, et plus).
• Stratégies d'indexation JSONB, incluant jsonb_path_ops et colonnes B-tree extraites.