#Persistance des donnéesCopier le lien

Choisissez un SGBD adapté. Un SQL relationnel convient aux invariants forts et aux requêtes expressives ; des bases clé‑valeur ou documents simplifient certains accès à grande échelle mais déplacent des contraintes vers l’application. Écrivez des requêtes lisibles, indexez avec mesure et entourez vos écritures de transactions.

Avant de continuer, vous devez savoir écrire du SQL élémentaire (SELECT, WHERE, JOIN) et avoir lu le chapitre REST pour le schéma des points d’entrée. À la fin de ce chapitre, vous saurez modéliser le fil rouge (users, sessions, posts), le versionner par migrations, indexer selon les requêtes réelles et choisir un niveau d’isolation.

Les index accélèrent les lectures au prix d’écritures plus coûteuses. Un index utile correspond à un prédicat fréquent et sélectif ; un index redondant alourdit sans bénéfice. Sur plusieurs colonnes, l’ordre compte. Mesurez avec des plans d’exécution et supprimez les index inactifs.

Les transactions protègent les invariants. Les niveaux d’isolation influencent les anomalies visibles : lecture sale, lecture non répétable, phantom reads. Par défaut, READ COMMITTED suffit souvent ; REPEATABLE READ ou SERIALIZABLE se réservent aux zones critiques avec parcimonie et des patterns compatibles.

Mini‑exercice : modélisez users, sessions, posts (SQL) et écrivez les requêtes de base (création, lookup, invalidation de session). Ajoutez un index composite pour accélérer SELECT * FROM posts WHERE author_id=? ORDER BY created_at DESC LIMIT ? et expliquez le choix.

#Animation : de la modélisation au backupCopier le lien

#Diagramme : requête dans une transactionCopier le lien

#Diagramme : écriture idempotente (Idempotency-Key)Copier le lien

#Conseils de persistanceCopier le lien

#Modélisation SQL : users, sessions, postsCopier le lien

1-- Utilisateurs2CREATE TABLE users (3  id           BIGSERIAL PRIMARY KEY,4  email        TEXT NOT NULL UNIQUE,5  password_hash TEXT NOT NULL,6  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()7);8 9-- Sessions côté serveur10CREATE TABLE sessions (11  id           UUID PRIMARY KEY,                  -- identifiant de session12  user_id      BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,13  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),14  last_seen_at TIMESTAMPTZ,

#Versionner le schéma par migrationsCopier le lien

Le schéma vit avec l’application. Une migration est un fichier numéroté, atomique, rejouable, qui fait avancer la base d’un état connu au suivant. Jamais de ALTER TABLE manuel en production : la base d’un collègue ou l’environnement de prod divergeraient.

1-- migrations/0001_create_core.sql2BEGIN;3CREATE TABLE users (/* ... */);4CREATE TABLE sessions (/* ... */);5CREATE TABLE posts (/* ... */);6COMMIT;7 8-- migrations/0002_add_posts_version.sql9BEGIN;10ALTER TABLE posts ADD COLUMN version INT NOT NULL DEFAULT 0;11COMMIT;

Une table dédiée suit l’état :

1CREATE TABLE schema_migrations (2  version    INT PRIMARY KEY,3  applied_at TIMESTAMPTZ NOT NULL DEFAULT now()4);

L’outil (node-pg-migrate, prisma migrate, flyway, ou un simple lanceur SQL) applique les versions manquantes dans l’ordre, chaque migration dans sa transaction : si une étape échoue, rien n’est écrit à moitié. Deux règles de survie :

• Les migrations sont immuables une fois appliquées quelque part : corriger un script déjà déployé crée des bases différentes. Écrivez une nouvelle migration.
• Les migrations destructives se font en deux temps : ajouter la nouvelle colonne, migrer les données, basculer le code, puis seulement supprimer l’ancienne colonne dans une migration ultérieure. Un DROP COLUMN immédiat casse le rollback.

#Transactions et anomalies d’isolationCopier le lien

1-- Niveau par défaut souvent suffisant2SET TRANSACTION ISOLATION LEVEL READ COMMITTED;3 4-- Zone critique : passer temporairement en REPEATABLE READ/SERIALIZABLE5BEGIN;6SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;7-- ... opérations critiques ...8COMMIT;

• Lecture non répétable : T1 lit A ; T2 modifie A et commit ; T1 relit A et observe une valeur différente (READ COMMITTED).
• Phantom read : T1 lit COUNT(*) avec un prédicat ; T2 insère une ligne correspondant au prédicat ; T1 relit et observe un nouvel élément (REPEATABLE READ évite non‑répétable mais pas toujours les phantoms selon SGBD).

La correspondance entre anomalies et niveaux se résume ainsi :

Note : sous PostgreSQL, REPEATABLE READ élimine aussi les phantoms par snapshot MVCC, mais peut lever des erreurs de sérialisation (40001) à rejouer. Sous MySQL/InnoDB le comportement diffère légèrement : vérifiez la documentation de votre SGBD.

#Concurrence : éviter la perte de mise à jourCopier le lien

1-- Contrôle d’accès optimiste (OCC) par version2ALTER TABLE posts ADD COLUMN version INT NOT NULL DEFAULT 0;3 4-- Mise à jour sûre5-- application: passe la version lue; si 0 ligne affectée → recharger/relire6UPDATE posts7SET title = $1, body = $2, version = version + 18WHERE id = $id AND version = $current_version;

Si zéro ligne est affectée, un autre écrivain a déjà fait avancer la version : l’application relit, fusionne et rejoue, exactement la logique ETag/If-Match du chapitre REST, appliquée à la base.

#Idempotency‑Key : schéma minimalCopier le lien

1CREATE TABLE idem_keys (2  key         TEXT PRIMARY KEY,3  result_hash TEXT NOT NULL,4  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()5);6 7-- Garde-fou: purge accélérée par âge8CREATE INDEX idx_idem_gc ON idem_keys (created_at);

#Gestion des deadlocks (retry borné)Copier le lien

1// Pseudo‑code Node + pg2async function withTxRetry(client, fn, { maxRetries = 3, baseMs = 50 } = {}) {3  for (let attempt = 0; attempt <= maxRetries; attempt++) {4    try {5      await client.query('BEGIN');6      const out = await fn(client);7      await client.query('COMMIT');8      return out;9    } catch (err:any) {10      await client.query('ROLLBACK');11      // Postgres: 40P01 = deadlock detected, 40001 = serialization_failure12      if (['40P01', '40001'].includes(err.code) && attempt < maxRetries) {13        const backoff = baseMs * 2 ** attempt + Math.floor(Math.random() * baseMs);14        await new Promise(r => setTimeout(r, backoff));

#Mesure : lire un plan d’exécutionCopier le lien

1EXPLAIN (ANALYZE, BUFFERS)2SELECT id, title3FROM posts4WHERE author_id = $15ORDER BY created_at DESC6LIMIT 20;7/*8  Index Scan using idx_posts_author_created_desc on posts ...9  Filter: (author_id = $1)10  Rows Removed by Filter: 011*/

Deux signaux dominent la lecture du plan : un Seq Scan sur une grosse table là où vous attendiez un Index Scan signale un index manquant ou inutilisable (par exemple un prédicat enveloppé dans une fonction) ; un nœud Sort avant le LIMIT signale que l’index ne fournit pas l’ordre. ANALYZE exécute réellement la requête : les estimations (rows=) proches des valeurs réelles indiquent des statistiques à jour.

#Sauvegarde et restauration (PostgreSQL)Copier le lien

1# Sauvegarde2pg_dump --format=custom --file=backup.dump "$DATABASE_URL"3# Restauration4pg_restore --clean --if-exists --dbname="$DATABASE_URL" backup.dump

Une sauvegarde non restaurée n'est pas une sauvegarde : planifiez un test de restauration périodique vers une base jetable, et chronométrez-le ; c'est ce chiffre (le temps de récupération), pas la taille du dump, qui alimente vos engagements de service.

#Quiz rapideCopier le lien