MySQL vs PostgreSQL : le choix honnête pour une base de données de site web (selon de vrais goulots d’étranglement)

La thèse : choisissez le goulot d’étranglement avec lequel vous préférez vivre

Si votre site est une application web typique — tables CRUD, sessions utilisateur, commandes, pages de type recherche, quelques jobs en arrière-plan — vous pouvez exploiter MySQL ou PostgreSQL avec beaucoup de succès. La différence honnête n’est pas « lequel est plus rapide ». C’est :

• Quel mode d’échec est le plus probable dans votre organisation ? (SQL mauvais ? migrations bâclées ? schémas négligés ? hygiène des index douteuse ?)
• Quel workflow opérationnel convient à votre équipe ? (réplication, basculement, sauvegardes, migrations, analyse des requêtes)
• Quel goulot êtes-vous prêts à surveiller ? (bloat/vacuum, contention de verrous, latence de réplication, pression du buffer pool, tempêtes de connexions)

Mon choix par défaut et opiné pour une nouvelle « base de site web » en 2025 : PostgreSQL, sauf si vous avez une raison concrète de ne pas l’utiliser. Pas « on a toujours utilisé MySQL ». Une raison valide serait : « nous exploitons déjà MySQL à grande échelle, nous avons un basculement éprouvé, des personnes qui savent déboguer InnoDB, et notre appli n’a pas besoin des points forts de Postgres. »

Quand je conseille MySQL ? Quand l’entreprise possède déjà une expertise opérationnelle MySQL, quand la charge est surtout des lectures/écritures simples avec des schémas d’accès prévisibles, et quand vous voulez moins d’aspérités autour du nombre de connexions (pas parce que MySQL est magiquement immunisé, mais parce que l’overhead par connexion de Postgres punit plus vite une mise en pool négligente).

Quand je conseille Postgres ? Quand l’exactitude des données et une sémantique saine comptent, quand vous faites plus que « SELECT par clé primaire », quand vous anticipez des requêtes de type analytique, et quand vous voulez un planificateur et un écosystème d’index qui récompensent une bonne modélisation.

Blague n°1 : Choisir une base de données par popularité, c’est comme choisir un parachute par couleur — parfait jusqu’au moment où vous en avez besoin.

Faits et histoire intéressants (parce que ça explique les aspérités)

Ce ne sont pas des trivia pour le plaisir. Ils expliquent pourquoi certains comportements et paramètres par défaut existent.

1. PostgreSQL vient de Postgres (années 1980) à l’UC Berkeley ; il a été façonné dès le départ par des idées académiques comme MVCC et l’extensibilité.
2. Le succès initial de MySQL était « rapide et simple » pour les charges web — surtout quand on n’avait pas besoin de transactions. Cet héritage reste visible dans son écosystème et ses usages.
3. InnoDB est devenu le moteur de stockage par défaut (pour MySQL) parce que le web demandait des transactions et une récupération après crash ; MySQL moderne est essentiellement « InnoDB avec du SQL dessus ».
4. Postgres a implémenté MVCC sans journaux d’annulation à la manière d’InnoDB ; les anciennes versions de lignes restent jusqu’à ce que le vacuum les nettoie. C’est la racine du bloat et des réglages de vacuum.
5. La réplication MySQL a historiquement privilégié la simplicité (basée sur le binlog) et la compatibilité large ; c’est pourquoi elle est omniprésente, y compris dans des stacks « on l’a construit en 2012 et ça tourne encore ».
6. Les extensions Postgres sont une culture de première classe (pensez : types d’index personnalisés, recherche en texte intégral, langages procéduraux). Ce n’est pas juste « une base de données », c’est une plateforme.
7. Le support JSON a divergé philosophiquement : JSONB de Postgres est profondément indexable ; le JSON de MySQL est utilisable et s’améliore mais tend à pousser vers une dérive vers des schémas semi-structurés plus tôt.
8. Le planificateur de requêtes de Postgres est célèbre pour être opiniâtre et parfois faux de manière surprenante ; la solution est généralement les statistiques et les index, pas la prière.
9. L’écosystème MySQL comprend plusieurs branches et vendeurs majeurs (builds communautaires, offres commerciales, forks). Bien pour les options ; moins bien pour « quel comportement exact avons-nous ? »

Ce que signifie réellement « base de site web » (et ce que ça ne signifie pas)

La plupart des sites ne sont pas du pur OLTP. Ce sont des mélanges désordonnés :

• Requêtes sur le chemin chaud : connexion, vérifications de session, pages produit, opérations panier. Sensibles à la latence.
• Jobs d’arrière-plan : envoi d’e-mails, indexation, synchronisations périodiques. Sensibles au débit.
• Administration / reporting : « montre-moi les commandes par statut » avec dix filtres et tris. Sensible au planificateur.
• Recherche et flux : pagination, tri par date, « recommandé ». Sensible aux index.
• Pics aléatoires : campagnes marketing, crawlers, cron incontrôlable. Sensible aux connexions.

Les deux bases peuvent gérer cela. Le piège est qu’elles échouent différemment :

• MySQL a tendance à vous punir par la contention de verrous, des surprises de réplication et un « ça allait puis ça n’allait plus » dû au buffer pool ou à la saturation I/O.
• Postgres a tendance à vous punir par des tempêtes de connexions, la dette de vacuum, le bloat et des requêtes qui passent de 50 ms à 50 s quand les stats dérivent.

Vous ne choisissez pas une base ; vous choisissez un ensemble de corvées opérationnelles. Choisissez celles que votre équipe fera réellement.

Goulots réels : où chacun fait mal

1) Verrous et concurrence : « pourquoi tout attend ? »

Postgres utilise MVCC et des verrous au niveau ligne. Les lectures ne bloquent généralement pas les écritures, et les écritures ne bloquent pas les lectures, jusqu’à ce que vous atteigniez des verrous explicites, des contrôles de clés étrangères, des transactions longues ou des changements de schéma. Quand ça tourne mal, c’est souvent : une transaction longue retient le vacuum et crée une file d’attente de requêtes bloquées.

MySQL/InnoDB utilise aussi MVCC, mais son comportement de verrouillage (gap locks, next-key locks) peut vous surprendre selon le niveau d’isolation et les motifs d’accès. Le mode d’échec courant : une requête qui « devrait toucher une ligne » prend des verrous sur une plage à cause d’un choix d’index, et soudain vous avez un embouteillage.

2) Planification et indexation des requêtes : « pourquoi cette requête est devenue stupide ? »

Postgres brille quand vous avez des requêtes complexes, plusieurs jointures, des indexes partiels et fonctionnels. Mais il exige de bonnes statistiques et une évolution de schéma disciplinée. Quand autovacuum/analyze prend du retard, les plans pourrissent. Vous le voyez par des scans séquentiels soudains ou de mauvais ordres de jointure.

MySQL peut être extrêmement rapide sur des chemins d’accès simples et des index prévisibles. Mais l’optimiseur a historiquement eu plus de « pièges » autour des derived tables, des sous-requêtes et de l’ordre des jointures. En pratique : les équipes réécrivent les requêtes ou dénormalisent plus tôt.

3) Réplication et basculement : « l’appli dit validé, mais la réplica n’est pas d’accord »

Les deux ont une réplication mature. Les deux peuvent vous faire mal.

• MySQL : la réplication est omniprésente et bien comprise. La douleur classique est la latence de réplication lors d’explosions d’écritures et la complexité opérationnelle des changements de topologie si vous n’avez pas planifié.
• Postgres : la réplication en streaming est robuste, mais il faut être explicite sur les compromis synchrone vs asynchrone. La douleur arrive souvent quand on suppose que les réplicas sont lisibles pour tout, puis on découvre des conflits hot standby ou de la latence sous des requêtes longues.

4) Comportement du moteur de stockage : cache, I/O et amplification d’écriture

MySQL/InnoDB veut un grand buffer pool bien dimensionné. S’il est trop petit ou si votre working set augmente, vous trépignez. Vous verrez l’I/O de lecture grimper, des pics de latence et un serveur qui a l’air « OK » sur le CPU mais qui meurt côté stockage.

Postgres s’appuie fortement sur le cache de pages du noyau plus shared_buffers. Il peut très bien performer, mais il est plus sensible au bloat de tables et d’index. Le bloat signifie que vous lisez des pages pleines de tuples morts et d’entrées d’index obsolètes. Votre sous-système de stockage paie pour vos péchés.

5) Maintenance : vacuum vs purge, et ce que « ennuyeux » signifie vraiment

Postgres nécessite du vacuum. Autovacuum est performant, mais « performant » n’est pas « réglé et oublié ». Si vous faites beaucoup de updates/deletes et que vous ne paramétrez pas le vacuum, vous finirez par buter : tables gonflées, requêtes lentes et éventuellement des urgences de transaction ID wraparound.

MySQL purge en interne (undo logs) et n’a pas d’équivalent direct du vacuum. Moins de boutons à tourner, mais pas de repas gratuit : vous gérez toujours les index, la fragmentation et vous payez pour de mauvaises décisions de schéma. De plus, de grosses opérations ALTER TABLE et le comportement du DDL en ligne peuvent devenir leur propre type d’ennui du weekend.

6) Gestion des connexions : Postgres punit la mise en pool négligente

Postgres utilise un modèle processus-par-connexion dans de nombreuses configurations ; trop de connexions = overhead mémoire, changements de contexte et douleur. Vous voulez presque toujours un pooler (comme pgbouncer) pour les applis web à trafic en rafales.

Le modèle de threads de MySQL et ses valeurs par défaut typiques peuvent tolérer plus de connexions avant de s’écrouler, mais « tolérer » n’est pas « sûr ». Si vous laissez l’appli ouvrir des milliers de connexions parce que « ça marchait en staging », vous finirez par vivre la même expérience en production.

7) Fonctionnalités qui affectent réellement l’architecture d’un site

• Postgres : extensions puissantes, types d’index riches, contraintes robustes, fonctions fenêtre avancées, excellent indexage JSONB, sémantique généralement plus stricte.
• MySQL : écosystème excellent, large support d’hébergement, patterns opérationnels courants, très bonnes performances pour beaucoup de charges OLTP simples.

Plan de diagnostic rapide (premier/deuxième/troisième)

Si votre site est lent ou en timeout, ne commencez pas par changer une config au hasard. Commencez par déterminer quelle classe de goulot vous subissez. Voici le triage le plus rapide qui fonctionne en incidents réels.

Premier : la base attend-elle le CPU, l’I/O ou des verrous ?

• CPU saturé : cherchez des requêtes coûteuses (mauvais plans), des index manquants, des rapports en fuite ou des débordements de hash/agrégation.
• I/O saturé : cherchez des misses de buffer/cache, des scans complets, du bloat ou une régression de la latence de stockage.
• Verrous : cherchez des requêtes bloquées, des transactions longues, du DDL ou une ligne/table chaude.
• Connexions : si le nombre de connexions grimpe, tout le reste peut sembler « OK » alors que l’appli fond.

Second : identifiez les 1–3 requêtes principales par temps total, pas seulement la plus lente

La requête la plus lente est souvent un cas isolé. Le goulot est généralement « cette requête de 20 ms s’exécute 10 000 fois par minute », ou « cette requête de 200 ms s’exécute maintenant en 500 copies concurrentes ».

Troisième : validez le plan et le chemin d’index

Dans Postgres : EXPLAIN (ANALYZE, BUFFERS). Dans MySQL : EXPLAIN plus performance_schema et les métriques handler. Vous cherchez : scans inattendus, mauvais ordre de jointure, tables temporaires, filesorts ou lectures massives de buffers.

Quatrième : vérifiez la réplication et les hypothèses lecture/écriture de l’appli

Beaucoup de tickets « la DB est lente » sont en réalité « latence de réplica + hypothèse read-after-write ». Ce n’est pas philosophique. C’est littéralement un utilisateur qui clique sur « sauvegarder » puis voit des données anciennes.

Cinquième : arrêtez l’hémorragie, puis corrigez la cause racine

Arrêter l’hémorragie consiste souvent à : tuer la pire requête, désactiver un rapport, ajouter un index manquant ou rediriger le trafic loin d’une réplica malade. Ensuite, corrigez le schéma et le code calmement.

Citation (idée paraphrasée), John Allspaw : La fiabilité vient de la façon dont vous répondez à la défaillance, pas du fait de prétendre qu’elle n’arrivera pas.

Tâches pratiques : commandes, sorties et la décision que vous prenez

Ci‑dessous des tâches pratiques que j’exécute vraiment lors d’incidents ou d’interventions de performance. Chacune inclut une commande, un extrait de sortie plausible, ce que ça signifie et la décision suivante.

Task 1 (Postgres) : voir qui fait quoi et qui attend

cr0x@server:~$ psql -X -c "SELECT pid, usename, state, wait_event_type, wait_event, now()-query_start AS age, left(query,80) AS query FROM pg_stat_activity WHERE datname='appdb' ORDER BY age DESC LIMIT 10;"
 pid  | usename | state  | wait_event_type |  wait_event   |   age   |                                      query
------+--------+--------+-----------------+---------------+---------+--------------------------------------------------------------------------------
 8123 | app    | active | Lock            | transactionid | 00:05:12| UPDATE orders SET status='paid' WHERE id=$1
 7991 | app    | active |                 |               | 00:01:44| SELECT * FROM orders WHERE user_id=$1 ORDER BY created_at DESC LIMIT 50
 7902 | app    | idle   | Client          | ClientRead    | 00:00:33| 

Ce que ça signifie : Le PID 8123 attend un verrou transactionid depuis 5 minutes. C’est généralement une transaction longue ailleurs qui retient quelque chose, ou un schéma de contention sur une ligne chaude.

Décision : Trouver le bloqueur (tâche suivante), puis décider de le tuer, corriger la logique applicative, ou ajouter un index/ajuster les patterns d’isolation.

Task 2 (Postgres) : trouver la requête bloquante

cr0x@server:~$ psql -X -c "SELECT blocked.pid AS blocked_pid, blocker.pid AS blocker_pid, now()-blocker.query_start AS blocker_age, left(blocker.query,120) AS blocker_query FROM pg_stat_activity blocked JOIN pg_locks bl ON bl.pid=blocked.pid JOIN pg_locks kl ON kl.locktype=bl.locktype AND kl.database IS NOT DISTINCT FROM bl.database AND kl.relation IS NOT DISTINCT FROM bl.relation AND kl.page IS NOT DISTINCT FROM bl.page AND kl.tuple IS NOT DISTINCT FROM bl.tuple AND kl.virtualxid IS NOT DISTINCT FROM bl.virtualxid AND kl.transactionid IS NOT DISTINCT FROM bl.transactionid AND kl.classid IS NOT DISTINCT FROM bl.classid AND kl.objid IS NOT DISTINCT FROM bl.objid AND kl.objsubid IS NOT DISTINCT FROM bl.objsubid AND kl.pid != bl.pid JOIN pg_stat_activity blocker ON blocker.pid=kl.pid WHERE NOT bl.granted;"
 blocked_pid | blocker_pid | blocker_age |                        blocker_query
-------------+-------------+-------------+-------------------------------------------------------------
 8123        | 7701        | 00:12:09    | BEGIN; UPDATE users SET last_seen=now() WHERE id=$1; -- no COMMIT yet

Ce que ça signifie : Une transaction est ouverte depuis 12 minutes et bloque les autres. C’est probablement un bug applicatif, un worker bloqué ou une connexion tenue ouverte pendant un appel réseau.

Décision : Tuer le PID bloqueur 7701 si c’est sûr, puis corriger le code pour éviter les transactions longues ; ajouter des timeouts ; auditer les scopes de transaction.

Task 3 (Postgres) : inspecter la santé du vacuum et le risque de bloat

cr0x@server:~$ psql -X -c "SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;"
   relname   | n_live_tup | n_dead_tup |    last_autovacuum     |    last_autoanalyze
-------------+------------+------------+------------------------+------------------------
 events      |   24000000 |    9800000 | 2025-12-29 01:12:43+00 | 2025-12-29 00:55:21+00
 sessions    |    3100000 |    1200000 | 2025-12-28 22:41:10+00 | 2025-12-28 22:40:58+00
 orders      |     900000 |      12000 | 2025-12-29 01:10:03+00 | 2025-12-29 01:10:02+00

Ce que ça signifie : La table events a un grand nombre de tuples morts. Si les requêtes sur cette table deviennent plus lentes avec le temps, le bloat en est un suspect principal.

Décision : Ajuster autovacuum pour cette table, envisager le partitionnement, réduire le churn des mises à jour et vérifier si les index sont eux aussi gonflés.

Task 4 (Postgres) : confirmer un mauvais plan avec EXPLAIN ANALYZE

cr0x@server:~$ psql -X -c "EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id=42 ORDER BY created_at DESC LIMIT 50;"
                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..2451.11 rows=50 width=512) (actual time=1832.504..1832.573 rows=50 loops=1)
   Buffers: shared hit=102 read=9180
   ->  Seq Scan on orders  (cost=0.00..98010.22 rows=1999 width=512) (actual time=0.041..1829.721 rows=950000 loops=1)
         Filter: (user_id = 42)
         Rows Removed by Filter: 899000
 Planning Time: 0.412 ms
 Execution Time: 1832.711 ms

Ce que ça signifie : Scan séquentiel avec de lourdes lectures. Le serveur a lu ~9k buffers depuis le disque. Ça sent le « index manquant » (probablement (user_id, created_at)).

Décision : Ajouter un index, puis retester. Si l’index existe, vérifier les statistiques et si la requête utilise l’ordre de l’index.

Task 5 (Postgres) : vérifier l’usage des index et trouver les indexes inutilisés

cr0x@server:~$ psql -X -c "SELECT relname AS table, indexrelname AS index, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size FROM pg_stat_user_indexes ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC LIMIT 10;"
  table  |          index           | idx_scan |  size
---------+--------------------------+----------+---------
 events  | events_payload_gin       |        0 | 312 MB
 orders  | orders_status_created_at |        2 | 148 MB
 users   | users_email_key          |     9012 |  42 MB

Ce que ça signifie : De gros index avec quasiment zéro scans peuvent être des aimants à bloat et amplifier les écritures. Mais ne les supprimez pas aveuglément — certains servent des requêtes d’administration rares ou des contraintes.

Décision : Confirmer via les logs de requêtes et la recherche dans le code ; si vraiment inutiles, supprimer pour accélérer les écritures et le vacuum.

Task 6 (MySQL) : voir les sessions actives et si elles attendent des verrous

cr0x@server:~$ mysql -e "SHOW FULL PROCESSLIST;"
Id	User	Host	db	Command	Time	State	Info
221	app	10.0.1.12:55342	appdb	Query	120	Waiting for row lock	UPDATE orders SET status='paid' WHERE id=12345
238	app	10.0.1.15:49810	appdb	Query	15	Sending data	SELECT * FROM orders WHERE user_id=42 ORDER BY created_at DESC LIMIT 50
250	app	10.0.1.16:51011	appdb	Sleep	300		NULL

Ce que ça signifie : Vous avez des attentes de verrous et aussi des connexions en sommeil. « Waiting for row lock » avec une file croissante signifie contention, généralement due à un motif d’index ou une transaction tenue ouverte.

Décision : Identifier le bloqueur via les tables de verrous InnoDB, puis corriger le scope de transaction et l’indexation.

Task 7 (MySQL) : trouver la chaîne d’attente des verrous InnoDB

cr0x@server:~$ mysql -e "SELECT * FROM information_schema.INNODB_TRX\G"
*************************** 1. row ***************************
trx_id: 54100912
trx_state: LOCK WAIT
trx_started: 2025-12-29 01:20:01
trx_mysql_thread_id: 221
trx_query: UPDATE orders SET status='paid' WHERE id=12345
*************************** 2. row ***************************
trx_id: 54100901
trx_state: RUNNING
trx_started: 2025-12-29 01:05:43
trx_mysql_thread_id: 199
trx_query: UPDATE orders SET shipping_label=... WHERE id=12345

Ce que ça signifie : Le thread 199 exécute une transaction depuis 01:05. Ce n’est pas normal pour une requête web. Probablement un job applicatif tenant une transaction pendant un appel lent (API, upload, boucle de retry).

Décision : Tuer la transaction fautive si c’est sûr ; corriger l’application pour committer plus tôt ; ajouter des timeouts ; s’assurer que les requêtes utilisent le bon index pour réduire l’empreinte de verrouillage.

Task 8 (MySQL) : vérifier rapidement la latence de réplication

cr0x@server:~$ mysql -e "SHOW SLAVE STATUS\G"
Seconds_Behind_Master: 87
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replica_SQL_Running_State: Waiting for dependent transaction to commit
Last_SQL_Error:

Ce que ça signifie : La réplica a ~87 secondes de retard. Les lectures depuis elle seront anciennes. « Waiting for dependent transaction to commit » peut indiquer des rafales d’écritures ou des verrous sur le thread d’application de la réplica.

Décision : Rediriger temporairement les lectures critiques vers le primaire ; réduire l’amplification des écritures ; vérifier les grosses transactions ; ajuster la parallélisation d’application si pertinent.

Task 9 (Postgres) : vérifier la latence de réplication et l’état de replay

cr0x@server:~$ psql -X -c "SELECT client_addr, state, sync_state, write_lag, flush_lag, replay_lag FROM pg_stat_replication;"
 client_addr |   state   | sync_state | write_lag | flush_lag | replay_lag
-------------+-----------+------------+-----------+-----------+------------
 10.0.2.21   | streaming | async      | 00:00:01  | 00:00:02  | 00:00:24

Ce que ça signifie : Le replay lag est de 24 secondes. Si votre appli lit depuis les réplicas, vous verrez des anomalies read-after-write à moins d’en tenir compte dans le code.

Décision : Mettre en place un routage read-your-writes (stickiness) ou passer à une réplication synchrone pour la partie du trafic qui en a besoin.

Task 10 (Postgres) : détecter une tempête de connexions et le besoin d’un pooler

cr0x@server:~$ psql -X -c "SELECT state, count(*) FROM pg_stat_activity GROUP BY state ORDER BY count(*) DESC;"
 state  | count
--------+-------
 idle   | 420
 active | 85
 null   | 0

Ce que ça signifie : Des centaines de connexions idle indiquent que votre appli ou pool tient des connexions ouvertes. C’est un overhead mémoire et peut devenir un fork-bomb lors des pics de trafic.

Décision : Mettre un vrai pooler en avant, définir des max connections raisonnables et ajuster les tailles de pool applicatives.

Task 11 (Linux) : confirmer si vous êtes lié par l’I/O

cr0x@server:~$ iostat -x 1 3
Device            r/s     w/s   rkB/s   wkB/s  await  svctm  %util
nvme0n1         820.0   210.0 51200.0 18400.0   9.80   0.45  89.0

Ce que ça signifie : %util proche de 90% et await ~10ms indiquent que le disque est occupé et que les requêtes font la queue. Si les requêtes sensibles à la latence grimpent en même temps, vous êtes I/O bound.

Décision : Réduire les lectures (corriger les scans, le bloat), augmenter la mémoire/taux de cache, ou passer à un stockage plus rapide. Vérifier aussi si une sauvegarde, un vacuum ou un job batch ne sature pas l’I/O.

Task 12 (Postgres) : voir le ratio de hit du cache et les lectures de buffers

cr0x@server:~$ psql -X -c "SELECT datname, blks_hit, blks_read, round(blks_hit*100.0/nullif(blks_hit+blks_read,0),2) AS hit_pct FROM pg_stat_database WHERE datname='appdb';"
 datname | blks_hit  | blks_read | hit_pct
---------+-----------+-----------+---------
 appdb   | 190224455 |   9221440 |   95.38

Ce que ça signifie : 95% de hit est correct, mais si ça baisse pendant les pics ou si une requête provoque de grosses lectures, vous le ressentirez. Le « taux de hit » n’est pas un trophée ; c’est un indice.

Décision : Si les lectures montent, investiguer le bloat et les index manquants. Si le hit rate est globalement bas, revoir le dimensionnement mémoire et les patterns de requêtes.

Task 13 (MySQL) : vérifier l’efficacité du buffer pool InnoDB

cr0x@server:~$ mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';"
Variable_name	Value
Innodb_buffer_pool_read_requests	184455912
Innodb_buffer_pool_reads	2219441

Ce que ça signifie : Les misses du buffer pool = Innodb_buffer_pool_reads. Si les misses grimpent vite pendant le trafic, le working set ne tient pas, ou les requêtes effectuent des scans.

Décision : Ajouter des index, stopper les scans et dimensionner le buffer pool correctement. Si le dataset dépasse la RAM, prévoir des réplicas de lecture, du sharding ou un meilleur cache.

Task 14 (Both) : vérifier la croissance d’espace disque et la trajectoire « on est plein »

cr0x@server:~$ df -h /var/lib
Filesystem      Size  Used Avail Use% Mounted on
/dev/nvme0n1p2  1.8T  1.6T  120G  94% /var/lib

Ce que ça signifie : 94% d’utilisation n’est pas « correct ». C’est le prélude à un incident. Les bases se comportent mal quand elles ne peuvent pas étendre des fichiers, checkpoint confortablement ou écrire des données temporaires.

Décision : Libérer de l’espace immédiatement (logs, vieux backups, fichiers temporaires), puis corriger le moteur de croissance : bloat, tables en dérive, retention manquante ou index surdimensionnés.

Task 15 (Postgres) : vérifier l’âge des transactions et le risque de wraparound

cr0x@server:~$ psql -X -c "SELECT datname, age(datfrozenxid) AS xid_age FROM pg_database ORDER BY xid_age DESC;"
 datname  | xid_age
----------+---------
 appdb    | 145000000
 template1|   4300000

Ce que ça signifie : Un âge XID élevé signifie que le vacuum ne fige pas les tuples assez vite. S’il monte vers des seuils dangereux, vous ferez face à un autovacuum d’urgence et à un effondrement de performance.

Décision : Investiguer pourquoi le vacuum ne suit pas (transactions longues, autovacuum mal réglé, tables gigantesques) et corriger avant que ce ne devienne existentiel.

Task 16 (MySQL) : capturer les digests de requêtes principales (performance_schema)

cr0x@server:~$ mysql -e "SELECT DIGEST_TEXT, COUNT_STAR, ROUND(SUM_TIMER_WAIT/1000000000000,2) AS total_s FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 3;"
DIGEST_TEXT	COUNT_STAR	total_s
SELECT * FROM orders WHERE user_id = ? ORDER BY created_at DESC LIMIT ?	120000	980.12
UPDATE users SET last_seen = NOW() WHERE id = ?	900000	410.33
SELECT * FROM products WHERE status = ? ORDER BY updated_at DESC LIMIT ?	80000	155.20

Ce que ça signifie : Le plus grand consommateur de temps total est la première requête, même si elle est « assez rapide » par appel. Elle s’exécute en continu.

Décision : L’indexer, la mettre en cache ou réduire son nombre d’appels. Le temps total prime sur « la requête la plus lente » pour la plupart des incidents web.

Trois mini-histoires d’entreprise (anonymisées, plausibles et douloureusement familières)

Mini-histoire 1 : l’incident causé par une hypothèse fausse (les réplicas de lecture sont « à peu près les mêmes »)

Une entreprise SaaS de taille moyenne avait une architecture propre : base primaire pour les écritures, une réplica de lecture pour « tout le reste ». Les développeurs traitaient la réplica comme interchangeable. Une requête écrivait une ligne puis la relisait immédiatement — depuis n’importe quelle connexion que l’ORM lui fournissait.

Ça a fonctionné pendant des mois. Le trafic a augmenté. Les écritures sont devenues rafales : emails marketing, jobs d’arrière-plan, et une nouvelle fonctionnalité qui mettait à jour des compteurs plus souvent qu’on ne l’admettait. La latence de réplication est passée de « millisecondes » à « parfois des secondes ». Personne n’a remarqué car la plupart des pages toléraient des données un peu anciennes.

Puis est venu l’incident : des utilisateurs ont mis à jour leurs informations de facturation, eu un toast « Enregistré ! », ont rafraîchi la page et vu l’ancienne adresse. Le support a été submergé. Les ingénieurs ont d’abord accusé le cache, puis le CDN, puis « le frontend ». Les graphes de base semblaient « corrects » car le CPU n’était pas saturé et la latence n’était que légèrement élevée.

La cause racine était ennuyeuse : incohérence read-after-write due à une réplication asynchrone lente. L’hypothèse erronée n’était fatale que lorsque le processus métier exigeait de la cohérence dans l’interface.

La réparation n’a pas été héroïque. Ils ont implémenté de la stickiness : après une écriture, les lectures de cet utilisateur pointent vers le primaire pour une courte fenêtre (ou jusqu’à l’expiration du token de session). Ils ont aussi rendu la latence de réplication visible sur les tableaux de bord et routé certains endpoints (facturation, auth) vers le primaire en permanence. Après ça, l’« incident DB » est devenu une contrainte de conception, pas une surprise.

Mini-histoire 2 : l’optimisation qui s’est retournée contre eux (indexer tout, puis en payer le prix)

Une plateforme e‑commerce a décidé de « prendre de l’avance sur la perf ». Un développeur senior a ajouté des index pour toutes les combinaisons de filtres concevables de l’UI admin : statut, plage de dates, pays, fournisseur de paiement, SKU, code campagne marketing. Le schéma ressemblait à un hérisson.

Les lectures étaient rapides. Tout le monde s’en félicitait. Puis est arrivé le Black Friday. Les écritures ont ralenti, pas un peu. Les latences de checkout ont grimpé. Le CPU allait bien. Le stockage de la base a commencé à trimer. La latence de réplication a bondi. La plateforme a survécu, mais en mode « on ne peut pas déployer pendant deux jours ».

En postmortem, ils ont trouvé l’évidence que personne ne voulait dire : chaque insert/update devait maintenir une pile d’index rarement utilisés. Le chemin d’écriture payait un loyer pour des fantasmes admin. Pire, la maintenance (vacuum/Postgres ou gestion de pages MySQL) avait plus de travail. Le système est devenu « lectures rapides, tout le reste lent », et le goulot métier était le checkout.

La correction a été une brutalité disciplinée. Ils ont gardé un petit ensemble d’index à forte valeur lié aux requêtes principales et ont supprimé le reste. Pour le reporting admin, ils ont déplacé les requêtes lourdes sur une réplica avec des SLO plus laxistes et construit des tables de synthèse pour les rapports fréquents. La performance s’est améliorée et surtout : la base a cessé d’être un cristal fragile.

Mini-histoire 3 : la pratique ennuyeuse mais correcte qui a sauvé la mise (exercices de restauration)

Un site riche en contenu exploitait Postgres. Rien de fancy : un primaire, une réplica, des backups nocturnes. L’équipe n’était pas réputée pour ses process. Mais un ingénieur staff insistait sur un exercice trimestriel de restauration, comme le fil dentaire de l’infra.

Ils avaient une checklist : récupérer le dernier base backup, le restaurer dans un environnement isolé, rejouer WAL jusqu’à un point dans le temps, exécuter des requêtes de sanity et vérifier que l’application peut démarrer dessus. Ils le faisaient quand personne n’était en feu, ce qui le faisait sembler du busywork. C’était aussi la seule raison pour laquelle ils savaient que leurs backups étaient réels.

Des mois plus tard, une migration a accidentellement supprimé une colonne utilisée par un job d’arrière-plan. Le job a retry, échoué, retry, et écrit des données corrompues dans une autre table. Le rayon d’action n’a pas été immédiat ; c’était une corruption lente, le pire type. Quand ils ont remarqué, les mauvaises données avaient été répliquées et sauvegardées plusieurs fois.

Ils ont fait une récupération point-in-time vers un timestamp juste avant la migration, vérifié avec les mêmes requêtes d’exercice, et restauré le service avec un minimum de drame. L’incident est quand même arrivé, mais n’est pas devenu un événement de carrière.

Blague n°2 : Les plans de sauvegarde sont comme les abonnements à la salle de sport — en posséder un ne change rien tant que vous ne l’utilisez pas.

Erreurs courantes : symptôme → cause profonde → correctif

1) Symptom : « Le CPU DB est bas, mais les requêtes timeout »

Cause profonde : Verrouillage ou saturation du pool de connexions. La base attend plutôt que de travailler.

Fix : Identifier les requêtes/transactions bloquantes ; raccourcir les scopes de transaction ; ajouter des timeouts ; limiter la concurrence ; introduire un pooler (Postgres) ou corriger les limites thread/connexion (MySQL).

2) Symptom : « Une requête est lente seulement en production »

Cause profonde : Distribution des données différente, statistiques manquantes ou indexes différents. La production a des skew et des hot spots ; staging est plein d’illusions.

Fix : Capturer EXPLAIN/ANALYZE en production ; mettre à jour les stats ; ajouter des index ciblés ; envisager des index partiels (Postgres) ou des indexes composites couvrants (MySQL).

3) Symptom : « La réplica montre des données anciennes »

Cause profonde : Latence de réplication asynchrone et hypothèses read-after-write de l’application.

Fix : Lire depuis le primaire après une écriture (stickiness), ou implémenter une réplication synchrone pour les opérations critiques, ou concevoir l’UI pour tolérer la cohérence éventuelle.

4) Symptom : « L’espace disque croît sans fin alors que le trafic est stable »

Cause profonde : Bloat Postgres dû au churn update/delete ; croissance de tables/index MySQL due à la fragmentation ou à une rétention non bornée.

Fix : Postgres : tuner autovacuum, réduire le churn, partitionner les tables à fort churn, envisager REINDEX/VACUUM FULL périodique avec plan d’indisponibilité. MySQL : archiver/purger les données anciennes, reconstruire les tables prudemment, vérifier les indexes surdimensionnés.

5) Symptom : « Un deploy a causé une panne complète, puis la récupération »

Cause profonde : DDL verrouillant ou une migration réécrivant une grosse table, saturant l’I/O et bloquant les requêtes.

Fix : Utiliser des changements de schéma en ligne, scinder les migrations, ajouter des colonnes sans defaults d’abord, backfiller par batch, puis appliquer les contraintes.

6) Symptom : « Tout est lent quand un rapport s’exécute »

Cause profonde : Requêtes longues consommant I/O et buffers, ou tenant des verrous, ou causant des conflits de réplication (hot standby Postgres).

Fix : Router le reporting vers une réplica, ajouter des timeouts de statement, pré-calculer des agrégats, indexer pour ce rapport ou l’interdire en heures de production.

7) Symptom : « La perf empire sur plusieurs jours, puis s’améliore temporairement après maintenance »

Cause profonde : Dette de vacuum et bloat Postgres, ou churn du buffer pool MySQL dû à un working set changeant et à la croissance des index.

Fix : Postgres : régler autovacuum et analyze ; surveiller les tuples morts et l’âge de freeze. MySQL : vérifier le dimensionnement du buffer pool, les index manquants, et supprimer les index qui amplifient les écritures.

8) Symptom : « Pics de latence élevés lors de rafales de trafic »

Cause profonde : Tempêtes de connexions, mise en file d’attente à la base, ou saturation I/O disque.

Fix : Imposer des limites strictes sur la concurrence, s’assurer d’une mise en pool, appliquer du backpressure et mesurer la profondeur des files/la latence storage (await).

Checklists / plan étape par étape

Checklist de décision : choisir MySQL ou PostgreSQL pour un site web

• Si votre équipe a une maturité opérationnelle MySQL solide (réplication, upgrades, tuning d’index, restaurations) et que votre charge est OLTP simple : choisissez MySQL et passez à autre chose.
• Si vous attendez des requêtes complexes, des contraintes riches, et que vous voulez une sémantique SQL saine avec un écosystème d’extensions : choisissez Postgres.
• Si vous ne pouvez pas vous engager sur une discipline de pooling des connexions : MySQL pardonnera généralement plus longtemps, mais vous accumulez de la dette. Corrigez la mise en pool dans tous les cas.
• Si vous faites beaucoup de mises à jour/suppressions sur de grandes tables : Postgres demande de l’amour pour le vacuum ; MySQL demande de la discipline sur les index et la purge. Choisissez selon le pattern de maintenance que vous pourrez tenir.
• Si vous comptez beaucoup sur les réplicas de lecture : concevez la cohérence éventuelle dès le départ, quel que soit le SGBD.

Installation première semaine : hygiène production (les deux bases)

1. Activer la visibilité des requêtes : slow query logs (MySQL) ou pg_stat_statements (Postgres). Si vous ne voyez pas les requêtes, vous déboguez à l’aveugle.
2. Définir des timeouts : statement timeouts, lock timeouts et timeouts de transaction adaptés aux requêtes web.
3. Établir backup + tests de restauration : une procédure de restauration scriptée que quelqu’un peut lancer sous stress.
4. Décider des sémantiques de réplication : quels endpoints peuvent lire depuis des réplicas ; l’appliquer dans le code.
5. Définir une politique de migration : pas de réécriture de table en période de peak, backfills scindés, mises à jour par lots.

Workflow de performance : quand une page est lente

1. Trouver l’endpoint et le corréler avec les requêtes principales par temps total.
2. Capturer EXPLAIN/ANALYZE (ou MySQL EXPLAIN + stats digest).
3. Vérifier si la requête manque d’un index ou utilise le mauvais.
4. Valider les counts de lignes et la sélectivité (le skew existe).
5. Corriger le chemin d’accès (index ou réécriture de requête) avant de toucher aux réglages serveur.
6. Ensuite seulement envisager des changements de config ou hardware.

Plan de changement de schéma : migrations sûres sans drame

1. Ajout d’abord : ajouter de nouvelles colonnes nullable, sans defaults, sans contraintes initialement.
2. Backfill par batch : petites transactions, pause entre les batches, mesurer la latence de réplication.
3. Double écriture si nécessaire : écrire à la fois dans l’ancien et le nouveau champ pendant le déploiement.
4. Ajouter les contraintes en dernier : valider soigneusement, de préférence en ligne/faible impact si supporté.
5. Supprimer les anciens champs seulement après vérification : et seulement après avoir des chemins de rollback connus.

FAQ

1) Lequel est plus rapide pour un site web typique : MySQL ou PostgreSQL ?

Aucun par défaut. La base la plus rapide est celle avec les bons indexes, des requêtes sensées et pas de problèmes de réplication/verrous auto-infligés. Pour l’OLTP simple, les deux sont rapides. Pour les requêtes complexes, Postgres gagne souvent — jusqu’à ce qu’on néglige les stats et le vacuum.

2) Lequel est « plus fiable » ?

La fiabilité est une propriété opérationnelle : backups, restaurations testées, monitoring et migrations sûres. Les deux peuvent être fiables. Postgres tend à être plus strict et cohérent ; MySQL tolère davantage les usages négligents jusqu’à ce que ça casse.

3) Ai‑je besoin d’un pooler de connexions avec PostgreSQL ?

Pour les applis web à trafic en rafales : oui, pratiquement toujours. Sans pooler, vous finirez par subir des tempêtes de connexions, un overhead mémoire et de mauvais tail latency. Avec un pooler, Postgres est beaucoup plus calme sous charge.

4) Le vacuum Postgres est‑il rédhibitoire ?

Non, mais c’est une responsabilité. Si vos données sont majoritairement des inserts avec peu de updates/deletes, le vacuum est simple. Si vous faites beaucoup de updates sur de grosses tables, il faut monitorer et tuner autovacuum ou partitionner. Si votre équipe ne fera pas ça, vous pariez contre la physique.

5) Les réplicas de lecture sont‑ils sûrs pour les « lectures normales » ?

Sûrs pour des lectures non critiques, oui — si vous acceptez la cohérence éventuelle. Non sûrs pour des workflows read-after-write à moins d’implémenter de la stickiness ou de la réplication synchrone pour ces chemins.

6) Le JSON est‑il mieux géré par Postgres ou MySQL pour un site web ?

JSONB de Postgres est généralement le meilleur outil quand vous avez besoin d’indexation et de requêtes sur des champs JSON. Le JSON de MySQL fonctionne, mais les équipes dérivent souvent vers un excès de données semi-structurées sans garde-fous. Si vous avez besoin d’un JSON pleinement interrogeable, Postgres est le pari le plus sûr.

7) Quelle est la cause n°1 des incidents « la base est lente » ?

Les mauvais chemins d’accès : index manquants ou mal choisis, plus des requêtes qui scannent accidentellement. Le deuxième est la contention due aux transactions longues. Le hardware est rarement la première cause, même s’il est souvent blâmé.

8) Si nous exploitons déjà une base, devons‑nous changer ?

Généralement non. Changer de SGBD est coûteux et risqué. Corrigez plutôt le vrai goulot : indexation, patterns de requêtes, conception de réplication, sauvegardes et maintenance. Ne migrez que si une capacité spécifique ou un mode d’échec vous tue systématiquement.

9) Comment choisir pour un site e‑commerce spécifiquement ?

Choisissez ce que votre équipe peut exploiter sans héroïsme. L’e‑commerce exige exactitude (inventaire, paiements) et latence prévisible. Postgres est un bon défaut si vous pouvez gérer la mise en pool et le vacuum. MySQL est excellent si vous avez déjà une ops MySQL mature et que vous maîtrisez transactions et index.

10) Que dois‑je monitorer dès le premier jour ?

Distribution des latences de requêtes (p95/p99), requêtes principales par temps total, attentes de verrous, latence de réplication, nombre de connexions, croissance de l’espace disque et latence de stockage (await). Ces éléments détectent les goulots avant que les clients ne s’en aperçoivent.

Prochaines étapes que vous pouvez faire cette semaine

1. Choisissez la base en fonction des goulots que vous pouvez gérer, pas de l’idéologie. Si vous hésitez, par défaut : Postgres pour les nouvelles constructions.
2. Instrumentez la visibilité des requêtes (digests, slow logs, pg_stat_statements). Pas de visibilité = pas de vérité.
3. Implémentez la discipline des connexions : poolers pour Postgres, taille de pool sensée pour les deux.
4. Écrivez les sémantiques de réplication : quels endpoints tolèrent des lectures anciennes ; appliquez‑les dans le code.
5. Planifiez un exercice de restauration et exécutez‑le. Si cela vous met mal à l’aise, vous avez trouvé votre risque réel.
6. Construisez une routine « top queries » : revue hebdomadaire des principaux consommateurs de temps et des principaux contributeurs aux waits de verrous.

Si vous faites cela, vous cesserez de traiter la base comme une boîte mystérieuse et commencerez à la considérer comme ce qu’elle est : une machine prévisible qui punit la pensée négligente et récompense la compétence ennuyeuse.