Partitionner et sharder une base : quand et comment choisir

Partitionner une table, c'est la découper en morceaux à l'intérieur d'une même base ; sharder, c'est répartir les données sur plusieurs serveurs. Cette leçon s'adresse à qui administre une base relationnelle et se demande si une table qui grossit doit être découpée. Elle vaut pour tous les moteurs, PostgreSQL comme MySQL. Elle montre d'abord ce qui rend vraiment une table ingérable, puis les trois réponses possibles dans l'ordre de leur coût, les trois axes de partitionnement et ce que le sharding impose. Le fil rouge reste le suivi d'incidents d'un parc de serveurs, qui grossit chaque jour. Le sharding y apparaît pour ce qu'il est : un dernier recours.

Ce que vous allez apprendre

• Identifier ce qui fait d'une table un problème, au-delà de sa taille
• Ordonner par coût monter la machine, répartir la lecture et découper les données
• Choisir un axe de partitionnement : intervalle, liste ou hachage
• Dire ce que le partitionnement apporte, et ce qu'il n'apporte pas
• Expliquer pourquoi la clé de répartition d'un sharding est difficile à défaire
• Choisir entre index, partitionnement, replicas, cache et sharding sur un cas donné

Quels prérequis, et où s'écrit le SQL ?

Vous savez ce qu'est une table, et vous avez suivi Modéliser des données, dont cette leçon reprend le suivi d'incidents. Savoir lire un plan d'exécution aide : Index et EXPLAIN en donne la mesure. Cette leçon n'écrit aucune syntaxe SQL : elle enseigne à décider. Les gestes relèvent des sous-formations PostgreSQL et MySQL.

Quand une grosse table devient-elle un problème ?

Une table devient un problème quand une opération ne tient plus dans sa fenêtre, ou quand ses index ne tiennent plus en mémoire, jamais à cause d'un nombre de lignes seul. Une recherche par index sur dix millions de lignes répond en quelques millisecondes ; quelques millions de lignes sans index adapté, non. Imaginons 50 000 incidents par jour : la table atteint 18 millions de lignes en un an et 36 millions en deux ans, volume que n'importe quel moteur absorbe. Quatre symptômes doivent vous alerter :

• le jeu de travail, la part des données et des index lue en permanence, dépasse la mémoire vive, et chaque lecture part sur disque ;
• la purge des incidents de plus de deux ans supprime des millions de lignes une à une, ce qui verrouille des lignes, écrit chaque suppression dans le journal des transactions et laisse de l'espace à récupérer ;
• la maintenance (sauvegarde, reconstruction d'un index, vérification) dure proportionnellement au volume et finit par déborder la nuit ;
• les écritures ralentissent, car chaque insertion met à jour chaque index.

Avant de découper, mesurez. Un index manquant donne les mêmes lenteurs qu'une table trop grosse et se règle en une minute : c'est la première vérification, avec le plan d'exécution de la requête lente.

Monter la machine, répartir la lecture ou découper les données ?

Il existe trois réponses à une base qui sature, et on les essaie dans l'ordre de leur coût de complexité croissant. La première donne plus de ressources à la même machine, la deuxième répartit les lectures sur des copies de la base, les replicas de lecture, la troisième découpe les données elles-mêmes. Le socle est la distinction entre montée en charge verticale et horizontale de Disponibilité, durabilité et montée en charge : la base monte d'abord verticalement, parce que la répartir touche à la cohérence de ses données.

Réponse	Ce qu'elle règle	Ce qu'elle ne règle pas	Complexité ajoutée
Monter la machine (plus de mémoire, disques plus rapides)	jeu de travail trop gros, maintenance trop longue	purge, plafond de la plus grosse machine	faible : une opération planifiée
Répartir la lecture (replicas de lecture)	rapports lourds qui gênent les écritures	volume, écritures	moyenne : retard de réplication à tolérer
Découper les données (partitionnement, puis sharding)	purge (partitionnement) ; volume et écritures au-delà d'une machine (sharding)	les requêtes qui ne filtrent pas sur la colonne du découpage	forte, et croissante

Glissez pour voir

Un replica de lecture sert les lectures pendant que l'original absorbe les écritures. Il se traite dans Répliquer, sauvegarder et basculer une base, qu'il suffit de connaître ici comme réponse possible. L'idée reçue selon laquelle le relationnel « ne passe pas à l'échelle » est dépassée : partitionnement natif, replicas, Citus (extension qui répartit PostgreSQL) et Vitess (couche de sharding pour MySQL) existent. Chaque palier ajoute pourtant de la complexité, d'où l'ordre.

Qu'est-ce que le partitionnement d'une table ?

Le partitionnement découpe une table en morceaux appelés partitions, dans une seule base et sur un seul serveur, sans que l'application le voie. L'application interroge toujours la table incidents ; le moteur range chaque ligne dans la bonne partition d'après la valeur d'une colonne, la clé de partitionnement, et choisit seul où lire. PostgreSQL parle de partitionnement déclaratif (MySQL, de partitionnement défini par l'utilisateur) : on décrit la règle de découpe, et le moteur la fait respecter. Chaque partition a ses propres index, donc plus petits qu'un index unique sur toute la table.

Quel axe choisir : intervalle, liste ou hachage ?

L'axe se choisit d'après les requêtes et la purge, pas d'après la taille. L'axe est la règle qui répartit les valeurs de la clé de partitionnement entre les partitions. Trois axes sont courants dans les deux moteurs, et le tableau les compare sur le parc d'incidents.

Axe	Principe	Exemple du parc	Convient quand	Piège
Intervalle	une plage de valeurs par partition	un mois d'ouverture par partition	données datées, purge par ancienneté	les futures partitions sont à créer avant d'en avoir besoin
Liste	des valeurs énumérées par partition	un site ou une région par partition	requêtes toujours filtrées par ce critère	une nouvelle valeur sans partition fait échouer l'insertion
Hachage	un calcul sur la clé répartit les lignes	l'identifiant de serveur	équilibrer le volume sans critère naturel	aucune purge par partition, aucune requête par plage utile

Glissez pour voir

Pour les incidents, l'intervalle sur la date d'ouverture s'impose : on interroge surtout le récent, et on purge par ancienneté. Le hachage ne cherche qu'à répartir uniformément, au prix de la purge par partition et des requêtes par plage.

Que gagne-t-on avec le partitionnement, et que ne gagne-t-on pas ?

Le partitionnement apporte deux gains précis, la purge rapide et l'élagage, et il n'apporte ni la répartition sur plusieurs machines ni l'accélération de toute requête. Le premier gain : supprimer les incidents d'un vieux mois revient à détacher ou supprimer une partition entière, une opération sur la structure et non sur les lignes, bien plus rapide que supprimer des millions de lignes une à une. Le second : quand une requête filtre sur la clé de partitionnement, le moteur ignore les autres partitions, c'est l'élagage (partition pruning), et lit un mois plutôt que deux ans.

Voici ce qu'il n'apporte pas et les limites que l'on découvre souvent trop tard :

• une requête qui ne filtre pas sur la clé parcourt toutes les partitions, et peut devenir plus lente qu'avant ;
• tout reste sur une machine : même processeur, mêmes disques, même point de défaillance ;
• les contraintes d'unicité, clé primaire comprise, doivent inclure la clé de partitionnement (règle des deux moteurs ; MySQL en dispense seulement une table sans aucune clé unique) ;
• MySQL 8.4 avec InnoDB interdit toute clé étrangère sur une table partitionnée, qu'elle en porte ou qu'elle soit référencée ;
• un grand nombre de partitions alourdit la planification des requêtes : PostgreSQL annonce bien gérer jusqu'à quelques milliers de partitions, à condition que l'élagage n'en laisse que quelques-unes, et MySQL plafonne à 8192 partitions par table, sous-partitions comprises.

Qu'est-ce que le sharding, et pourquoi change-t-il tout ?

Le sharding répartit les lignes d'une table sur plusieurs serveurs : chaque serveur, ou shard, héberge une tranche des données et fonctionne comme une base complète. La clé de répartition décide quelle ligne va sur quel shard. Une couche de routage (Vitess pour MySQL, Citus pour PostgreSQL, ou le code de l'application) envoie chaque requête au bon shard. Tout ce qui était local devient une conversation entre machines à travers le réseau : c'est ce qui change tout.

Dans le schéma ci-dessous, regardez en haut le partitionnement : l'application ne voit qu'une table, rangée en trois partitions dans un seul serveur. En bas, le sharding : un routage lit la clé et dirige la requête vers un serveur de base de données parmi trois, chacun gardant les incidents d'une tranche des machines supervisées (« Serveurs 1 à 500 », et ainsi de suite). La frontière entre les deux est la machine.

Source · SVG

Échap pour fermer, + et − pour zoomer, défilement pour se déplacer. 100 % − Ajuster + Fermer

Pourquoi la clé de répartition est-elle une décision structurante ?

La clé de répartition fixe quelles requêtes restent locales à un shard et lesquelles traversent tous les serveurs : on la choisit une fois, et on la change à grand prix. Pour les incidents, trois choix donnent trois vies différentes :

• l'identifiant de la machine supervisée garde « les incidents de web-01 » sur un seul shard, mais « tous les incidents ouverts aujourd'hui » interroge tous les shards ;
• la date d'ouverture facilite les plages, mais envoie toutes les écritures du moment sur le même shard, un point chaud qui sature pendant que les autres dorment ;
• le hachage de l'identifiant de l'incident équilibre le volume, mais ne sert aucune requête par plage.

Changer de clé impose un resharding : redistribuer les données entre serveurs, en production, pendant que l'application continue d'écrire. Des outils comme Vitess l'outillent, ils ne le rendent pas gratuit. Et chaque requête de l'application a été écrite en pensant à la clé : la modifier revient à réécrire une partie du code.

Que coûtent les requêtes et les transactions qui traversent plusieurs shards ?

Toute opération qui touche plusieurs shards coûte un aller-retour réseau par shard, et attend le plus lent. Quatre coûts reviennent :

• une requête sans la clé est envoyée à tous les shards, puis les résultats sont rassemblés : sa durée est celle du shard le plus lent ;
• une jointure entre données de shards différents ramène les lignes à travers le réseau, ou impose de copier partout les petites tables de référence ;
• unicité et clés étrangères ne se vérifient plus d'office à l'échelle de la base : l'intégrité référentielle devient en pratique l'affaire de l'application ;
• une transaction sur deux shards exige un protocole de validation en deux phases (les shards votent d'abord, puis valident ensemble), lent et fragile si le coordinateur tombe ; sinon l'on accepte une cohérence à terme, présentée dans Synchrone, asynchrone, eventual consistency : les patterns cloud.

S'ajoute le coût d'exploitation : N serveurs à sauvegarder, superviser et mettre à jour, et une sauvegarde qui doit rester cohérente d'un shard à l'autre. D'où la règle : on ne shard que lorsqu'une machine unique, déjà montée, partitionnée et épaulée de replicas, ne suffit plus, typiquement pour les écritures ou le volume. Les replicas n'allègent que les lectures.

Un cache peut-il remplacer un découpage des données ?

Non : un cache accélère les lectures répétées d'une même donnée, mais n'allège ni les écritures, ni la purge, ni le volume, et il peut servir une donnée périmée. Il garde en mémoire, souvent dans Redis ou Valkey, le résultat des requêtes les plus fréquentes. Il aide un tableau de bord consulté mille fois par minute ; il ne résout pas une base saturée d'insertions. Le cache-aside, les durées de vie (TTL), l'invalidation et les pannes d'un cache froid sont traités dans Cache : ce qu'il accélère, ce qu'il rend faux, ce qu'il casse, page de référence qui n'est pas redite ici.

Comment choisir : quel symptôme, quelle réponse ?

On choisit en partant du symptôme mesuré, jamais de la technique à la mode, et on monte d'un palier seulement quand le précédent est épuisé. Le tableau reprend des cas courants, à peu près du plus simple au plus lourd.

Symptôme mesuré	Première réponse	Pourquoi pas plus lourd
« Les incidents d'un serveur » s'affichent lentement	un index adapté, vérifié avec EXPLAIN	le plan montre la cause avant tout découpage
Trop de connexions simultanées sur la base	un pool de connexions (un intermédiaire qui réutilise quelques connexions ouvertes) comme PgBouncer	le goulot est le nombre de sessions, pas les données
Le jeu de travail dépasse la mémoire	une machine plus grosse	une seule opération, aucune réécriture
Le même tableau de bord est lu sans cesse	un cache	la donnée change peu, la lecture se répète
Les rapports du lundi gênent la prise d'incident	un replica de lecture pour les rapports	le problème est la lecture, pas le volume
La purge mensuelle dure des heures	un partitionnement par intervalle sur la date	détacher ou supprimer une partition remplace des millions de suppressions de lignes
Séries de mesures datées très volumineuses	une extension de séries temporelles comme TimescaleDB	elle automatise le partitionnement par le temps
Les écritures dépassent ce que la plus grosse machine absorbe	le sharding	aucune des réponses précédentes ne tient plus

Glissez pour voir

PgBouncer, TimescaleDB, Vitess, Redis et Valkey ont leur fiche dans le catalogue d'outils du site. Retenez cet ordre : mesurer, indexer, monter la machine, répartir la lecture, partitionner, puis seulement sharder. Un palier sauté se paie en complexité que rien ne justifiait.

À retenir

• Une table est un problème par ce qu'elle empêche (purge, mémoire, maintenance, écritures), pas par son nombre de lignes : mesurez d'abord.
• Les trois réponses, par complexité croissante : monter la machine, répartir la lecture par replicas, découper les données.
• Le partitionnement découpe une table dans une seule base, transparente pour l'application ; les axes courants sont l'intervalle, la liste et le hachage.
• Ses apports sont la purge par partition et l'élagage ; il n'apporte ni répartition sur plusieurs machines, ni accélération des requêtes qui ne filtrent pas sur la clé.
• Le sharding répartit les données sur plusieurs serveurs : c'est un dernier recours, qui change l'exploitation et le code.
• La clé de répartition est une décision structurante : la changer exige un resharding en production.
• Requêtes sans clé, jointures et transactions entre shards coûtent cher ; l'unicité globale et les clés étrangères deviennent en pratique l'affaire de l'application.
• Un cache soulage les lectures répétées mais ne règle ni les écritures ni la taille, et peut servir une donnée périmée.

Contrôle de connaissances

Huit questions vérifient ce qui fait d'une grosse table un problème, l'ordre dans lequel on essaie les remèdes, le choix d'un axe de partitionnement et ce que le partitionnement n'apporte pas. Elles portent aussi sur la clé de répartition d'un sharding, le coût d'une requête sans clé, le rôle d'un cache et le choix d'un replica pour un symptôme donné.

Contrôle de connaissances

Validez vos connaissances avec ce quiz interactif

8 questions

8 min.

70% requis

Informations

• Le chronomètre démarre au clic sur Démarrer
• Questions à choix multiples, vrai/faux et réponses courtes
• Vous pouvez naviguer entre les questions
• Les résultats détaillés sont affichés à la fin

Démarrer le quiz

Lance le quiz et démarre le chronomètre

×

📖 Voir la documentation →