- Une alerte de manque d’espace de stockage a déclenché un nettoyage PostgreSQL, qui a permis de récupérer plus de 70 Go d’espace non optimisé ou inutilisé sans supprimer d’index ni de données
- Au départ, les techniques classiques de récupération ont été passées en revue : index inutilisés, bloat des tables et des index,
REINDEX CONCURRENTLY, pg_repack, ou encore la déduplication B-Tree de PostgreSQL 13
- La différence décisive venait du fait que PostgreSQL, contrairement à Oracle, indexe aussi les valeurs NULL ; en remplaçant un index complet sur
cancelled_by_user_id, majoritairement NULL, par un index partiel, sa taille est passée de 769 Mo à moins de 5 Mo
- Les index mono-colonne dont
pg_stats.null_frac est élevé ont été recherchés pour identifier des candidats à un index partiel avec la condition WHERE column IS NOT NULL, puis leur usage réel a été vérifié via pg_stat_all_indexes après réinitialisation des compteurs statistiques
- Dans Django, un
ForeignKey nullable peut créer par défaut un index B-Tree ; il faut donc préciser db_index=False et définir explicitement un index partiel avec models.Index(..., condition=Q(...__isnull=False))
Un nettoyage parti d’une alerte de manque d’espace
- Tous les quelques mois, la supervision de la base de données déclenchait une alerte de manque d’espace, à laquelle on répondait habituellement en ajoutant du stockage
- À ce moment-là, en période d’isolement, la charge du système était plus faible, ce qui créait de bonnes conditions pour tenter une opération de nettoyage plus lourde qu’à l’ordinaire
- Au final, plus de 70 Go d’espace non optimisé ou inutilisé ont été récupérés sans supprimer d’index ni effacer de données
- La réorganisation classique des index et des tables a déjà permis de réduire fortement l’espace utilisé, puis une découverte inattendue a permis de supprimer environ 20 Go supplémentaires de valeurs indexées inutilisées
Premières causes suspectées
-
Index inutilisés
- Les index inutilisés sont créés pour améliorer les performances de lecture, mais ils occupent de l’espace et peuvent ralentir les
INSERT et les UPDATE
- Dans
pg_stat_all_indexes, les index avec idx_scan = 0, idx_tup_read = 0 et idx_tup_fetch = 0 ont été recherchés afin d’identifier ceux qui n’avaient été ni scannés ni fetchés depuis la dernière réinitialisation des statistiques
- Certains index peuvent sembler inutilisés en surface, mais ne doivent pas être supprimés
- Quand l’optimiseur PostgreSQL utilise les métadonnées de l’index plutôt que l’index lui-même
- Les index qui garantissent une contrainte unique ou de clé primaire sur une table rarement mise à jour
- Il faut examiner la liste au cas par cas pour savoir quels index peuvent réellement être supprimés, et gérer les compteurs statistiques en les réinitialisant après vérification
- Dans ce cas précis, comme ce travail était déjà effectué régulièrement, il n’y avait pas d’index inutilisé à supprimer
-
Bloat des index et des tables
- Quand PostgreSQL met à jour une ligne, il marque le tuple existant comme dead et ajoute un nouveau tuple ailleurs, ce qui peut provoquer du bloat
- Le bloat touche non seulement les tables, mais aussi les index, et leur fait consommer plus d’espace de stockage que nécessaire
- L’estimation du bloat n’étant pas triviale, des requêtes issues du wiki PostgreSQL et de
pgsql-bloat-estimation ont été utilisées pour estimer le bloat des tables et des index B-Tree
Méthodes pour réduire le bloat des index
-
Recréation et réorganisation des index
- Pour éliminer le bloat d’un index, il faut le reconstruire
- En le supprimant puis en le recréant directement, on peut obtenir un nouvel index sous forme optimisée
- La commande
REINDEX de PostgreSQL reconstruit l’index existant sur place
- Les méthodes classiques de recréation posent un lock sur la table et empêchent les modifications ; en production,
REINDEX INDEX CONCURRENTLY index_name est donc plus adapté
REINDEX CONCURRENTLY crée un nouvel index avec le suffixe _ccnew, synchronise les changements, puis remplace l’ancien index par le nouveau à la fin et supprime l’ancien- Si la reconstruction est interrompue en cours de route, le nouvel index peut rester invalide tout en occupant de l’espace ; il faut donc repérer et supprimer les index portant
_ccnew et dont indisvalid n’est pas vrai
-
Déduplication B-Tree dans PostgreSQL 13
- PostgreSQL 13 a introduit la B-Tree Deduplication, qui stocke plus efficacement les valeurs dupliquées dans les index B-Tree
- Jusqu’à PostgreSQL 12, même avec beaucoup de valeurs dupliquées, chaque doublon pouvait être stocké de manière répétée dans les feuilles de l’index, consommant beaucoup d’espace
- Dans PostgreSQL 13, lorsque la déduplication est activée, une valeur dupliquée n’est stockée qu’une seule fois, ce qui peut avoir un impact important sur la taille des index contenant beaucoup de doublons
- Dans PostgreSQL 13,
deduplicate_items = ON est la valeur par défaut ; après une mise à niveau depuis une version antérieure, il faut reconstruire les index avec REINDEX pour en obtenir tout le bénéfice
- Dans un exemple à 1 million de lignes, un index non unique sur une colonne très dupliquée est passé de 21 Mo à 6840 ko avec la déduplication, tandis qu’un index sur une colonne unique est resté à 21 Mo
- Dans ce cas, PostgreSQL 13 était encore récent et le fournisseur cloud ne le prenait pas en charge, il n’était donc pas possible de réduire l’espace grâce à la déduplication
Bloat des tables et pg_repack
-
Contraintes liées à la suppression du bloat des tables
- Les tables peuvent aussi souffrir de bloat et de fragmentation à cause des tuples dead
- Contrairement aux index, les tables sont difficiles à simplement recréer : il faut créer une nouvelle table, migrer les données, les synchroniser, puis recréer les index, contraintes et contraintes de référence
VACUUM FULL table_name peut récupérer l’espace occupé par le bloat et les tuples dead, mais nécessite un lock sur la table- La recréation d’une table comme
VACUUM FULL peuvent chacun se traduire par un gros chantier de développement ou du downtime
-
Utilisation de pg_repack
- pg_repack est une extension PostgreSQL utilisée pour réorganiser tables et index sans downtime
- Pour l’utiliser, il faut créer l’extension avec
CREATE EXTENSION pg_repack;, puis lancer en console une commande de type pg_repack -k --table table_name db_name
pg_repack crée une nouvelle table, y charge les données d’origine tout en la synchronisant avec les nouvelles données, reconstruit aussi les index, puis échange les deux tables à la fin- Son utilisation en production comporte quelques contraintes
- Il faut en gros prévoir un espace de stockage supplémentaire équivalent à la taille de la table et des index à réorganiser
- Si le repack échoue ou est interrompu manuellement, des objets intermédiaires peuvent subsister et nécessiter un nettoyage manuel
- Si l’espace de stockage est déjà insuffisant, le besoin d’espace supplémentaire peut rendre l’outil inadapté ; il faut donc surveiller l’espace libre et planifier à l’avance
Découverte inattendue : PostgreSQL indexe aussi les NULL
- Après avoir appliqué les techniques classiques de suppression d’index inutilisés et de nettoyage du bloat des tables et des index, il restait encore de l’espace à gagner
- L’une des plus grosses tables stockait des données de transaction et enregistrait aussi les cas où un utilisateur annulait après paiement et était remboursé
- La table des transactions contenait des foreign keys à la fois vers l’utilisateur acheteur et vers l’utilisateur ayant annulé, avec un index B-Tree défini sur chaque champ
- L’utilisateur acheteur avait une contrainte
NOT NULL, donc chaque ligne contenait une valeur
- L’utilisateur ayant annulé était nullable et, les annulations étant rares, la plupart des valeurs étaient NULL
- On s’attendait à ce que l’index sur l’utilisateur ayant annulé soit beaucoup plus petit que celui sur l’utilisateur acheteur, mais ils avaient en réalité la même taille
- Avec Oracle, on apprend que les NULL ne sont pas indexés, mais dans PostgreSQL les valeurs NULL sont bien indexées
- L’index complet existant était le suivant
CREATE INDEX transaction_cancelled_by_ix
ON transactions(cancelled_by_user_id);
- L’hypothèse a été vérifiée en le remplaçant par un index partiel excluant les valeurs NULL
DROP INDEX transaction_cancelled_by_ix;
CREATE INDEX transaction_cancelled_by_part_ix
ON transactions(cancelled_by_user_id)
WHERE cancelled_by_user_id IS NOT NULL;
- Après réindexation, l’index complet faisait 769 Mo et plus de 99 % des valeurs étaient NULL
- L’index partiel excluant les NULL faisait moins de 5 Mo, soit une réduction de plus de 99 % de l’index
- Après réinitialisation des statistiques de la table et observation pendant un certain temps, le nouvel index s’est avéré utilisé comme l’ancien, et plus de 760 Mo de tuples indexés inutilisés ont été supprimés sans dégradation de performance
Trouver des candidats à un index partiel
- Après les bons résultats obtenus sur un premier index partiel, des index similaires ont été recherchés parmi les colonnes dont
pg_stats.null_frac est élevé
null_frac correspond à la proportion de valeurs NULL estimée par PostgreSQL pour la colonne concernée- La requête de recherche de candidats appliquait les conditions suivantes
- Exclure les primary keys, qui ne peuvent pas être partielles
- Exclure les index déjà partiels
- Exclure les index composites
- Inclure uniquement les index de taille supérieure à 10 Mo
- Dans l’exemple de résultat,
tx_cancelled_by_ix faisait 1418 Mo avec un null_frac de 96,15 %, ce qui en faisait un candidat permettant potentiellement d’économiser environ 1363 Mo
- Exclure tous les NULL de l’index n’est pas toujours avantageux
- Les requêtes qui cherchent des NULL avec une condition
IS NULL peuvent bénéficier d’un index sur les valeurs NULL
- Outre les valeurs non NULL, il est aussi possible d’exclure d’un index partiel d’autres valeurs rarement recherchées
- Les 20 Go du titre s’expliquent par une architecture où réduire d’environ 10 Go la base primaire réduit aussi d’un volume similaire l’espace utilisé par les réplicas
Migrer vers un index partiel avec l’ORM Django
-
ForeignKey nullable et index implicite
- Sauf si
db_index=False est explicitement précisé, Django crée implicitement un index B-Tree sur les champs models.ForeignKey
- Si aucun réglage n’est appliqué à un
ForeignKey nullable qui stocke l’utilisateur ayant annulé, Django crée un index complet
- Pour créer un index partiel, il faut mettre
db_index=False sur le champ FK et ajouter un models.Index conditionnel dans Meta.indexes
class Transaction(models.Model):
cancelled_by_user = models.ForeignKey(
to=User,
null=True,
on_delete=models.CASCADE,
db_index=False,
)
class Meta:
indexes = (
models.Index(
fields=('cancelled_by_user_id', ),
name='%(class_name)s_cancelled_by_part_ix',
condition=Q(cancelled_by_user_id__isnull=False),
),
)
- Une foreign key nullable est souvent un bon candidat à un index partiel
- Pour éviter qu’une fonctionnalité implicite ne crée des index à l’insu de l’équipe, un Django check a été ajouté afin d’imposer que
db_index soit toujours explicitement défini sur les foreign keys
-
Remplacer un index complet par un index partiel sans downtime
- Pour remplacer un index complet par un index partiel sans downtime ni baisse de performance, il faut procéder par étapes
- Le modèle Django concerné est modifié pour remplacer l’index complet par un index partiel, mais la migration générée par Django n’est pas exécutée telle quelle
- Cette migration peut désactiver la contrainte FK, supprimer l’ancien index complet puis créer le nouvel index partiel, ce qui peut provoquer du downtime et une dégradation des performances
- Le SQL est généré avec
./manage.py sqlmigrate, seul le CREATE INDEX est extrait, modifié avec CONCURRENTLY, puis exécuté manuellement sur la base de données
- Comme l’ancien index complet est encore présent, les requêtes peuvent continuer à l’utiliser pendant la création de l’index partiel
- Pour vérifier que le nouvel index partiel est utilisé, les compteurs statistiques de l’index complet sont réinitialisés avec
pg_stat_reset_single_table_counters(<full index oid>)
- Ensuite,
idx_scan, idx_tup_read et idx_tup_fetch dans pg_stat_all_indexes sont observés pour comparer l’usage de l’index complet et de l’index partiel
- Une fois l’index partiel considéré comme utilisé, l’index complet est supprimé et l’espace réellement récupéré est vérifié
- Une fois l’état de la base aligné avec l’état du modèle,
./manage.py migrate --fake est exécuté pour marquer la migration Django comme appliquée
- Dans les environnements dev, QA ou staging, où le downtime est moins critique, la migration Django peut s’exécuter normalement et remplacer l’index complet par l’index partiel
Conclusion
- Les améliorations de performance et d’espace ont des limites si l’on se contente d’optimiser les disques, les paramètres de stockage ou la configuration
- Le dernier levier d’amélioration se trouvait dans les objets de la base eux-mêmes, en l’occurrence dans la définition des index
- Le processus appliqué se résume en trois étapes
- Supprimer les index inutilisés
- Repack des tables et des index, et activation de la déduplication B-Tree si possible
- Utiliser des index partiels pour n’indexer que les valeurs nécessaires
- Ce type de nettoyage peut constituer une option très concrète pour gagner quelques jours avant de devoir provisionner du stockage supplémentaire
1 commentaires
Avis de Hacker News
Même avec beaucoup d’espace de stockage, il est bon de réduire la taille des index. Cela permet de faire tenir davantage de données dans le hot set.
Cela dit, quand on exploite des bases de données de l’ordre du To, « il suffit d’ajouter du stockage » reste toujours une option valable. C’est particulièrement vrai hors cloud : sur du matériel propre, un SSD NVMe d’entreprise coûte environ 80 $/To, et la RAM DDR4 environ 1,20 $/Go. Si l’on estime 4 heures d’ingénierie à environ 1 000 $, cela permet d’acheter 800 Go de RAM ou 12 To de stockage.
Rien qu’avec des sauvegardes hebdomadaires sur un an, 1 To devient 50 To, même avec du stockage hors ligne. Dans notre entreprise aussi, ajouter du stockage à la base live est facile, et nous l’avons fait pendant des années, mais déplacer des sauvegardes de plusieurs To devient de plus en plus pénible ; nous en sommes au point où il faut réduire les données de production, même si cela coûte du temps d’ingénierie.
Pour une action ponctuelle, l’infrastructure existante peut parfois absorber des coûts comme de l’espace libre en rack ou des slots PCIe disponibles, mais ce type d’amortissement ne fonctionne bien qu’à petite échelle. En outre, plus le système grossit, plus les coûts de gestion et l’augmentation de la latence font inévitablement baisser les performances par unité de matériel. Si l’on continue à résoudre les problèmes en agrandissant le système, l’ensemble devient de plus en plus lent, jusqu’à pouvoir devenir inexploitable. À l’inverse, une solution qui réduit les ressources système nécessaires augmente les performances par unité de ressource et crée en soi un actif de meilleure qualité.
Une optimisation qui économise 1 To aujourd’hui peut en économiser 2 To dans quelques années, et quelques décisions de ce type peuvent suffire à créer un écart d’un ordre de grandeur ou plus. C’est particulièrement vrai à plus grande échelle ; au final, il faut toujours trouver un équilibre.
« Jeter du matériel sur le problème » peut parfois être un choix judicieux, mais dans la plupart des cas, je doute qu’on puisse faire passer aujourd’hui un serveur de 128 Go de RAM à 1 To de RAM demain pour 1 000 $.
Cela dit, comme il ne s’agit pas d’une dépense unique mais d’un coût mensuel, le calcul est un peu plus complexe. Bien sûr, si la base de données croît à un rythme constant, le provisionnement ponctuel on-premise ressemble en pratique lui aussi à un coût mensuel.
L’article mentionne la déduplication des B-tree, présente dans PostgreSQL 13 mais pas dans la version 12 utilisée, et indique que la grande majorité des valeurs de certains index de clés étrangères sont NULL.
Je me demande si la déduplication des B-tree aurait aidé dans ce cas. À lire la section 63.4.2 de la documentation PostgreSQL 13, on dirait que oui : “B-Tree deduplication is just as effective with ‘duplicates’ that contain a NULL value...” Bien sûr, ce ne serait pas aussi efficace que l’index partiel appliqué dans l’article, mais c’est intéressant.
https://www.postgresql.org/docs/13/btree-implementation.html
https://news.ycombinator.com/item?id=25989467
Cet article a été utile non seulement pour la « découverte » centrale des index partiels, mais aussi parce qu’il rassemble globalement de bonnes techniques à connaître quand on s’inquiète d’une utilisation inefficace de l’espace dans PostgreSQL.
Cela dit, si vous êtes une startup ou une entreprise en phase initiale, il faut garder en tête que, face à ce type de problème de stockage, ajouter de l’espace disque est presque toujours préférable à se soucier d’optimiser la taille. Les développeurs coûtent cher, les disques coûtent peu.
J’ai travaillé autrefois dans une startup en forte croissance où l’un des fondateurs insistait pour continuer à mettre à niveau un seul serveur. Nous avions de la redondance et des sauvegardes, mais l’architecture restait simple, donc facile à gérer, à déboguer et à restaurer. Pendant les cinq premières années de l’entreprise, tout le système tournait sur un seul serveur tout en croissant exponentiellement et en servant des millions d’utilisateurs dans le monde. Depuis que j’ai vu cela, je suis convaincu qu’il faut mettre à niveau seulement quand c’est nécessaire, de la manière la plus simple et la plus directe possible.
Utiliser un index partiel qui correspond clairement au cas d’usage, comme lorsque la plupart des valeurs sont NULL, relève plutôt d’une bonne modélisation ; il ne faut pas y voir une optimisation prématurée ni une perte de temps développeur.
Dans le même ordre d’idées, ces scripts m’ont été très utiles :
https://github.com/NikolayS/postgres_dba
En réorganisant l’ordre des colonnes de grosses tables, j’ai pu récupérer environ 10 % de l’espace de stockage, soit environ 100 Go.
La semaine dernière, avec seulement
reindexetpg_repack, j’ai récupéré 200 Go sur une base de données de 600 Go. La pire table contenait 17 Go de données mais 142 Go d’index ; après réindexation, c’est tombé à 21 Go.Cette table était surindexée, avec plusieurs index sur diverses combinaisons de colonnes. L’une des causes de ces énormes index semble avoir été la distribution des données. Il y avait des insertions, mises à jour et suppressions continues depuis 2015 ; les données anciennes étant plus susceptibles d’être supprimées, les années récentes sont davantage représentées, mais environ 0,1 % des données datent toujours de 2015. Je pense que cette longue traîne dans une distribution biaisée a pu rendre plus difficile pour vacuum la gestion du gonflement des index.
On en avait déjà discuté à l’époque : An unexpected find that freed 20GB of unused index space in PostgreSQL - https://news.ycombinator.com/item?id=25988871 - février 2021, 78 commentaires
Je recommande vivement pganalyze.com pour trouver les index inutilisés, les opportunités d’optimisation et les requêtes à forte latence
L’expression « une astuce bizarre pour économiser 20 Go » n’était vraiment pas exagérée. Dans de vieux environnements Oracle / SQL Server, on me demande souvent d’utiliser des formes d’index particulières à PostgreSQL dont on entend presque jamais parler, mais je ne savais pas que les valeurs NULL étaient aussi indexées
Sur le premier grand projet auquel j’ai participé, l’équipe n’arrivait pas à comprendre pourquoi les traitements ralentissaient à mesure que le jeu de données grossissait
Les index ont un temps d’insertion de log(n) par enregistrement. Si la base de test passe de 1 000 enregistrements à près de 65k, le temps d’insertion augmente de 60 % (2^10 contre 2^16). Le succès rend tout plus lent, et les mises à niveau des serveurs ont leurs limites. Ajoutez quelques index de plus à cause d’une fonctionnalité vague demandée par le métier, et cela double maintenant
Je gère aussi beaucoup de bases avec des centaines de millions d’enregistrements et plus de 40 index par table/collection
Une recherche binaire dans une page B-tree prend moins de 100 cycles. Même une recherche dans un B-tree de 100 millions d’enregistrements devrait encore se compter en microsecondes, et la recherche binaire par-dessus aussi, en microsecondes si ce n’est en centaines de nanosecondes
Ce principe s’applique-t-il aussi quand une valeur, ou quelques valeurs, sont très fréquentes ? Par exemple si 90 % d’une colonne d’entiers vaut 0
where val = 0conserve 90 % des lignes, il peut choisir un scan de table plutôt qu’un index