Libérer 20 Go inattendus d’espace d’index inutilisé découverts en 2021

• Cet article traite de techniques permettant de récupérer de l’espace de stockage dans une base de données sans supprimer d’index ni de données.
• Les index inutilisés sont identifiés puis supprimés afin de libérer de l’espace de stockage.
• Les tables et les index sont reconstruits pour éliminer l’espace inutile.
• PostgreSQL 13 a introduit la déduplication des index B-Tree, ce qui permet de réduire la taille des index contenant des valeurs dupliquées.
• L’extension pg_repack permet de reconstruire des tables pour réduire le bloat sans downtime.
• Dans PostgreSQL, les valeurs NULL sont également indexées et occupent donc de l’espace inutilement.
• Des index partiels sont utilisés pour réduire la taille des index en excluant les valeurs NULL.
• Des requêtes et des commandes sont fournies pour effectuer ces opérations.
• Les techniques mentionnées peuvent optimiser l’utilisation de l’espace de stockage et améliorer les performances de la base de données.
• Dans une application de grande taille construite avec Django, des index partiels sont utilisés pour optimiser le stockage et améliorer les performances.
• Les index partiels peuvent exclure non seulement les valeurs nulles, mais aussi des valeurs rarement, voire jamais, interrogées.
• Convertir un index donné en index partiel excluant les valeurs nulles permet d’économiser environ 1,3 Go de stockage.
• Supprimer des index sur des réplicas peut libérer encore plus d’espace de stockage.
• Des conseils sont donnés pour empêcher Django de créer implicitement des index sur les clés étrangères et pour migrer des index complets existants vers des index partiels sans downtime ni dégradation des performances.
• L’importance d’optimiser la définition des index afin de maximiser l’efficacité du stockage et les performances est soulignée.

[GN⁺]

Commentaire Hacker News

• Réduire la taille des index présente des avantages même lorsqu’on dispose d’une capacité de stockage suffisante.
• Pour ceux qui possèdent leur propre matériel, allouer davantage d’espace de stockage est une option valable.
• La déduplication des B-tree de PostgreSQL 13 peut aider pour les valeurs NULL dans les index de clés étrangères.
• Cet article fournit des techniques et des analyses utiles pour optimiser l’efficacité de l’espace dans PostgreSQL.
• Pour les entreprises à leurs débuts ou en phase initiale, il vaut généralement mieux ajouter de l’espace disque plutôt que d’optimiser la taille.
• Réorganiser l’ordre des colonnes d’une table et utiliser des scripts peut permettre d’économiser un espace de stockage considérable.
• Une distribution asymétrique des données peut entraîner un gonflement des index et nécessiter des opérations de vacuum plus efficaces.
• Cet article a reçu des retours positifs sur Hacker News.
• pganalyze.com est recommandé pour découvrir les index inutilisés et les possibilités d’optimisation.
• Les formats d’index PostgreSQL peu courants et les index sur des valeurs NULL sont peu connus.
• Augmenter le volume de données peut avoir un impact important sur le temps d’insertion et les performances.
• Le principe concerné peut toujours s’appliquer même lorsqu’une colonne contient une forte proportion de valeurs communes.