Indexation MariaDB vs PostgreSQL : pourquoi les « meilleures pratiques » se retournent contre les charges réelles

Pourquoi les « meilleures pratiques » échouent en production

La plupart des conseils d’indexation sont écrits pour une base de données fictive : schéma stable, requêtes prévisibles, concurrence modérée et couche de stockage qui se comporte comme la brochure le promet. Votre base de données n’est pas cette base de données.

« Ajouter un index pour chaque clause WHERE » est un classique. C’est aussi la façon de transformer un système OLTP fonctionnel en tas compost amplifiant les écritures. Chaque index supplémentaire est une structure de plus à mettre à jour, un ensemble de pages supplémentaires à salir, une raison de plus pour le décalage de réplication et une chance de plus pour l’optimiseur de choisir le mauvais chemin.

L’indexation en production est une question de contraintes. Combien d’écritures pouvez-vous vous permettre ? Quelle mémoire est réservée pour le cache des pages d’index ? À quelle fréquence vos données changent-elles de forme (clés chaudes, skew, rafales) ? Quelle est votre tolérance aux fenêtres de maintenance ? PostgreSQL et MariaDB répondent différemment à ces questions car leurs internes et leurs outils opérationnels diffèrent.

Autre point : la « meilleure pratique » suppose souvent que le planificateur dispose de statistiques exactes. Quand les statistiques dérivent — ou que la distribution des données change — votre index parfait devient un piège. Ce n’est pas que la base de données est stupide. C’est que vous avez changé les règles alors qu’elle jouait.

Une citation qu’il faudrait coller sur l’écran : « L’espoir n’est pas une stratégie. » — Gene Kranz. Indexer sans mesurer, c’est espérer avec des étapes en plus.

Blague #1 : Ajouter des index sans mesurer, c’est comme acheter plus d’étagères parce que vous ne trouvez rien — au bout du compte vous possédez une bibliothèque et vous perdez toujours vos clés.

Faits et historique qui comptent encore

Quelques points de contexte qui semblent académiques jusqu’à ce qu’ils expliquent votre panne :

1. PostgreSQL a intégré MVCC tôt (contrôle de concurrence multi-version), ce qui signifie que les tuples morts et le vacuum font partie de l’histoire des index, pas une simple note de bas de page.
2. InnoDB est devenu le moteur MySQL par défaut il y a longtemps, et MariaDB suit largement la même lignée InnoDB — donc la conception des index secondaires hérite de la réalité du « primary key clusterisé ».
3. Les index GIN et GiST de PostgreSQL ont été conçus pour les types de données complexes et les modèles de recherche ; ils sont puissants, mais introduisent des dynamiques de maintenance et de bloat que la foule des B-tree oublie.
4. MariaDB/MySQL se sont historiquement appuyés sur « index condition pushdown » et des heuristiques d’optimiseur ; c’est efficace jusqu’à ce que ça ne le soit plus, et alors la seule vérité reste le plan réel.
5. PostgreSQL a ajouté les colonnes INCLUDE pour les index B-tree (comportement proche du covering) relativement tard par rapport à certains moteurs ; cela a changé la façon de construire des index « covering ».
6. Les index secondaires InnoDB stockent la clé primaire dans les feuilles. Ce détail explique une grande partie de la croissance d’espace et du renouvellement de cache lorsque votre clé primaire est large.
7. PostgreSQL utilise des visibility maps pour permettre les index-only scans ; si vacuum ne suit pas, votre « index-only » devient « index plus table » de toute façon.
8. MySQL/MariaDB supportent depuis longtemps les « index invisibles » (dans MySQL) et MariaDB offre des capacités similaires via des commutateurs d’optimiseur et des hints ; pouvoir tester la suppression d’un index en toute sécurité est une mine d’or opérationnelle.

Ce que les index coûtent vraiment (et qui paie)

1) Amplification des écritures : chaque insert/update paie un loyer

En OLTP, la note arrive lors des écritures. Les inserts touchent la table et chaque index secondaire. Les updates peuvent être pires : mettez à jour une colonne présente dans plusieurs index et vous multipliez le travail. Les deletes ne sont pas gratuits non plus — les deux moteurs doivent enregistrer la suppression à leur façon, et tous deux peuvent laisser le nettoyage à plus tard.

MariaDB (InnoDB) met à jour des structures B-tree et écrit des journaux redo/undo. PostgreSQL écrit de nouvelles versions de tuples (MVCC) et met à jour les index pour le nouveau tuple ; les anciennes versions subsistent jusqu’au nettoyage par vacuum. Mécanismes différents, même morale : plus d’index signifie plus de churn.

2) Pression sur le cache : les index se disputent ce dont vous avez vraiment besoin

Les index ne sont pas des « lectures gratuites ». Ce sont des structures de données en mémoire et sur disque. Si votre buffer pool (MariaDB) ou shared_buffers + cache OS (PostgreSQL) ne peut pas héberger l’ensemble de travail, vous obtenez du churn d’E/S. Le churn d’E/S transforme un « bon index selon la théorie » en « pourquoi avons-nous 4000 IOPS au repos ? »

3) Risque de planification : l’optimiseur peut choisir le « bon » mauvais plan

Plus vous avez d’index, plus le planificateur a de choix. Cela paraît bien jusqu’à ce que les estimations de cardinalité soient fausses. Alors il choisit avec assurance un plan qui semble économique dans son modèle et coûteux en réalité.

4) Maintenance : bloat, fragmentation et le mensonge du « paramétrer et oublier »

Le bloat PostgreSQL provient souvent des tuples morts et des entrées d’index qui ne sont pas immédiatement récupérées. Vacuum atténue cela, mais vacuum nécessite des ressources et une configuration. MariaDB/InnoDB peuvent souffrir de fragmentation et de splits de pages, et « OPTIMIZE TABLE » n’est pas une commande qu’on souhaite lancer à la légère à grande échelle.

Blague #2 : Le vacuum est le seul concierge que les gens insultent pour travailler trop lentement, et aussi pour travailler du tout.

Où MariaDB et PostgreSQL diffèrent dans le comportement des index

Clé primaire clusterisée vs table heap

InnoDB (MariaDB) stocke les données de la table groupées par la clé primaire. Les entrées des index secondaires incluent la clé primaire, utilisée comme « pointeur de ligne ». Cela rend la largeur et l’aléa de la PK très importants. Une PK large gonfle chaque index secondaire. Une PK aléatoire augmente les splits de page et réduit la localité.

PostgreSQL utilise des tables heap ; les index pointent vers des emplacements de tuple (TID). Votre clé primaire ne clusterise pas physiquement la table sauf si vous exécutez explicitement CLUSTER (et acceptez les implications de maintenance). Cela rend certains arguments sur la localité de la PK moins pertinents en Postgres, mais introduit d’autres problèmes comme les HOT updates et la fragmentation du heap.

Index couvrants : « INCLUDE » vs « ajouter simplement des colonnes »

Dans MariaDB, un « index couvrant » s’obtient généralement en plaçant les colonnes nécessaires dans la clé de l’index. Cela augmente la taille de l’index et peut aggraver le coût des écritures. Dans PostgreSQL, vous pouvez utiliser INCLUDE pour ajouter des colonnes non-clés, permettant des index-only scans tout en gardant l’ordre de l’index défini par moins de clés. Ce n’est pas magique ; les colonnes incluses occupent toujours de l’espace et nécessitent une maintenance, mais vous avez un contrôle plus précis.

Les index-only scans sont conditionnels, pas garantis

Les index-only scans de PostgreSQL requièrent que les bits de la visibility map soient définis, ce qui dépend du vacuum. Si votre table est fréquemment mise à jour, la visibility map peut être en retard, et Postgres ira voir le heap quand même. Dans MariaDB, « couvrir » signifie que le moteur peut éviter les lectures de table si toutes les colonnes requises sont dans l’index, mais vous payez toujours avec des index plus volumineux et plus de pression sur le cache.

Modes d’échec des statistiques différents

Les deux moteurs peuvent mal estimer. PostgreSQL expose plus de réglages (default_statistics_target, cibles de statistiques par colonne, statistiques étendues). MariaDB s’appuie sur des statistiques persistantes et des histogrammes dans les versions récentes, mais la voie pour « corriger les estimations » ressemble souvent à « ANALYZE et prier », plus une conception d’index et une mise en forme des requêtes prudentes.

La concurrence et les rythmes de maintenance diffèrent

Dans Postgres, vacuum fait partie des opérations en steady-state. Dans InnoDB, on se concentre souvent davantage sur le dimensionnement du buffer pool, le redo log et éviter le churn pathologique de pages. Les deux nécessitent une maintenance régulière ; ils échouent simplement différemment quand on les néglige.

Schémas de charge qui font échouer les conseils

Schéma A : systèmes « orientés lecture » qui sont en réalité orientés écriture

Télémetrie, journaux d’audit, flux d’événements, tables « append-only » — ces systèmes semblent orientés lecture parce que vous remarquez les requêtes de tableau de bord. Mais la base passe la plupart du temps à ingérer. Ajouter des index pour chaque filtre de tableau de bord peut multiplier le coût des écritures et déclencher un retard de réplication.

Schéma B : colonnes haute cardinalité vs basse cardinalité

Indexer un booléen ou un petit enum peut être utile dans Postgres avec des indexes partiels, mais dans MariaDB un index de faible cardinalité peut être ignoré ou provoquer un travail inutile. Le conseil « indexez tout dans WHERE » ignore la sélectivité.

Schéma C : le mythe de l’ordre dans les index composites

Oui, l’ordre des colonnes dans un index composite compte. Non, il n’y a pas d’ordre universel. « Mettre la colonne la plus sélective en premier » est souvent faux quand votre requête utilise des conditions de plage, ORDER BY, ou a besoin d’un covering. Dans MariaDB, la règle du préfixe gauche est importante. Dans Postgres, le planificateur est plus flexible, mais reste contraint par l’utilisation possible de l’index.

Schéma D : sur-indexation pour couvrir les ORM

Les ORM génèrent des requêtes avec des prédicats incohérents et beaucoup de filtres optionnels. Les équipes répondent en ajoutant des index « de support » pour chaque permutation. C’est ainsi qu’on se retrouve avec 25 index sur une table de 10 colonnes.

Schéma E : tri et pagination qui vous punissent

La pagination OFFSET/LIMIT combinée à ORDER BY sur des colonnes non indexées est un désastre au ralenti. « Ajouter un index sur la colonne de tri » aide jusqu’à ce que vous ajoutiez une clause WHERE qui change l’index optimal. Alors il vous faut un index composé qui combine filtre + ordre, ou passer à la pagination par clé (keyset). La « meilleure pratique » ici n’est pas « ajouter un index » ; c’est « changer la requête ».

Mode d’emploi pour un diagnostic rapide

Quand la latence augmente et que tout le monde actualise Slack comme s’il s’agissait d’une page d’état, voici l’ordre qui trouve généralement le coupable le plus vite.

Première étape : confirmer si vous êtes CPU-bound, IO-bound ou lock-bound

• CPU-bound : CPU élevé, faible attente IO, requêtes consommant du cycle ; les plans montrent souvent des jointures, tris ou fonctions coûteux.
• IO-bound : IOPS de lecture/écriture élevés, await élevé ; misses dans le buffer cache ; l’ensemble de travail index/table ne tient pas en mémoire.
• Lock-bound : threads en attente ; graphes de verrous ; transactions longues ; autovacuum bloqué (Postgres) ou metadata locks (MariaDB).

Deuxième étape : identifier les 1–3 requêtes principales par temps total, pas par temps par appel

La « requête la plus lente » au temps par appel est souvent un leurre. La requête principale par temps total est celle qui paie votre loyer.

Troisième étape : valider les plans par rapport à la réalité

Dans Postgres, comparez EXPLAIN (ANALYZE, BUFFERS) aux estimations. Dans MariaDB, inspectez EXPLAIN, les compteurs handler read et l’usage des index. Recherchez des scans inattendus, filesorts, tables temporaires et boucles imbriquées qui auraient dû être des hash joins (Postgres) ou être limitées plus tôt.

Quatrième étape : vérifier l’état et la santé des index

Dans Postgres : activité de vacuum, tuples morts, indicateurs de bloat, visibility map. Dans MariaDB : taux de hit du buffer pool, comportement du change buffer, croissance de la taille des index, et si votre choix de PK boursouille chaque index secondaire.

Cinquième étape : ce n’est qu’après que vous créez ou supprimez des index

Si vous créez des index avant de connaître votre classe de goulot d’étranglement, vous n’ajoutez que du poids à un navire que vous n’avez pas inspecté pour des voies d’eau.

Pratique : 14 tâches avec commandes, sorties et décisions

Ce sont des tâches réalistes en production : exécutez une commande, interprétez la sortie et prenez une décision concrète. Les commandes sont affichées comme si vous étiez sur une machine avec les outils clients installés.

Task 1 (PostgreSQL): Find the biggest time consumers by total time

cr0x@server:~$ psql -d appdb -c "SELECT query, calls, total_exec_time::numeric(12,1) AS total_ms, mean_exec_time::numeric(10,2) AS mean_ms FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;"
                               query                                | calls |  total_ms  | mean_ms
--------------------------------------------------------------------+-------+------------+---------
 SELECT ... FROM orders WHERE account_id=$1 AND status=$2 ORDER BY... | 98234 |  8543210.5 |   86.97
 UPDATE inventory SET qty=qty-$1 WHERE sku=$2                         | 45012 |  3011220.2 |   66.90
 SELECT ... FROM events WHERE created_at >= $1 AND tenant_id=$2        | 12033 |  2210098.7 |  183.67

Ce que cela signifie : La première requête consomme le plus de temps réel au total ; une latence « modérée » par appel s’accumule avec un volume élevé d’appels.

Décision : Optimisez d’abord cette requête principale. Ne poursuivez pas le rapport rare de 30 secondes à moins qu’il n’impacte les SLA.

Task 2 (PostgreSQL): Compare estimates vs reality with buffers

cr0x@server:~$ psql -d appdb -c "EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE account_id=42 AND status='open' ORDER BY created_at DESC LIMIT 50;"
                                                                    QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..102.55 rows=50 width=128) (actual time=1.212..12.844 rows=50 loops=1)
   Buffers: shared hit=120 read=480
   ->  Index Scan Backward using idx_orders_account_created on orders  (cost=0.42..10523.31 rows=5200 width=128) (actual time=1.210..12.832 rows=50 loops=1)
         Index Cond: (account_id = 42)
         Filter: (status = 'open'::text)
         Rows Removed by Filter: 940
         Buffers: shared hit=120 read=480
 Planning Time: 0.290 ms
 Execution Time: 12.930 ms

Ce que cela signifie : L’index aide pour ORDER BY/LIMIT, mais le filtre sur status élimine beaucoup de lignes. Les Buffers montrent des lectures réelles : vous touchez de nombreuses pages.

Décision : Envisagez un index composite sur (account_id, status, created_at DESC) ou un index partiel sur le statut open si cela est stable. Vérifiez aussi si « open » représente une petite fraction ; les index partiels excellent dans ce cas.

Task 3 (PostgreSQL): Identify unused indexes (with caution)

cr0x@server:~$ psql -d appdb -c "SELECT relname AS table, indexrelname AS index, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size FROM pg_stat_user_indexes JOIN pg_class ON pg_class.oid=relid WHERE idx_scan=0 ORDER BY pg_relation_size(indexrelid) DESC LIMIT 10;"
  table   |         index          | idx_scan |  size
----------+------------------------+----------+--------
 events   | idx_events_payload_gin |        0 | 2048 MB
 orders   | idx_orders_status      |        0 |  512 MB

Ce que cela signifie : Ces index n’ont pas été scannés depuis la réinitialisation des statistiques. Cela ne garantit pas qu’ils sont inutiles ; ils peuvent être utilisés rarement, uniquement pour des contraintes, ou les stats ont été réinitialisées récemment.

Décision : Validez avec les logs de requêtes, les usages applicatifs et une fenêtre de test sûre. Pour Postgres, envisagez de supprimer les index réellement inutilisés pour réduire le coût des écritures et du vacuum.

Task 4 (PostgreSQL): Find index bloat signals via dead tuples

cr0x@server:~$ psql -d appdb -c "SELECT relname, n_live_tup, n_dead_tup, (n_dead_tup::numeric/(n_live_tup+1))*100 AS dead_pct FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;"
  relname  | n_live_tup | n_dead_tup | dead_pct
-----------+------------+------------+----------
 events    |   80000000 |   22000000 |  27.50
 orders    |    1200000 |     210000 |  17.50

Ce que cela signifie : Beaucoup de tuples morts veut dire pression sur le vacuum et risque de bloat d’index. Cela peut ruiner les index-only scans et gonfler les E/S.

Décision : Ajustez autovacuum pour ces tables (paramètres par table) et envisagez le partitionnement ou des changements de modèle d’écritures si c’est chronique.

Task 5 (PostgreSQL): Check autovacuum activity and blockers

cr0x@server:~$ psql -d appdb -c "SELECT pid, now()-xact_start AS xact_age, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state<>'idle' ORDER BY xact_start NULLS LAST LIMIT 8;"
 pid  |  xact_age  | wait_event_type |     wait_event     |                       query
------+------------+-----------------+--------------------+---------------------------------------------------
 9123 | 02:41:10   | Lock            | relation           | VACUUM (ANALYZE) events
 7331 | 02:42:55   |                 |                    | BEGIN; SELECT ... FROM events FOR UPDATE;

Ce que cela signifie : Une transaction longue qui tient des verrous peut bloquer la progression du vacuum. Le vacuum attend ; les tuples morts s’accumulent ; les index s’enflent ; les performances déclinent.

Décision : Corrigez le pattern de transaction de l’app. Ajoutez des timeouts de requête, raccourcissez les transactions et évitez les sessions idle-in-transaction.

Task 6 (PostgreSQL): Confirm whether index-only scans are actually happening

cr0x@server:~$ psql -d appdb -c "SELECT relname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes JOIN pg_class ON pg_class.oid=relid WHERE relname='orders' ORDER BY idx_scan DESC;"
 relname | idx_scan | idx_tup_read | idx_tup_fetch
---------+----------+--------------+--------------
 orders  |  1200000 |     98000000 |     97000000

Ce que cela signifie : idx_tup_fetch est presque égal à idx_tup_read, ce qui implique de nombreux fetchs depuis le heap. Les index-only scans ne vous économisent pas beaucoup.

Décision : Améliorez l’efficacité du vacuum et envisagez de rendre l’index couvrant avec INCLUDE (Postgres) ou de revoir la forme de la requête.

Task 7 (MariaDB): Find top statements by total time (Performance Schema)

cr0x@server:~$ mariadb -e "SELECT DIGEST_TEXT, COUNT_STAR, ROUND(SUM_TIMER_WAIT/1000000000000,2) AS total_s, ROUND(AVG_TIMER_WAIT/1000000000000,4) AS avg_s FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 3\G"
*************************** 1. row ***************************
DIGEST_TEXT: SELECT * FROM orders WHERE account_id = ? AND status = ? ORDER BY created_at DESC LIMIT ?
COUNT_STAR: 98234
total_s: 8543.21
avg_s: 0.0869

Ce que cela signifie : Même principe : le temps total identifie la vraie requête dominante.

Décision : Concentrez l’indexation et les changements de requêtes sur ces quelques statements d’abord. N’indexez pas pour une requête qui s’exécute deux fois par jour sauf si elle bloque tout.

Task 8 (MariaDB): Explain a query and watch for filesort/temp

cr0x@server:~$ mariadb -e "EXPLAIN SELECT * FROM orders WHERE account_id=42 AND status='open' ORDER BY created_at DESC LIMIT 50\G"
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
type: ref
possible_keys: idx_orders_account_created,idx_orders_status
key: idx_orders_account_created
key_len: 8
ref: const
rows: 5200
Extra: Using where; Using filesort

Ce que cela signifie : « Using filesort » signifie que le moteur prévoit de trier plutôt que de lire les lignes dans l’ordre désiré depuis l’index. Cela implique souvent que votre index ne correspond pas à l’ORDER BY après filtrage.

Décision : Envisagez un index qui correspond au filtre et à l’ordre : (account_id, status, created_at). Validez la cardinalité ; n’empilez pas d’index aveuglément.

Task 9 (MariaDB): Measure index usage by Handler counters (quick smell test)

cr0x@server:~$ mariadb -e "SHOW GLOBAL STATUS LIKE 'Handler_read%';"
+-----------------------+-----------+
| Variable_name         | Value     |
+-----------------------+-----------+
| Handler_read_first    | 182349    |
| Handler_read_key      | 982341234 |
| Handler_read_next     | 774123998 |
| Handler_read_rnd_next | 889120044 |
+-----------------------+-----------+

Ce que cela signifie : Un Handler_read_rnd_next élevé indique généralement beaucoup de scans de table (ou d’index se comportant comme des scans). Un Handler_read_next élevé suggère des scans de plage. C’est rudimentaire, mais rapide.

Décision : Si les scans explosent, identifiez les requêtes et les index manquants/inutilisés — ou acceptez que vous soyez IO-bound et changez les requêtes ou partitionnez.

Task 10 (MariaDB): See index sizes and spot “PK too wide” collateral damage

cr0x@server:~$ mariadb -e "SELECT table_name, index_name, ROUND(stat_value*@@innodb_page_size/1024/1024,1) AS index_mb FROM mysql.innodb_index_stats WHERE database_name='appdb' AND stat_name='size' AND table_name='orders' ORDER BY index_mb DESC;"
+------------+---------------------------+----------+
| table_name | index_name                | index_mb |
+------------+---------------------------+----------+
| orders     | PRIMARY                   |  820.0   |
| orders     | idx_orders_account_status |  740.0   |
| orders     | idx_orders_created        |  610.0   |
+------------+---------------------------+----------+

Ce que cela signifie : Les index secondaires peuvent être presque aussi volumineux que le PRIMARY parce qu’ils portent la PK dans leurs feuilles. Si votre PK est un grand UUID en texte, vous le payez partout.

Décision : Envisagez des PK compactes (clés entières surrogates) ou le stockage binaire des UUID quand c’est approprié. Réexaminez si tous les index secondaires sont nécessaires.

Task 11 (PostgreSQL): Test a new index without blocking writes (concurrently)

cr0x@server:~$ psql -d appdb -c "CREATE INDEX CONCURRENTLY idx_orders_account_status_created ON orders (account_id, status, created_at DESC) INCLUDE (total_amount);"
CREATE INDEX

Ce que cela signifie : CONCURRENTLY évite les longs blocages d’écritures, mais prend plus de temps et peut échouer s’il y a des opérations en conflit.

Décision : Utilisez concurrently en production sauf si vous êtes en fenêtre de maintenance contrôlée et acceptez le blocage. Relancez ensuite EXPLAIN (ANALYZE) pour confirmer les changements de plan.

Task 12 (MariaDB): Add an index online (InnoDB) and validate the algorithm

cr0x@server:~$ mariadb -e "ALTER TABLE orders ADD INDEX idx_orders_account_status_created (account_id, status, created_at), ALGORITHM=INPLACE, LOCK=NONE;"
Query OK, 0 rows affected (12 min 31.44 sec)

Ce que cela signifie : In-place, lock-none signifie généralement un DDL moins perturbateur, mais l’opération consomme quand même des E/S et peut affecter la latence pendant la construction.

Décision : Planifiez pendant une période de faible trafic, surveillez le retard de réplication et confirmez avec EXPLAIN qu’il est réellement utilisé. S’il n’est pas utilisé, vous venez d’acheter un surcoût d’écritures pour rien.

Task 13 (PostgreSQL): Find duplicate or redundant indexes

cr0x@server:~$ psql -d appdb -c "SELECT i1.relname AS index1, i2.relname AS index2, pg_get_indexdef(i1.oid) AS def1, pg_get_indexdef(i2.oid) AS def2 FROM pg_class i1 JOIN pg_index ix1 ON ix1.indexrelid=i1.oid JOIN pg_class i2 ON i2.relkind='i' JOIN pg_index ix2 ON ix2.indexrelid=i2.oid WHERE ix1.indrelid=ix2.indrelid AND i1.oid<>i2.oid AND ix1.indkey=ix2.indkey LIMIT 3;"
  index1              | index2                 | def1                                   | def2
----------------------+------------------------+----------------------------------------+----------------------------------------
 idx_orders_account   | idx_orders_account_v2  | CREATE INDEX ... (account_id)          | CREATE INDEX ... (account_id)

Ce que cela signifie : Deux index avec des clés identiques sont généralement redondants sauf si l’un est partiel, utilise un opclass différent ou a un prédicat différent.

Décision : Supprimez l’index redondant après validation des dépendances et de l’usage. Moins d’index réduit le coût des écritures et du vacuum.

Task 14 (MariaDB): Verify optimizer actually chooses your intended index

cr0x@server:~$ mariadb -e "EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE account_id=42 AND status='open' ORDER BY created_at DESC LIMIT 50\G"
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "orders",
      "access_type": "range",
      "possible_keys": ["idx_orders_account_status_created"],
      "key": "idx_orders_account_status_created",
      "rows_examined_per_scan": 60,
      "filtered": 100,
      "using_filesort": false
    }
  }
}

Ce que cela signifie : Le moteur a choisi l’index composite, prévoit peu de lignes par scan et aucun filesort. C’est ce que vous vouliez.

Décision : Déployez, puis surveillez la latence des écritures, le retard de réplication et les misses du buffer pool. Un « gain lecture » qui provoque une « perte écriture » reste une perte.

Trois micro-récits d’entreprise depuis les tranchées de l’indexation

Micro-récit 1 : L’incident causé par une mauvaise hypothèse (le leurre « la PK UUID n’a pas d’importance »)

L’entreprise exploitait un SaaS multi-tenant sur MariaDB avec InnoDB. Un nouveau service a été déployé avec une table dont la clé primaire était un UUID en chaîne. Le raisonnement était standard : des IDs globalement uniques simplifient les fusions et évitent la coordination. Ils avaient lu les billets de blog. Ils avaient les autocollants.

En une semaine, la latence des écritures a dérivé à la hausse. Pas un pic, une pente. L’on-call a vu l’IO augmenter et le taux de hit du buffer pool tomber comme s’il rendait l’âme. Le retard de réplication est apparu pendant les pics, puis est resté après les heures de pointe.

La mauvaise hypothèse : « Le choix de la clé primaire affecte surtout la table, pas les index. » Dans InnoDB, chaque index secondaire stocke la clé primaire. Leur PK « inoffensive » s’est transformée en taxe sur tous les autres index. Ils avaient aussi ajouté quelques index secondaires pour supporter des filtres analytiques, multipliant la taxe.

Ils ont corrigé de manière pragmatique : introduction d’une clé primaire entière surrogate et conservation de l’UUID comme clé secondaire unique pour les références externes. La migration n’a pas été amusante, mais le système a arrêté l’hémorragie. L’équipe a retenu une règle à imprimer dans la documentation d’onboarding : dans InnoDB, votre clé primaire est une décision de stockage, pas seulement d’identité.

Micro-récit 2 : L’optimisation qui a mal tourné (la manie des index couvrants)

Une autre organisation utilisait PostgreSQL pour une plateforme d’ingestion d’événements. Un ingénieur principal a lancé une campagne d’index-only scans. La logique était solide : réduire les fetchs heap en rendant les index « couvrants » pour les requêtes courantes. Ils ont ajouté quelques index B-tree multi-colonnes avec INCLUDE, et la latence des requêtes s’est améliorée en staging.

En production, tout allait bien pendant un mois. Puis le volume d’ingestion a augmenté, les mises à jour se sont faites plus fréquentes (changements de statut et enrichissements), et autovacuum a commencé à être dépassé. Les bits de la visibility map ne se mettaient plus assez vite, et les index-only scans se sont silencieusement transformés en scans d’index plus fetchs heap. Parallèlement, les index plus volumineux ont augmenté la pression sur le vacuum et les checkpoints.

Les symptômes sont apparus sous forme de pics de latence périodiques et d’averses d’E/S. L’ironie était pénible : le projet « index-only » avait augmenté la quantité de données à vacuum et à mettre en cache, ce qui rendait plus difficile le maintien de la précondition des index-only scans (la visibilité).

La correction n’a pas été « ajouter plus d’index ». Ils ont annulé les INCLUDE les plus larges, partitionné la table d’événements la plus chaude, et ajusté autovacuum par partition. Les index-only scans sont revenus où ils avaient du sens, et le système a cessé d’osciller entre « rapide » et « en feu ».

Micro-récit 3 : La pratique ennuyeuse mais correcte qui a sauvé la mise (changements d’index réversibles)

Une équipe fintech exploitait MariaDB et PostgreSQL sur des services différents. Ils avaient l’habitude — fastidieuse en apparence — de préparer un plan de rollback pour chaque changement de schéma/index, et traitaient le déploiement d’un index comme un déploiement applicatif. Le DBA ne cherchait pas à être difficile ; il voulait garder les week-ends intacts.

Quand une régression de requête a surgi après une release mineure, la cause racine était un basculement du plan : Postgres a commencé à préférer un nouvel index qui semblait meilleur en estimation mais pire en réalité à cause du skew. Parce que l’équipe avait déployé l’index dans une fenêtre séparée et l’avait tagué, ils ont pu isoler le problème rapidement.

Ils n’ont pas paniqué en réécrivant les requêtes sous pression. Ils ont simplement désactivé le chemin en supprimant l’index de manière concurrente (ou, dans d’autres cas, évité les hints du planificateur ; ils préféraient les corrections structurelles). Ensuite ils ont rassemblé des statistiques réelles et repensé l’index pour qu’il corresponde au pattern d’accès dominant.

La « pratique ennuyeuse » n’était pas de l’héroïsme. C’était de l’hygiène de changement : déployer un seul changement affectant la performance à la fois, mesurer et garder le rollback peu coûteux. Cette pratique ne fait pas le buzz, mais évite les ponts d’incident.

Erreurs courantes : symptôme → cause racine → correction

1) Les écritures ralentissent soudainement après une « optimisation de lecture »

• Symptôme : latence d’insert/update en hausse, retard de réplication accru, CPU et E/S qui montent.
• Cause racine : trop d’index secondaires, ou un nouvel index composite/covering large ajouté sur une table chaude.
• Correction : supprimer les index redondants ; ne garder que ceux qui soutiennent les requêtes principales. Dans Postgres, privilégier les index ciblés et partiels ; dans MariaDB, garder la PK compacte et éviter d’indexer « tout ».

2) « L’index existe » mais la requête scanne ou trie encore

• Symptôme : EXPLAIN MariaDB affiche « Using filesort » ou « Using temporary » ; plan Postgres montre Seq Scan ou Sort.
• Cause racine : l’index ne correspond pas à l’ordre des prédicats (ordre composite erroné), ou le prédicat utilise des fonctions/casts empêchant l’utilisation de l’index, ou la faible sélectivité rend le scan moins cher.
• Correction : alignez l’index avec filtre + order ; réécrivez les prédicats pour qu’ils soient sargables (évitez d’envelopper la colonne dans une fonction) ; envisagez un index partiel (Postgres) ou des colonnes calculées (MariaDB).

3) Le planificateur choisit un plan terrible après croissance des données

• Symptôme : une requête qui allait bien la semaine dernière explose ; EXPLAIN montre une nested loop sur un grand nombre de lignes, mauvais ordre de jointure ou mauvais index.
• Cause racine : statistiques périmées, distribution skewée, plans sensibles aux paramètres, ou absence de statistiques étendues (Postgres).
• Correction : exécutez ANALYZE ; augmentez les cibles de statistiques pour les colonnes skewées ; utilisez les statistiques étendues dans Postgres ; dans MariaDB, rafraîchissez les statistiques persistantes/histogrammes et validez avec JSON EXPLAIN.

4) Les index-only scans Postgres ne restent pas index-only

• Symptôme : les plans montrent Index Only Scan mais les fetchs heap sont élevés ; la performance régresse lors de pics d’écriture.
• Cause racine : visibility map non définie à cause d’un vacuum en retard ; les mises à jour fréquentes invalident les bits all-visible.
• Correction : ajustez autovacuum (scale factors, cost delay) par table/partition chaude ; réduisez le churn des updates ; reconsidérez l’ajout excessif d’INCLUDE ; partitionnez les données chaudes.

5) Le taux de hit du buffer pool MariaDB s’effondre « sans raison »

• Symptôme : plus de lectures disque, latence plus élevée, même si le mix de requêtes semble similaire.
• Cause racine : l’ensemble de travail des index a grossi (nouveaux index, PK élargie, ou explosion de cardinalité d’index) et ne tient plus en mémoire.
• Correction : supprimez les index inutilisés ; gardez la PK compacte ; ajustez la taille du buffer pool ; assurez-vous que vos index chauds tiennent en mémoire, pas les index froids.

6) Les temps d’attente de verrous augmentent après ajout/suppression d’index

• Symptôme : timeouts de verrou, attentes DDL, writers bloqués.
• Cause racine : DDL non-online, transactions longues, ou changements de schéma en concurrence avec du trafic chaud.
• Correction : Dans Postgres utilisez CREATE INDEX CONCURRENTLY et DROP INDEX CONCURRENTLY ; dans MariaDB utilisez ALGORITHM=INPLACE, LOCK=NONE quand c’est supporté ; corrigez d’abord les transactions longues.

Listes de contrôle / plan pas à pas

Un workflow pragmatique d’indexation (fonctionne pour les deux moteurs)

1. Choisissez la cible : sélectionnez les requêtes principales par temps total, pas par « la plus lente une fois ».
2. Obtenez un plan réel : Postgres : EXPLAIN (ANALYZE, BUFFERS). MariaDB : EXPLAIN FORMAT=JSON plus compteurs d’état.
3. Classez la douleur : CPU vs IO vs verrous. Si vous ne savez pas, vous n’êtes pas prêt à indexer.
4. Proposez le plus petit index qui aide : ne le rendez pas couvrant « au cas où ».
5. Vérifiez l’impact sur les écritures : la table est-elle chaude ? combien d’écritures/sec ? combien d’index existent déjà ?
6. Déployez en sécurité : Postgres en concurrent ; MariaDB en place/online ; surveillez le retard de réplication.
7. Vérifiez l’usage : confirmez que l’index est choisi et réduit les lignes/E/S. S’il n’est pas utilisé, retirez-le.
8. Mesurez les effets secondaires : cache buffer, vacuum, checkpoints/redo, attente IO, attente de verrous.
9. Réévaluez trimestriellement : les index ne sont pas éternels ; les charges se dégradent.

Checklist de conception d’index : à faire et à éviter

• Faire : alignez les index composites sur votre prédicat + ordre le plus courant. En OLTP, optimisez le chemin principal.
• Faire : en Postgres, utilisez les index partiels pour les motifs « status=active » quand le prédicat est stable et sélectif.
• Faire : en InnoDB, gardez la primary key compacte et évitez les PK larges et aléatoires si vous tenez à la taille des index secondaires.
• Ne pas faire : indexer des colonnes de faible sélectivité seules en espérant un miracle.
• Ne pas faire : ajouter des colonnes INCLUDE tant que vous n’avez pas prouvé que les fetchs heap sont le goulot et que le vacuum peut suivre.
• Ne pas faire : « corriger » une mauvaise requête en ajoutant trois index si la réécriture supprime le besoin de tri ou de scan.

Quand choisir des tactiques à la MariaDB vs des tactiques à la Postgres

• Si la boursouille de la PK vous tue : MariaDB/InnoDB profite souvent d’un redesign de la PK ; Postgres en bénéficie moins, mais la discipline de largeur d’index reste utile.
• Si des prédicats skewés trompent le planificateur : Postgres vous donne plus d’outils statistiques ; utilisez-les avant d’ajouter des index redondants.
• Si vous avez besoin d’index partiels et de types d’index riches : Postgres est généralement la meilleure boîte à outils.
• Si vous cherchez une prédictibilité opérationnelle pour des charges B-tree simples : MariaDB peut être simple — jusqu’à ce que vous sur-indexiez et que le buffer pool devienne un champ de bataille.

FAQ

1) Dois-je « indexer chaque clé étrangère » dans MariaDB et PostgreSQL ?

Généralement oui pour la performance des jointures OLTP, mais pas aveuglément. Dans MariaDB, l’absence d’index sur les FK peut provoquer une amplification des verrous et ralentir les deletes/updates. Dans Postgres, les jointures peuvent fonctionner sans eux, mais vous paierez souvent en scans. Vérifiez avec les requêtes dominantes et les plans réels.

2) Un index composite est-il toujours meilleur que plusieurs index mono-colonne ?

Non. Un composite est meilleur quand votre requête utilise ces colonnes ensemble d’une manière exploitable par l’index (filtre + ordre, ou préfixe sélectif). Plusieurs index mono-colonne peuvent être utiles, et Postgres peut parfois les combiner (bitmap index scans), mais cela peut se traduire par du travail supplémentaire sur le heap.

3) Pourquoi ajouter un index rend parfois une requête plus lente ?

Parce que le planificateur le choisit et se trompe, ou parce que l’index provoque des I/O aléatoires pires qu’un scan séquentiel. De plus, un nouvel index peut changer la forme du plan (ordre des jointures, boucles imbriquées) de façon qui semble moins chère en estimation mais ne l’est pas en réalité.

4) Les index couvrants aident-ils toujours ?

Non. Dans MariaDB, rendre un index « couvrant » en ajoutant des colonnes augmente la taille de l’index et le coût des écritures. Dans Postgres, INCLUDE peut réduire les fetchs heap, mais seulement si vacuum maintient les visibility maps. Le covering est un outil, pas un mode de vie.

5) Comment savoir si mes stats Postgres mentent ?

Quand les lignes estimées diffèrent fortement des lignes réelles dans EXPLAIN (ANALYZE), ou quand le plan change après des modifications mineures. Corrigez avec ANALYZE, des cibles de statistiques par colonne, et des statistiques étendues pour les colonnes corrélées.

6) Puis-je forcer l’utilisation d’un index avec des hints ?

Dans MariaDB vous pouvez, et certains le font. Dans Postgres, les hints ne sont pas intégrés (des extensions existent). Forcer un plan est une dette opérationnelle : cela vous verrouille sur un plan qui peut devenir incorrect au fil des changements de données. Préférez rendre le plan correct naturellement via le schéma, les stats et la forme des requêtes.

7) Quelle est la façon la plus rapide et sûre de supprimer un index ?

Postgres : DROP INDEX CONCURRENTLY pour éviter de bloquer les writers. MariaDB : la suppression d’index est généralement rapide mais peut verrouiller les métadonnées ; faites-le pendant des périodes calmes et surveillez le retard de réplication ou les attentes DDL.

8) Pourquoi un index boolean/status aide parfois en Postgres mais pas en MariaDB ?

Postgres peut exploiter des index partiels comme WHERE status='open' et éviter d’indexer le reste, ce qui augmente la sélectivité. MariaDB n’a pas la même fonctionnalité d’index partiel ; un index full sur une colonne de faible cardinalité n’est souvent pas assez sélectif pour battre un scan.

9) Le partitionnement est-il une solution d’indexation ?

Le partitionnement est une solution de gestion des données qui peut rendre l’indexation moins coûteuse en réduisant la taille des index par partition et le coût de maintenance/vacuum. Il peut aussi complexifier les requêtes et les plans. Faites-le quand l’ensemble chaud/froid d’une table a des besoins opérationnels différents.

10) Combien d’index est « trop » ?

Quand la latence d’écriture et le coût de maintenance dominent, ou quand votre cache ne peut pas héberger l’ensemble de travail. Le nombre n’est pas universel. Comptez les index par table chaude, mesurez l’amplification des écritures et supprimez impitoyablement ceux qui ne paient pas leur loyer.

Prochaines étapes concrètes

Si vous exécutez MariaDB ou PostgreSQL en production et que l’indexation dérive vers de la folklore, faites ceci dans la semaine :

1. Récupérez les requêtes principales par temps total (pg_stat_statements ou performance_schema digests). Faites une courte liste.
2. Pour chaque requête, capturez un plan réel (Postgres : ANALYZE+BUFFERS ; MariaDB : explain JSON). Sauvegardez-le comme artefact.
3. Classez le goulot d’étranglement (CPU/IO/verrous). Si vous ne pouvez pas, ajoutez de l’observabilité avant d’ajouter des index.
4. Supprimez un index réellement inutile ou redondant sur une table chaude, avec plan de rollback. Mesurez la latence d’écriture avant/après.
5. Ajoutez un index ciblé en utilisant les méthodes online/concurrentes, puis prouvez qu’il est utilisé et améliore la bonne métrique (pas seulement le benchmark).
6. Planifiez des contrôles de maintenance : santé du vacuum dans Postgres, taille du buffer pool et croissance des index dans MariaDB.

L’objectif n’est pas de devenir un philosophe de l’indexation. C’est de garder la latence prévisible, les coûts maîtrisés et votre rotation on-call ennuyeuse. L’ennui est le plan premium.