Conception de tables PostgreSQL

Règles fondamentales

Définissez une PRIMARY KEY pour les tables de référence (users, orders, etc.). Pas toujours nécessaire pour les données de séries temporelles/événements/logs. Si utilisée, préférez BIGINT GENERATED ALWAYS AS IDENTITY ; n'utilisez UUID que si l'unicité globale/l'opacité est nécessaire.
Normalisez d'abord (jusqu'à 3NF) pour éliminer la redondance de données et les anomalies de mise à jour ; dénormalisez uniquement pour des lectures mesurées et à fort ROI où les problèmes de performance des jointures sont démontrés. La dénormalisation prématurée crée une charge de maintenance.
Ajoutez NOT NULL partout où c'est sémantiquement requis ; utilisez des DEFAULTs pour les valeurs courantes.
Créez des indexes pour les chemins d'accès que vous interrogez réellement : PK/unique (auto), colonnes FK (manuel !), filtres/tris fréquents et clés de jointure.
Préférez 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 quoted → minuscules. Évitez les noms quoted/casse mixte. Convention : utilisez snake_case pour les noms de tables/colonnes.
UNIQUE + NULLs : UNIQUE permet plusieurs NULLs. Utilisez UNIQUE (...) NULLS NOT DISTINCT (PG15+) pour restreindre à un seul NULL.
Index FK : PostgreSQL n'indexe pas automatiquement les colonnes FK. Ajoutez-les.
Pas de coercitions silencieuses : les débordements de longueur/précision génèrent des erreurs (pas de troncature). Exemple : insérer 999 dans NUMERIC(2,0) échoue avec une erreur, contrairement à certaines bases qui tronquent ou arrondissent silencieusement.
Les séquences/identités ont des lacunes (normal ; ne « corrigez » pas). Les rollbacks, crashs et transactions concurrentes créent des lacunes dans les séquences d'ID (1, 2, 5, 6...). C'est un comportement attendu—ne tentez pas de rendre les IDs consécutifs.
Stockage heap : pas de PK clustered par défaut (contrairement à SQL Server/MySQL InnoDB) ; CLUSTER est une réorganisation ponctuelle, non maintenue lors des insertions ultérieures. L'ordre des lignes sur disque est l'ordre d'insertion sauf si explicitement clustered.
MVCC : les mises à jour/suppressions laissent des tuples morts ; vacuum les traite—concevez pour éviter le churn de lignes larges chaudes.

Types de données

IDs : BIGINT GENERATED ALWAYS AS IDENTITY préféré (GENERATED BY DEFAULT aussi correct) ; UUID lors de fusion/fédération/utilisation dans un système distribué ou pour des IDs opaques. Générez avec uuidv7() (préféré si utilisant PG18+) ou gen_random_uuid() (si utilisant une version plus ancienne de PG).
Entiers : préférez BIGINT à moins que l'espace de stockage soit critique ; INTEGER pour les plages plus petites ; évitez SMALLINT sauf si contraint.
Flottants : préférez DOUBLE PRECISION à REAL à moins que l'espace de stockage soit critique. Utilisez NUMERIC pour l'arithmétique décimale exacte.
Chaînes : préférez TEXT ; si les limites de longueur sont nécessaires, utilisez CHECK (LENGTH(col) <= n) au lieu de VARCHAR(n) ; évitez CHAR(n). Utilisez BYTEA pour les données binaires. Les grandes chaînes/données binaires (>2 KB par défaut) sont automatiquement stockées en TOAST avec compression. Stockage TOAST : PLAIN (pas de TOAST), EXTENDED (compression + hors ligne), EXTERNAL (hors ligne, pas de compression), MAIN (compression, garder en ligne si possible). EXTENDED par défaut généralement optimal. Contrôlez avec ALTER TABLE tbl ALTER COLUMN col SET STORAGE strategy et ALTER TABLE tbl SET (toast_tuple_target = 4096) pour le seuil. Insensible à la casse : pour la gestion des locales/accents utilisez les collations non déterministes ; pour l'ASCII simple utilisez des index d'expression sur LOWER(col) (préféré sauf si la colonne a besoin d'une PK/FK/UNIQUE insensible à la casse) ou CITEXT.
Argent : NUMERIC(p,s) (jamais flottant).
Temps : TIMESTAMPTZ pour les timestamps ; DATE pour date seulement ; INTERVAL pour les durées. Évitez TIMESTAMP (sans fuseau horaire). Utilisez now() pour le début de la transaction, clock_timestamp() pour l'heure murale actuelle.
Booléens : BOOLEAN avec contrainte NOT NULL sauf si des valeurs à trois états sont requises.
Enums : CREATE TYPE ... AS ENUM pour les petits ensembles stables (ex. états US, jours de la semaine). Pour les valeurs pilotées par la logique métier et évolutives (ex. statuts de commande) → utilisez TEXT (ou INT) + CHECK ou table de lookup.
Tableaux : TEXT[], INTEGER[], etc. Utilisez pour les listes ordonnées où vous interrogez les éléments. Indexez avec GIN pour les requêtes de contenance (@>, <@) et chevauchement (&&). Accès : arr[1] (1-indexé), arr[1:3] (slicing). Bon pour les tags, catégories ; évitez pour les relations—utilisez plutôt les tables de jonction. Syntaxe littérale : '{val1,val2}' ou ARRAY[val1,val2].
Types de plage : daterange, numrange, tstzrange pour les intervalles. Support chevauchement (&&), contenance (@>), opérateurs. Indexez avec GiST. Bon pour la planification, versioning, plages numériques. Choisissez un schéma de bornes et utilisez-le de manière cohérente ; préférez [) (inclusif/exclusif) par défaut.
Types réseau : INET pour les adresses IP, CIDR pour les plages réseau, MACADDR pour les adresses MAC. Support opérateurs réseau (<<, >>, &&).
Types géométriques : POINT, LINE, POLYGON, CIRCLE pour les données spatiales 2D. Indexez avec GiST. Considérez PostGIS pour les fonctionnalités spatiales avancées.
Recherche textuelle : TSVECTOR pour les documents de recherche plein texte, TSQUERY pour les requêtes de recherche. Indexez tsvector avec GIN. Spécifiez toujours la langue : to_tsvector('english', col) et to_tsquery('english', 'query'). N'utilisez jamais les versions à un seul argument. Ceci s'applique à la fois aux expressions d'index et aux requêtes.
Types domaine : CREATE DOMAIN email AS TEXT CHECK (VALUE ~ '^[^@]+@[^@]+$') pour les types personnalisés réutilisables avec validation. Applique les contraintes sur les tables.
Types composites : CREATE TYPE address AS (street TEXT, city TEXT, zip TEXT) pour les données structurées dans les colonnes. Accès avec la syntaxe (col).field.
JSONB : préféré à JSON ; indexez avec GIN. Utilisez uniquement pour les attributs optionnels/semi-structurés. N'utilisez JSON QUE si l'ordre d'origine du contenu DOIT être préservé.
Types vectoriels : type vector de pgvector pour la recherche de similarité vectorielle pour les embeddings.

Ne pas utiliser les types de données suivants

N'utilisez PAS timestamp (sans fuseau horaire) ; utilisez timestamptz à la place.
N'utilisez PAS char(n) ou varchar(n) ; utilisez text à la place.
N'utilisez PAS le type money ; utilisez numeric à la place.
N'utilisez PAS le type timetz ; utilisez timestamptz à la place.
N'utilisez PAS timestamptz(0) ou toute autre spécification de précision ; utilisez timestamptz à la place.
N'utilisez PAS le type serial ; utilisez generated always as identity à la place.

Types de table

Régulière : par défaut ; entièrement durable, enregistrée.
TEMPORARY : portée de session, auto-supprimée, non enregistrée. Plus rapide pour le travail temporaire.
UNLOGGED : persistante mais non crash-safe. Écritures plus rapides ; bonne pour les caches/staging.

Sécurité au niveau des lignes

Activez avec ALTER TABLE tbl ENABLE ROW LEVEL SECURITY. Créez des policies : CREATE POLICY user_access ON orders FOR SELECT TO app_users USING (user_id = current_user_id()). Contrôle d'accès basé sur les utilisateurs intégré au niveau des lignes.

Contraintes

PK : UNIQUE + NOT NULL implicites ; crée un index B-tree.
FK : spécifiez l'action ON DELETE/UPDATE (CASCADE, RESTRICT, SET NULL, SET DEFAULT). Ajoutez un index explicite sur la colonne référencée—accélère les jointures et évite les problèmes de verrouillage lors des suppressions/mises à jour du parent. Utilisez DEFERRABLE INITIALLY DEFERRED pour les dépendances circulaires de FK vérifiées à la fin de la transaction.
UNIQUE : crée un index B-tree ; permet plusieurs NULLs sauf NULLS NOT DISTINCT (PG15+). Comportement standard : (1, NULL) et (1, NULL) sont autorisés. Avec NULLS NOT DISTINCT : un seul (1, NULL) autorisé. Préférez NULLS NOT DISTINCT à moins d'avoir spécifiquement besoin de NULLs dupliqués.
CHECK : contraintes au niveau ligne ; les valeurs NULL passent la vérification (logique à trois valeurs). Exemple : CHECK (price > 0) autorise les prix NULL. Combinez avec NOT NULL pour appliquer : price NUMERIC NOT NULL CHECK (price > 0).
EXCLUDE : empêche les valeurs chevauchantes en utilisant des opérateurs. EXCLUDE USING gist (room_id WITH =, booking_period WITH &&) empêche la double réservation de chambres. Nécessite un type d'index approprié (souvent GiST).

Indexation

B-tree : par défaut pour les requêtes d'égalité/plage (=, <, >, BETWEEN, ORDER BY)
Composite : l'ordre compte—index utilisé si égalité sur le préfixe le plus à gauche (WHERE a = ? AND b > ? utilise l'index sur (a,b), mais WHERE b = ? non). Mettez les colonnes les plus sélectives/filtrées fréquemment en premier.
Covering : CREATE INDEX ON tbl (id) INCLUDE (name, email) - inclut les colonnes non-clé pour les scans index-only sans accéder à la table.
Partiel : pour les sous-ensembles chauds (WHERE status = 'active' → CREATE INDEX ON tbl (user_id) WHERE status = 'active'). Toute requête avec status = 'active' peut utiliser cet index.
Expression : pour les clés de recherche calculées (CREATE INDEX ON tbl (LOWER(email))). L'expression doit correspondre exactement dans la clause WHERE : WHERE LOWER(email) = 'user@example.com'.
GIN : contenance/existence JSONB, tableaux (@>, ?), recherche plein texte (@@)
GiST : plages, géométrie, contraintes d'exclusion
BRIN : données très grandes, naturellement ordonnées (séries temporelles)—surcharge de stockage minimale. Efficace quand l'ordre des lignes sur disque se corrèle à la colonne indexée (ordre d'insertion ou après CLUSTER).

Partitionnement

Utilisez pour les très grandes tables (>100M lignes) où les requêtes filtrent constamment sur la clé de partition (souvent temps/date).
Utilisation alternative : utilisez pour les tables où les tâches de maintenance dictent les données, ex. données purgées ou remplacées en masse périodiquement.
RANGE : courant pour les séries temporelles (PARTITION BY RANGE (created_at)). Créez les partitions : CREATE TABLE logs_2024_01 PARTITION OF logs FOR VALUES FROM ('2024-01-01') TO ('2024-02-01'). TimescaleDB automatise le partitionnement basé sur le temps ou l'ID avec les policies de rétention et compression.
LIST : pour les valeurs discrètes (PARTITION BY LIST (region)). Exemple : FOR VALUES IN ('us-east', 'us-west').
HASH : pour la distribution uniforme quand aucune clé naturelle (PARTITION BY HASH (user_id)). Crée N partitions avec modulus.
Constraint exclusion : nécessite des contraintes CHECK sur les partitions pour le planificateur de requêtes à élaguer. Auto-créées pour le partitionnement déclaratif (PG10+).
Préférez le partitionnement déclaratif ou les hypertables. N'utilisez PAS l'héritage de table.
Limitations : pas de contraintes UNIQUE globales—incluez la clé de partition dans PK/UNIQUE. Les FKs des tables partitionnées ne sont pas supportées ; utilisez des triggers.

Considérations spéciales

Tables à mise à jour intensive

Séparez les colonnes chaudes/froides—mettez les colonnes fréquemment mises à jour dans une table séparée pour minimiser la fragmentation.
Utilisez fillfactor=90 pour laisser de la place aux mises à jour HOT qui évitent la maintenance des index.
Évitez de mettre à jour les colonnes indexées—empêche les mises à jour HOT bénéfiques.
Partitionnez par patterns de mise à jour—séparez les lignes fréquemment mises à jour dans une partition différente des données stables.

Charges de travail à insertion intensive

Minimisez les index—créez uniquement ceux que vous interrogez ; chaque index ralentit les insertions.
Utilisez COPY ou INSERT multi-lignes au lieu des insertions single-ligne.
Tables UNLOGGED pour les données de staging reconstruisibles—écritures beaucoup plus rapides.
Déférez la création d'index pour les chargements en masse—supprimez l'index, chargez les données, recréez les indexes.
Partitionnez par temps/hash pour distribuer la charge. TimescaleDB automatise le partitionnement et la compression des données à insertion intensive.
Utilisez une clé naturelle pour la primary key comme (timestamp, device_id) si forcer l'unicité globale est important ; nombreuses tables à insertion intensive n'ont pas du tout besoin de primary key.
Si vous avez besoin d'une clé substitut, Préférez BIGINT GENERATED ALWAYS AS IDENTITY à UUID.

Conception Upsert-friendly

Nécessite un index UNIQUE sur les colonnes de conflit—ON CONFLICT (col1, col2) a besoin d'un index unique correspondant exactement (les index partiels ne fonctionnent pas).
Utilisez EXCLUDED.column pour référencer les valeurs qui auraient été insérées ; mettez à jour uniquement les colonnes qui ont réellement changé pour réduire la surcharge d'écriture.
DO NOTHING plus rapide que DO UPDATE quand aucune mise à jour réelle n'est nécessaire.

Évolution de schéma sûre

DDL transactionnelle : la plupart des opérations DDL peuvent s'exécuter dans les transactions et être annulées—BEGIN; ALTER TABLE...; ROLLBACK; pour des tests sûrs.
Création d'index concurrente : CREATE INDEX CONCURRENTLY évite de bloquer les écritures mais ne peut pas s'exécuter dans les transactions.
Les valeurs par défaut volatiles causent des réécritures : ajouter des colonnes NOT NULL avec des valeurs par défaut volatiles (ex. now(), gen_random_uuid()) réécrit la table entière. Les valeurs par défaut non-volatiles sont rapides.
Supprimez les contraintes avant les colonnes : ALTER TABLE DROP CONSTRAINT puis DROP COLUMN pour éviter les problèmes de dépendances.
Changements de signature de fonction : CREATE OR REPLACE avec des arguments différents crée des surcharges, pas des remplacements. Supprimez l'ancienne version si aucune surcharge désirée.

Colonnes générées

... GENERATED ALWAYS AS (<expr>) STORED pour les champs calculés, indexables. PG18+ ajoute les colonnes VIRTUAL (calculées à la lecture, non stockées).

Extensions

pgcrypto : crypt() pour le hachage de mot de passe.
uuid-ossp : fonctions UUID alternatives ; préférez pgcrypto pour les nouveaux projets.
pg_trgm : recherche textuelle floue avec opérateur %, fonction similarity(). Indexez avec GIN pour l'accélération LIKE '%pattern%'.
citext : type texte insensible à la casse. Préférez les index d'expression sur LOWER(col) sauf si vous avez besoin de contraintes insensibles à la casse.
btree_gin/btree_gist : permettent les index de types mixtes (ex. index GIN sur colonnes JSONB et text).
hstore : paires clé-valeur ; largement remplacé par JSONB mais utile pour les mappages de chaînes simples.
timescaledb : essentiel pour les séries temporelles—partitionnement automatisé, rétention, compression, agrégats continus.
postgis : support géospatial complet au-delà des types géométriques basiques—essentiel pour les applications basées sur la localisation.
pgvector : recherche de similarité vectorielle pour les embeddings.
pgaudit : enregistrement d'audit pour toute activité de base de données.

Guidance JSONB

Préférez JSONB avec index GIN.
Par défaut : CREATE INDEX ON tbl USING GIN (jsonb_col); → accélère :
- Contenance jsonb_col @> '{"k":"v"}'
- Existence de clé jsonb_col ? 'k', toutes/certaines clés ?\|, ?&
- Contenance de chemin sur les docs imbriquées
- Disjonction jsonb_col @> ANY(ARRAY['{"status":"active"}', '{"status":"pending"}'])
Charges de travail @> intensives : considérez opclass jsonb_path_ops pour les indexes plus petits/rapides d'existence uniquement :
- CREATE INDEX ON tbl USING GIN (jsonb_col jsonb_path_ops);
- Trade-off : perd le support des requêtes d'existence de clé (?, ?|, ?&)—supporte uniquement la contenance (@>)
Égalité/plage sur un champ scalaire spécifique : extrayez et indexez avec B-tree (colonne générée ou expression) :
- ALTER TABLE tbl ADD COLUMN price INT GENERATED ALWAYS AS ((jsonb_col->>'price')::INT) STORED;
- CREATE INDEX ON tbl (price);
- Préférez les requêtes comme WHERE price BETWEEN 100 AND 500 (utilise B-tree) à WHERE (jsonb_col->>'price')::INT BETWEEN 100 AND 500 sans index.
Tableaux à l'intérieur de JSONB : utilisez GIN + @> pour la contenance (ex. tags). Considérez jsonb_path_ops si vous faites uniquement la contenance.
Gardez les relations essentielles dans les tables ; utilisez JSONB pour les attributs optionnels/variables.
Utilisez les contraintes pour limiter les valeurs JSONB autorisées dans une colonne ex. config JSONB NOT NULL CHECK(jsonb_typeof(config) = 'object')

Exemples

Utilisateurs

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);

Commandes

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);

JSONB

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);

postgresql-table-design