Written with AI assistance from the source video. The video is the authority.
Log in to leave a comment
No posts yet
Dans les systèmes hérités, les CTE récursives utilisées pour explorer les relations parent-enfant ou les chemins multiples s'effondrent lorsque les données deviennent trop profondes. Dès que le fan-out des index vacille, le moteur crée des tables temporaires à chaque étape et répète les auto-jonctions. Le jeu de résultats remplit la mémoire et un dépassement (spillover) se produit, déversant les données sur le disque. Les E/S sont bloquées et le the CPU est épuisé. Bien que la syntaxe native CYCLE introduite dans PostgreSQL 14 réduise le coût de recherche des tableaux, le coût des recherches d'index secondaires répétées et de la copie de mémoire de tuples à grande échelle persiste à mesure que la profondeur d'exploration augmente.
Pour augmenter la vitesse de recherche et économiser le CPU, il faut passer à la syntaxe de graphe natif basée sur SQL/PGQ. Premièrement, utilisez la syntaxe CREATE PROPERTY GRAPH tout en conservant la structure physique des tables relationnelles existantes. Spécifiez les tables d'entités uniques comme VERTEX TABLES et les tables de jonction many-to-many comme EDGE TABLES. Deuxièmement, étant donné que les colonnes spécifiées comme clés ne sont pas automatiquement incluses dans les propriétés du graphe, ajoutez les champs dans la clause PROPERTIES pour conserver les droits de filtrage GRAPH_TABLE. Troisièmement, pour les requêtes dont la profondeur d'exploration dépasse 3 niveaux, avec un taux de branchement moyen de 10 ou plus, ou un nombre de tuples dépassant 10 millions, remplacez-les par le motif GRAPH_TABLE MATCH. Le code récursif complexe est réduit à un motif d'une seule ligne et l'utilisation de la mémoire tampon diminue sensiblement.
Les erreurs d'insertion en double qui se produisent lorsque plusieurs threads envoient des données simultanément dans un environnement distribué ne peuvent pas être résolues par des verrous d'application. Un surdébit RTT réseau se produit et une condition de concurrence éclate dans la faille juste avant la validation de la transaction. Les verrous au niveau des lignes doivent être intériorisés avec les clauses INSERT ON CONFLICT et MERGE. La clause ON CONFLICT utilise une technique d'insertion spéculative pour verrouiller la page d'index unique et tenter l'insertion. En cas de conflit, elle bifurque immédiatement vers DO UPDATE ou DO NOTHING pour réduire la fréquence des sessions de blocage mutuel (deadlock).
Modifier le niveau d'isolement des transactions lors de l'utilisation de requêtes atomiques a un impact considérable. Sous READ COMMITTED, la transaction suivante attend que la transaction précédente soit validée, puis relit le tuple le plus récent pour un traitement sécurisé. Cependant, si vous l'augmentez à REPEATABLE READ ou SERIALIZABLE, en cas de conflit avec la transaction précédente, elle renvoie immédiatement une erreur d'échec de sérialisation sans attendre et effectue un retour en arrière (rollback). Pour réduire le coût du rollback et éviter l'épuisement du pool de connexions, vous devez appliquer une boucle de réessai uniquement sur les codes d'erreur 40001 et 40P01. Ajoutez une valeur de gigue aléatoire au temps d'attente de base pour disperser les moments de tentative, et limitez le nombre maximal de réessais entre 3 et 5 avant de remonter l'erreur vers la couche métier supérieure.
Le point de départ de la gestion du stockage est de saisir le moment où l'efficacité des balayages d'index diminue en raison de l'accumulation de fragmentation de blocs et de tuples morts. En raison des caractéristiques de l'architecture MVCC, les tuples morts créés par UPDATE ou DELETE ne sont pas immédiatement renvoyés au système d'exploitation mais restent sous forme de fragmentation. Lorsque vous observez conjointement l'indicateur n_dead_tup de la vue pg_stat_user_tables et l'extension pgstattuple, si le dead_tuple_ratio dépasse 20 pour cent ou si le taux d'free_space représente 30 pour cent, vous devez récupérer l'espace disque. Si vous laissez la situation telle quelle, la lecture des blocs d'E/S augmentera et l'efficacité du tampon partagé (buffer pool) sera compromise.
Pour récupérer le stockage en arrière-plan sans arrêter le service, utilisez l'outil pg_repack. Premièrement, créez une table fantôme (shadow table) de la table source cible et attachez un déclencheur de journalisation pour suivre les modifications. Deuxièmement, copiez en masse les tuples valides dans la table fantôme, recréez les index de manière asynchrone et en parallèle, puis modifiez le catalogue système. Troisièmement, exécutez directement la commande du processus d'arrière-plan depuis le terminal.
`bash
pg_repack
--dbname=production_db
--table=public.orders
--jobs=4
--wait-timeout=10
--no-superuser-check
`
Pour éviter les conflits de verrous, définissez SET lock_timeout = '3s'; au niveau de la session afin que, si un verrou ne peut pas être obtenu en 3 secondes, l'opération génère immédiatement une erreur au lieu de stagner dans la file d'attente. Ajoutez à cela l'option --wait-timeout=10 pour couper net les situations où les requêtes suivantes sont bloquées en cascade.
Un changement soudain de plan de requête dû à un décalage dans le cycle de collecte des statistiques entraîne des pannes en production. Dans les environnements soumis à de fortes charges de travail, les statistiques et la distribution réelle des données divergent, provoquant un basculement de plan (plan flip). Si l'optimiseur choisit une jonction par boucle imbriquée (nested loop join) au lieu d'un balayage d'index, des pics de CPU apparaissent et un goulot d'étranglement des E/S se produit. Pour diagnostiquer le problème, vous devez inclure le module auto_explain dans shared_preload_libraries et définir log_min_duration à 500 millisecondes pour consigner le plan d'exécution réel des requêtes lentes dans les journaux du serveur.
Pour fixer de force le chemin d'exécution d'une requête spécifique, utilisez un module d'indices (hints). Après avoir identifié la requête problématique dans les journaux, installez l'extension pg_hint_plan et ajoutez des commentaires avant l'instruction SQL ou enregistrez la requête dans la table de catalogue hint_plan.hints. Si la situation rend difficile le déploiement du code source, vous pouvez injecter directement une chaîne d'indices statique au niveau du catalogue pour corriger immédiatement le plan sans redéploiement. C'est le moyen le plus rapide de préserver la vitesse de réponse des requêtes dans les situations d'urgence où la re-collecte des statistiques ou la reconstruction des index prendrait trop de temps.
