# Fichier: python_cheats/cheatsheets/introduction_oracle_et_postgresql.txt
# Introduction Oracle & PostgreSQL - Pour Grands Débutants
# Comprendre l'Exécution des Requêtes SQL en Profondeur

[OK] INTRODUCTION : POURQUOI CE GUIDE ?

# Tu débutes avec les bases de données ?
# Ce guide est fait pour TOI !

# Objectif :
# Te montrer EXACTEMENT comment Oracle et PostgreSQL fonctionnent "sous le capot"
# Comprendre ce qui se passe quand tu exécutes une requête SQL
# Découvrir TOUS les fichiers créés et leur rôle précis
# Aucun concept ne sera laissé dans le flou !

# Plan du guide :
# 1. Qu'est-ce qu'un SGBD ? (Système de Gestion de Base de Données)
# 2. Oracle vs PostgreSQL : Différences fondamentales
# 3. Installation et fichiers créés (détail complet)
# 4. Architecture interne des SGBD
# 5. Du SQL à l'exécution : voyage complet d'une requête
# 6. Stockage physique des données
# 7. Performance et optimisations
# 8. Premiers pas pratiques


[OK] PARTIE 1 : QU'EST-CE QU'UN SGBD ?

# === DÉFINITION SIMPLE ===

Un SGBD (Système de Gestion de Base de Données) est un LOGICIEL qui :
[OK] Stocke des données de manière organisée
[OK] Permet de retrouver ces données rapidement
[OK] Garantit que les données restent cohérentes
[OK] Gère plusieurs utilisateurs en même temps
[OK] Protège les données contre les pannes

# === ANALOGIE : LA BIBLIOTHÈQUE ===

"""
Imagine une BIBLIOTHÈQUE avec des millions de livres :

SANS SGBD (Chaos total) :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
[DOCS] Les livres sont empilés au hasard dans un entrepôt
[X] Tu veux trouver "Les Misérables" ? Bonne chance !
[X] Tu dois fouiller des milliers de livres
[X] Plusieurs personnes veulent le même livre -> conflit
[X] Quelqu'un a déplacé un livre ? Tu ne le retrouveras jamais
[ALARM_CLOCK] Temps de recherche : Des heures, voire des jours


AVEC UN SGBD (Organisation parfaite) :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
[GUIDE] Chaque livre a un code unique
[DOSSIER] Un catalogue informatisé (index) permet de retrouver n'importe quel livre
[RECHERCHE] Tu cherches "Les Misérables" -> le système te dit : "Rayon 42, Étagère 3"
[OK] Plusieurs personnes peuvent consulter le catalogue en même temps
[OK] Un bibliothécaire (le SGBD) garantit l'ordre et la cohérence
[ALARM_CLOCK] Temps de recherche : Quelques secondes

LE SGBD = LE BIBLIOTHÉCAIRE ULTRA-EFFICACE
"""

# === POURQUOI UTILISER UN SGBD ? ===

"""
SANS SGBD (fichiers simples) :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Tu stockes les données dans des fichiers texte :

clients.txt :
Jean Dupont,30,Paris
Marie Martin,25,Lyon
Pierre Durand,35,Marseille

Problèmes :
[X] Comment chercher rapidement tous les clients de Paris ?
   -> Tu dois lire TOUT le fichier ligne par ligne
[X] Comment garantir qu'on n'insère pas deux fois le même client ?
   -> Impossible sans vérifier manuellement
[X] Que se passe-t-il si deux programmes modifient le fichier en même temps ?
   -> Corruption des données !
[X] Comment gérer les relations entre tables ?
   -> Très compliqué manuellement


AVEC UN SGBD :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
SELECT * FROM clients WHERE ville = 'Paris';
-> Résultat instantané grâce aux INDEX

[OK] Recherche ultra-rapide (même sur des millions de lignes)
Pourquoi ?
Les SGBD utilisent des structures avancées :
B-Trees, Hash Index, GiST, BRIN, selon le type de données
Caches mémoire internes (buffer pool)
Optimiseur de requêtes qui choisit le meilleur plan
-> Le moteur fait des recherches logarithmiques O(log n) au lieu de linéaires O(n).

[OK] Contraintes d'unicité garanties (UNIQUE, PRIMARY KEY)
Pourquoi ?
Le SGBD maintient automatiquement les contraintes via :
des index uniques
une vérification à l’insertion et à la mise à jour
un rollback si la contrainte est violée
-> Tu ne peux jamais avoir deux emails identiques si email est UNIQUE.
-> Tu ne peux pas avoir deux identifiants si id est PRIMARY KEY.

[OK] Transactions ACID (Atomicité, Cohérence, Isolation, Durabilité)
Un SGBD garantit ACID grâce à plusieurs mécanismes internes.

A = Atomicité

Pourquoi ?
-> Le SGBD utilise un journal (Write-Ahead Log) :
Si une opération échoue -> tout est annulé.

C = Cohérence

Pourquoi ?
-> Toutes les règles (clé étrangère, UNIQUE, check…) sont respectées automatiquement.
-> Pas de données en “état invalide”.

I = Isolation

Pourquoi ?
-> Le moteur gère les niveaux d’isolation (READ COMMITTED, SERIALIZABLE…).
-> Il empêche un utilisateur de lire un état intermédiaire.

D = Durabilité

Pourquoi ?
-> Toute transaction validée est écrite dans un fichier journal.
-> Même après un crash, le SGBD rejoue les logs pour restaurer les données.

[OK] Gestion automatique de la concurrence (plusieurs utilisateurs)
Pourquoi ?
Parce qu’un SGBD intègre :
verrous (locks) pour protéger les lignes
MVCC (ex: PostgreSQL) pour permettre lectures/écritures simultanées
deadlock detection pour éviter les blocages
-> Deux utilisateurs peuvent modifier la même table sans casser la base.
-> Les lectures restent cohérentes, même lors d’écritures concurrentes.

[OK] Jointures automatiques entre tables
Pourquoi ?
Le SGBD connaît :
les relations (clé primaire -> clé étrangère)
les index des tables
les statistiques (nombre de lignes, distribution des valeurs)

Il peut donc :
-> assembler les données de plusieurs tables
-> choisir le meilleur algorithme de jointure (nested loop, hash join...)
-> optimiser automatiquement le plan d’exécution

Tu écris juste :

SELECT nom, ville, commandes.total
FROM clients
JOIN commandes ON clients.id = commandes.client_id;

…et le moteur s’occupe de tout.

[OK] Sauvegardes et récupération après panne
Pourquoi ?
Le SGBD intègre :
des mécanismes de dump (logiques)
des snapshots (physiques)
un fichier WAL / redo log
un système de replay des transactions
un système de checkpoint
-> En cas de crash, le SGBD peut rejouer ou annuler ce qui était en cours.
-> Tu peux restaurer la base à un instant précis (“Point-In-Time Recovery”).

"""

# === LES DEUX GÉANTS : ORACLE VS POSTGRESQL ===

"""
┌────────────────────────────────────────────────────────────────────────┐
│                    ORACLE VS POSTGRESQL                                │
└────────────────────────────────────────────────────────────────────────┘

ORACLE DATABASE :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
[ENTREPRISE] Développé par Oracle Corporation (depuis 1979)
[ARGENT] PAYANT (très cher : des milliers à des millions d'euros)
[OBJECTIF] Utilisé par les GRANDES ENTREPRISES (banques, assurances, gouvernements)
[RAPIDE] Extrêmement performant et robuste
[DOCS] Très complet : des tonnes de fonctionnalités avancées
[VERROUILLE] Propriétaire : le code source est secret
[ECRAN] Supporte : Windows, Linux, Unix, Solaris

Exemples d'utilisateurs :
- Banque de France
- Airbus
- EDF
- La plupart des grandes banques mondiales


POSTGRESQL :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
[POSTGRES] Projet open-source (depuis 1996)
🆓 GRATUIT (licence PostgreSQL, très permissive)
[MONDE] Utilisé par TOUS (startups, PME, grandes entreprises)
[RAPIDE] Très performant (comparable à Oracle pour beaucoup de cas)
[DOCS] Respect des standards SQL
[DEVERROUILLE] Open-source : tu peux lire et modifier le code source
[ECRAN] Supporte : Windows, Linux, macOS, BSD, Unix

Exemples d'utilisateurs :
- Instagram (des milliards de photos)
- Spotify
- Reddit
- Apple (pour certains services)


TABLEAU COMPARATIF :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
┌─────────────────────┬──────────────────────┬──────────────────────┐
│   Caractéristique   │        Oracle        │      PostgreSQL      │
├─────────────────────┼──────────────────────┼──────────────────────┤
│ Licence             │ Propriétaire payante │ Open-source gratuite │
│ Coût                │ $$$$$ (très cher)    │ Gratuit              │
│ Performance         │[RAPIDE][RAPIDE][RAPIDE][RAPIDE][RAPIDE] (excellent)│ [RAPIDE][RAPIDE][RAPIDE][RAPIDE] (très bon) │
│ Communauté          │ Support commercial   │ Communauté mondiale  │
│ Documentation       │ Excellente (payante) │ Excellente (gratuite)│
│ Complexité          │ Élevée               │ Moyenne              │
│ Taille entreprise   │ Grandes entreprises  │ Toutes tailles       │
│ Standards SQL       │ Partiellement        │ Très respectueux     │
│ Extensibilité       │ Limitée              │ Très extensible      │
│ Réplication         │ Avancée (payante)    │ Intégrée (gratuite)  │
└─────────────────────┴──────────────────────┴──────────────────────┘
"""


[OK] PARTIE 2 : INSTALLATION ET FICHIERS CRÉÉS

# === INSTALLATION ORACLE DATABASE ===

"""
┌────────────────────────────────────────────────────────────────────────┐
│            INSTALLATION ORACLE DATABASE 19c (LINUX)                    │
└────────────────────────────────────────────────────────────────────────┘

ÉTAPE 1 : TÉLÉCHARGEMENT
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
1. Créer un compte sur oracle.com
2. Télécharger Oracle Database 19c (environ 3 GB)
3. Fichier : LINUX.X64_193000_db_home.zip


ÉTAPE 2 : PRÉPARATION DU SYSTÈME
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
# Créer un utilisateur Oracle
sudo groupadd oinstall
sudo groupadd dba
sudo useradd -g oinstall -G dba -d /home/oracle oracle
sudo passwd oracle

# Créer les répertoires nécessaires
sudo mkdir -p /u01/app/oracle/product/19.3.0/dbhome_1
sudo mkdir -p /u02/oradata
sudo chown -R oracle:oinstall /u01 /u02
sudo chmod -R 775 /u01 /u02

POURQUOI ces répertoires ?
-> /u01 : Installation du logiciel Oracle
-> /u02 : Stockage des fichiers de données
-> Séparation logicielle/données = bonne pratique


ÉTAPE 3 : INSTALLATION
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
su - oracle
cd /u01/app/oracle/product/19.3.0/dbhome_1
unzip LINUX.X64_193000_db_home.zip

# Lancer l'installeur graphique
./runInstaller

Options d'installation :
[OK] Type : "Create and configure a single instance database"
[OK] Classe : "Server class"
[OK] Type de base : "General Purpose"
[OK] Nom de la base : ORCL
[OK] Mot de passe SYS/SYSTEM : (choisir un mot de passe fort)
[OK] Jeu de caractères : AL32UTF8 (Unicode complet)

[ALARM_CLOCK] Durée d'installation : 30-60 minutes
"""

# === FICHIERS CRÉÉS PAR ORACLE (DÉTAIL COMPLET) ===

"""
┌────────────────────────────────────────────────────────────────────────┐
│                STRUCTURE DES FICHIERS ORACLE                           │
└────────────────────────────────────────────────────────────────────────┘

ARBORESCENCE COMPLÈTE :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

/u01/app/oracle/
├── product/
│   └── 19.3.0/
│       └── dbhome_1/                    <- ORACLE_HOME
│           ├── bin/                     <- Exécutables
│           │   ├── sqlplus              <- Client SQL
│           │   ├── oracle               <- Binaire principal
│           │   ├── lsnrctl              <- Contrôle du listener
│           │   └── ...
│           ├── dbs/                     <- Fichiers de configuration
│           │   ├── initORCL.ora         <- Paramètres d'initialisation
│           │   ├── spfileORCL.ora       <- Server Parameter File (binaire)
│           │   ├── orapwORCL            <- Fichier de mots de passe
│           │   └── hc_ORCL.dat          <- Health Check
│           ├── network/                 <- Configuration réseau
│           │   └── admin/
│           │       ├── listener.ora     <- Config du listener
│           │       ├── tnsnames.ora     <- Alias de connexion
│           │       └── sqlnet.ora       <- Paramètres réseau
│           ├── rdbms/                   <- Code du moteur de base
│           │   ├── admin/               <- Scripts d'administration
│           │   └── log/                 <- Logs du système
│           ├── assistants/              <- Assistants graphiques
│           ├── jdbc/                    <- Drivers Java
│           └── lib/                     <- Bibliothèques C/C++

/u02/oradata/
└── ORCL/                                <- Nom de la base de données
    ├── system01.dbf                     <- Tablespace SYSTEM
    ├── sysaux01.dbf                     <- Tablespace SYSAUX
    ├── undotbs01.dbf                    <- Tablespace UNDO
    ├── users01.dbf                      <- Tablespace USERS
    ├── temp01.dbf                       <- Fichier temporaire
    ├── redo01.log                       <- Redo Log (groupe 1)
    ├── redo02.log                       <- Redo Log (groupe 2)
    ├── redo03.log                       <- Redo Log (groupe 3)
    └── control01.ctl                    <- Fichier de contrôle


DÉTAIL DE CHAQUE TYPE DE FICHIER :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

1. FICHIERS DE DONNÉES (.dbf - Data Files)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
system01.dbf         : Dictionnaire de données Oracle (métadonnées)
                       Contient les définitions de tables, vues, index, etc.
                       Taille typique : 500 MB - 2 GB

sysaux01.dbf         : Tablespace auxiliaire du système
                       Utilisé par les outils Oracle (AWR, Scheduler, etc.)
                       Taille typique : 500 MB - 1 GB

undotbs01.dbf        : Gestion des UNDO (annulations de transactions)
                       Permet le ROLLBACK des transactions
                       Nécessaire pour la cohérence des lectures
                       Taille typique : 200 MB - plusieurs GB

users01.dbf          : Tablespace par défaut pour les utilisateurs
                       C'est là que TES tables seront stockées par défaut
                       Taille typique : 100 MB au départ, extensible

temp01.dbf           : Tablespace temporaire
                       Utilisé pour les tris, jointures complexes
                       Contenu volatile (effacé au redémarrage)
                       Taille typique : 100 MB - plusieurs GB

POURQUOI plusieurs fichiers ?
-> Séparation logique des données
-> Meilleure performance (I/O parallèles)
-> Flexibilité de sauvegarde (backup sélectif)

COMMENT sont-ils organisés ?
-> Chaque fichier contient des BLOCS de données (8 KB par défaut)
-> Structure interne :
   [En-tête du fichier]
   [Bitmap d'allocation]
   [Bloc 1: données]
   [Bloc 2: données]
   [Bloc 3: données]
   ...


2. FICHIERS REDO LOG (.log - Redo Log Files)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
redo01.log           : Journal de transactions (groupe 1)
redo02.log           : Journal de transactions (groupe 2)
redo03.log           : Journal de transactions (groupe 3)

RÔLE ESSENTIEL :
-> Enregistre TOUTES les modifications de la base
-> Permet la récupération après crash (recovery)
-> Fonctionnement circulaire : quand redo03 est plein, on revient à redo01

POURQUOI 3 fichiers (3 groupes) ?
-> Pendant qu'on écrit dans redo01, redo02 et redo03 sont disponibles
-> Rotation automatique pour ne jamais perdre de données
-> Permet l'archivage (copie de sauvegarde)

CONTENU TYPE :
[13:45:23] INSERT INTO clients VALUES (1, 'Jean Dupont')
[13:45:24] UPDATE clients SET age = 30 WHERE id = 1
[13:45:25] COMMIT
[13:45:26] DELETE FROM commandes WHERE id = 100
[13:45:27] COMMIT

QUAND sont-ils utilisés ?
-> À CHAQUE modification de données (INSERT, UPDATE, DELETE)
-> AVANT que les données ne soient écrites sur disque
-> En cas de crash : Oracle rejoue le redo log pour récupérer les transactions

Taille typique : 50 MB - 1 GB chacun


3. FICHIERS DE CONTRÔLE (.ctl - Control Files)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
control01.ctl        : Métadonnées critiques de la base
control02.ctl        : Copie miroir (pour redondance)
control03.ctl        : Copie miroir (pour redondance)

CONTENU :
-> Nom de la base de données
-> Liste de tous les fichiers de données
-> Liste de tous les fichiers redo log
-> Horodatage de création de la base
-> Numéro de séquence des redo logs
-> Informations de checkpoint (point de cohérence)

POURQUOI plusieurs copies ?
-> Si control01.ctl est corrompu, Oracle utilise automatiquement control02.ctl
-> Sécurité maximale : la perte du fichier de contrôle = base inutilisable

QUAND est-il mis à jour ?
-> À chaque changement structurel (ajout de fichier, création de tablespace)
-> À chaque checkpoint (synchronisation disque)
-> À chaque switch de redo log

Taille typique : 10-20 MB


4. FICHIERS DE PARAMÈTRES (Configuration)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
initORCL.ora         : Paramètres d'initialisation (format texte)
                       Fichier TEXTE lisible et modifiable
                       Contient les paramètres de démarrage de la base

Exemple de contenu :
db_name='ORCL'
memory_target=2G                    # Mémoire totale allouée
processes=300                       # Nombre max de processus
db_block_size=8192                  # Taille des blocs (8 KB)
undo_tablespace='UNDOTBS1'          # Tablespace d'annulation
control_files='/u02/oradata/ORCL/control01.ctl'

spfileORCL.ora       : Server Parameter File (format binaire)
                       Fichier BINAIRE (non modifiable directement)
                       Permet de modifier les paramètres sans redémarrer

POURQUOI deux fichiers ?
-> init*.ora : ancien format, modifiable manuellement
-> spfile*.ora : nouveau format, modifiable via SQL (ALTER SYSTEM)
-> Oracle utilise spfile en priorité s'il existe

COMMENT les paramètres sont-ils lus ?
1. Au démarrage, Oracle cherche spfileORCL.ora
2. Si absent, il cherche initORCL.ora
3. Les paramètres définissent le comportement de toute l'instance


5. FICHIERS RÉSEAU (Configuration client/serveur)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
listener.ora         : Configuration du LISTENER (serveur d'écoute)

Exemple :
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = ORCL)
    )
  )

RÔLE :
-> Le listener ÉCOUTE sur le port 1521
-> Quand un client veut se connecter, il contacte le listener
-> Le listener crée un processus serveur dédié au client

tnsnames.ora         : Alias de connexion (côté client)

Exemple :
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

UTILISATION :
sqlplus user/password@ORCL
        ^
        utilise la définition dans tnsnames.ora


6. FICHIERS EXÉCUTABLES (Binaires)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
/u01/app/oracle/product/19.3.0/dbhome_1/bin/

oracle               : Binaire PRINCIPAL d'Oracle Database
                       Contient TOUT le moteur de base de données
                       Taille : ~500 MB (énorme !)
                       Écrit en C/C++

sqlplus              : Client en ligne de commande
                       Permet d'exécuter des requêtes SQL
                       
lsnrctl              : Contrôle du listener
                       Commandes : start, stop, status

exp/imp              : Export/Import de données (ancienne méthode)
expdp/impdp          : Data Pump (nouvelle méthode, plus rapide)
rman                 : Recovery Manager (sauvegardes)
"""

# === INSTALLATION POSTGRESQL ===

"""
┌────────────────────────────────────────────────────────────────────────┐
│              INSTALLATION POSTGRESQL 15 (LINUX)                        │
└────────────────────────────────────────────────────────────────────────┘

ÉTAPE 1 : INSTALLATION VIA PACKAGE MANAGER
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
# Ubuntu/Debian
sudo apt update
sudo apt install postgresql postgresql-contrib

# CentOS/RedHat
sudo yum install postgresql-server postgresql-contrib

# Initialiser le cluster de bases de données
sudo postgresql-setup --initdb --unit postgresql

# Démarrer le service
sudo systemctl start postgresql
sudo systemctl enable postgresql

[ALARM_CLOCK] Durée d'installation : 2-5 minutes (bien plus rapide qu'Oracle !)


ÉTAPE 2 : CONFIGURATION INITIALE
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
# Se connecter en tant qu'utilisateur postgres
sudo -i -u postgres

# Lancer le client psql
psql

# Créer un mot de passe pour l'utilisateur postgres
postgres=# \password postgres
Enter new password: ****
Enter it again: ****

# Créer une base de données de test
postgres=# CREATE DATABASE testdb;

# Quitter
postgres=# \q
"""

# === FICHIERS CRÉÉS PAR POSTGRESQL (DÉTAIL COMPLET) ===

"""
┌────────────────────────────────────────────────────────────────────────┐
│              STRUCTURE DES FICHIERS POSTGRESQL                         │
└────────────────────────────────────────────────────────────────────────┘

ARBORESCENCE COMPLÈTE :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

/var/lib/postgresql/15/           <- Répertoire principal
├── main/                          <- Cluster principal (PGDATA)
│   ├── base/                      <- Données des bases
│   │   ├── 1/                     <- Base template1 (OID 1)
│   │   ├── 13442/                 <- Base template0 (OID 13442)
│   │   ├── 13443/                 <- Base postgres (OID 13443)
│   │   └── 16384/                 <- Base testdb (OID 16384)
│   │       ├── 16385              <- Fichier de table (8KB par bloc)
│   │       ├── 16385_fsm          <- Free Space Map
│   │       ├── 16385_vm           <- Visibility Map
│   │       └── ...
│   ├── global/                    <- Objets partagés entre bases
│   │   ├── pg_control             <- Fichier de contrôle principal
│   │   ├── pg_filenode.map        <- Mapping OID -> fichier
│   │   └── ...
│   ├── pg_wal/                    <- Write-Ahead Log (WAL)
│   │   ├── 000000010000000000000001
│   │   ├── 000000010000000000000002
│   │   └── archive_status/        <- Statut d'archivage
│   ├── pg_xact/                   <- Statuts des transactions (COMMIT/ABORT)
│   ├── pg_commit_ts/              <- Timestamps des commits
│   ├── pg_multixact/              <- Transactions multiples (locks)
│   ├── pg_notify/                 <- Files LISTEN/NOTIFY
│   ├── pg_serial/                 <- Infos isolation sérialisable
│   ├── pg_snapshots/              <- Snapshots exportés
│   ├── pg_stat/                   <- Statistiques d'activité
│   ├── pg_stat_tmp/               <- Stats temporaires
│   ├── pg_subtrans/               <- Sous-transactions
│   ├── pg_tblspc/                 <- Liens vers tablespaces
│   ├── pg_twophase/               <- Transactions à 2 phases (2PC)
│   ├── postgresql.conf            <- Configuration principale
│   ├── pg_hba.conf                <- Authentification (Host-Based Auth)
│   ├── pg_ident.conf              <- Mapping utilisateurs
│   ├── postmaster.pid             <- PID du processus principal
│   └── postmaster.opts            <- Options de démarrage

/usr/lib/postgresql/15/bin/       <- Exécutables
├── postgres                       <- Serveur principal
├── psql                           <- Client en ligne de commande
├── pg_dump                        <- Export de base
├── pg_restore                     <- Restauration de backup
├── pg_basebackup                  <- Sauvegarde physique
├── createdb                       <- Créer une base
├── dropdb                         <- Supprimer une base
└── ...

/etc/postgresql/15/main/          <- Configs alternatives (Ubuntu/Debian)
├── postgresql.conf
├── pg_hba.conf
└── pg_ident.conf


DÉTAIL DE CHAQUE TYPE DE FICHIER :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

1. RÉPERTOIRE base/ (Données des bases)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Chaque base de données a son propre sous-répertoire
Le nom du répertoire = OID (Object Identifier) de la base

Exemple :
base/16384/   <- Base 'testdb'
    ├── 16385              <- Fichier d'une table (relation)
    ├── 16385_fsm          <- Free Space Map (espaces libres dans la table)
    ├── 16385_vm           <- Visibility Map (pages contenant des tuples visibles)
    ├── 16386              <- Index sur la table 16385
    └── ...

POURQUOI des numéros au lieu de noms ?
-> Performance : accès direct via OID
-> Simplicité interne
-> Mapping OID <-> nom dans le catalogue système (pg_class)

COMMENT retrouver l'OID d'une base ?
SELECT oid, datname FROM pg_database;
  oid  |  datname
-------+-----------
     1 | template1
 13442 | template0
 13443 | postgres
 16384 | testdb

STRUCTURE INTERNE D'UN FICHIER DE TABLE :
-> Taille de bloc : 8 KB (par défaut)
-> Chaque fichier peut faire jusqu'à 1 GB
-> Si une table dépasse 1 GB, PostgreSQL crée :
   16385, 16385.1, 16385.2, etc.

Structure d'un bloc (8 KB) :
[En-tête de page: 24 bytes]
[Pointeurs vers tuples: N * 4 bytes]
[Espace libre]
[Tuple N]
[Tuple N-1]
...
[Tuple 1]


2. RÉPERTOIRE pg_wal/ (Write-Ahead Log)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Équivalent des REDO LOGS d'Oracle
Enregistre TOUTES les modifications AVANT qu'elles ne soient écrites en base

Fichiers typiques :
000000010000000000000001    <- Segment WAL (16 MB par défaut)
000000010000000000000002
000000010000000000000003

RÔLE CRITIQUE :
-> Garantit la DURABILITÉ (le D dans ACID)
-> En cas de crash, PostgreSQL rejoue le WAL pour récupérer les données
-> Utilisé aussi pour la réplication

FONCTIONNEMENT :
1. Tu exécutes : UPDATE clients SET nom = 'Martin' WHERE id = 1;
2. AVANT de modifier le fichier de la table, PostgreSQL écrit dans le WAL :
   [LSN: 0/1A2B3C4] UPDATE clients (page 42, offset 5) SET nom = 'Martin'
3. Puis PostgreSQL met à jour le fichier de la table
4. En cas de crash entre 2 et 3, le WAL permet de rejouer la modification

LSN (Log Sequence Number) :
-> Position unique dans le WAL
-> Format : file/offset
-> Exemple : 0/1A2B3C4 signifie fichier 0, offset 0x1A2B3C4

QUAND les fichiers WAL sont-ils recyclés ?
-> Quand un CHECKPOINT est effectué
-> Un checkpoint = point de cohérence où toutes les modifications sont sur disque
-> Les anciens WAL peuvent être archivés ou supprimés


3. FICHIER pg_control (Fichier de Contrôle)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
global/pg_control

CONTENU (métadonnées critiques) :
-> Version de PostgreSQL
-> État du cluster (en cours, arrêté proprement, en recovery)
-> Dernier checkpoint effectué (LSN)
-> Position actuelle dans le WAL
-> Identifiant unique du cluster (System Identifier)
-> Taille des blocs (8 KB par défaut)

POURQUOI est-il critique ?
-> Sans pg_control, PostgreSQL ne peut PAS démarrer
-> Il contient toutes les infos pour localiser les données

COMMENT le lire ?
pg_controldata /var/lib/postgresql/15/main/

Sortie typique :
pg_control version number:            1300
Catalog version number:               202209061
Database system identifier:           7156123456789012345
Database cluster state:               in production
pg_control last modified:             Thu Dec 05 10:30:00 2024
Latest checkpoint location:           0/1A2B3C4
Latest checkpoint's REDO location:    0/1A2B3C0


4. FICHIERS DE CONFIGURATION
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
postgresql.conf      : Configuration principale du serveur

Paramètres importants :
listen_addresses = 'localhost'         # Interfaces d'écoute
port = 5432                            # Port d'écoute
max_connections = 100                  # Connexions max simultanées
shared_buffers = 128MB                 # Cache mémoire partagé
work_mem = 4MB                         # Mémoire par opération (tri, etc.)
maintenance_work_mem = 64MB            # Mémoire pour VACUUM, CREATE INDEX
wal_level = replica                    # Niveau de détail du WAL
fsync = on                             # Forcer synchro disque (CRITIQUE)
synchronous_commit = on                # Attendre écriture WAL
checkpoint_timeout = 5min              # Fréquence des checkpoints
effective_cache_size = 4GB             # Estimation du cache OS

POURQUOI shared_buffers est important ?
-> C'est le CACHE MÉMOIRE de PostgreSQL
-> Les données fréquemment accédées restent en RAM
-> Plus il est grand, moins d'accès disque
-> Recommandation : 25% de la RAM totale

pg_hba.conf          : Host-Based Authentication
                       Contrôle QUI peut se connecter et COMMENT

Format :
# TYPE  DATABASE  USER  ADDRESS        METHOD
local   all       all                  peer
host    all       all   127.0.0.1/32   md5
host    all       all   ::1/128        md5

Explications :
local  : Connexions Unix socket (sans réseau)
host   : Connexions TCP/IP
peer   : Authentification par utilisateur système
md5    : Mot de passe crypté MD5
scram-sha-256 : Meilleure sécurité (recommandé)

EXEMPLE :
# Autoriser l'utilisateur 'appuser' depuis 192.168.1.0/24
host    mydb      appuser   192.168.1.0/24    scram-sha-256

pg_ident.conf        : Mapping entre utilisateurs système et PostgreSQL
                       Rarement utilisé (sauf pour ident authentication)


5. RÉPERTOIRES DE MÉTADONNÉES
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
pg_xact/             : Statuts des transactions (COMMIT ou ABORT)
                       Fichiers binaires : 0000, 0001, 0002, ...
                       Chaque bit = statut d'une transaction

POURQUOI c'est important ?
-> PostgreSQL utilise MVCC (Multi-Version Concurrency Control)
-> Plusieurs versions d'une même ligne peuvent coexister
-> pg_xact permet de savoir quelle transaction est valide

pg_multixact/        : Gestion des verrous partagés
                       Ex : plusieurs transactions veulent un SHARE LOCK

pg_commit_ts/        : Timestamps des commits (si activé)
                       Permet de savoir QUAND une transaction a été validée

pg_stat/             : Statistiques d'activité en temps réel
                       Utilisé par les vues pg_stat_*

EXEMPLE :
SELECT * FROM pg_stat_user_tables WHERE relname = 'clients';
-> Nombre de lectures, écritures, vacuum effectués, etc.

pg_tblspc/           : Liens symboliques vers des tablespaces
                       Permet de stocker des tables sur différents disques

Exemple :
CREATE TABLESPACE fast_storage LOCATION '/mnt/ssd/pgdata';
CREATE TABLE clients (...) TABLESPACE fast_storage;
-> La table sera physiquement sur /mnt/ssd/pgdata


6. EXÉCUTABLES POSTGRESQL
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
/usr/lib/postgresql/15/bin/

postgres             : Serveur principal
                       Gère toutes les connexions et requêtes
                       Écrit en C

psql                 : Client en ligne de commande (équivalent de sqlplus)
                       Interface interactive pour SQL

pg_dump              : Export logique d'une base
                       Produit un fichier SQL ou un fichier binaire

Exemple :
pg_dump -U postgres -d testdb -f backup.sql
pg_dump -U postgres -d testdb -Fc -f backup.dump  # Format custom (compressé)

pg_restore           : Restauration depuis un backup binaire
pg_restore -U postgres -d testdb backup.dump

pg_basebackup        : Sauvegarde physique complète (PITR)
                       Copie tous les fichiers du cluster

createdb             : Raccourci pour CREATE DATABASE
createdb -U postgres mydb

dropdb               : Raccourci pour DROP DATABASE
dropdb -U postgres mydb

pg_ctl               : Contrôle du serveur (start, stop, restart, reload)
pg_ctl -D /var/lib/postgresql/15/main start

vacuumdb             : Lance VACUUM sur une base
                       VACUUM récupère l'espace des tuples morts
"""


[OK] PARTIE 3 : ARCHITECTURE INTERNE DES SGBD

# === ARCHITECTURE ORACLE ===

"""
┌────────────────────────────────────────────────────────────────────────┐
│                    ARCHITECTURE ORACLE DATABASE                        │
└────────────────────────────────────────────────────────────────────────┘

COMPOSANTS PRINCIPAUX :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

┌─────────────────────────────────────────────────────────────────────┐
│                         INSTANCE ORACLE                             │
│  (Processus + Mémoire - EN RAM)                                     │
│                                                                     │
│  ┌───────────────────────────────────────────────────────────────┐  │
│  │                   SGA (System Global Area)                    │  │
│  │                     Mémoire partagée                          │  │
│  │                                                               │  │
│  │  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐         │  │
│  │  │ Buffer Cache │  │ Shared Pool  │  │  Redo Log    │         │  │
│  │  │  (données)   │  │   (SQL)      │  │   Buffer     │         │  │
│  │  └──────────────┘  └──────────────┘  └──────────────┘         │  │
│  │                                                               │  │
│  │  ┌──────────────┐  ┌──────────────┐                           │  │
│  │  │   Large Pool │  │  Java Pool   │                           │  │
│  │  └──────────────┘  └──────────────┘                           │  │
│  └───────────────────────────────────────────────────────────────┘  │
│                                                                     │
│  ┌───────────────────────────────────────────────────────────────┐  │
│  │                   PROCESSUS D'ARRIÈRE-PLAN                    │  │
│  │                                                               │  │
│  │  SMON  PMON  DBWn  LGWR  CKPT  ARCn  RECO  ...                │  │
│  └───────────────────────────────────────────────────────────────┘  │
└─────────────────────────────────────────────────────────────────────┘
                                │
                                v
┌─────────────────────────────────────────────────────────────────────┐
│                        BASE DE DONNÉES                              │
│                    (Fichiers - SUR DISQUE)                          │
│                                                                     │
│  ┌──────────┐  ┌──────────┐  ┌──────────┐  ┌──────────┐             │
│  │  Data    │  │   Redo   │  │ Control  │  │  Temp    │             │
│  │  Files   │  │   Logs   │  │  Files   │  │  Files   │             │
│  └──────────┘  └──────────┘  └──────────┘  └──────────┘             │
└─────────────────────────────────────────────────────────────────────┘


DISTINCTION FONDAMENTALE :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

INSTANCE :
-> Processus + Mémoire (SGA + PGA)
-> Existe EN RAM
-> Détruite à l'arrêt du serveur
-> Peut être redémarrée sans perdre les données

BASE DE DONNÉES :
-> Fichiers physiques sur disque
-> Persistants
-> Contiennent les données réelles
-> Survivent à l'arrêt de l'instance


DÉTAIL DE LA SGA (System Global Area) :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

1. BUFFER CACHE (Cache des données)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
RÔLE :
-> Cache EN MÉMOIRE les blocs de données récemment accédés
-> Évite les lectures disque (TRÈS lentes)
-> Utilise l'algorithme LRU (Least Recently Used)

FONCTIONNEMENT :
1. Tu exécutes : SELECT * FROM clients WHERE id = 1;
2. Oracle cherche d'abord dans le Buffer Cache
3. SI TROUVÉ (cache hit) : retour instantané [RAPIDE]
4. SI PAS TROUVÉ (cache miss) : lecture depuis le disque [LENT]
5. Le bloc lu est mis en cache pour les prochaines fois

POURQUOI c'est crucial ?
-> RAM : ~100 nanosecondes
-> SSD : ~100 microsecondes (1000x plus lent)
-> HDD : ~10 millisecondes (100 000x plus lent)

Taille typique : 40-60% de la SGA


2. SHARED POOL (Cache SQL)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Contient :
-> Library Cache : Plans d'exécution SQL compilés(Le code PL/SQL compilé (procédures, fonctions), Les requêtes SQL déjà analysées)
Pourquoi ?
Analyser et optimiser une requête coûte du CPU et du temps
Oracle stocke le résultat de ce travail pour le réutiliser

-> Dictionary Cache : Métadonnées (définitions de tables, Colonnes, types de données, Index, contraintes, Droits utilisateurs)
Pourquoi ?
À chaque requête, Oracle doit vérifier :
la table existe-t-elle ?
l’utilisateur a-t-il les droits ?
quels index sont disponibles ?
Sans cache -> Oracle relirait les tables système à chaque fois (lent).

RÔLE :
-> Évite de recompiler les mêmes requêtes SQL
-> Cache les métadonnées pour accès rapide

EXEMPLE :
1. Première exécution de :
   SELECT * FROM clients WHERE ville = 'Paris';
   -> Oracle PARSE la requête (analyse syntaxique, Vérifie les droits, Vérifie l’existence des objets)
   -> Oracle OPTIMISE la requête (choix du meilleur plan, Choisit index ou full scan, Choisit l’ordre des opérations)
   -> Oracle COMPILE le plan d'exécution
   -> Oracle STOCKE le plan dans le Shared Pool
   -> Oracle EXÉCUTE la requête
   [TEMPS] Temps total : 10 ms

2. Deuxième exécution de la MÊME requête :
   -> Oracle trouve le plan déjà compilé dans le Shared Pool
   -> Oracle EXÉCUTE directement
   [TEMPS] Temps total : 1 ms (10x plus rapide !)

POURQUOI utiliser des BIND VARIABLES ?
Mauvais :
SELECT * FROM clients WHERE id = 1;
SELECT * FROM clients WHERE id = 2;
SELECT * FROM clients WHERE id = 3;
-> 3 requêtes différentes -> 3 compilations

Bon :
SELECT * FROM clients WHERE id = :id;
-> 1 seule compilation, réutilisée pour toutes les valeurs


3. REDO LOG BUFFER
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
RÔLE :
-> Cache EN MÉMOIRE les modifications AVANT écriture dans les Redo Logs

FLUX :
1. Tu exécutes : UPDATE clients SET nom = 'Martin' WHERE id = 1;
2. Oracle écrit dans le Redo Log Buffer (RAM) [RAPIDE]
3. Au COMMIT, le buffer est VIDÉ dans les Redo Log Files (disque)
4. Écriture disque SYNCHRONE (fsync) pour garantir durabilité

POURQUOI un buffer ?
-> Grouper plusieurs modifications en une seule écriture disque
-> Plus efficace que d'écrire à chaque modification

Taille typique : Quelques MB (suffisant)


PROCESSUS D'ARRIÈRE-PLAN (Background Processes) :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

SMON (System Monitor) :
-> Récupération automatique après crash
-> Nettoie les segments temporaires
-> Fusionne les espaces libres contigus

PMON (Process Monitor) :
-> Surveille les processus utilisateurs
-> Nettoie les ressources des sessions mortes
-> Relance le listener si nécessaire

DBWn (Database Writer) :
-> Écrit les blocs modifiés du Buffer Cache vers le disque
-> Travaille en ASYNCHRONE (pas au COMMIT)
-> Plusieurs processus possibles (DBW0, DBW1, ...)

POURQUOI écriture asynchrone ?
-> Ne pas bloquer les transactions
-> Écriture optimisée par batch

LGWR (Log Writer) :
-> Écrit le Redo Log Buffer vers les Redo Log Files
-> Travaille en SYNCHRONE au COMMIT
-> CRITIQUE pour la durabilité

CKPT (Checkpoint) :
-> Déclenche les checkpoints périodiques
-> Met à jour les Control Files

ARCn (Archiver) :
-> Copie les Redo Logs remplis vers l'archive
-> Nécessaire pour la récupération point-in-time

RECO (Recoverer) :
-> Résout les transactions distribuées en échec
"""

# === ARCHITECTURE POSTGRESQL ===

"""
┌────────────────────────────────────────────────────────────────────────┐
│                  ARCHITECTURE POSTGRESQL                               │
└────────────────────────────────────────────────────────────────────────┘

DIFFÉRENCE MAJEURE AVEC ORACLE :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
PostgreSQL utilise un modèle PROCESSUS (pas de threads)
-> Chaque connexion = un NOUVEAU PROCESSUS UNIX
-> Plus simple mais plus gourmand en ressources


ARCHITECTURE GLOBALE :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

┌─────────────────────────────────────────────────────────────────────┐
│                    POSTMASTER (processus principal)                 │
│                                                                     │
│  ┌─────────────────────────────────────────────────────────────┐  │
│  │               SHARED MEMORY (Mémoire partagée)              │  │
│  │                                                             │    │
│  │  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐        │  │
│  │  │Shared Buffers│  │   WAL Buffers│  │  Proc Array  │    │  │
│  │  │  (8 KB blocs)│  │              │  │              │    │  │
│  │  └──────────────┘  └──────────────┘  └──────────────┘    │  │
│  └─────────────────────────────────────────────────────────────┘  │
│                                                                     │
│  ┌─────────────────────────────────────────────────────────────┐  │
│  │            PROCESSUS D'ARRIÈRE-PLAN                         │  │
│  │                                                             │  │
│  │  Checkpointer  Background Writer  WAL Writer  Autovacuum   │  │
│  │  Stats Collector  Logical Replication  ...                 │  │
│  └─────────────────────────────────────────────────────────────┘  │
│                                                                     │
│  ┌─────────────────────────────────────────────────────────────┐  │
│  │         PROCESSUS BACKEND (un par connexion)                │  │
│  │                                                             │  │
│  │  Backend 1    Backend 2    Backend 3    ...                │  │
│  │  (Client A)   (Client B)   (Client C)                      │  │
│  └─────────────────────────────────────────────────────────────┘  │
└─────────────────────────────────────────────────────────────────────┘
                              │
                              v
┌─────────────────────────────────────────────────────────────────────┐
│                    FICHIERS SUR DISQUE                              │
│                                                                     │
│  ┌──────────┐  ┌──────────┐  ┌──────────┐  ┌──────────┐          │
│  │  Data    │  │   WAL    │  │pg_control│  │  Temp    │          │
│  │  Files   │  │  Files   │  │          │  │  Files   │          │
│  └──────────┘  └──────────┘  └──────────┘  └──────────┘          │
└─────────────────────────────────────────────────────────────────────┘


DÉTAIL DES COMPOSANTS :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

1. POSTMASTER (Processus maître)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
RÔLE :
-> Premier processus lancé au démarrage de PostgreSQL
-> Écoute sur le port 5432 (par défaut)
-> Accepte les connexions des clients
-> FORKE un nouveau processus backend pour chaque connexion

ANALOGIE :
-> Le postmaster = réceptionniste d'un hôtel
-> Chaque client qui arrive = nouvelle connexion
-> Le réceptionniste assigne une chambre (processus backend)


2. SHARED BUFFERS (Cache des données)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Équivalent du Buffer Cache d'Oracle

RÔLE :
-> Cache les blocs de 8 KB récemment accédés
-> Mémoire PARTAGÉE entre tous les processus backend
-> Utilise un algorithme clock-sweep (variante de LRU)

CONFIGURATION :
shared_buffers = 128MB    # Par défaut (trop petit !)
shared_buffers = 2GB      # Recommandation : 25% de la RAM

FONCTIONNEMENT :
1. Backend exécute : SELECT * FROM clients WHERE id = 1;
2. Backend cherche le bloc dans Shared Buffers
3. SI TROUVÉ : lecture instantanée
4. SI PAS TROUVÉ : lecture depuis le disque + mise en cache

DIFFÉRENCE AVEC ORACLE :
-> PostgreSQL s'appuie aussi sur le CACHE DU SYSTÈME D'EXPLOITATION
-> effective_cache_size : indique à PostgreSQL la taille du cache OS


3. WAL BUFFERS (Write-Ahead Log)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Équivalent du Redo Log Buffer d'Oracle

RÔLE :
-> Cache les modifications AVANT écriture dans pg_wal/

FLUX AU COMMIT :
1. UPDATE clients SET nom = 'Martin' WHERE id = 1;
2. Modification écrite dans WAL Buffers (RAM)
3. COMMIT;
4. WAL Writer vide le buffer dans pg_wal/ (disque)
5. fsync() pour garantir l'écriture physique

Taille typique : 16 MB (suffisant pour la plupart des cas)


4. PROCESSUS BACKEND (un par connexion)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
RÔLE :
-> Exécute les requêtes SQL d'un client
-> Accède aux Shared Buffers
-> Gère sa propre mémoire locale (work_mem)

MÉMOIRE LOCALE (non partagée) :
work_mem = 4MB         # Mémoire pour tris, hash joins, etc.
temp_buffers = 8MB     # Buffers pour tables temporaires

PROBLÈME POTENTIEL :
-> Chaque processus consomme de la mémoire
-> 100 connexions × 4 MB work_mem = 400 MB minimum
-> Limiter max_connections ou utiliser un connection pooler (PgBouncer)


PROCESSUS D'ARRIÈRE-PLAN :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Checkpointer :
-> Déclenche les checkpoints périodiques
-> Force l'écriture des Shared Buffers sur disque
-> Permet de recycler les anciens WAL

Background Writer :
-> Écrit progressivement les buffers modifiés
-> Réduit la charge du checkpointer

WAL Writer :
-> Écrit les WAL Buffers dans pg_wal/
-> Garantit la durabilité des COMMIT

Autovacuum :
-> Lance VACUUM automatiquement
-> Récupère l'espace des tuples morts (UPDATE/DELETE)
-> Met à jour les statistiques pour l'optimiseur

POURQUOI VACUUM est nécessaire ?
-> PostgreSQL utilise MVCC (Multi-Version Concurrency Control)
-> Un UPDATE ne modifie PAS la ligne en place
-> Il crée une NOUVELLE version et marque l'ancienne comme morte
-> VACUUM supprime les versions mortes

Stats Collector :
-> Collecte les statistiques d'activité
-> Alimente les vues pg_stat_*
"""


[OK] PARTIE 4 : DU SQL À L'EXÉCUTION - VOYAGE COMPLET D'UNE REQUÊTE

# === ORACLE : EXÉCUTION D'UNE REQUÊTE SELECT ===

"""
┌────────────────────────────────────────────────────────────────────────────────────────┐
│          ORACLE : EXÉCUTION DE "SELECT * FROM clients WHERE ville = 'Paris'"           │
└────────────────────────────────────────────────────────────────────────────────────────┘

ÉTAPE 1 :CONNEXION DU CLIENT
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
$ sqlplus user/password@ORCL

1. Le client envoie une demande de connexion au LISTENER (port 1521)
2. Le LISTENER consulte listener.ora
3. Le LISTENER FORKE un processus SERVER dédié à ce client
4. Le processus SERVER vérifie les credentials dans le dictionnaire
5. Connexion établie [OK]

   Client <--> Listener <--> Processus Server <--> Instance Oracle


ÉTAPE 2 : ENVOI DE LA REQUÊTE SQL
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
SQL> SELECT * FROM clients WHERE ville = 'Paris';

Le processus SERVER reçoit la requête (texte brut)


ÉTAPE 3 : PARSING (Analyse syntaxique)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Le processus SERVER cherche DANS LE SHARED POOL (Library Cache)

3a. SOFT PARSE (cas idéal [RAPIDE]) :
-> La requête EXISTE DÉJÀ dans le cache (même texte, même casse)
-> Oracle réutilise le plan d'exécution compilé
-> Très rapide (microsecondes)

3b. HARD PARSE (cas coûteux [LENT]) :
-> La requête N'EXISTE PAS dans le cache
-> Oracle doit TOUT refaire :

   i. Vérification syntaxique :
      -> "SELECT" est-il bien écrit ?
      -> Y a-t-il une virgule manquante ?
      -> Erreur -> ORA-00942: table or view does not exist

   ii. Vérification sémantique :
       -> La table "clients" existe-t-elle ?
       -> La colonne "ville" existe-t-elle dans cette table ?
       -> L'utilisateur a-t-il le droit de SELECT sur cette table ?
       -> Consultation du DICTIONARY CACHE (métadonnées)

   iii. Transformation de la requête :
        -> Réécriture éventuelle pour optimisation
        -> Ex : WHERE 1=1 AND ville='Paris' -> WHERE ville='Paris'

   iv. Optimisation :
       -> L'OPTIMISEUR (Cost-Based Optimizer) analyse :
          - Statistiques sur la table (nombre de lignes, distribution)
          - Index disponibles
          - Coût estimé de chaque plan possible

       Plans possibles :
       - PLAN A : Full Table Scan (parcourir toute la table)
       - PLAN B : Index Range Scan (utiliser un index sur "ville")

       -> Oracle CHOISIT le plan avec le coût le plus bas

   v. Génération du code exécutable :
      -> Le plan d'exécution est traduit en opérations internes

   vi. Stockage dans le Shared Pool :
       -> Pour réutilisation future

[TEMPS] Temps d'un HARD PARSE : 1-10 ms (100-1000x plus lent qu'un SOFT PARSE)


ÉTAPE 4 : BINDING (Liaison des variables)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Si la requête utilise des bind variables :
SELECT * FROM clients WHERE ville = :ville;

-> Oracle associe la valeur 'Paris' à la variable :ville


ÉTAPE 5 : EXÉCUTION
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Le processus SERVER exécute le plan d'exécution

Supposons que l'optimiseur a choisi : INDEX RANGE SCAN

i. Accès à l'index :
   -> Oracle cherche dans l'index sur "ville"
   -> Structure B-Tree : recherche efficace en O(log n)
   -> Trouve les ROWIDs correspondant à 'Paris'

   ROWID = adresse physique de la ligne
   Format : AAABCDEFGHaaabbbcccc
            ^       ^  ^      ^
            Objet   File Block Row

ii. Lecture des données :
    -> Avec les ROWIDs, Oracle sait exactement OÙ lire
    -> Oracle cherche d'abord dans le BUFFER CACHE (RAM)

    CAS 1 : Cache Hit [RAPIDE]
    -> Le bloc est déjà en mémoire
    -> Lecture instantanée (100 nanosecondes)

    CAS 2 : Cache Miss [LENT]
    -> Le bloc n'est PAS en mémoire
    -> Oracle doit lire depuis le DISQUE
    -> Le processus SERVER demande au DBWn
    -> Lecture du fichier .dbf (10 millisecondes)
    -> Le bloc est chargé dans le Buffer Cache
    -> Pour les prochaines fois : cache hit !

iii. Filtrage :
     -> Oracle vérifie que la ligne correspond bien aux critères
     -> Peut éliminer des lignes si nécessaire


ÉTAPE 6 : FETCH (Récupération des résultats)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Les lignes trouvées sont envoyées au CLIENT

-> Envoi par BATCH (paquet) pour optimiser le réseau
-> Par défaut : arraysize = 15 lignes par fetch

Exemple :
1000 lignes trouvées, arraysize=15
-> 67 allers-retours réseau (1000/15)


ÉTAPE 7 : AFFICHAGE
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
sqlplus affiche les résultats

ID   NOM             VILLE
---  --------------  --------
1    Jean Dupont     Paris
2    Marie Martin    Paris
...


RÉCAPITULATIF DU TEMPS :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Parsing (soft) :      0.01 ms   [RAPIDE]
Exécution :           0.5 ms    (avec cache hit)
Fetch :               1 ms      (réseau local)
──────────────────────────────
TOTAL :               ~1.5 ms

Avec Hard Parse :     10 ms     [LENT]
Avec Cache Miss :     20-50 ms  [LENT][LENT]
"""

# === POSTGRESQL : EXÉCUTION D'UNE REQUÊTE SELECT ===

"""
┌────────────────────────────────────────────────────────────────────────────────┐
│     POSTGRESQL : EXÉCUTION DE "SELECT * FROM clients WHERE ville = 'Paris'"    │
└────────────────────────────────────────────────────────────────────────────────┘

ÉTAPE 1 : CONNEXION DU CLIENT
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
$ psql -U user -d mydb

1. Le client envoie une demande au POSTMASTER (port 5432)
2. Le POSTMASTER consulte pg_hba.conf pour l'authentification
3. Le POSTMASTER FORKE un nouveau processus BACKEND
4. Le backend vérifie les credentials
5. Connexion établie [OK]

   Client <--> Postmaster <--> Processus Backend <--> Shared Memory


ÉTAPE 2 : ENVOI DE LA REQUÊTE SQL
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
mydb=# SELECT * FROM clients WHERE ville = 'Paris';

Le processus BACKEND reçoit la requête


ÉTAPE 3 : PARSING (Analyse)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
DIFFÉRENCE AVEC ORACLE :
-> PostgreSQL N'A PAS de cache de plans partagé (avant PG12)
-> Depuis PG12 : cache de plans générique (limité)
-> Chaque backend PARSE ses propres requêtes

i. Analyse lexicale :
   -> Décomposition en tokens
   "SELECT", "*", "FROM", "clients", "WHERE", "ville", "=", "'Paris'"

ii. Analyse syntaxique :
    -> Construction d'un arbre de syntaxe abstraite (Parse Tree)

iii. Analyse sémantique :
     -> La table existe-t-elle ? (consultation de pg_class)
     -> La colonne existe-t-elle ? (consultation de pg_attribute)
     -> Permissions OK ? (consultation de pg_authid, pg_roles)

iv. Réécriture (Rewriter) :
    -> Application des RÈGLES (RULES) si définies
    -> Expansion des vues
    -> Transformation de la requête


ÉTAPE 4 : PLANIFICATION (Optimizer)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Le PLANNER (optimiseur) génère le meilleur plan d'exécution

i. Génération de TOUS les plans possibles :
   -> Full Seq Scan (parcours séquentiel)
   -> Index Scan (si index sur "ville")
   -> Bitmap Index Scan (combinaison d'index)

ii. Estimation du COÛT de chaque plan :
    -> Basé sur les STATISTIQUES (pg_stats)
    -> Nombre de lignes dans la table
    -> Sélectivité de la condition (combien de 'Paris' ?)
    -> Coût CPU + Coût I/O

iii. Sélection du plan le MOINS coûteux

VOIR LE PLAN :
EXPLAIN SELECT * FROM clients WHERE ville = 'Paris';

Sortie typique :
                                 QUERY PLAN
------------------------------------------------------------------------
 Index Scan using idx_clients_ville on clients  (cost=0.42..8.44 rows=10)
   Index Cond: (ville = 'Paris'::text)

Interprétation :
-> Méthode : Index Scan
-> Index utilisé : idx_clients_ville
-> Coût estimé : 0.42 (startup) à 8.44 (total)
-> Nombre de lignes estimées : 10


ÉTAPE 5 : EXÉCUTION (Executor)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
L'EXECUTOR exécute le plan nœud par nœud

Supposons : Index Scan

i. Lecture de l'index :
   -> PostgreSQL parcourt le B-Tree de l'index
   -> Trouve les TID (Tuple Identifier) pour 'Paris'
   -> TID = (block_number, tuple_offset)

ii. Lecture des tuples :
    -> Avec les TID, PostgreSQL sait OÙ lire
    -> Vérification dans SHARED BUFFERS (cache)

    CAS 1 : Buffer Hit [RAPIDE]
    -> Bloc déjà en cache
    -> Lecture instantanée

    CAS 2 : Buffer Miss [LENT]
    -> Lecture depuis le disque (base/16384/16385)
    -> Mise en cache dans Shared Buffers

iii. Vérification MVCC (Multi-Version Concurrency Control) :
     -> PostgreSQL doit vérifier que le tuple est VISIBLE
     -> Consultation de pg_xact/ pour le statut de la transaction
     -> Vérification des champs xmin/xmax du tuple

     TUPLE STRUCTURE :
     [xmin: 1000]  [xmax: 0]  [data: "Jean Dupont", "Paris"]
      ^             ^
      Transaction   Transaction
      de création   de suppression (0 = non supprimé)

     -> Si xmin est COMMIT et xmax = 0 : tuple visible [OK]
     -> Sinon : tuple ignoré (version obsolète)


ÉTAPE 6 : RETOUR DES RÉSULTATS
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Les tuples sont envoyés au CLIENT via le protocole PostgreSQL

-> Format binaire ou texte
-> Envoi progressif (streaming)


ÉTAPE 7 : AFFICHAGE
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
psql affiche les résultats

 id |     nom      |  ville
----+--------------+---------
  1 | Jean Dupont  | Paris
  2 | Marie Martin | Paris
...


RÉCAPITULATIF DU TEMPS :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Parsing :             0.1 ms
Planning :            0.5 ms
Exécution :           0.5 ms    (avec buffer hit)
Fetch :               0.5 ms
──────────────────────────────
TOTAL :               ~1.6 ms

Comparable à Oracle pour des requêtes simples !
"""


[OK] PARTIE 5 : STOCKAGE PHYSIQUE DES DONNÉES

# === ORACLE : STRUCTURE DE STOCKAGE ===

"""
┌────────────────────────────────────────────────────────────────────────┐
│              ORACLE : HIÉRARCHIE DE STOCKAGE                           │
└────────────────────────────────────────────────────────────────────────┘

BASE DE DONNÉES
     v
TABLESPACE (logique)
     v
DATAFILE (fichier .dbf physique)
     v
SEGMENT (table, index, etc.)
     v
EXTENT (ensemble de blocs contigus)
     v
BLOCK (plus petite unité, 8 KB par défaut)
     v
ROW (ligne de données)


DÉTAIL DE CHAQUE NIVEAU :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

1. TABLESPACE (Espace de stockage logique)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
DÉFINITION :
-> Conteneur LOGIQUE pour organiser les objets
-> Un tablespace peut contenir plusieurs datafiles
-> Permet de séparer les données par usage

Tablespaces par défaut :
SYSTEM      : Dictionnaire Oracle (métadonnées)
SYSAUX      : Outils Oracle (AWR, etc.)
USERS       : Tables utilisateurs (par défaut)
TEMP        : Fichiers temporaires (tris)
UNDOTBS1    : Gestion des annulations (UNDO)

Créer un tablespace :
CREATE TABLESPACE app_data
  DATAFILE '/u02/oradata/ORCL/app_data01.dbf'
  SIZE 100M
  AUTOEXTEND ON
  NEXT 10M
  MAXSIZE 1G;

Créer une table dans ce tablespace :
CREATE TABLE clients (...) TABLESPACE app_data;


2. DATAFILE (Fichier physique)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Fichier .dbf sur le disque
-> Taille fixe ou auto-extensible
-> Un tablespace peut avoir plusieurs datafiles (I/O parallèles)

Voir les datafiles :
SELECT file_name, tablespace_name, bytes/1024/1024 AS size_mb
FROM dba_data_files;


3. SEGMENT (Objet de base de données)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
TYPES DE SEGMENTS :
-> TABLE SEGMENT : stockage d'une table
-> INDEX SEGMENT : stockage d'un index
-> LOB SEGMENT : stockage de CLOB, BLOB
-> UNDO SEGMENT : données d'annulation

Voir les segments :
SELECT segment_name, segment_type, bytes/1024/1024 AS size_mb
FROM dba_segments
WHERE owner = 'USER';


4. EXTENT (Ensemble de blocs)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Groupe de BLOCS CONTIGUS
-> Quand un segment grandit, Oracle alloue un nouvel extent

Exemple :
Table créée -> 1er extent alloué (64 KB = 8 blocs de 8 KB)
Table grandit -> 2ème extent alloué (64 KB)
Table grandit -> 3ème extent alloué (128 KB)
...

Politique d'allocation :
-> AUTOALLOCATE (Oracle décide)
-> UNIFORM SIZE (taille fixe pour tous les extents)


5. BLOCK (Bloc de données)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Plus petite unité de stockage
-> Taille par défaut : 8 KB (paramètre db_block_size)
-> Taille fixée à la création de la base (non modifiable)

STRUCTURE D'UN BLOC ORACLE :

┌──────────────────────────────────────────────────────────────┐
│                    BLOCK HEADER (96-124 bytes)               │
│  - Adresse du bloc (Data Block Address)                      │
│  - Type de segment                                           │
│  - Informations de transaction (ITL)                         │
│  - Pointeurs vers les lignes                                 │
├──────────────────────────────────────────────────────────────┤
│                    TABLE DIRECTORY                           │
│  - Informations sur les tables dans ce bloc                  │
├──────────────────────────────────────────────────────────────┤
│                    ROW DIRECTORY                             │
│  - Pointeurs vers chaque ligne                               │
│  - [offset1] [offset2] [offset3] ...                         │
├──────────────────────────────────────────────────────────────┤
│                    FREE SPACE                                │
│                    (espace libre)                            │
├──────────────────────────────────────────────────────────────┤
│                    ROW DATA (lignes)                         │
│  [Row 3]                                                     │
│  [Row 2]                                                     │
│  [Row 1]                                                     │
│  (stockées de bas en haut)                                   │
└──────────────────────────────────────────────────────────────┘

PCTFREE et PCTUSED :
PCTFREE = 10 : Garder 10% d'espace libre pour les UPDATE
PCTUSED = 40 : Réutiliser le bloc quand il descend sous 40% d'occupation


6. ROW (Ligne de données)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
STRUCTURE D'UNE LIGNE :

[Row Header: 3 bytes]
  - Lock byte
  - Flag byte
  - Column count
[Column Length-Value pairs]
  - [len: 1] [value: 'A']
  - [len: 10] [value: 'Jean Dupont']
  - [len: 2] [value: 30]
[Row Trailer (si nécessaire)]

ROWID :
-> Adresse PHYSIQUE d'une ligne
-> Format : AAABCDEFGHaaabbbcccc
   AAABCD = Data Object Number
   EFG    = Datafile Number
   aaabbb = Block Number
   cccc   = Row Number

Exemple :
SELECT ROWID, nom FROM clients WHERE id = 1;

ROWID              NOM
-----------------  ------------
AAASxhAAEAAAACHAAA Jean Dupont

-> Oracle peut retrouver cette ligne en UNE SEULE lecture disque
"""

# === POSTGRESQL : STRUCTURE DE STOCKAGE ===

"""
┌────────────────────────────────────────────────────────────────────────┐
│            POSTGRESQL : HIÉRARCHIE DE STOCKAGE                         │
└────────────────────────────────────────────────────────────────────────┘

CLUSTER
     v
DATABASE
     v
SCHEMA (namespace logique)
     v
RELATION (table, index, etc.)
     v
FILE (fichier physique, max 1 GB)
     v
PAGE (bloc de 8 KB)
     v
TUPLE (ligne de données)


DÉTAIL DE CHAQUE NIVEAU :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

1. CLUSTER (Instance PostgreSQL)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Répertoire PGDATA : /var/lib/postgresql/15/main/
-> Contient TOUTES les bases de données
-> Un seul cluster par installation (sauf configuration multiple)

Initialiser un nouveau cluster :
initdb -D /chemin/vers/nouveau/cluster


2. DATABASE (Base de données)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Chaque base = sous-répertoire dans base/
-> Nom du répertoire = OID de la base

Créer une base :
CREATE DATABASE mydb
  ENCODING 'UTF8'
  LC_COLLATE = 'fr_FR.UTF-8'
  LC_CTYPE = 'fr_FR.UTF-8'
  TEMPLATE template0;

Lister les bases :
\l+ dans psql


3. SCHEMA (Namespace)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Organise les objets (tables, vues, fonctions)
-> Permet d'avoir des tables de même nom dans des schemas différents

Schema par défaut : public

Créer un schema :
CREATE SCHEMA app;

Créer une table dans ce schema :
CREATE TABLE app.clients (...);

Search path (ordre de recherche) :
SET search_path TO app, public;


4. RELATION (Table, Index, Vue matérialisée)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Tout objet stockant des données = relation
-> Identifié par un OID

Voir les relations :
SELECT oid, relname, relkind, relpages, reltuples
FROM pg_class
WHERE relname = 'clients';

relkind :
'r' = table (ordinary relation)
'i' = index
'm' = materialized view
'S' = sequence


5. FILE (Fichier physique)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Chaque relation = un ou plusieurs fichiers
-> Taille max par fichier : 1 GB (paramètre compilé)
-> Si > 1 GB : création de fichiers .1, .2, .3, etc.

Exemple :
base/16384/16385       <- Table (0-1 GB)
base/16384/16385.1     <- Suite (1-2 GB)
base/16384/16385.2     <- Suite (2-3 GB)
base/16384/16385_fsm   <- Free Space Map
base/16384/16385_vm    <- Visibility Map

FREE SPACE MAP (FSM) :
-> Indique l'espace libre dans chaque page
-> Optimise les INSERT (trouver rapidement une page avec de l'espace)

VISIBILITY MAP (VM) :
-> Indique quelles pages contiennent uniquement des tuples visibles
-> Optimise VACUUM (pas besoin de scanner ces pages)
-> Optimise les Index-Only Scans


6. PAGE (Bloc de 8 KB)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
STRUCTURE D'UNE PAGE POSTGRESQL :

┌──────────────────────────────────────────────────────────────┐
│                  PAGE HEADER (24 bytes)                      │
│  - pd_lsn : LSN de la dernière modification WAL              │
│  - pd_checksum : Checksum de la page (si activé)             │
│  - pd_flags : Flags (all visible, has free space, etc.)      │
│  - pd_lower : Offset de la fin du tableau de pointeurs       │
│  - pd_upper : Offset du début des tuples                     │
│  - pd_special : Offset de la zone spéciale (index)           │
├──────────────────────────────────────────────────────────────┤
│              ITEM POINTERS ARRAY (Line Pointers)             │
│  - [offset1, length1]                                        │
│  - [offset2, length2]                                        │
│  - [offset3, length3]                                        │
│  - ...                                                       │
├──────────────────────────────────────────────────────────────┤
│                     FREE SPACE                               │
│                                                              │
├──────────────────────────────────────────────────────────────┤
│                   TUPLE DATA (tuples)                        │
│  [Tuple 3]                                                   │
│  [Tuple 2]                                                   │
│  [Tuple 1]                                                   │
│  (stockés de bas en haut)                                    │
├──────────────────────────────────────────────────────────────┤
│               SPECIAL SPACE (si index B-Tree)                │
└──────────────────────────────────────────────────────────────┘

FILLFACTOR :
-> Équivalent de PCTFREE dans Oracle
-> % de la page à remplir lors d'INSERT

Exemple :
CREATE TABLE clients (...) WITH (fillfactor = 70);
-> Garder 30% d'espace libre pour les UPDATE


7. TUPLE (Ligne de données)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
STRUCTURE D'UN TUPLE (MVCC) :

[TUPLE HEADER: 23 bytes]
  - t_xmin : Transaction qui a créé ce tuple
  - t_xmax : Transaction qui a supprimé/modifié ce tuple (0 si actif)
  - t_cid : Command ID dans la transaction
  - t_ctid : TID de la nouvelle version (si UPDATE)
[NULL BITMAP: variable]
  - 1 bit par colonne (NULL ou non)
[OID: 4 bytes si table WITH OIDS]
[COLUMN DATA]
  - Valeurs des colonnes

EXEMPLE CONCRET :
Table : clients (id, nom, ville)
Ligne : (1, 'Jean Dupont', 'Paris')

TUPLE :
[t_xmin: 1000]    Transaction 1000 a créé cette ligne
[t_xmax: 0]       Ligne active (non supprimée)
[t_ctid: (0,1)]   TID de ce tuple = block 0, offset 1
[null_bitmap: 000] Aucune colonne NULL
[id: 1]
[nom: 'Jean Dupont']
[ville: 'Paris']


UPDATE clients SET ville = 'Lyon' WHERE id = 1;

PostgreSQL NE MODIFIE PAS le tuple en place !
Il crée une NOUVELLE version :

ANCIEN TUPLE (devient mort) :
[t_xmin: 1000]
[t_xmax: 1001]    Transaction 1001 l'a "supprimé"
[t_ctid: (0,2)]   Pointe vers la nouvelle version
[data: ...]

NOUVEAU TUPLE :
[t_xmin: 1001]    Transaction 1001 l'a créé
[t_xmax: 0]       Actif
[t_ctid: (0,2)]   Son propre TID
[data: 'Lyon']

-> L'ancien tuple devient "mort" (dead tuple)
-> VACUUM le supprimera physiquement


TID (Tuple Identifier) :
-> Adresse physique d'un tuple
-> Format : (block_number, tuple_offset)
-> Exemple : (42, 5) = page 42, tuple 5

Voir les TID :
SELECT ctid, * FROM clients;

 ctid  | id |     nom      |  ville
-------+----+--------------+---------
 (0,1) |  1 | Jean Dupont  | Paris
 (0,2) |  2 | Marie Martin | Lyon
"""


[OK] PARTIE 6 : PERFORMANCE ET OPTIMISATIONS

# === INDEX : ACCÉLÉRER LES RECHERCHES ===

"""
┌────────────────────────────────────────────────────────────────────────┐
│                        INDEX : POURQUOI ET COMMENT ?                   │
└────────────────────────────────────────────────────────────────────────┘

ANALOGIE : L'INDEX D'UN LIVRE
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Sans index :
-> Tu veux trouver le mot "PostgreSQL" dans un livre de 1000 pages
-> Tu dois lire TOUTES les pages une par une
-> Temps : 1 heure [LENT]

Avec un index (à la fin du livre) :
-> Tu regardes l'index : "PostgreSQL -> page 427, 531, 892"
-> Tu vas directement à ces pages
-> Temps : 2 minutes [RAPIDE]

C'EST EXACTEMENT PAREIL DANS UNE BASE DE DONNÉES !


SANS INDEX (FULL TABLE SCAN) :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
SELECT * FROM clients WHERE ville = 'Paris';

-> PostgreSQL/Oracle doit lire TOUTES les lignes
-> 1 million de lignes -> 1 million de comparaisons
-> Temps : plusieurs secondes [LENT]


AVEC INDEX (INDEX SCAN) :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
CREATE INDEX idx_clients_ville ON clients(ville);

-> L'index contient : ville -> TID/ROWID
-> Recherche dans l'index : O(log n) au lieu de O(n)
-> 1 million de lignes -> 20 comparaisons environ
-> Temps : quelques millisecondes [RAPIDE]


TYPES D'INDEX :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

1. B-TREE (par défaut, Oracle et PostgreSQL)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Arbre équilibré
-> Parfait pour : =, <, >, <=, >=, BETWEEN, LIKE 'abc%'
-> Supporte ORDER BY

CREATE INDEX idx_clients_nom ON clients(nom);

SELECT * FROM clients WHERE nom = 'Dupont';          [OK] Utilise l'index
SELECT * FROM clients WHERE nom LIKE 'Dup%';         [OK] Utilise l'index
SELECT * FROM clients WHERE nom LIKE '%pont';        [X] N'utilise PAS l'index


2. HASH INDEX (PostgreSQL uniquement, limité)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Basé sur une fonction de hachage
-> Très rapide pour : = uniquement
-> Ne supporte PAS : <, >, ORDER BY

CREATE INDEX idx_clients_email_hash ON clients USING HASH (email);

SELECT * FROM clients WHERE email = 'jean@example.com';  [OK]
SELECT * FROM clients WHERE email > 'a@example.com';     [X]


3. GIN (Generalized Inverted Index) - PostgreSQL
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Pour recherche full-text, JSONB, arrays
-> Excellent pour : LIKE '%mot%', recherche dans JSON

CREATE INDEX idx_clients_description_gin 
ON clients USING GIN (to_tsvector('french', description));

SELECT * FROM clients 
WHERE to_tsvector('french', description) @@ to_tsquery('french', 'PostgreSQL');


4. GIST (Generalized Search Tree) - PostgreSQL
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Pour données géométriques, full-text
-> Plus flexible que GIN, mais plus lent

CREATE INDEX idx_locations_gist ON locations USING GIST (coordinates);


BITMAP INDEX (Oracle uniquement) :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Parfait pour colonnes à faible cardinalité (peu de valeurs distinctes)
-> Exemple : sexe (M/F), statut (actif/inactif)

CREATE BITMAP INDEX idx_clients_sexe ON clients(sexe);

-> Très efficace pour combiner plusieurs conditions :
SELECT * FROM clients WHERE sexe = 'M' AND statut = 'actif';


QUAND CRÉER UN INDEX ?
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
[OK] OUI : Colonnes dans WHERE, JOIN, ORDER BY
[OK] OUI : Clés étrangères
[OK] OUI : Colonnes souvent recherchées

[X] NON : Petites tables (< 10 000 lignes)
[X] NON : Colonnes rarement utilisées
[X] NON : Colonnes avec beaucoup de NULL
[X] NON : Tables avec beaucoup d'INSERT/UPDATE/DELETE


COÛT D'UN INDEX :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-> Espace disque supplémentaire
-> INSERT/UPDATE/DELETE plus lents (l'index doit être mis à jour)
-> Maintenance nécessaire (REBUILD/REINDEX)

RÈGLE D'OR : Pas trop d'index ! (max 5-7 par table)
"""

# === STATISTIQUES ET ANALYSE ===

"""
┌────────────────────────────────────────────────────────────────────────┐
│              STATISTIQUES : NOURRIR L'OPTIMISEUR                       │
└────────────────────────────────────────────────────────────────────────┘

POURQUOI LES STATISTIQUES SONT CRITIQUES ?
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
L'optimiseur doit DEVINER le meilleur plan d'exécution
Pour cela, il a besoin de STATISTIQUES :
-> Combien de lignes dans la table ?
-> Combien de valeurs distinctes dans une colonne ?
-> Distribution des valeurs (histogrammes)
-> Corrélation physique/logique des données

SANS statistiques -> Plans d'exécution CATASTROPHIQUES


ORACLE : GATHER STATS
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', 'CLIENTS');

Que fait Oracle ?
-> Compte le nombre de lignes
-> Calcule le nombre de valeurs distinctes par colonne
-> Crée des histogrammes pour les colonnes non uniformes
-> Stocke tout dans le dictionnaire (DBA_TAB_STATISTICS)

Automatisation :
-> Par défaut, Oracle collecte les stats automatiquement
-> Job auto_optimizer_stats_collection


POSTGRESQL : ANALYZE
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
ANALYZE clients;

Que fait PostgreSQL ?
-> Échantillonne aléatoirement les lignes (default_statistics_target = 100)
-> Calcule les statistiques
-> Stocke dans pg_stats

Voir les statistiques :
SELECT * FROM pg_stats WHERE tablename = 'clients';

Automatisation :
-> Autovacuum lance ANALYZE automatiquement après INSERT/UPDATE


IMPORTANCE DES STATS À JOUR :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Exemple :

Table clients : 10 000 lignes
Stats à jour : l'optimiseur SAIT qu'il y a 10 000 lignes
-> Il choisit un Index Scan [OK]

Après 6 mois : 10 000 000 de lignes
Stats PAS à jour : l'optimiseur croit qu'il y a toujours 10 000 lignes
-> Il choisit ENCORE un Index Scan [X]
-> CATASTROPHE : très lent !

Solution : ANALYZE/GATHER_STATS régulièrement
"""


[OK] PARTIE 7 : PREMIERS PAS PRATIQUES

# === ORACLE : EXEMPLE COMPLET ===

"""
-- Se connecter en tant que SYSTEM
sqlplus system/password@ORCL

-- Créer un utilisateur
CREATE USER appuser IDENTIFIED BY password123
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;

-- Donner les privilèges
GRANT CONNECT, RESOURCE TO appuser;

-- Se connecter avec le nouvel utilisateur
CONNECT appuser/password123@ORCL

-- Créer une table
CREATE TABLE clients (
    id NUMBER PRIMARY KEY,
    nom VARCHAR2(100) NOT NULL,
    prenom VARCHAR2(100),
    email VARCHAR2(200) UNIQUE,
    ville VARCHAR2(50),
    date_creation DATE DEFAULT SYSDATE
);

-- Créer une séquence pour l'auto-incrémentation
CREATE SEQUENCE seq_clients START WITH 1 INCREMENT BY 1;

-- Insérer des données
INSERT INTO clients (id, nom, prenom, email, ville)
VALUES (seq_clients.NEXTVAL, 'Dupont', 'Jean', 'jean.dupont@example.com', 'Paris');

INSERT INTO clients (id, nom, prenom, email, ville)
VALUES (seq_clients.NEXTVAL, 'Martin', 'Marie', 'marie.martin@example.com', 'Lyon');

COMMIT;

-- Créer un index
CREATE INDEX idx_clients_ville ON clients(ville);

-- Analyser la table
EXEC DBMS_STATS.GATHER_TABLE_STATS('APPUSER', 'CLIENTS');

-- Voir le plan d'exécution
EXPLAIN PLAN FOR
SELECT * FROM clients WHERE ville = 'Paris';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Requête avec jointure
CREATE TABLE commandes (
    id NUMBER PRIMARY KEY,
    client_id NUMBER REFERENCES clients(id),
    montant NUMBER(10,2),
    date_commande DATE DEFAULT SYSDATE
);

SELECT c.nom, c.prenom, SUM(co.montant) AS total
FROM clients c
JOIN commandes co ON c.id = co.client_id
GROUP BY c.nom, c.prenom
HAVING SUM(co.montant) > 1000;
"""

# === POSTGRESQL : EXEMPLE COMPLET ===

"""
-- Se connecter en tant que postgres
psql -U postgres

-- Créer une base de données
CREATE DATABASE myapp
    ENCODING 'UTF8'
    LC_COLLATE = 'fr_FR.UTF-8'
    LC_CTYPE = 'fr_FR.UTF-8';

-- Se connecter à la nouvelle base
\c myapp

-- Créer un utilisateur
CREATE USER appuser WITH PASSWORD 'password123';

-- Donner les privilèges
GRANT ALL PRIVILEGES ON DATABASE myapp TO appuser;

-- Se connecter avec le nouvel utilisateur
\c myapp appuser

-- Créer une table
CREATE TABLE clients (
    id SERIAL PRIMARY KEY,                    -- Auto-incrémentation
    nom VARCHAR(100) NOT NULL,
    prenom VARCHAR(100),
    email VARCHAR(200) UNIQUE,
    ville VARCHAR(50),
    date_creation TIMESTAMP DEFAULT NOW()
);

-- Insérer des données
INSERT INTO clients (nom, prenom, email, ville)
VALUES 
    ('Dupont', 'Jean', 'jean.dupont@example.com', 'Paris'),
    ('Martin', 'Marie', 'marie.martin@example.com', 'Lyon');

-- Créer un index
CREATE INDEX idx_clients_ville ON clients(ville);

-- Analyser la table
ANALYZE clients;

-- Voir le plan d'exécution
EXPLAIN ANALYZE
SELECT * FROM clients WHERE ville = 'Paris';

-- Requête avec jointure
CREATE TABLE commandes (
    id SERIAL PRIMARY KEY,
    client_id INTEGER REFERENCES clients(id) ON DELETE CASCADE,
    montant NUMERIC(10,2),
    date_commande TIMESTAMP DEFAULT NOW()
);

SELECT c.nom, c.prenom, SUM(co.montant) AS total
FROM clients c
JOIN commandes co ON c.id = co.client_id
GROUP BY c.nom, c.prenom
HAVING SUM(co.montant) > 1000;

-- Voir les statistiques
\d+ clients

SELECT * FROM pg_stats WHERE tablename = 'clients';
```


[OK] RÉCAPITULATIF FINAL

"""
┌────────────────────────────────────────────────────────────────────────┐
│                    RÉCAPITULATIF : ORACLE VS POSTGRESQL                │
└────────────────────────────────────────────────────────────────────────┘

EXÉCUTION D'UNE REQUÊTE :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
1. Parsing (analyse syntaxique/sémantique)
2. Optimization (choix du meilleur plan)
3. Execution (lecture des données)
4. Fetch (retour des résultats)

Oracle : Cache de plans partagé (Shared Pool)
PostgreSQL : Cache par processus (limité), cache de plans depuis PG12

FICHIERS PRINCIPAUX :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Oracle :
-> Datafiles (.dbf) : données
-> Redo Logs (.log) : journal des modifications
-> Control Files (.ctl) : métadonnées critiques
-> Parameter Files (init/spfile) : configuration

PostgreSQL :
-> Data Files (base/OID/OID) : données
-> WAL (pg_wal/) : journal des modifications
-> pg_control : métadonnées critiques
-> postgresql.conf : configuration

MÉMOIRE :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Oracle SGA :
-> Buffer Cache (données)
-> Shared Pool (SQL)
-> Redo Log Buffer

PostgreSQL Shared Memory :
-> Shared Buffers (données)
-> WAL Buffers

PROCESSUS :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Oracle : Threads (léger en ressources)
PostgreSQL : Processus (un par connexion)

OPTIMISATION :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
[OK] Créer des INDEX sur les colonnes recherchées
[OK] Mettre à jour les STATISTIQUES régulièrement
[OK] Utiliser EXPLAIN pour analyser les requêtes
[OK] Optimiser la mémoire (shared_buffers, SGA)
[OK] Surveiller les logs et les requêtes lentes

CONCLUSION :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Oracle et PostgreSQL sont deux EXCELLENTS SGBD
Oracle : Plus de fonctionnalités, support commercial, très cher
PostgreSQL : Open-source, gratuit, excellent pour 90% des cas

Les CONCEPTS sont similaires :
-> Parsing -> Planning -> Execution
-> Cache mémoire (Buffer Cache / Shared Buffers)
-> Journal des transactions (Redo Log / WAL)
-> Optimiseur basé sur les coûts

Maintenant tu COMPRENDS comment fonctionne une base de données !
De la requête SQL jusqu'au disque dur, tu connais TOUT le chemin [RAPIDE]
"""

# FIN DU GUIDE
```

[OK] RÉCAPITULATIF FINAL

"""
┌────────────────────────────────────────────────────────────────────────┐
│                    RÉCAPITULATIF : ORACLE VS POSTGRESQL                │
└────────────────────────────────────────────────────────────────────────┘

EXÉCUTION D'UNE REQUÊTE :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
1. Parsing (analyse syntaxique/sémantique)
2. Optimization (choix du meilleur plan)
3. Execution (lecture des données)
4. Fetch (retour des résultats)

Oracle : Cache de plans partagé (Shared Pool)
PostgreSQL : Cache par processus (limité), cache de plans depuis PG12

FICHIERS PRINCIPAUX :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Oracle :
-> Datafiles (.dbf) : données
-> Redo Logs (.log) : journal des modifications
-> Control Files (.ctl) : métadonnées critiques
-> Parameter Files (init/spfile) : configuration

PostgreSQL :
-> Data Files (base/OID/OID) : données
-> WAL (pg_wal/) : journal des modifications
-> pg_control : métadonnées critiques
-> postgresql.conf : configuration

MÉMOIRE :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Oracle SGA :
-> Buffer Cache (données)
-> Shared Pool (SQL)
-> Redo Log Buffer

PostgreSQL Shared Memory :
-> Shared Buffers (données)
-> WAL Buffers

PROCESSUS :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Oracle : Threads (léger en ressources)
PostgreSQL : Processus (un par connexion)

OPTIMISATION :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
[OK] Créer des INDEX sur les colonnes recherchées
[OK] Mettre à jour les STATISTIQUES régulièrement
[OK] Utiliser EXPLAIN pour analyser les requêtes
[OK] Optimiser la mémoire (shared_buffers, SGA)
[OK] Surveiller les logs et les requêtes lentes

CONCLUSION :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Oracle et PostgreSQL sont deux EXCELLENTS SGBD
Oracle : Plus de fonctionnalités, support commercial, très cher
PostgreSQL : Open-source, gratuit, excellent pour 90% des cas

Les CONCEPTS sont similaires :
-> Parsing -> Planning -> Execution
-> Cache mémoire (Buffer Cache / Shared Buffers)
-> Journal des transactions (Redo Log / WAL)
-> Optimiseur basé sur les coûts

Maintenant tu COMPRENDS comment fonctionne une base de données !
De la requête SQL jusqu'au serveur se trouvant à Londres, tu connais TOUT le chemin [RAPIDE]
"""


[OK] BONUS : VOYAGE COMPLET D'UNE REQUÊTE - DAKAR -> LONDRES

"""
┌────────────────────────────────────────────────────────────────────────┐
│    SCÉNARIO RÉEL : CONNEXION DEPUIS DAKAR VERS UN SERVEUR À LONDRES    │
└────────────────────────────────────────────────────────────────────────┘

CONTEXTE :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Tu es à DAKAR (Sénégal)
Tu veux interroger une base de données PostgreSQL hébergée à LONDRES (UK)
Distance : ~4 500 km
Latence réseau typique : 150-200 ms (aller-retour)


ÉTAPE 0 : CONFIGURATION INITIALE
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Serveur PostgreSQL à Londres :
-> Adresse IP : 51.124.45.67
-> Port : 5432
-> Base de données : ecommerce
-> Hébergement : AWS eu-west-2 (Londres)

Configuration sur le serveur (Londres) :
1. postgresql.conf :
   listen_addresses = '*'              # Écouter sur toutes les interfaces
   port = 5432
   max_connections = 200
   
2. pg_hba.conf :
   # Autoriser les connexions depuis Dakar
   host    ecommerce    appuser    41.82.0.0/16    scram-sha-256
   
3. Firewall AWS :
   -> Port 5432 ouvert pour l'IP de Dakar (ou plage d'IPs)

Ton ordinateur à Dakar :
-> Adresse IP publique : 41.82.123.45
-> Client PostgreSQL installé (psql)


ÉTAPE 1 : RÉSOLUTION DNS (DAKAR)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
$ psql -h db.mycompany.com -U appuser -d ecommerce

Ton ordinateur doit d'abord résoudre "db.mycompany.com" en adresse IP

1. Consultation du cache DNS local (Dakar)
   -> Si absent, consulter le résolveur DNS du FAI sénégalais
   
2. Requête DNS envoyée :
   Dakar -> DNS Sonatel/Orange Sénégal -> DNS Root Servers -> DNS .com
   -> DNS mycompany.com -> Réponse : 51.124.45.67
   
3. Réponse reçue et mise en cache
   [TEMPS] Temps : 50-100 ms


ÉTAPE 2 : ÉTABLISSEMENT DE LA CONNEXION TCP (DAKAR -> LONDRES)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Ton client psql initie une connexion TCP

HANDSHAKE TCP EN 3 ÉTAPES :

1. SYN (Synchronize) :
   [Dakar] -> [Routeur FAI Dakar] -> [Câble sous-marin ACE (Dakar-Portugal)]
   -> [Routeurs européens] -> [AWS Londres] -> [Serveur PostgreSQL]
   
   Paquet TCP : SYN, seq=1000
   [TEMPS] Temps : ~100 ms (aller)

2. SYN-ACK (Synchronize-Acknowledge) :
   [Serveur Londres] -> [AWS] -> [Routeurs européens] -> [Câble sous-marin]
   -> [Routeur FAI Dakar] -> [Ton PC Dakar]
   
   Paquet TCP : SYN-ACK, seq=5000, ack=1001
   [TEMPS] Temps : ~100 ms (retour)

3. ACK (Acknowledge) :
   [Dakar] -> [Londres]
   
   Paquet TCP : ACK, seq=1001, ack=5001
   [TEMPS] Temps : ~100 ms (aller)

Total pour établir la connexion TCP : ~300 ms

CHEMIN PHYSIQUE DES PAQUETS :
┌──────────────────────────────────────────────────────────────────┐
│  Ton PC (Dakar)                                                  │
│       v                                                          │
│  Box Internet / Routeur domestique                               │
│       v                                                          │
│  Réseau FAI Sénégalais (Sonatel/Orange)                         │
│       v                                                          │
│  Point d'atterrissage câble sous-marin (Dakar)                  │
│       v                                                          │
│  CÂBLE SOUS-MARIN ACE (Africa Coast to Europe)                  │
│    -> Traverse l'Océan Atlantique (4500 km)                      │
│    -> Fibre optique, vitesse lumière : ~200 000 km/s             │
│    -> Temps de propagation : ~22 ms                               │
│       v                                                          │
│  Point d'atterrissage Europe (Portugal/Espagne)                 │
│       v                                                          │
│  Backbone Internet européen                                      │
│    -> Routeurs à Paris, Amsterdam                                │
│       v                                                          │
│  Point d'échange Internet (IXP) Londres                         │
│       v                                                          │
│  Réseau AWS (Amazon Web Services)                               │
│       v                                                          │
│  Data Center AWS eu-west-2 (Londres)                            │
│       v                                                          │
│  Serveur PostgreSQL (51.124.45.67:5432)                         │
└──────────────────────────────────────────────────────────────────┘


ÉTAPE 3 : NÉGOCIATION SSL/TLS (SÉCURISATION) - OPTIONNEL
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Si connexion sécurisée (recommandé pour Internet) :

$ psql "host=db.mycompany.com user=appuser dbname=ecommerce sslmode=require"

1. Client Hello (Dakar -> Londres) :
   "Je veux SSL, voici les protocoles que je supporte"
   [TEMPS] +100 ms

2. Server Hello + Certificat (Londres -> Dakar) :
   "OK, voici mon certificat SSL"
   [TEMPS] +100 ms

3. Vérification du certificat (Dakar) :
   -> Vérifier la chaîne de certification
   -> Confirmer que le certificat est valide
   
4. Clés de session échangées (Dakar <-> Londres) :
   -> Utilisation de Diffie-Hellman ou RSA
   [TEMPS] +200 ms

Total pour SSL/TLS : ~400 ms supplémentaires
Connexion maintenant CHIFFRÉE [OK]


ÉTAPE 4 : AUTHENTIFICATION POSTGRESQL (DAKAR <-> LONDRES)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
1. Postmaster (Londres) reçoit la demande de connexion
   -> Consulte pg_hba.conf
   -> IP 41.82.123.45 autorisée ? [OK]
   -> Méthode : scram-sha-256
   
2. Postmaster FORKE un processus backend dédié
   [TEMPS] Temps local : ~1 ms

3. Échange d'authentification SCRAM-SHA-256 :
   
   a) Backend -> Client (Londres -> Dakar) :
      "Je veux SCRAM-SHA-256, voici le challenge"
      [TEMPS] +100 ms
   
   b) Client calcule la réponse (Dakar) :
      -> Hash du mot de passe + salt + challenge
      [TEMPS] Temps local : ~1 ms
   
   c) Client -> Backend (Dakar -> Londres) :
      "Voici ma réponse au challenge"
      [TEMPS] +100 ms
   
   d) Backend vérifie (Londres) :
      -> Consulte pg_authid
      -> Vérifie le hash
      -> Authentification OK [OK]
      [TEMPS] Temps local : ~2 ms
   
   e) Backend -> Client (Londres -> Dakar) :
      "Authentification réussie"
      [TEMPS] +100 ms

Total authentification : ~300 ms


ÉTAPE 5 : ENVOI DE LA REQUÊTE SQL (DAKAR -> LONDRES)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
ecommerce=> SELECT * FROM clients WHERE pays = 'Senegal';

Requête envoyée via la connexion TCP chiffrée :

[Dakar] Composition de la requête en protocole PostgreSQL :
-> Message type : 'Q' (Query)
-> Longueur : 56 bytes
-> Contenu : "SELECT * FROM clients WHERE pays = 'Senegal'"

[Dakar -> Londres]
-> Paquet TCP (encapsulé dans IP)
-> Traverse tout le chemin réseau
-> [TEMPS] Temps : ~100 ms

[Londres] Backend reçoit la requête :
-> Déchiffrement SSL
-> Parsing du message PostgreSQL
-> Extraction de la requête SQL
-> [TEMPS] Temps local : <1 ms


ÉTAPE 6 : TRAITEMENT CÔTÉ SERVEUR (LONDRES)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Tout se passe maintenant SUR LE SERVEUR à Londres :

1. PARSING (analyse) :
   -> Vérification syntaxe SQL
   -> Vérification que la table "clients" existe
   -> Vérification que la colonne "pays" existe
   -> Vérification des permissions
   [TEMPS] Temps : 0.1-0.5 ms

2. REWRITER (réécriture) :
   -> Application des règles (RULES)
   -> Pas de modification dans ce cas
   [TEMPS] Temps : <0.1 ms

3. PLANNER (optimisation) :
   -> Consultation des statistiques (pg_stats)
   -> Table clients : 1 000 000 lignes
   -> Colonne pays : 195 valeurs distinctes
   -> Sélectivité pour 'Senegal' : ~0.5% (5000 lignes)
   
   Plans possibles :
   A) Seq Scan (parcours séquentiel) : coût estimé 15000
   B) Index Scan sur idx_clients_pays : coût estimé 250
   
   -> Choix : Index Scan [OK]
   [TEMPS] Temps : 1-3 ms

4. EXECUTOR (exécution) :
   
   a) Accès à l'index B-Tree (idx_clients_pays) :
      -> Recherche de 'Senegal' dans l'arbre
      -> Trouve les TID des 5000 lignes concernées
      -> Index peut être en Shared Buffers (cache) [RAPIDE]
      [TEMPS] Temps : 2-5 ms (si en cache)
   
   b) Lecture des tuples :
      -> Avec les TID, accès direct aux pages de données
      -> Vérification MVCC (visibilité des tuples)
      -> Consultation de pg_xact pour chaque tuple
      
      CAS 1 : Données en Shared Buffers (cache) [RAPIDE]
      -> Lecture RAM ultra-rapide
      -> 5000 tuples × 10 µs = 50 ms
      
      CAS 2 : Données PAS en cache (cache miss) [LENT]
      -> Lecture depuis le SSD AWS
      -> 5000 tuples, ~100 pages
      -> 100 lectures SSD × 1 ms = 100 ms
   
   c) Construction du résultat :
      -> Copie des données dans le buffer de résultat
      [TEMPS] Temps : 5-10 ms

Total exécution (meilleur cas avec cache) : ~60 ms
Total exécution (pire cas sans cache) : ~110 ms


ÉTAPE 7 : RETOUR DES RÉSULTATS (LONDRES -> DAKAR)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Backend (Londres) envoie les résultats au client (Dakar)

1. Formatage des données :
   -> Conversion en protocole PostgreSQL
   -> Message type : 'D' (DataRow)
   -> Chiffrement SSL
   [TEMPS] Temps : 5-10 ms

2. Envoi par paquets TCP (Londres -> Dakar) :
   -> 5000 lignes × ~200 bytes/ligne = 1 MB de données
   -> Segmentation en paquets TCP (MTU 1500 bytes)
   -> ~700 paquets TCP
   
   a) Premier paquet (Londres -> Dakar) :
      [TEMPS] +100 ms (latence réseau)
   
   b) Paquets suivants en streaming :
      -> Envoi continu grâce au fenêtrage TCP (TCP Window)
      -> Bande passante disponible : ~10 Mbps (lien international typique)
      -> Temps de transfert : 1 MB / (10 Mbps / 8) = ~0.8 seconde
      [TEMPS] +800 ms
   
   c) Acquittements (ACK) périodiques (Dakar -> Londres) :
      -> Confirmer la réception des paquets
      -> [TEMPS] +100 ms par acquittement (plusieurs en parallèle)

Total transmission : ~1 seconde


ÉTAPE 8 : RÉCEPTION ET AFFICHAGE (DAKAR)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
1. Réception des paquets TCP :
   -> Assemblage des paquets dans le bon ordre
   -> Déchiffrement SSL
   [TEMPS] Temps : quelques ms en continu

2. Parsing du protocole PostgreSQL :
   -> Extraction des lignes de données
   [TEMPS] Temps : ~50 ms

3. Affichage dans psql :
   -> Formatage en tableau ASCII
   -> Affichage progressif (streaming)
   [TEMPS] Temps : ~100 ms

 id  |     nom          |  pays   |    email
-----+------------------+---------+-------------------------
 142 | Amadou Diallo    | Senegal | amadou@example.sn
 305 | Fatou Sall       | Senegal | fatou@example.sn
 ...
(5000 rows)


ÉTAPE 9 : FERMETURE DE LA CONNEXION (SI TERMINÉ)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
\q

1. Client envoie message de terminaison (Dakar -> Londres) :
   [TEMPS] +100 ms

2. Backend (Londres) ferme la connexion :
   -> Libération des ressources
   -> Fin du processus backend
   
3. Fermeture TCP en 4 étapes (FIN-ACK-FIN-ACK) :
   [TEMPS] +200 ms


RÉCAPITULATIF TEMPOREL COMPLET :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Résolution DNS :                    50-100 ms
Handshake TCP (3-way) :            300 ms
Négociation SSL/TLS :              400 ms (si activé)
Authentification PostgreSQL :       300 ms
──────────────────────────────────────────
TOTAL CONNEXION :                  ~1050 ms (1.05 secondes)

Envoi requête (Dakar -> Londres) :  100 ms
Traitement serveur (Londres) :      60-110 ms
Retour résultats (Londres -> Dakar) : 1000 ms
Affichage (Dakar) :                 150 ms
──────────────────────────────────────────
TOTAL REQUÊTE :                    ~1300 ms (1.3 secondes)

GRAND TOTAL (première connexion + première requête) :
~2.35 secondes (2350 ms)

Requêtes suivantes (connexion déjà établie) :
~1.3 secondes par requête


FACTEURS IMPACTANT LA PERFORMANCE :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

1. LATENCE RÉSEAU (round-trip time) :
   [X] Dakar <-> Londres : ~200 ms
   [OK] Dakar <-> Dakar (serveur local) : ~1 ms
   -> Impact : 200x plus lent à cause de la distance !

2. BANDE PASSANTE :
   [X] Lien international : 10-50 Mbps typique
   [OK] LAN local : 1 Gbps
   -> Impact sur le transfert de gros volumes de données

3. QUALITÉ DU CÂBLE SOUS-MARIN :
   -> Câble ACE : bon état (récent, maintenu)
   -> En cas de coupure : reroutage via SAT-3/WASC (plus lent)
   -> En cas de congestion : augmentation latence

4. CACHE (SHARED BUFFERS) :
   [OK] Données en cache : 60 ms de traitement
   [X] Données sur SSD : 110 ms de traitement
   -> Impact : quasi-double si cache miss

5. INDEX :
   [OK] Avec index : 60-110 ms
   [X] Sans index (Full Seq Scan) : 5000 ms (5 secondes !)
   -> Impact : 50x plus lent sans index !


OPTIMISATIONS POSSIBLES :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

1. RÉPLICATION GÉOGRAPHIQUE :
   -> Avoir un serveur PostgreSQL RÉPLIQUÉ à Dakar
   -> Lecture locale (latence ~1 ms)
   -> Écriture toujours à Londres (ou réplication bi-directionnelle)

2. CONTENT DELIVERY NETWORK (CDN) pour données statiques :
   -> Mettre en cache les données rarement modifiées près de l'utilisateur

3. CONNECTION POOLING :
   -> PgBouncer entre Dakar et Londres
   -> Réutiliser les connexions (éviter handshake TCP + auth à chaque fois)
   -> Économie de 1 seconde par requête !

4. COMPRESSION :
   -> Activer la compression SSL/TLS
   -> Réduire la taille des données transférées
   -> Trade-off : CPU vs bande passante

5. BATCH REQUESTS :
   -> Grouper plusieurs requêtes en une seule
   -> Réduire le nombre d'allers-retours réseau

6. CACHE APPLICATION :
   -> Redis/Memcached à Dakar
   -> Mettre en cache les résultats fréquents
   -> Éviter d'aller jusqu'à Londres pour chaque requête


ANALOGIE FINALE : LE VOYAGE POSTAL
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Requête SQL = Envoyer une lettre de Dakar à Londres et recevoir la réponse

SANS BASE DE DONNÉES DISTANTE (serveur local) :
-> Tu cries à ton voisin (Dakar) : "Qui habite au Sénégal ?"
-> Il te répond immédiatement : "Amadou, Fatou, ..."
-> Temps : 1 seconde (immédiat)

AVEC BASE DE DONNÉES À LONDRES (serveur distant) :
1. Tu écris une lettre : "Qui habite au Sénégal ?"
2. Tu l'envoies par avion à Londres (4500 km)
3. La personne à Londres cherche dans son fichier
4. Elle écrit la réponse sur 5000 pages
5. Elle renvoie le tout par avion à Dakar
6. Tu reçois et tu lis les 5000 pages

-> Temps : 2.35 secondes (beaucoup plus lent !)

C'EST EXACTEMENT ÇA LA LATENCE RÉSEAU !
Plus le serveur est LOIN, plus c'est LENT
-> Même avec la vitesse de la lumière (300 000 km/s), la distance compte !


CONCLUSION :
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Maintenant tu COMPRENDS TOUT le chemin d'une requête SQL :
-> De ton clavier à Dakar
-> À travers le câble sous-marin ACE
-> Jusqu'au serveur PostgreSQL à Londres
-> Traitement de la requête (parsing, planning, execution)
-> Retour des résultats via le même chemin
-> Affichage sur ton écran à Dakar

Tu connais chaque milliseconde, chaque composant, chaque fichier ! [RAPIDE]
"""

# FIN DU GUIDE