Partitionnement Déclaratif de Tables dans PostgreSQL : Gérer des Milliards de Lignes

1. Pourquoi les tables monolithiques s’essoufflent

Une seule table accumulant des milliards de lignes se dégrade sur plusieurs axes indépendants à la fois. La taille des index croît à peu près linéairement avec le nombre de lignes, si bien qu’un B-tree sur une table d’événements de cent millions de lignes peut facilement dépasser le shared_buffers disponible, forçant chaque parcours d’index à toucher des pages sur disque plutôt qu’en cache — exactement l’inverse des recherches rapides, majoritairement en mémoire, qu’un index sain doit fournir. Vacuum doit parcourir et traiter toute la table à chaque passage (malgré la régulation par coût d’autovacuum), et un VACUUM FULL ou un REINDEX sur une table monolithique de plusieurs téraoctets peut tourner pendant des heures, pendant lesquelles il entre en concurrence pour la bande passante I/O avec le trafic réel.

La rétention de données devient un passif opérationnel plutôt qu’une décision de politique : supprimer d’anciennes lignes avec `DELETE FROM events WHERE created_at < $1` sur une table énorme génère un volume proportionnel de tuples morts qu’il faudra ensuite passer à vacuum, produit une quantité importante de WAL, peut retenir des verrous de ligne pendant une durée prolongée, et ne réduit en rien l’empreinte sur disque de la table — l’espace n’est réutilisable que pour de futurs inserts, pas réellement rendu à l’OS, sans un VACUUM FULL ultérieur qui prend lui-même un verrou ACCESS EXCLUSIVE pendant toute sa durée.

La planification des requêtes souffre aussi de façon plus subtile : les statistiques d’une table monolithique représentent une moyenne sur toute son historique, et la taille d’échantillon fixe de default_statistics_target signifie que la vue du planificateur sur la distribution des valeurs d’une table devient proportionnellement plus grossière à mesure que la table grossit, augmentant le risque du décalage entre lignes estimées et réelles évoqué dans l’article EXPLAIN ANALYZE de cette série, ce qui se répercute en mauvaises décisions d’ordre de jointure et d’algorithme de jointure sur presque toute requête touchant cette table.

Aucun de ces problèmes ne se résout en ajoutant davantage d’index ou une instance plus grosse à la table — ce sont des conséquences structurelles d’une relation physique unique croissant sans limite, et la solution est elle aussi structurelle : partitionner la table logique en plusieurs tables physiques gérables indépendamment.

2. Partitionnement déclaratif par intervalle mensuel

Le partitionnement déclaratif de PostgreSQL (depuis la version 10) permet de définir une table parent avec `PARTITION BY RANGE (colonne)` et d’y attacher des tables filles, chacune possédant une tranche disjointe de l’intervalle de clé, que le planificateur et l’exécuteur traitent comme une seule table logique pour l’interrogation tout en les stockant et maintenant physiquement comme des relations séparées, avec leurs propres index, statistiques et cycles de vacuum. Le partitionnement par intervalle sur une colonne d’horodatage, une partition par mois (ou par semaine/jour pour les tables à volume extrêmement élevé), est le schéma standard pour les séries temporelles et les données d’événements.