Techniques d’optimisation PostgreSQL non conventionnelles

• Présentation de trois approches créatives pour améliorer les performances des requêtes PostgreSQL en sortant des méthodes classiques, au lieu d’ajouter des index ou de réécrire les requêtes
• Élimination des scans complets de table via des contraintes CHECK, optimisation de la faible cardinalité avec des index basés sur des fonctions, et mise en œuvre d’une contrainte d’unicité avec des index Hash
• 1. Utiliser le paramètre constraint_exclusion pour éviter les scans inutiles dans les requêtes comportant des conditions erronées
• 2. Utiliser des index basés sur des fonctions et des colonnes générées virtuelles (virtual generated column) pour réduire la taille des index et garantir la cohérence des requêtes
• 3. Combiner des index Hash et des contraintes d’exclusion (exclusion constraint) pour implémenter efficacement une contrainte d’unicité sur de gros champs texte, avec d’importantes économies d’espace de stockage

---

Éliminer les scans complets de table avec des contraintes CHECK

• Même si une contrainte CHECK n’autorise que les valeurs 'free' et 'pro' dans la colonne plan, si une requête erronée est exécutée avec 'Pro', PostgreSQL parcourt toute la table
  • Le plan d’exécution lit les 10000 lignes, alors que le résultat réel est de 0 ligne
• En définissant le paramètre constraint_exclusion sur 'on', PostgreSQL tient compte des contraintes et supprime entièrement le scan
  • Le temps d’exécution passe de 7,4 ms à 0,008 ms
• La valeur par défaut est 'partition', et sur des requêtes simples, la surcharge de planification peut au contraire augmenter
  • En revanche, dans un environnement de BI et de reporting, où les utilisateurs saisissent souvent des conditions incorrectes, le réglage sur 'on' peut être utile

Optimiser la faible cardinalité avec des index basés sur des fonctions

• Sur la table sale, qui contient 10 millions d’enregistrements de ventes, une requête d’agrégation du chiffre d’affaires quotidien prend 627 ms avec un scan complet
• L’ajout d’un index B-Tree sur la colonne sold_at réduit ce temps à 187 ms, mais la taille de l’index grimpe à 214 MB
• En créant un index basé sur une fonction sur l’expression date_trunc('day', sold_at), la taille tombe à 66 MB et le temps d’exécution s’améliore encore à 145 ms
  • La faible cardinalité permet une déduplication de l’index
• En revanche, l’expression de la requête doit correspondre exactement à la définition de l’index, ce qui impose de maintenir la cohérence des expressions
  • Pour cela, on peut créer une VIEW contenant la même expression, ou
  • ajouter une colonne générée virtuelle (virtual generated column), prise en charge à partir de PostgreSQL 18, afin d’automatiser cette cohérence
• Avec une colonne générée virtuelle, l’index est utilisé automatiquement, ce qui permet d’obtenir à la fois un petit index, des requêtes plus rapides et une cohérence des expressions
• Toutefois, dans PostgreSQL 18, la création d’index sur des colonnes virtuelles n’est pas encore prise en charge, un support étant prévu dans la version 19

Implémenter une contrainte d’unicité avec des index Hash

• Dans une table urls stockant de longues URL, la création d’un index unique basé sur B-Tree pour empêcher les doublons d’URL fait monter la taille de l’index à 154 MB
• Un index Hash ne stocke pas les valeurs réelles mais uniquement leur hachage, ce qui le rend beaucoup plus compact
  • PostgreSQL ne prend pas en charge par défaut les index Hash uniques, mais
  • il est possible de contourner cette limite pour implémenter une contrainte d’unicité à l’aide d’une contrainte d’exclusion (exclusion constraint) sous la forme EXCLUDE USING HASH (url WITH =)
• Avec cette méthode, une insertion en double provoque également une erreur, et les performances des requêtes sont supérieures à celles du B-Tree (0.022 ms vs 0.046 ms)
• La taille de l’index est de 32 MB, soit plus de 5 fois plus petit qu’un B-Tree
• Inconvénients :
  • impossible de référencer la colonne avec une clé étrangère (contrainte REFERENCES impossible)
  • compatibilité limitée avec la syntaxe INSERT ... ON CONFLICT
  • remplacement possible par ON CONFLICT ON CONSTRAINT ou la syntaxe MERGE
• Les index Hash sont adaptés à la garantie d’unicité sur de gros champs texte et constituent une alternative efficace en espace lorsqu’aucune clé étrangère n’est nécessaire

[GN⁺]

Commentaires sur Hacker News

• L’index fait 214 Mo, soit environ la moitié de la taille de la table entière
  C’est bien du point de vue de l’analyste, mais côté performances d’écriture cela crée un problème de write amplification
  La conception des index change selon le ratio lecture/écriture, et c’est pour cela qu’on met en place un data warehouse ou des read replicas
  Si l’on doit gérer énormément d’utilisateurs, mieux vaut éviter de mettre des index BI/OLAP sur une base OLTP

  • J’aimerais bien que PostgreSQL prenne en charge les clustered indexes (les Index Organized Tables d’Oracle)
    Si les schémas d’accès à la table sont réguliers, la table elle-même peut devenir l’index et offrir ce gain d’efficacité sans write amplification

• Pour le premier exemple, je pense qu’il vaudrait mieux définir Plan comme un type enum
  C’est plus léger que du texte, et en cas de filtre invalide on obtient une erreur au lieu d’un résultat vide, ce qui est plus sûr

• Excellent article. J’utilise PostgreSQL et MySQL depuis des décennies, et même après cette lecture j’ai eu l’impression de ne connaître qu’une fraction de ce qui est possible

  • Moi aussi j’utilise Postgres depuis plus de dix ans, mais chaque fois que je consulte la documentation, j’ai encore l’impression de n’en gratter que la surface. C’est un système incroyablement puissant
  • PostgreSQL me fait penser à Emacs. En apparence c’est simple, mais en réalité il offre une flexibilité presque digne d’un système d’exploitation

• Le plus intéressant pour moi, c’était la syntaxe MERGE mentionnée à la fin de l’article
  D’ordinaire je gère les upserts avec INSERT ... ON CONFLICT DO UPDATE, mais MERGE semble plus puissant et utilisable dans davantage de situations

  • MERGE existe depuis longtemps dans le standard SQL, mais Postgres a repoussé son adoption à cause de problèmes de non-atomicité dans le modèle MVCC
    C’est aussi expliqué dans cet article du blog pganalyze
    Personnellement, je préfère INSERT ... ON CONFLICT et je n’utilise MERGE que lorsque c’est vraiment nécessaire, avec une gestion des erreurs très prudente
  • Du point de vue de la concurrence, INSERT ... ON CONFLICT est plus prévisible
    Voir cet article comparatif sur modern-sql.com
  • Pour des insertions par lots massives, utiliser COPY INTO au format binaire est le plus rapide. L’overhead côté serveur est quasi nul

• Les index BRIN, qui ne sont pas abordés dans l’article, m’ont semblé intéressants
  Si les données sont monotones croissantes, c’est un index très petit et très rapide, idéal dans ce cas

  • Il n’est pas nécessaire que les données soient parfaitement monotones. Si elles le sont en grande majorité, cela fonctionne déjà très bien
    Par exemple, pour des données de timestamp reçues par un serveur, où l’ordre est parfois légèrement perturbé
    Avec UUIDv7, il peut être nécessaire d’ajuster pages_per_range

• J’ai toujours trouvé dommage qu’on ne puisse pas appliquer de contrainte d’unicité sur un index hash
  On dirait qu’un simple morceau de glue code pour le transformer en exclusion constraint suffirait, alors je me demande pourquoi cela n’existe toujours pas

• La vérification d’unicité fondée sur un hash n’est pas prise en charge par l’index parce qu’elle ne gère pas les collisions
  La solution proposée rencontre le même problème

  • Ce n’est absolument pas vrai. L’index ne stocke que le hash, mais la table stocke la valeur complète
    Postgres considère qu’il y a doublon uniquement si le hash et la valeur réelle correspondent tous les deux
  • On peut aussi le vérifier dans cet exemple dbfiddle

• Le contenu de l’article était rafraîchissant. Les colonnes virtuelles et les index hash sont intéressants, mais j’ai encore l’impression qu’ils ne sont pas totalement intégrés à l’écosystème

  • Les colonnes virtuelles sont presque finalisées. L’essentiel est implémenté dans PostgreSQL 18
    Les index hash ont longtemps eu beaucoup de limitations, mais ils s’améliorent peu à peu, et la contrainte d’unicité automatique reste le chantier principal

• Je me suis demandé s’il n’était pas possible de créer directement un index en utilisant une stored generated column

  • L’article explique justement pourquoi cette approche est évitée
    Elle est prise en charge depuis PostgreSQL 14, mais comme le résultat est physiquement stocké et occupe donc de l’espace supplémentaire, on préfère l’éviter
  • Je me demande aussi s’il ne serait pas possible de créer un partial index fondé sur une expression
  • Au final, cela augmente tout de même l’espace de stockage, donc c’est une approche que l’exemple de l’article cherche à éviter

• Depuis mon passage au cloud, je manipule moins souvent pgsql directement dans un environnement à serveurs fixes
  Je me demande si la mise en évidence de la syntaxe SQL montrée dans l’article est une fonctionnalité intégrée ou un outil séparé

  • J’utilise pgcli. Il offre pas mal de fonctions pratiques, comme l’affichage de l’état de la transaction, l’autocomplétion et le surlignage
    En revanche, c’est gênant que lors de la copie de longues requêtes, des espaces soient automatiquement ajoutés après les retours à la ligne
  • Avec un IDE comme IntelliJ, on peut aussi profiter de la coloration syntaxique et de l’autocomplétion