Cours complet — Bases de Données

1. SQL (PostgreSQL)

Architecture PostgreSQL

Client → Parser → Rewrite → Planner → Executor → Storage
                        ↓                    ↓
                   Optimizer              Buffer Cache
                        ↓                    ↓
                   Statistics (pg_stat)    WAL (Write-Ahead Log)

Processus PostgreSQL

Postmaster (superviseur)
  ├── WAL Writer (écriture WAL)
  ├── Checkpointer (points de reprise)
  ├── Autovacuum (nettoyage MVCC)
  ├── Stats Collector (statistiques)
  ├── Logical Replication
  └── Backends (connexions clients)

MVCC (Multi-Version Concurrency Control)

• Chaque transaction voit un snapshot des données
• Lignes marquées xmin/xmax (création/suppression)
• VACUUM nettoie les versions mortes
• Pas de read locks

-- Visualiser les versions d'une ligne
SELECT xmin, xmax, ctid, * FROM users WHERE id = 1;

Indexes PostgreSQL

B-tree (par défaut) :

CREATE INDEX idx_users_email ON users(email);
-- WHERE, ORDER BY, JOIN, IN, =, <, >, BETWEEN
-- Complexité : O(log n) pour la recherche

Hash :

CREATE INDEX idx_users_email_hash ON users USING hash(email);
-- Uniquement pour les égalités (=)
-- Plus compact que B-tree, mais pas de range queries

GIN (Generalized Inverted Index) :

CREATE INDEX idx_posts_tags ON posts USING gin(tags);
-- jsonb, arrays, full-text search
-- Inversé : chaque valeur → liste de documents

GiST (Generalized Search Tree) :

CREATE INDEX idx_locations ON geom USING gist(location);
-- Géospatial (PostGIS), full-text, range types

BRIN (Block Range Index) :

CREATE INDEX idx_logs_created ON logs USING brin(created_at);
-- Pour données corrélées physiquement (logs, time-series)
-- Très compact (100x moins que B-tree)

Index composites :

-- Ordre des colonnes important !
CREATE INDEX idx_users_name_email ON users(name, email);
-- Utile pour : WHERE name = 'x' AND email = 'y'
-- Utile pour : WHERE name = 'x' ORDER BY email
-- PAS utile pour : WHERE email = 'y' (sans name)

Partial index :

CREATE INDEX idx_active_users ON users(email) WHERE active = true;
-- Plus petit, plus rapide pour les requêtes filtrées

Covering index (INCLUDE) :

CREATE INDEX idx_users_email ON users(email) INCLUDE (name, avatar_url);
-- Index-only scan ! Pas besoin d'accès à la table

Transactions et Isolation

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- ROLLBACK si erreur

Niveaux d'isolation (du plus faible au plus fort) :

*PostgreSQL : REPEATABLE READ = pas de phantom read (snapshot isolation).

-- Définir le niveau
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
  SELECT * FROM products WHERE id = 1;  -- Snapshot
  -- Même si un autre UPDATE, on voit l'ancienne valeur
  SELECT * FROM products WHERE id = 1;  -- Même snapshot
COMMIT;

-- Sérialisable (avec retry)
BEGIN ISOLATION LEVEL SERIALIZABLE;
  -- Si conflit, PostgreSQL lance :
  -- ERROR: could not serialize access due to read/write dependencies
  -- → Retry la transaction
COMMIT;

Phénomènes :

• Dirty Read : lire des données non commitées
• Non-repeatable Read : même requête, résultats différents (UPDATE entre deux SELECT)
• Phantom Read : nouvelles lignes apparaissent (INSERT entre deux SELECT)
• Serialization Anomaly : résultat incohérent malgré des transactions individuelles correctes

Locks PostgreSQL

-- Row-level
SELECT * FROM products WHERE id = 1 FOR UPDATE;      -- Écriture (bloque autres FOR UPDATE)
SELECT * FROM products WHERE id = 1 FOR NO KEY UPDATE;
SELECT * FROM products WHERE id = 1 FOR SHARE;        -- Lecture (bloque FOR UPDATE)
SELECT * FROM products WHERE id = 1 FOR KEY SHARE;

-- Advisory locks (application-level)
SELECT pg_advisory_lock(42);
SELECT pg_advisory_unlock(42);

-- Deadlock detection
-- PostgreSQL détecte et résout automatiquement
-- ERROR: deadlock detected

2. NoSQL

MongoDB

• Document : BSON (binary JSON), collections sans schéma fixe
• VS SQL : pas de JOIN, pas de transactions ACID (multi-doc avant 4.0)
• Indexes : B-tree, compound, text, geospatial, TTL, hashed
• Aggregation Pipeline : $match,$group, $sort,$lookup (JOIN-like)
• Replica Set : 1 primary + N secondaries (automatic failover)
• Sharding : horizontal scaling (range, hash, zone-based)

// MongoDB aggregation
db.orders.aggregate([
  { $match: { status: "completed" } },
  { $group: { _id: "$customerId", total: { $sum: "$amount" } } },
  { $sort: { total: -1 } },
  { $limit: 10 },
  { $lookup: {
      from: "customers",
      localField: "_id",
      foreignField: "_id",
      as: "customer"
  }}
])

Redis

• In-memory : data structure store (string, hash, list, set, sorted set, stream)
• Persistence : RDB (snapshot) + AOF (append-only log)
• Réplication : leader-follower, Sentinel (HA)
• Cluster : hash slots (16384), automatic sharding
• Cas d'usage : cache, session store, rate limiter, queue, pub/sub

# Redis patterns
SET user:1:name "Alice" EX 3600    # Cache avec TTL
LPUSH queue:jobs "task1"            # Queue
BRPOP queue:jobs 0                  # Blocking pop
PUBLISH channel:updates "new data"  # Pub/Sub
ZADD leaderboard 1000 "player1"     # Sorted set
INCR rate:ip:192.168.1.1            # Rate limiting
EXPIRE rate:ip:192.168.1.1 60       # TTL

Redis Streams (Kafka-like) :

XADD orders * customer "Alice" amount 99.99
XREAD COUNT 10 STREAMS orders 0
XGROUP CREATE orders mygroup $
XREADGROUP GROUP mygroup consumer1 COUNT 1 STREAMS orders >

3. ORM vs Raw SQL

ORM

Quand utiliser ORM ?

Pour :

• CRUD simple (CREATE, READ, UPDATE, DELETE)
• Relations standards (belongs_to, has_many)
• Migrations automatiques
• Moins de boilerplate
• Type safety (Prisma, Drizzle)

Contre :

• Requêtes complexes (CTE, window functions, recursive)
• Performance (N+1, requêtes générées sous-optimales)
• Requêtes massives (bulk insert, upsert)
• Fonctionnalités spécifiques PostgreSQL (GIN, partial index, etc.)

Raw SQL patterns

-- CTE (Common Table Expression) — lisible et performant
WITH popular_products AS (
    SELECT product_id, COUNT(*) as sales_count
    FROM orders
    WHERE created_at > NOW() - INTERVAL '30 days'
    GROUP BY product_id
    HAVING COUNT(*) > 10
)
SELECT p.*, pp.sales_count
FROM products p
JOIN popular_products pp ON pp.product_id = p.id
ORDER BY pp.sales_count DESC;

-- Window function
SELECT
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) as dept_avg,
    RANK() OVER (ORDER BY salary DESC) as company_rank
FROM employees;

-- Recursive CTE (hiérarchie)
WITH RECURSIVE org_tree AS (
    SELECT id, name, manager_id, 1 as level
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, ot.level + 1
    FROM employees e
    JOIN org_tree ot ON ot.id = e.manager_id
)
SELECT * FROM org_tree;

4. Migrations

Outils

• Alembic (Python) : SQLAlchemy-based, auto-generation
• Prisma Migrate (TS) : auto-generated, déclaratif
• golang-migrate (Go) : fichiers SQL, drivers multiples
• Flyway (Java) : SQL-based, versionné
• sqitch : SQL-only, plan de déploiement

Best practices

-- 001_create_users.sql
-- Always reversible !
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 002_add_age_to_users.sql
ALTER TABLE users ADD COLUMN age INTEGER CHECK (age >= 0);

-- 003_create_orders.sql
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    total NUMERIC(10,2) NOT NULL CHECK (total >= 0),
    status VARCHAR(20) NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);

Règles :

1. Chaque migration = un up + un down
2. Tester le down avant de déployer
3. Ne jamais modifier une migration déjà déployée
4. Utiliser des transactions (sauf DDL non transactional)
5. Préférer ADD COLUMN avec DEFAULT NULL (pas de rewrite)

# golang-migrate
migrate create -ext sql -dir migrations -seq create_users
migrate -path migrations -database "postgres://..." up
migrate -path migrations -database "postgres://..." down 1

5. ACID

Atomicité

• Tout ou rien : la transaction est complète ou annulée
• Implémenté via WAL (Write-Ahead Log) : avant d'écrire, on logue
• Si crash : on rejoue (REDO) ou annule (UNDO) selon le WAL

Cohérence (Consistency)

• Les contraintes d'intégrité (FK, CHECK, UNIQUE) sont respectées
• Les données sont dans un état valide avant/après transaction
• Responsabilité partagée : DB (contraintes) + application (logique métier)

Isolation

• Transactions concurrentes ne s'affectent pas
• Niveaux : Read Uncommitted → Serializable
• PostgreSQL : MVCC + snapshot isolation

Durabilité

• Une fois commité, les données survivent au crash
• WAL écrit de manière synchrone
• Option : synchronous_commit = on/off

6. CAP Theorem

Théorème

Dans un système distribué, on ne peut garantir que 2 des 3 propriétés :
  C = Consistency (tous les nœuds voient les mêmes données)
  A = Availability (toute requête reçoit une réponse)
  P = Partition Tolerance (le système continue malgré la perte de messages)

Choix par base de données

PACELC

Extension de CAP : en l'absence de partition (PC), faire un choix entre Latence (L) et Cohérence (C).

PACELC : If Partition → choose Availability/Consistency
         Else → choose Latency/Consistency

7. Query Optimization

EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS, TIMING) 
SELECT * FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.created_at > '2025-01-01'
  AND u.active = true;

Lecture du plan :

Seq Scan on orders (cost=0.00..1000.00 rows=1000 width=200)
  Filter: (created_at > '2025-01-01')
  Rows Removed by Filter: 90000
  Buffers: shared hit=1000
  Planning Time: 0.5 ms
  Execution Time: 45.3 ms

• cost : estimation (setup..total)
• rows : nombre estimé de lignes
• width : taille estimée en bytes
• Buffers: shared hit : pages lues depuis le cache
• Buffers: shared read : pages lues depuis le disque

Stratégies d'optimisation

1. Index manquant : Seq Scan + Filter sur colonne non indexée
2. Mauvais index : Bitmap Scan avec trop de lignes
3. Nested Loop : bon pour peu de lignes, mauvais pour beaucoup
4. Hash Join : bon pour beaucoup de lignes (une table en hash)
5. Merge Join : bon si les deux tables sont triées

-- Créer l'index si Seq Scan + Filter
CREATE INDEX CONCURRENTLY idx_orders_created ON orders(created_at);
-- CONCURRENTLY = pas de lock table

-- Vérifier les index existants
SELECT schemaname, tablename, indexname, indexdef
FROM pg_indexes
WHERE tablename = 'orders';

-- Statistiques des tables
SELECT schemaname, tablename, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch
FROM pg_stat_user_tables
WHERE tablename = 'orders';

-- Requêtes lentes
SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Query tuning tips

-- 1. Pagination : cursor-based > OFFSET
-- Lent : OFFSET 100000 LIMIT 20 → doit lire 100020 lignes
SELECT * FROM users ORDER BY id LIMIT 20 OFFSET 100000;

-- Rapide : WHERE id > 100000 LIMIT 20
SELECT * FROM users WHERE id > 100000 ORDER BY id LIMIT 20;

-- 2. Éviter SELECT * (surtout avec TEXT/BLOB)
SELECT id, name FROM users; -- vs SELECT *

-- 3. Utiliser EXISTS au lieu de COUNT pour vérifier existence
-- Lent :
IF (SELECT COUNT(*) FROM users WHERE active = true) > 0
-- Rapide :
IF EXISTS (SELECT 1 FROM users WHERE active = true)

-- 4. Utiliser UNNEST pour les mises à jour en masse
UPDATE products SET price = data_table.new_price
FROM (VALUES (1, 19.99), (2, 29.99)) AS data_table(id, new_price)
WHERE products.id = data_table.id;

-- 5. Materialized views pour les calculs lourds
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT date_trunc('month', created_at) as month,
       SUM(total) as revenue
FROM orders
WHERE status = 'completed'
GROUP BY month;

Partitionnement

-- Partitionnement par range (PostgreSQL 10+)
CREATE TABLE orders (
    id BIGSERIAL,
    created_at TIMESTAMPTZ NOT NULL,
    total NUMERIC(10,2)
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2025_q1 PARTITION OF orders
FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');

CREATE TABLE orders_2025_q2 PARTITION OF orders
FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');

-- Partition pruning : PostgreSQL n'accède qu'aux partitions concernées

Références

• PostgreSQL Documentation (postgresql.org/docs)
• Use The Index, Luke (use-the-index-luke.com)
• High Performance PostgreSQL (E-book)
• MongoDB University (university.mongodb.com)
• Redis Documentation (redis.io/docs)