LEÇON 2 : Gestion des Contraintes et Index - Explications Détaillées

2.1 Auto-Incrémentation : SERIAL vs IDENTITY

Comprendre le Problème des Identifiants

Imaginez que vous gérez une bibliothèque. Chaque livre doit avoir un numéro unique pour l’identifier. Vous pourriez décider manuellement “ce livre aura le numéro 1, celui-ci le numéro 2”, mais c’est très fastidieux et source d’erreurs. Vous pourriez oublier où vous en êtes et attribuer deux fois le même numéro.

Dans une base de données, c’est exactement le même problème. Lorsque vous ajoutez un nouveau client, une nouvelle commande ou n’importe quel enregistrement, il faut lui donner un identifiant unique. Faire cela manuellement serait une catastrophe, surtout avec des milliers d’ajouts par jour.

C’est là qu’intervient l’auto-incrémentation : le système génère automatiquement le prochain numéro disponible, un peu comme un distributeur de tickets dans une file d’attente.

MySQL et AUTO_INCREMENT

MySQL utilise une approche simple appelée AUTO_INCREMENT. Quand vous créez une table, vous marquez une colonne comme AUTO_INCREMENT :

CREATE TABLE customers (
    customer_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_name VARCHAR(200)
);

Que se passe-t-il ici ? Le mot-clé AUTO_INCREMENT dit à MySQL : “Je ne veux pas gérer moi-même ces numéros. Toi, MySQL, tu t’en occupes. Commence à 1, et à chaque nouvel enregistrement, incrémente de 1.”

Quand vous insérez un client, vous n’avez pas besoin de spécifier le customer_id :

INSERT INTO customers (company_name) VALUES ('Entreprise ABC');
-- MySQL attribue automatiquement customer_id = 1

INSERT INTO customers (company_name) VALUES ('Société XYZ');
-- MySQL attribue automatiquement customer_id = 2

C’est simple, efficace, et vous ne pouvez pas vous tromper. MySQL garantit que chaque numéro n’est utilisé qu’une seule fois.

PostgreSQL - Première Méthode : SERIAL

PostgreSQL a longtemps utilisé un système appelé SERIAL. C’est similaire à AUTO_INCREMENT, mais avec une différence importante : en coulisses, PostgreSQL crée un objet séparé appelé “séquence”.

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    company_name VARCHAR(200) NOT NULL
);

Quand vous écrivez SERIAL, PostgreSQL fait en réalité deux choses pour vous :

1. Il crée une séquence (un compteur) nommée automatiquement customers_customer_id_seq
2. Il configure la colonne pour utiliser cette séquence

C’est équivalent à écrire :

-- D'abord, créer le compteur
CREATE SEQUENCE customers_customer_id_seq;

-- Ensuite, créer la table qui utilise ce compteur
CREATE TABLE customers (
    customer_id INT DEFAULT nextval('customers_customer_id_seq') PRIMARY KEY,
    company_name VARCHAR(200) NOT NULL
);

La fonction nextval() signifie “next value” (valeur suivante). Chaque fois qu’elle est appelée, elle retourne le numéro suivant de la séquence.

Pourquoi cette complexité ? Parce que les séquences sont des objets indépendants qui peuvent être partagés, réutilisés, ou manipulés directement. C’est plus flexible, mais aussi plus complexe.

PostgreSQL - Deuxième Méthode : IDENTITY (Moderne)

À partir de PostgreSQL 10, une nouvelle méthode a été introduite : IDENTITY. C’est la méthode recommandée aujourd’hui car elle suit le standard SQL et est plus cohérente.

CREATE TABLE customers (
    customer_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    company_name VARCHAR(200) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

Le mot-clé GENERATED ALWAYS AS IDENTITY signifie : “Cette colonne est toujours générée automatiquement comme identité”. Vous ne pouvez pas insérer manuellement une valeur dans cette colonne (sauf si vous utilisez une commande spéciale pour forcer).

Il existe aussi une variante plus permissive :

CREATE TABLE orders (
    order_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    order_number VARCHAR(50) UNIQUE NOT NULL
);

GENERATED BY DEFAULT AS IDENTITY permet d’insérer manuellement une valeur si besoin, mais si vous ne le faites pas, PostgreSQL génère automatiquement le numéro. C’est utile lors de migrations de données ou d’imports.

La différence entre les deux :

• ALWAYS : Interdit strictement l’insertion manuelle (sécurité maximale)
• BY DEFAULT : Autorise l’insertion manuelle si nécessaire (flexibilité)

Exemple Pratique : Système Multi-Tenant

Un système multi-tenant, c’est une application où plusieurs entreprises (tenants) partagent la même base de données, mais leurs données sont complètement isolées. Pensez à un logiciel de comptabilité en ligne où chaque entreprise cliente a ses propres données.

CREATE TABLE tenants (
    tenant_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_slug VARCHAR(50) UNIQUE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

La table tenants stocke les informations sur chaque entreprise cliente. Chaque tenant reçoit un ID unique automatique. Le tenant_slug est un identifiant court et lisible, par exemple “entreprise-abc” qui pourrait apparaître dans les URLs.

CREATE TABLE tenant_users (
    user_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id BIGINT NOT NULL REFERENCES tenants(tenant_id) ON DELETE CASCADE,
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(tenant_id, email)
);

Cette table stocke les utilisateurs. Chaque utilisateur appartient à un tenant. La contrainte UNIQUE(tenant_id, email) signifie : “Un email doit être unique au sein d’un tenant, mais deux tenants différents peuvent avoir le même email”. Par exemple, “admin@example.com” peut exister dans l’entreprise A et dans l’entreprise B, mais pas deux fois dans l’entreprise A.

Le ON DELETE CASCADE signifie : si vous supprimez un tenant (l’entreprise ferme son compte), tous ses utilisateurs sont automatiquement supprimés aussi. C’est logique : sans entreprise, les comptes utilisateurs n’ont plus de sens.

CREATE TABLE tenant_data (
    record_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id BIGINT NOT NULL REFERENCES tenants(tenant_id) ON DELETE CASCADE,
    data_type VARCHAR(50) NOT NULL,
    data_json JSONB NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

Cette table stocke des données génériques au format JSON. Le champ data_type pourrait contenir “invoice”, “product”, “customer”, etc. C’est un design flexible qui permet de stocker différents types de données dans la même table.

Les index qui suivent sont cruciaux pour la performance :

CREATE INDEX idx_tenant_users_tenant ON tenant_users (tenant_id, user_id);
CREATE INDEX idx_tenant_data_tenant ON tenant_data (tenant_id, data_type);

Pourquoi ces index ? Parce que presque toutes les requêtes dans un système multi-tenant commencent par “pour le tenant X, trouve…”. Sans index sur tenant_id, PostgreSQL devrait scanner toute la table pour trouver les données d’un tenant spécifique. Avec l’index, il va directement au bon endroit.

Manipulation Avancée des Séquences

Parfois, vous devez manipuler directement les séquences. Voici des cas réels :

Réinitialiser une séquence après un import :

Imaginez que vous importez des données d’un ancien système. Vous insérez des clients avec les IDs 1, 2, 3… jusqu’à 5000. Mais PostgreSQL ne sait pas que vous êtes déjà à 5000. Si vous insérez ensuite un nouveau client sans spécifier l’ID, PostgreSQL essaiera d’utiliser 1, puis 2, puis 3… et échouera à chaque fois car ces IDs existent déjà.

La solution :

SELECT setval('customers_customer_id_seq', (SELECT MAX(customer_id) FROM customers));

Cette commande dit : “Règle la séquence au maximum actuel des IDs dans la table”. Maintenant, le prochain ID sera correctement 5001.

Voir la valeur actuelle sans l’incrémenter :

SELECT currval('customers_customer_id_seq');

Cela retourne la dernière valeur utilisée dans votre session. C’est utile après une insertion pour savoir quel ID a été attribué.

Obtenir et incrémenter manuellement :

SELECT nextval('customers_customer_id_seq');

Cela retourne la prochaine valeur et incrémente le compteur. Attention : cette valeur est maintenant “consommée”. Si vous ne l’utilisez pas, il y aura un trou dans la numérotation.

Séquence partagée entre plusieurs tables :

Parfois, vous voulez que plusieurs tables partagent la même numérotation. Par exemple, vous voulez que factures et devis aient une numérotation commune :

CREATE SEQUENCE global_id_seq START WITH 1000 INCREMENT BY 1;

CREATE TABLE invoices (
    id BIGINT DEFAULT nextval('global_id_seq') PRIMARY KEY,
    invoice_number VARCHAR(50)
);

CREATE TABLE quotes (
    id BIGINT DEFAULT nextval('global_id_seq') PRIMARY KEY,
    quote_number VARCHAR(50)
);

Maintenant, si vous créez un devis (ID 1000), puis une facture (ID 1001), puis un autre devis (ID 1002), tout partage la même séquence. C’est utile pour garantir qu’aucun document n’aura jamais le même numéro qu’un autre, quel que soit son type.

---

2.2 Index : Performance et Stratégies

Comprendre le Problème de Performance

Imaginez que vous avez une bibliothèque avec 10 millions de livres, tous mélangés sur les étagères sans aucun ordre. Quelqu’un vous demande un livre sur PostgreSQL. Sans système d’organisation, vous devez regarder chaque livre un par un. Cela pourrait prendre des jours.

Maintenant, imaginez que vous avez un catalogue : tous les livres sont listés par sujet, par auteur, par titre. Vous ouvrez la section “Informatique > Bases de données > PostgreSQL” et vous trouvez immédiatement ce que vous cherchez en quelques secondes.

Dans une base de données, c’est exactement le même principe. Un index est comme un catalogue qui permet de trouver rapidement les données sans avoir à scanner toute la table.

Comment Fonctionne un Index B-tree

L’index B-tree (arbre équilibré) est le type d’index par défaut et le plus utilisé. Il fonctionne comme un arbre de décision :

Imaginez une table de produits avec des prix entre 1€ et 1000€. Sans index, chercher tous les produits entre 100€ et 200€ nécessite de lire chaque ligne de la table. Avec un index B-tree sur le prix :

1. PostgreSQL va à la racine de l’arbre
2. L’arbre lui dit : “Les prix < 500€ sont à gauche, les prix >= 500€ sont à droite”
3. PostgreSQL va à gauche
4. Le sous-arbre lui dit : “Les prix < 250€ sont à gauche, les prix >= 250€ sont à droite”
5. Il continue jusqu’à trouver exactement la plage 100-200€
6. Il lit seulement ces lignes

Au lieu de lire 10 millions de lignes, il en lit peut-être 10 000. C’est 1000 fois plus rapide.

Création d’Index Simples

CREATE TABLE products (
    product_id BIGSERIAL PRIMARY KEY,
    sku VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(200) NOT NULL,
    category_id BIGINT NOT NULL,
    price NUMERIC(10,2) NOT NULL,
    stock_quantity INT NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

Cette table représente des produits dans un site e-commerce. Créons des index pour les requêtes courantes :

CREATE INDEX idx_products_category ON products (category_id);

Cet index permet de trouver rapidement tous les produits d’une catégorie spécifique. Sans lui, la requête SELECT * FROM products WHERE category_id = 5 devrait lire toute la table. Avec l’index, PostgreSQL va directement aux produits de la catégorie 5.

CREATE INDEX idx_products_price ON products (price);

Cet index permet les recherches par prix : “produits entre 10€ et 50€”, “produits < 100€”, etc. C’est crucial pour les filtres de prix dans une boutique en ligne.

CREATE INDEX idx_products_name ON products (name);

Cet index permet de trier rapidement par nom ou de rechercher par nom exact. Attention : il ne permet PAS les recherches “contient” comme WHERE name LIKE '%smartphone%' (nous verrons ça plus tard avec les index trigrammes).

Index Composites : L’Ordre est Crucial

Un index composite combine plusieurs colonnes. C’est comme un annuaire téléphonique trié d’abord par nom de famille, puis par prénom.

CREATE INDEX idx_products_category_price ON products (category_id, price);

Cet index est trié d’abord par catégorie, puis par prix au sein de chaque catégorie. Il est utile pour :

1. Filtrer sur les deux colonnes :

SELECT * FROM products WHERE category_id = 5 AND price BETWEEN 100 AND 200;

Très rapide : PostgreSQL va directement à la catégorie 5, puis dans cette catégorie aux produits entre 100€ et 200€.

2. Filtrer sur la première colonne seulement :

SELECT * FROM products WHERE category_id = 5;

Toujours rapide : PostgreSQL utilise le début de l’index (la catégorie).

3. MAIS PAS sur la deuxième colonne seule :

SELECT * FROM products WHERE price BETWEEN 100 AND 200;

Lent : PostgreSQL ne peut pas utiliser cet index car il est trié d’abord par catégorie. C’est comme chercher un prénom dans l’annuaire sans connaître le nom de famille : impossible sans tout lire.

Règle d’or : dans un index composite, l’ordre des colonnes est fondamental. Mettez en premier la colonne la plus utilisée seule, puis les colonnes secondaires.

Index Partiels : Économiser l’Espace

Un index partiel n’indexe qu’une partie des données. C’est comme créer un catalogue uniquement pour les livres en français, si vous ne cherchez jamais les autres langues.

CREATE INDEX idx_products_active_category ON products (category_id)
WHERE is_active = TRUE;

Cet index ne contient que les produits actifs. Pourquoi c’est utile ?

1. Économie d’espace : Si 90% de vos produits sont archivés (is_active = FALSE), l’index sera 10 fois plus petit
2. Performance accrue : Un index plus petit tient mieux en mémoire et est plus rapide à parcourir
3. Requêtes ciblées : La plupart de vos requêtes cherchent uniquement les produits actifs

Exemple de requête bénéficiant de cet index :

SELECT * FROM products WHERE category_id = 5 AND is_active = TRUE;

PostgreSQL utilise l’index partiel qui est petit et rapide, au lieu d’un index complet qui serait beaucoup plus gros.

Autre exemple pratique :

CREATE INDEX idx_orders_recent ON orders (created_at DESC)
WHERE created_at > NOW() - INTERVAL '6 months';

Cet index ne couvre que les 6 derniers mois de commandes. Si vous avez 10 ans d’historique mais que 99% de vos requêtes portent sur les commandes récentes, cet index sera beaucoup plus efficace qu’un index complet.

Un dernier exemple :

CREATE INDEX idx_products_low_stock ON products (product_id, stock_quantity)
WHERE stock_quantity < 10 AND is_active = TRUE;

Cet index aide à trouver rapidement les produits en rupture de stock imminente. Si vous n’avez que 100 produits en stock faible sur 1 million de produits, cet index sera 10 000 fois plus petit qu’un index complet.

Index GIN : Pour les Données Complexes

GIN signifie “Generalized Inverted Index” (Index Inversé Généralisé). Il est spécialisé pour les recherches dans des types de données complexes comme le texte, les tableaux JSON, ou les tableaux classiques.

Recherche Full-Text (Texte Intégral) :

La recherche full-text permet de chercher des mots dans du texte, comme un moteur de recherche. Créons une table d’articles :

CREATE TABLE articles (
    article_id BIGSERIAL PRIMARY KEY,
    title VARCHAR(500) NOT NULL,
    content TEXT NOT NULL,
    tags TEXT[] DEFAULT '{}',
    metadata JSONB,
    search_vector tsvector GENERATED ALWAYS AS (
        to_tsvector('french', coalesce(title, '') || ' ' || coalesce(content, ''))
    ) STORED
);

La colonne search_vector est spéciale : elle contient une version “tokenisée” du titre et du contenu. “Tokenisée” signifie que le texte est découpé en mots, nettoyé (suppression de la ponctuation, conversion en minuscules), et transformé en racines. Par exemple, “marchons”, “marché”, “marcher” deviennent tous “march”.

Le mot-clé GENERATED ALWAYS AS ... STORED signifie que cette colonne est automatiquement calculée à partir du titre et du contenu, et stockée physiquement dans la table. Quand vous insérez ou modifiez un article, PostgreSQL recalcule automatiquement le search_vector.

Créons l’index GIN :

CREATE INDEX idx_articles_fts ON articles USING GIN (search_vector);

Maintenant, vous pouvez chercher rapidement dans des millions d’articles :

SELECT * FROM articles
WHERE search_vector @@ to_tsquery('french', 'postgresql & performance');

Cette requête trouve tous les articles contenant les mots “postgresql” ET “performance” (l’opérateur & signifie ET). L’opérateur @@ est l’opérateur de recherche full-text.

Pourquoi c’est rapide ? L’index GIN crée essentiellement un dictionnaire : pour chaque mot qui apparaît dans vos articles, il liste tous les articles qui le contiennent. C’est comme l’index à la fin d’un livre qui dit “PostgreSQL : voir pages 45, 67, 123”.

Index sur les Tableaux :

CREATE INDEX idx_articles_tags ON articles USING GIN (tags);

Si vos articles ont des tags ([“tutorial”, “database”, “advanced”]), cet index permet de trouver rapidement tous les articles avec un tag spécifique :

SELECT * FROM articles WHERE 'tutorial' = ANY(tags);

Ou tous les articles avec plusieurs tags :

SELECT * FROM articles WHERE tags @> ARRAY['tutorial', 'database'];

L’opérateur @> signifie “contient”. Cette requête trouve les articles qui ont à la fois le tag “tutorial” et “database”.

Index sur JSONB :

CREATE INDEX idx_articles_metadata ON articles USING GIN (metadata);

Si vous stockez des métadonnées en JSON, par exemple :

{
    "author": "John Doe",
    "category": "Technology",
    "difficulty": "advanced"
}

L’index GIN permet des requêtes rapides :

SELECT * FROM articles WHERE metadata @> '{"author": "John Doe"}';

Cette requête trouve tous les articles dont les métadonnées contiennent “author”: “John Doe”. Sans l’index GIN, PostgreSQL devrait décomposer et analyser le JSON de chaque ligne, ce qui serait extrêmement lent.

Exemple Complet : Marketplace E-commerce

Imaginons une marketplace avec des millions de produits. Chaque produit a des attributs complexes (couleur, taille, marque…) stockés en JSON :

CREATE TABLE marketplace_products (
    product_id BIGSERIAL PRIMARY KEY,
    seller_id BIGINT NOT NULL,
    title VARCHAR(500) NOT NULL,
    description TEXT,
    category_path VARCHAR(500),
    price NUMERIC(10,2) NOT NULL CHECK (price >= 0),
    sale_price NUMERIC(10,2) CHECK (sale_price >= 0 AND sale_price < price),
    stock INT NOT NULL CHECK (stock >= 0),
    attributes JSONB DEFAULT '{}',
    tags TEXT[] DEFAULT '{}',
    is_active BOOLEAN DEFAULT TRUE,
    rating_avg NUMERIC(3,2) DEFAULT 0,
    sales_count INT DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

Le champ category_path pourrait contenir “Electronics/Computers/Laptops” pour représenter une hiérarchie de catégories. Le attributes JSON pourrait être :

{
    "color": "red",
    "size": "XL",
    "brand": "Nike",
    "material": "cotton"
}

Créons une stratégie d’indexation complète :

1. Index B-tree pour filtrages simples :

CREATE INDEX idx_mp_seller ON marketplace_products (seller_id) WHERE is_active = TRUE;

Index partiel sur le vendeur, uniquement pour les produits actifs. Utile pour “Voir tous les produits de ce vendeur”.

CREATE INDEX idx_mp_category ON marketplace_products (category_path);

Index sur la catégorie pour “Voir tous les laptops”.

2. Index composite pour tri + filtre :

CREATE INDEX idx_mp_active_rating ON marketplace_products (is_active, rating_avg DESC, sales_count DESC);

Cet index composite permet d’afficher les produits actifs triés par note et nombre de ventes. C’est parfait pour la page d’accueil “Meilleures ventes”.

3. Index partiel pour pages populaires :

CREATE INDEX idx_mp_bestsellers ON marketplace_products (sales_count DESC, rating_avg DESC)
WHERE is_active = TRUE AND stock > 0;

Index ultra-ciblé pour la page “Bestsellers” : uniquement les produits actifs et en stock, triés par ventes puis par note. Cet index est petit et super rapide.

4. Index GIN pour recherche avancée :

CREATE INDEX idx_mp_attributes ON marketplace_products USING GIN (attributes);
CREATE INDEX idx_mp_tags ON marketplace_products USING GIN (tags);

Ces index permettent de filtrer par attributs : “Montrer tous les produits Nike rouges en taille XL”.

5. Index trigramme pour recherche floue :

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_mp_title_trgm ON marketplace_products USING GIN (title gin_trgm_ops);

Les trigrammes permettent la recherche floue. Si quelqu’un cherche “samsoung” (faute de frappe), PostgreSQL peut trouver “Samsung”. Ou si quelqu’un cherche “smart” il trouve “smartphone”, “smartwatch”, etc.

Un trigramme décompose les mots en groupes de 3 caractères. “Samsung” devient “Sam”, “ams”, “msu”, “sun”, “ung”. Cette technique permet de trouver des correspondances approximatives.

Requête complexe utilisant tous ces index :

EXPLAIN ANALYZE
SELECT product_id, title, price, rating_avg
FROM marketplace_products
WHERE
    is_active = TRUE
    AND stock > 0
    AND category_path LIKE 'Electronics%'
    AND price BETWEEN 100 AND 500
    AND attributes @> '{"brand": "Samsung"}'
ORDER BY rating_avg DESC, sales_count DESC
LIMIT 20;

Cette requête trouve les 20 meilleurs produits Samsung en électronique, actifs, en stock, entre 100€ et 500€, triés par note. Sans les index, cette requête pourrait prendre plusieurs secondes sur des millions de produits. Avec les bons index, elle s’exécute en quelques millisecondes.

Le EXPLAIN ANALYZE montre exactement quels index PostgreSQL utilise et combien de temps prend chaque étape. C’est l’outil indispensable pour optimiser vos requêtes.

Monitoring des Index en Production

En production, il faut surveiller vos index pour deux raisons :

1. Détecter les index inutilisés qui gaspillent de l’espace disque et ralentissent les insertions
2. Identifier les index les plus utilisés pour comprendre quelles requêtes sont courantes

Trouver les index jamais utilisés :

SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan as scans,
    pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Cette requête liste tous les index qui n’ont jamais été utilisés (idx_scan = 0), triés par taille. Si vous voyez un index de 5 Go qui n’a jamais servi depuis 6 mois, supprimez-le ! Il ne fait que gaspiller de l’espace et ralentir les insertions.

Voir les index les plus sollicités :

SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC
LIMIT 20;

Cette requête montre les 20 index les plus utilisés. Si un index a des millions de idx_scan, c’est qu’il est crucial pour la performance de votre application.

Analyser la taille des tables et index :

SELECT
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) as table_size,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) as indexes_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Cette requête montre la taille totale de chaque table (données + index), la taille des données seules, et la taille de tous les index. Parfois, les index prennent plus de place que les données elles-mêmes ! C’est normal si vous avez beaucoup d’index, mais il faut le surveiller.

Par exemple, vous pourriez voir :

• Table products : 10 Go de données, 15 Go d’index
• Table orders : 50 Go de données, 30 Go d’index

Si les index sont trop volumineux, envisagez de supprimer ceux qui ne servent pas ou de créer des index partiels plutôt que complets.

---

2.3 Clés Étrangères : Intégrité Référentielle

Comprendre le Problème d’Intégrité

Imaginez un système de facturation. Vous avez des clients et des factures. Chaque facture appartient à un client. Maintenant, imaginez que quelqu’un supprime un client alors qu’il a 50 factures. Que deviennent ces factures ? Elles pointent vers un client qui n’existe plus. C’est ce qu’on appelle une “référence orpheline” ou “dangling reference”.

C’est un problème grave car :

1. Vos factures deviennent incohérentes (elles référencent un client inexistant)
2. Afficher une facture plantera si vous essayez d’afficher le nom du client
3. Vos rapports seront faux (CA par client, etc.)

Les clés étrangères (Foreign Keys) résolvent ce problème en établissant et en maintenant automatiquement les relations entre les tables.

Contraintes ON DELETE et ON UPDATE

Une clé étrangère peut avoir différents comportements quand la donnée référencée est modifiée ou supprimée. C’est comme définir une règle : “Si le parent change ou disparaît, que fait-on des enfants ?”

Voici les options principales :

RESTRICT : Interdit la suppression/modification si des enregistrements dépendants existent. C’est la protection maximale.

CASCADE : Supprime/modifie automatiquement les enregistrements dépendants. C’est la suppression en cascade.

SET NULL : Met la référence à NULL dans les enregistrements dépendants. Garde les enregistrements mais casse le lien.

SET DEFAULT : Met une valeur par défaut dans les enregistrements dépendants.

NO ACTION : Similaire à RESTRICT, mais vérifié à la fin de la transaction (avancé).

Exemple Complet : Système de Facturation

CREATE TABLE customers (
    customer_id BIGSERIAL PRIMARY KEY,
    company_name VARCHAR(200) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    is_active BOOLEAN DEFAULT TRUE
);

Table simple de clients. Chaque client a un ID unique, un nom d’entreprise et un email unique.

CREATE TABLE invoices (
    invoice_id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    invoice_number VARCHAR(50) UNIQUE NOT NULL,
    invoice_date DATE NOT NULL DEFAULT CURRENT_DATE,
    total_amount NUMERIC(12,2) NOT NULL DEFAULT 0,
    status VARCHAR(20) CHECK (status IN ('draft', 'sent', 'paid', 'cancelled')),

    CONSTRAINT fk_invoice_customer
        FOREIGN KEY (customer_i)
REFERENCES customers(customer_id)
ON DELETE RESTRICT
ON UPDATE CASCADE
);

Analysons la clé étrangère :

FOREIGN KEY (customer_id) : Cette colonne est une référence vers une autre table.

REFERENCES customers(customer_id) : Elle pointe vers la colonne customer_id de la table customers.

ON DELETE RESTRICT : Si vous essayez de supprimer un client qui a des factures, PostgreSQL refuse l’opération avec une erreur. C’est logique : vous ne pouvez pas supprimer un client qui a des factures historiques. Vous devez d’abord soit supprimer les factures, soit les transférer à un autre client, soit désactiver le client au lieu de le supprimer.

Tentative de suppression :

DELETE FROM customers WHERE customer_id = 5;
-- Erreur : UPDATE ou DELETE sur la table « customers » viole la contrainte de clé étrangère

ON UPDATE CASCADE : Si vous changez l’ID d’un client (rare mais possible), toutes les factures sont automatiquement mises à jour pour pointer vers le nouvel ID. C’est pratique pour les migrations.

UPDATE customers SET customer_id = 9999 WHERE customer_id = 5;
-- Toutes les factures avec customer_id = 5 deviennent automatiquement customer_id = 9999

Maintenant, les lignes de facture :

CREATE TABLE invoice_lines (
    line_id BIGSERIAL PRIMARY KEY,
    invoice_id BIGINT NOT NULL,
    product_name VARCHAR(200) NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    unit_price NUMERIC(10,2) NOT NULL CHECK (unit_price >= 0),
    line_total NUMERIC(12,2) GENERATED ALWAYS AS (quantity * unit_price) STORED,

    CONSTRAINT fk_line_invoice
        FOREIGN KEY (invoice_id)
        REFERENCES invoices(invoice_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Ici, ON DELETE CASCADE a du sens : si vous supprimez une facture, vous voulez supprimer toutes ses lignes. Une ligne de facture sans facture n’a aucun sens. C’est la suppression en cascade.

DELETE FROM invoices WHERE invoice_id = 100;
-- Toutes les lignes de la facture 100 sont automatiquement supprimées

La colonne line_total est intéressante : GENERATED ALWAYS AS (quantity * unit_price) STORED signifie qu’elle est automatiquement calculée. Vous ne pouvez pas la définir manuellement. PostgreSQL multiplie toujours la quantité par le prix unitaire. Si vous modifiez la quantité ou le prix, le total est recalculé automatiquement.

Les index pour les clés étrangères :

CREATE INDEX idx_invoices_customer ON invoices (customer_id);
CREATE INDEX idx_lines_invoice ON invoice_lines (invoice_id);

Ces index sont CRUCIAUX pour les performances. Voici pourquoi :

Sans index sur customer_id dans la table invoices, afficher toutes les factures d’un client nécessite un scan complet de la table des factures. Avec 10 millions de factures, c’est lent.

De plus, sans index, PostgreSQL doit scanner toute la table pour vérifier les contraintes de clé étrangère lors des suppressions. Si vous supprimez un client (même si c’est refusé par RESTRICT), PostgreSQL doit d’abord vérifier qu’il n’a pas de factures. Sans index, il doit lire toutes les factures.

Règle d’or : Toujours créer un index sur les colonnes de clé étrangère.

Exemple Réel : Blog avec Commentaires

Un blog est un exemple classique de relations complexes avec différents besoins de gestion :

CREATE TABLE blog_users (
    user_id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    deleted_at TIMESTAMP WITH TIME ZONE
);

Le champ deleted_at implémente le “soft delete” (suppression douce). Au lieu de supprimer réellement un utilisateur, vous marquez la date de suppression. Cela permet de garder l’historique tout en “cachant” l’utilisateur.

CREATE TABLE blog_posts (
    post_id BIGSERIAL PRIMARY KEY,
    author_id BIGINT NOT NULL,
    title VARCHAR(500) NOT NULL,
    content TEXT NOT NULL,
    published_at TIMESTAMP WITH TIME ZONE,

    CONSTRAINT fk_post_author
        FOREIGN KEY (author_id)
        REFERENCES blog_users(user_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

ON DELETE SET NULL : Si un utilisateur est supprimé (vraie suppression, pas soft delete), ses articles restent mais author_id devient NULL. Pourquoi ? Parce que les articles ont une valeur historique. Vous ne voulez pas perdre tout le contenu juste parce qu’un utilisateur a fermé son compte. Vous pouvez afficher “[Utilisateur supprimé]” à la place du nom.

CREATE TABLE blog_comments (
    comment_id BIGSERIAL PRIMARY KEY,
    post_id BIGINT NOT NULL,
    user_id BIGINT,
    parent_comment_id BIGINT,
    comment_text TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    CONSTRAINT fk_comment_post
        FOREIGN KEY (post_id)
        REFERENCES blog_posts(post_id)
        ON DELETE CASCADE,

    CONSTRAINT fk_comment_user
        FOREIGN KEY (user_id)
        REFERENCES blog_users(user_id)
        ON DELETE SET NULL,

    CONSTRAINT fk_comment_parent
        FOREIGN KEY (parent_comment_id)
        REFERENCES blog_comments(comment_id)
        ON DELETE CASCADE
);

Analysons chaque clé étrangère :

fk_comment_post avec CASCADE : Si l’article est supprimé, tous ses commentaires disparaissent. C’est logique : des commentaires sans article n’ont aucun sens.

fk_comment_user avec SET NULL : Si l’utilisateur est supprimé, ses commentaires restent mais user_id devient NULL. Vous pouvez afficher “[Utilisateur supprimé]” comme auteur. Le contenu du commentaire reste.

fk_comment_parent avec CASCADE : C’est une auto-référence pour les commentaires imbriqués (réponses). Si vous supprimez un commentaire parent, toutes les réponses sont supprimées aussi. Sinon, vous auriez des réponses à un commentaire qui n’existe plus.

Exemple de hiérarchie de commentaires : Comment 1 (comment_id = 10, parent_comment_id = NULL) └─ Réponse 1.1 (comment_id = 11, parent_comment_id = 10) └─ Réponse 1.1.1 (comment_id = 12, parent_comment_id = 11) └─ Réponse 1.2 (comment_id = 13, parent_comment_id = 10)

Si vous supprimez le comment 10, les comments 11, 12 et 13 sont automatiquement supprimés par la cascade.

Les index nécessaires :

CREATE INDEX idx_posts_author ON blog_posts (author_id);
CREATE INDEX idx_comments_post ON blog_comments (post_id);
CREATE INDEX idx_comments_parent ON blog_comments (parent_comment_id);

Ces index permettent :

• Trouver rapidement tous les articles d’un auteur
• Afficher tous les commentaires d’un article
• Charger les réponses à un commentaire

Vérification de l’Intégrité en Production

En production, il est important de pouvoir inspecter vos contraintes de clé étrangère :

SELECT
    tc.table_name,
    kcu.column_name,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name,
    rc.delete_rule,
    rc.update_rule
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
  ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
  ON ccu.constraint_name = tc.constraint_name
JOIN information_schema.referential_constraints AS rc
  ON rc.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND tc.table_name = 'invoices';

Cette requête complexe interroge les tables système de PostgreSQL pour lister toutes les clés étrangères de la table invoices. Vous verrez :

• Quelle colonne est la clé étrangère
• Vers quelle table elle pointe
• Quelle est la règle ON DELETE (CASCADE, RESTRICT, etc.)
• Quelle est la règle ON UPDATE

Manipulation avancée des contraintes :

Parfois, en maintenance, vous devez temporairement désactiver les contraintes. Par exemple, vous importez des données historiques dans un ordre spécifique et les contraintes vous bloquent.

-- DANGER : Supprimer une contrainte
ALTER TABLE invoice_lines DROP CONSTRAINT fk_line_invoice;

Maintenant, vous pouvez insérer des lignes de facture même si la facture n’existe pas. C’est dangereux mais parfois nécessaire pour des imports complexes.

-- Réactiver la contrainte après la maintenance
ALTER TABLE invoice_lines ADD CONSTRAINT fk_line_invoice
    FOREIGN KEY (invoice_id) REFERENCES invoices(invoice_id) ON DELETE CASCADE;

Quand vous réactivez la contrainte, PostgreSQL vérifie TOUTES les lignes existantes. Si certaines violent la contrainte (référencent une facture inexistante), la commande échoue. Vous devez d’abord corriger les données.

Une alternative plus sûre :

-- Valider la contrainte sans bloquer la table
ALTER TABLE invoice_lines ADD CONSTRAINT fk_line_invoice
    FOREIGN KEY (invoice_id) REFERENCES invoices(invoice_id)
    ON DELETE CASCADE
    NOT VALID;

-- Puis valider en arrière-plan
ALTER TABLE invoice_lines VALIDATE CONSTRAINT fk_line_invoice;

L’option NOT VALID ajoute la contrainte mais ne vérifie pas les données existantes immédiatement. Ensuite, VALIDATE CONSTRAINT vérifie les données sans bloquer les insertions/modifications, ce qui est plus sûr en production.

---