TL;DR

• Le WAL (Write-Ahead Logging) garantit qu'aucune donnée n'est perdue en cas de crash. Postgres ecrit d'abord dans le journal, ensuite sur disque.
• MVCC permet a plusieurs transactions de lire et ecrire simultanement sans se bloquer. Chaque transaction voit un snapshot coherent.
• Le query planner decide comment executer ta requete : seq scan, index scan, bitmap scan. EXPLAIN ANALYZE est ton meilleur ami.
• B-tree pour 90% des cas, GIN pour le full-text et JSONB, GiST pour la geo, BRIN pour les donnees chronologiques.
• VACUUM nettoie les dead tuples laisses par MVCC. Sans lui, ta base grossit indefiniment et tes requetes ralentissent.

Tu utilises Postgres tous les jours. Mais sais-tu comment il marche ?

On lance un CREATE TABLE, on ecrit des requetes, on ajoute un index quand ca rame. Mais ce qui se passe entre le moment ou tu envoies un INSERT et celui ou la donnee est reellement persistee sur disque, la plupart des devs n'en ont aucune idee.

Et c'est un probleme. Parce que quand ta requete met 3 secondes au lieu de 30 millisecondes, ou quand ta table de 10 millions de lignes commence a ralentir toute l'application, il faut comprendre les mecanismes internes pour diagnostiquer et corriger.

PostgreSQL est la base de donnees open-source la plus fiable et la plus complete du marche. Mais sa fiabilite repose sur des mecanismes precis qu'on va decortiquer ensemble.

Le WAL : pourquoi Postgres ne perd jamais de donnees

Le Write-Ahead Logging est le premier mecanisme a comprendre. Le principe est simple : avant d'ecrire quoi que ce soit sur les fichiers de donnees, Postgres ecrit d'abord dans un journal sequentiel appele WAL.

Pourquoi ? Parce qu'ecrire de maniere sequentielle est beaucoup plus rapide qu'ecrire a des endroits aleatoires sur le disque. Et surtout, en cas de crash (coupure de courant, kill du processus, panique du kernel), le WAL permet de rejouer toutes les operations qui n'avaient pas encore ete ecrites sur les fichiers de donnees.

Le cycle est le suivant :

1. Tu envoies un INSERT INTO users (name) VALUES ('Alice').
2. Postgres ecrit l'operation dans le WAL (journal sequentiel).
3. Postgres met a jour les pages en memoire (shared buffers).
4. A un moment donne, un processus en arriere-plan (le checkpointer) ecrit les pages modifiees sur disque.
5. Si un crash survient entre les etapes 2 et 4, Postgres rejoue le WAL au redemarrage.

C'est pour ca que fsync = on est critique en production. Sans ca, le WAL lui-meme pourrait ne pas etre ecrit sur disque.

-- Voir l'état du WAL
SELECT pg_current_wal_lsn(), pg_walfile_name(pg_current_wal_lsn());

-- Taille du WAL en attente d'archivage
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes
FROM pg_stat_replication;

Le WAL est aussi la base de la replication (streaming replication) et des sauvegardes incrementales (pg_basebackup + archivage WAL). Un seul mecanisme, trois cas d'usage critiques.

MVCC : comment plusieurs transactions coexistent

Le Multi-Version Concurrency Control est ce qui permet a Postgres de gerer des centaines de transactions simultanees sans que les lecteurs bloquent les ecrivains (et inversement).

Le principe : chaque UPDATE ne modifie pas la ligne en place. Il cree une nouvelle version de la ligne et marque l'ancienne comme "morte". Chaque transaction voit un snapshot coherent de la base a un instant donne.

Concretement, chaque ligne dans Postgres a deux colonnes cachees :

• xmin : l'ID de la transaction qui a cree cette version de la ligne.
• xmax : l'ID de la transaction qui a supprime ou remplace cette version.

Quand tu fais un SELECT, Postgres verifie pour chaque ligne si xmin est visible pour ta transaction et si xmax ne l'a pas encore invalidee. C'est comme ca qu'un SELECT en cours ne voit jamais une ecriture non commitee.

-- Voir les colonnes cachées de MVCC
SELECT xmin, xmax, * FROM users LIMIT 5;

-- Nombre de dead tuples (lignes mortes)
SELECT relname, n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_ratio
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

L'avantage de MVCC : les lecteurs ne bloquent jamais les ecrivains. Un SELECT sur une table de 100 millions de lignes ne ralentit pas les INSERT en cours. C'est fondamentalement different de MySQL avec InnoDB ou les locks sont beaucoup plus agressifs dans certains cas.

L'inconvenient : les anciennes versions des lignes s'accumulent. C'est la que VACUUM entre en jeu.

Le query planner : comment Postgres decide d'executer ta requete

Quand tu envoies une requete SQL, Postgres ne l'execute pas directement. Il passe par plusieurs etapes :

Le planner est la piece maitresse. Il evalue differentes strategies d'execution et choisit celle qui a le cout estime le plus bas. Pour ca, il s'appuie sur les statistiques collectees par ANALYZE (nombre de lignes, distribution des valeurs, cardinalite).

Les principaux types de scan :

Seq Scan. Lecture sequentielle de toute la table. C'est le plan par defaut quand il n'y a pas d'index, ou quand la requete va de toute facon lire une grande partie de la table. Ce n'est pas forcement mauvais -- sur une petite table, un seq scan est souvent plus rapide qu'un index scan.

Index Scan. Utilise un index pour trouver directement les lignes. Ideal quand la requete filtre sur une colonne indexee et retourne peu de lignes.

Bitmap Index Scan. Un compromis entre les deux. Postgres parcourt l'index pour creer un bitmap des pages a lire, puis lit ces pages. Utile quand la requete retourne un nombre moyen de lignes.

Index Only Scan. Le graal : toutes les donnees sont dans l'index, pas besoin de lire la table. Possible avec des covering indexes.

-- Toujours utiliser EXPLAIN ANALYZE, pas juste EXPLAIN
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders
WHERE user_id = 42 AND created_at > '2025-01-01';

-- Résultat typique :
-- Index Scan using idx_orders_user_date on orders
--   Index Cond: (user_id = 42) AND (created_at > '2025-01-01')
--   Buffers: shared hit=12
--   Planning Time: 0.15 ms
--   Execution Time: 0.42 ms

Le piege classique : un EXPLAIN sans ANALYZE te montre les estimations du planner, pas la realite. Avec ANALYZE, tu vois les temps reels et le nombre de lignes reellement traitees. La difference entre les deux peut reveler des statistiques obsoletes.

Les types d'index : B-tree n'est pas la seule option

Quand tu fais CREATE INDEX, par defaut c'est un B-tree. Et dans 90% des cas, c'est le bon choix. Mais Postgres offre d'autres types d'index, chacun optimise pour un cas d'usage precis.

B-tree. Le standard. Fonctionne pour les comparaisons d'egalite (=), de range (<, >, BETWEEN), et le tri (ORDER BY). Utilise-le pour les cles primaires, les cles etrangeres, et les colonnes de filtre classiques.

GIN (Generalized Inverted Index). Optimise pour les types contenant plusieurs valeurs : tableaux, JSONB, full-text search (tsvector). Quand tu fais WHERE tags @> ARRAY['python'] ou WHERE document @@ to_tsquery('postgres'), c'est un GIN qu'il te faut.

GiST (Generalized Search Tree). Pour les donnees geometriques et les ranges. PostGIS l'utilise pour les requetes spatiales (ST_Contains, ST_DWithin). Aussi utile pour les colonnes de type tsrange, daterange.

BRIN (Block Range Index). Le plus econome en espace. Fonctionne sur des donnees naturellement ordonnees physiquement, comme une colonne created_at dans une table ou les insertions sont chronologiques. Un BRIN sur une table de 100 millions de lignes peut faire quelques Mo au lieu de plusieurs Go pour un B-tree.

-- B-tree classique
CREATE INDEX idx_users_email ON users (email);

-- GIN pour JSONB
CREATE INDEX idx_events_data ON events USING gin (data);

-- GiST pour la géo
CREATE INDEX idx_places_location ON places USING gist (location);

-- BRIN pour les données chronologiques
CREATE INDEX idx_logs_created ON logs USING brin (created_at);

-- Covering index (Index Only Scan)
CREATE INDEX idx_orders_cover ON orders (user_id) INCLUDE (total, status);

La regle : commence toujours par un B-tree. Si ca ne suffit pas et que le type de donnees le justifie, envisage un index specialise. Et n'oublie pas que chaque index ralentit les ecritures -- ne mets pas un index sur chaque colonne.

VACUUM : le ramasse-miettes de Postgres

On l'a vu avec MVCC : chaque UPDATE ou DELETE laisse des dead tuples. Ces lignes mortes occupent de l'espace disque et ralentissent les scans (le moteur doit les lire puis les ignorer).

VACUUM est le processus qui nettoie ces dead tuples. Il existe en deux versions :

VACUUM standard. Marque l'espace des dead tuples comme reutilisable, mais ne le rend pas au systeme d'exploitation. La table ne retrecit pas, mais les futurs INSERT peuvent reutiliser cet espace.

VACUUM FULL. Reecrit entierement la table pour recuperer l'espace. Beaucoup plus lent, et prend un verrou exclusif sur la table pendant toute l'operation. A utiliser avec parcimonie.

L'autovacuum est active par defaut et se declenche automatiquement. Mais ses parametres par defaut sont souvent trop conservateurs pour les tables a fort trafic.

-- État de l'autovacuum
SELECT relname, last_vacuum, last_autovacuum, n_dead_tup,
       autovacuum_count, vacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- Tuner l'autovacuum pour une table à fort trafic
ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.01,  -- 1% au lieu de 20%
  autovacuum_analyze_scale_factor = 0.005
);

Les symptomes d'un VACUUM en retard : des requetes qui ralentissent progressivement, un pg_stat_user_tables.n_dead_tup qui explose, et un espace disque qui croit sans que le nombre de lignes actives augmente.

Un piege courant : les transactions longues empechent VACUUM de nettoyer les dead tuples. Si une transaction reste ouverte pendant des heures, elle maintient un snapshot ancien et Postgres ne peut pas recycler les lignes mortes anterieures a ce snapshot. Surveille pg_stat_activity pour les transactions idle in transaction.

En pratique : le diagnostic en 5 minutes

Quand une requete est lente, voici la checklist :

1. EXPLAIN ANALYZE. Regarde le plan reel. Identifie les seq scans non voulus, les estimations de lignes incorrectes, les boucles imbriquees couteuses.
2. Statistiques a jour. Lance ANALYZE sur la table concernee. Si les estimations du planner sont fausses, c'est souvent ca.
3. Index manquant. Verifie que les colonnes filtrees et jointes sont indexees. pg_stat_user_indexes montre les index existants et leur utilisation.
4. Dead tuples. Verifie n_dead_tup dans pg_stat_user_tables. Si le ratio dead/live est eleve, lance un VACUUM.
5. Transactions longues. Verifie pg_stat_activity pour les connexions idle in transaction depuis plus de quelques minutes.

-- Requêtes les plus lentes (nécessite pg_stat_statements)
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Postgres est extraordinairement fiable et performant quand on comprend ses mecanismes internes. Le WAL garantit la durabilite, MVCC garantit la concurrence, le planner optimise l'execution, les index accelerent les lectures, et VACUUM maintient la sante de la base. Ces cinq mecanismes forment un systeme coherent -- et les comprendre fait la difference entre un dev qui utilise Postgres et un dev qui maitrise Postgres.

Ressources

• PostgreSQL Documentation officielle -- la reference absolue, bien ecrite et complete
• The Internals of PostgreSQL -- livre gratuit en ligne, deep dive dans les mecanismes internes
• pganalyze Blog -- articles techniques sur la performance et le monitoring
• Use The Index, Luke -- guide d'indexation SQL, applicable a Postgres
• EXPLAIN ANALYZE visualizer -- outil en ligne pour visualiser les plans d'execution