PostgreSQL a introduit le partitionnement déclaratif dans la version 10 pour remplacer le modèle de partitionnement basé sur l'héritage, compliqué. Le planificateur de requêtes effectue une suppression statique de partitions lors de la phase de planification en comparant les prédicats de requête aux limites de partition, mais il exige que les expressions soient évaluable à des constantes au moment du plan pour déterminer quelles partitions peuvent être éliminées.
La contrainte architecturale principale est que les fonctions STABLE, telles que now() ou current_timestamp, ne sont pas évaluées durant la planification car leurs résultats peuvent différer entre la planification et l'exécution, voire même pendant l'exécution de la requête. Par conséquent, le planificateur considère les prédicats impliquant ces fonctions comme des boîtes noires, incapables de prouver que certaines partitions ne peuvent pas contenir des lignes correspondantes, ce qui force un scan de toutes les partitions.
La solution consiste soit à réécrire le prédicat pour utiliser des fonctions IMMUTABLE ou des constantes littérales, soit à s'appuyer sur la suppression dynamique de partitions disponible dans PostgreSQL 11 et versions ultérieures. En définissant enable_partition_pruning sur on, l'exécuteur évalue les résultats des fonctions STABLE par rapport aux limites de partition au moment de l'exécution, en sautant dynamiquement les partitions non pertinentes après la phase de planification initiale.
Une entreprise d'analytique financière a partitionné une table de transactions par TIMESTAMPTZ sur la colonne execution_time en utilisant des plages journalières pour gérer des téraoctets de données de tick. Les analystes interrogeaient fréquemment les activités récentes avec WHERE execution_time >= now() - interval '1 hour', mais ont constaté que ces requêtes souffraient d'une dégradation catastrophique des performances, scannant toutes les 365 partitions journalières au lieu de seulement la plus récente.
La première approche envisagée était de faire calculer la limite de timestamp par la couche d'application et de l'injecter comme une constante littérale. Cela a permis une suppression statique immédiate et a réduit le temps de requête de 45 secondes à 80 millisecondes. Cependant, cela a rompu les requêtes SQL existantes intégrées dans des outils BI tiers qui ne pouvaient pas être facilement modifiés.
La deuxième approche impliquait de créer une fonction immuable personnalisée qui renvoyait un timestamp fixe basé sur la date actuelle. Cela a été rejeté car cela produirait des résultats incorrects si la transaction de base de données restait ouverte à franchir la frontière de minuit, violant le contrat STABLE que now() fournit dans une transaction. Une telle violation pourrait entraîner des résultats de requête incorrects silencieusement si le planificateur mettait en cache une plage de partitions obsolète.
La solution choisie a tiré parti des capacités de suppression de partitions en temps d'exécution de PostgreSQL 12. Les administrateurs de bases de données ont veillé à ce que enable_partition_pruning soit activé et ont réorganisé l'application pour utiliser des instructions préparées avec la limite de temps passée en paramètre plutôt que concaténée dans la chaîne SQL. Cela a permis à l'exécuteur de supprimer les partitions de manière dynamique en utilisant la valeur du paramètre réel au moment de l'exécution, atteignant une performance presque optimale sans nécessiter de modifications à la génération de texte SQL dans les outils de reporting hérités.
Comment le mécanisme d'exclusion de contrainte de PostgreSQL pour les tables héritées diffère-t-il de la suppression de partition native et pourquoi le premier nécessite une configuration GUC explicite tandis que le second ne l'exige pas ?
L'exclusion de contrainte était la méthode de partitionnement utilisée avant le partitionnement déclaratif, reposant sur des contraintes CHECK sur les tables enfants pour prouver que les tables ne pouvaient pas contenir de lignes pertinentes. Évaluer ces contraintes sur chaque table héritée pendant la planification est coûteux lorsque des centaines de tables existent, il est donc contrôlé par le paramètre constraint_exclusion, qui par défaut est partition (vérifiant uniquement lors de la requête via l'héritage). La suppression de partitions native utilise des structures de données spécialisées dans le planificateur qui comprennent directement la hiérarchie de partition, ce qui la rend plus rapide et toujours activée, ne nécessitant aucun ajustement GUC pour un fonctionnement correct.
Lors de la mise à jour d'une ligne pour la déplacer entre des partitions en modifiant la clé de partition, pourquoi PostgreSQL exécute-t-il en interne un DELETE et un INSERT plutôt qu'une mise à jour en place, et quelles sont les implications des déclencheurs ?
Parce que chaque partition est une relation distincte avec un stockage physique séparé, changer la clé de partition nécessite de déplacer le tuple d'un fichier à un autre. PostgreSQL met en œuvre cette transition en supprimant la ligne de la partition source et en l'insérant dans la partition de destination. Cela signifie que les déclencheurs au niveau de la ligne BEFORE DELETE, AFTER DELETE, BEFORE INSERT, et AFTER INSERT se déclenchent tous pendant ce qui semble être une seule opération UPDATE. De plus, la réplication logique diffuse cela en tant que deux entrées WAL distinctes (suppression et insertion), ce qui peut provoquer des conflits sur les abonnés si l'identité du réplica n'est pas correctement configurée.
Quelle surcharge de verrouillage et de validation spécifique se produit lors de l'attachement d'une nouvelle partition à une table qui possède une partition DEFAULT, et comment éviter le scan complet de la partition par défaut ?
Lors de l'attachement d'une nouvelle partition à une table partitionnée par plage ou par liste contenant une partition DEFAULT, PostgreSQL doit scanner l'ensemble de la partition DEFAULT pour vérifier qu'aucune ligne n'existe qui devrait appartenir à la nouvelle partition plus spécifique. Ce scan de validation acquiert un verrou ACCESS EXCLUSIVE sur la table partitionnée et peut prendre des heures pour de grandes partitions par défaut. Pour éviter cela, il est recommandé de détacher la partition DEFAULT avant d'attacher la nouvelle partition, puis de ré-attacher la partition DEFAULT seulement après s'être assuré qu'aucune ligne conflictuelle n'existe, ou alternativement, d'utiliser CREATE TABLE ... PARTITION OF pour créer une nouvelle partition vide et de migrer des données en utilisant INSERT ... SELECT avec une clause WHERE qui filtre pour la plage de partition spécifique, contournant le scan de validation du contenu de la partition par défaut.