Nouveautés du planificateur de requêtes de Postgres 16

• PostgreSQL 16 ajoute 10 améliorations au planificateur/optimiseur de requêtes, élargissant le choix des plans d’exécution pour DISTINCT, les agrégations, les jointures, les fonctions de fenêtre et les requêtes sur tables partitionnées
• SELECT DISTINCT, les agrégations avec ORDER BY/DISTINCT et les traitements après Merge Join exploitent plus activement les entrées partiellement triées, ce qui permet de produire les résultats avec moins de mémoire qu’un tri complet
• La prise en charge de Memoize dans UNION ALL, de Right Anti Join et des jointures par hachage parallèles pour les jointures FULL/RIGHT vise à réduire le coût des recherches répétées et de la création de grandes tables de hachage
• Les fonctions de fenêtre évitent les traitements RANGE inutiles et réduisent les WindowAgg qui devaient s’exécuter jusqu’au bout ; certaines fonctions peuvent désormais s’arrêter plus tôt selon les conditions
• Toutes ces améliorations sont activées par défaut ; il est donc utile de comparer les EXPLAIN et les temps d’exécution de vos workloads réels avant et après une mise à niveau vers PostgreSQL 16

---

Périmètre des améliorations du planificateur dans PostgreSQL 16

• PostgreSQL 16 introduit plusieurs améliorations dans le planificateur de requêtes, permettant à de nombreuses requêtes SQL de s’exécuter plus rapidement que dans les versions précédentes de PostgreSQL
• Les améliorations du planificateur incluses dans les notes de version de PG16 sont expliquées plus en détail, avec des comparaisons de sorties EXPLAIN entre PG15 et PG16 ainsi que des exemples de tests reproductibles
• Ici, le planificateur correspond au composant couramment appelé optimiseur dans d’autres bases de données relationnelles

Optimisation du tri et de DISTINCT

• Utilisation d’Incremental Sort dans SELECT DISTINCT

  • Incremental Sort a été ajouté pour la première fois dans PostgreSQL 13 ; lorsque les résultats sont déjà triés selon les premières colonnes, il réduit le coût en ne triant que les colonnes restantes
  • Le planificateur de PostgreSQL 16 prend aussi en compte Incremental Sort pour les requêtes SELECT DISTINCT
  • Par exemple, s’il existe un index btree sur la colonne a et qu’un ordre a, b est nécessaire, il est possible d’obtenir via l’index un résultat trié selon a, puis de ne trier que b à chaque changement de valeur de a
  • Dans le quicksort de PostgreSQL, trier plusieurs petits groupes peut être plus efficace que trier un seul grand groupe
  • Dans l’exemple de requête, PG15 utilisait HashAggregate et un scan séquentiel, tandis que PG16 choisit l’index distinct_test_a_idx et Incremental Sort
  • Presorted Key: a dans la sortie de PG16 signifie que l’entrée déjà triée selon a a été exploitée
  • La méthode par hachage de PG15 a déversé environ 30 Mo sur disque, tandis que la mémoire maximale d’Incremental Sort dans PG16 était de 26 Ko
  • Le temps d’exécution passe de 414,226 ms avec PG15 à 263,167 ms avec PG16

• Optimisation des agrégations avec ORDER BY ou DISTINCT

  • Dans PostgreSQL 15 et les versions antérieures, les fonctions d’agrégation avec une clause ORDER BY ou DISTINCT effectuaient toujours un tri à l’intérieur du nœud Aggregate
  • Le planificateur de PostgreSQL 16 peut produire un plan d’exécution fournissant au nœud Aggregate des lignes dans le bon ordre, et l’exécuteur omet le tri interne si l’entrée est déjà triée
  • Dans l’exemple COUNT(DISTINCT b), PG15 comme PG16 utilisent GroupAggregate et Index Only Scan, mais la sortie de PG15 affiche temp read=4540 written=4560
  • Ces E/S de fichiers temporaires résultent du tri implicite de PG15 qui a déversé sur disque
  • La sortie de PG16 ne contient pas ces E/S temporaires, et le temps d’exécution passe de 302,693 ms avec PG15 à 115,534 ms avec PG16, soit plus de deux fois plus rapide

Améliorations des recherches répétées et des plans de jointure

• Application de Memoize à l’intérieur de UNION ALL

  • Le nœud de plan Memoize a été introduit pour la première fois dans PostgreSQL 14 et agit comme une couche de cache entre une Nested Loop paramétrée et son entrée interne
  • Le planificateur de PostgreSQL 16 envisage aussi l’utilisation de Memoize lorsqu’une requête UNION ALL se trouve à l’intérieur d’une Nested Loop paramétrée
  • Dans l’exemple, PG15 exécutait Append un million de fois, tandis que PG16 place Memoize au-dessus d’Append
  • Le Memoize de PG16 indique Hits: 999990, Misses: 10, Memory Usage: 2kB
  • Le nombre d’exécutions d’Append passe d’un million avec PG15 à 10 avec PG16
  • Le temps d’exécution passe de 1926,151 ms avec PG15 à 282,120 ms avec PG16, soit environ 6 fois plus rapide

• Prise en charge de Right Anti Join

  • Dans un Hash Join d’un INNER JOIN, il est généralement préférable de construire la table de hachage sur la plus petite table
  • Une petite table de hachage nécessite moins de travail de construction, est plus favorable au cache CPU et réduit aussi le risque de CPU stall en attendant des données depuis la mémoire principale
  • Avant PostgreSQL 16, un Anti Join plaçait toujours la table mentionnée dans NOT EXISTS à l’intérieur de la jointure, ce qui pouvait imposer de construire la table de hachage sur la plus grande table
  • PostgreSQL 16 prend en charge Right Anti Join, ce qui permet de hacher le plus petit des deux côtés
  • Dans l’exemple, PG15 hachait la table large d’un million de lignes avec une utilisation mémoire de 6446 Ko, tandis que PG16 hache la table small de 100 lignes et n’utilise que 12 Ko
  • Le temps d’exécution passe de 139,023 ms avec PG15 à 77,076 ms avec PG16, soit presque deux fois moins

• Jointures par hachage parallèles pour les jointures FULL/RIGHT

  • PostgreSQL 11 a introduit Parallel Hash Join, où plusieurs workers parallèles participent à la création d’une seule table de hachage
  • Le Parallel Hash Join de PostgreSQL 16 prend en charge les types de jointure FULL et RIGHT
  • Les plans FULL OUTER JOIN et Right Join peuvent également s’exécuter en parallèle
  • Dans l’exemple de FULL JOIN, PG15 utilisait un simple Hash Full Join, tandis que PG16 utilise Parallel Hash Full Join et Gather
  • La sortie de PG16 affiche Workers Planned: 1, Workers Launched: 1
  • Le temps d’exécution baisse nettement, de 220,677 ms avec PG15 à 129,769 ms avec PG16

Optimisation des fonctions de fenêtre

• Omission du traitement RANGE inutile

  • Pour les fonctions de fenêtre comme row_number(), rank(), dense_rank(), percent_rank(), cume_dist() et ntile(), PostgreSQL utilise par défaut l’option RANGE si la clause de fenêtre ne contient pas d’option ROWS
  • L’option RANGE doit examiner les lignes précédentes pour trouver les peer rows ayant la même valeur de tri, ce qui peut devenir coûteux s’il existe beaucoup de valeurs identiques selon ORDER BY
  • Le comportement de ces fonctions ne change pas selon que ROWS ou RANGE est spécifié, mais avant PostgreSQL 16, l’exécuteur ne pouvait pas faire cette distinction et devait vérifier les peer rows dans tous les cas
  • Le planificateur de PostgreSQL 16 sait quelles fonctions de fenêtre sont affectées par les options ROWS/RANGE et transmet à l’exécuteur les informations nécessaires pour ignorer les traitements inutiles
  • Dans l’exemple row_number() <= 10, PG15 lisait 50 410 lignes depuis l’index avant de s’arrêter, tandis que PG16 n’en lit que 11
  • PG16 exploite le fait qu’une fois row_number arrivé à 11, aucune autre ligne ne peut satisfaire la condition <= 10
  • Le temps d’exécution passe de 29,775 ms avec PG15 à 0,058 ms avec PG16, soit plus de 500 fois plus rapide

• Extension de l’arrêt anticipé pour les fonctions de fenêtre monotones croissantes

  • PostgreSQL 15 a permis d’arrêter plus tôt l’exécution de WindowAgg lorsque, pour certaines fonctions de fenêtre, une condition de la clause WHERE ne peut plus redevenir vraie après être devenue fausse
  • PostgreSQL 16 étend cette optimisation à ntile(), cume_dist() et percent_rank()
  • Dans PostgreSQL 15, elle ne s’appliquait qu’à row_number(), rank(), dense_rank(), count() et count(*)
  • Dans l’exemple percent_rank() <= 0.01, PG15 traitait la condition comme un Filter dans la sous-requête, et WindowAgg traitait les 50 000 lignes
  • PG16 utilise la même condition comme Run Condition et interrompt plus tôt l’exécution de WindowAgg
  • Le temps d’exécution passe de 84,358 ms avec PG15 à 19,454 ms avec PG16, soit plus de 4 fois plus rapide

Tables partitionnées et traitement des DISTINCT triviaux

• Suppression des LEFT JOIN sur les tables partitionnées

  • PostgreSQL peut depuis longtemps supprimer les LEFT JOIN qui ne sont pas nécessaires à une requête et ne peuvent pas créer de lignes en double
  • Avant PostgreSQL 16, la suppression de LEFT JOIN n’était pas prise en charge pour les tables partitionnées
  • La preuve nécessaire pour déterminer si les lignes internes risquaient de dupliquer les lignes externes n’existait pas pour les tables partitionnées
  • Le planificateur de PostgreSQL 16 applique aussi l’optimisation de suppression des LEFT JOIN aux tables partitionnées
  • Cette optimisation peut être particulièrement utile dans les vues
    • Car même si une vue comporte beaucoup de colonnes, les requêtes réelles ne consultent pas toujours toutes les colonnes
  • Dans l’exemple, le plan de PG15 inclut la jointure vers part_tab, tandis que le plan de PG16 effectue seulement un scan séquentiel de normal_table

• Traitement d’un DISTINCT au résultat déterminé comme un Limit

  • Le planificateur PostgreSQL peut omettre le nœud de plan de déduplication des résultats s’il peut détecter que toutes les lignes ont la même valeur
  • PostgreSQL 16 exploite le fait que le résultat ne contient que les mêmes valeurs lorsque toutes les colonnes ciblées par DISTINCT sont fixées par des conditions d’égalité dans la clause WHERE, et le traite avec LIMIT 1
  • Dans la requête d’exemple SELECT DISTINCT a,b,c FROM abc WHERE a = 5 AND b = 5 AND c = 5, chaque colonne DISTINCT est limitée à la même valeur
  • PG15 lit tout le résultat et le réduit à une ligne avec l’opérateur Unique
  • PG16 utilise Limit et un scan séquentiel pour ne renvoyer qu’une seule ligne
  • Le temps d’exécution passe de 30,381 ms avec PG15 à 0,025 ms avec PG16, soit plus de 1200 fois plus rapide

Utilisation accrue d’Incremental Sort après Merge Join

• Avant PostgreSQL 16, lorsque le planificateur envisageait un Merge Join, il n’utilisait l’ordre de tri de la jointure que si cet ordre correspondait exactement aux exigences de l’opération supérieure DISTINCT, GROUP BY ou ORDER BY
• Cette règle ne reflétait pas suffisamment le fait qu’Incremental Sort peut exploiter une entrée partiellement triée dans l’opération supérieure
• PostgreSQL 16 assouplit la règle de prise en compte de l’ordre du Merge Join, qui passe de « correspondance exacte requise » à « au moins une colonne de tête doit être correctement triée »
• Grâce à ce changement, le planificateur peut utiliser plus souvent Incremental Sort pour adapter le résultat d’un Merge Join à l’opération supérieure
  • Incremental Sort exploite les entrées partiellement triées pour trier par petits lots, réduisant ainsi l’utilisation mémoire et le nombre de comparaisons par rapport à un tri complet
• Dans l’exemple, PG15 utilisait un Sort complet après Merge Join, tandis que PG16 utilise Incremental Sort
  • La mémoire maximale d’Incremental Sort dans PG16 était de 26 Ko
  • Le temps d’exécution diminue légèrement, de 1010,738 ms avec PG15 à 915,589 ms avec PG16, et la mémoire utilisée pour le tri baisse fortement

Mode d’application et vérification en pratique

• Les 10 améliorations du planificateur dans PostgreSQL 16 sont toutes activées par défaut
• Chaque optimisation s’applique dans tous les cas possibles, ou de manière sélective lorsque le planificateur estime qu’elle est utile
• Si vous utilisez une version antérieure de PostgreSQL, vous pouvez exécuter votre workload réel sur PostgreSQL 16 pour identifier les requêtes qui deviennent plus rapides
• Les retours d’expérience en production peuvent être partagés sur la liste de diffusion [e-mail]

[GN⁺]

Avis Hacker News

• Ce serait vraiment bien si le query planner de PostgreSQL pouvait replanifier une requête en cours d’exécution
  Les requêtes pathologiquement lentes viennent souvent du fait que le planner ne connaît pas les informations nécessaires sur la distribution des données et estime mal les coûts, ce qui peut facilement produire un écart de 1000x, par exemple avec un temps d’exécution de 1 s au lieu de 1 ms
  Comme les statistiques de table ne peuvent pas être exactes à 100 %, si, après le démarrage de la requête, l’avancement est plus lent que prévu, il serait bien de réinjecter dans le planner les informations de progression courantes, comme le nombre de pages parcourues et les tuples correspondants, afin de produire un nouveau plan
  Mais PostgreSQL envoie les résultats en streaming au fur et à mesure au lieu d’attendre de tout produire avant l’envoi, donc changer de plan au milieu demanderait de suivre les résultats déjà envoyés au client, ce qui implique de gros changements d’infrastructure
  En plus, le client peut aussi inverser le sens au milieu de la requête et redemander les résultats précédents dans l’ordre inverse, ce qui augmente encore la complexité

  • En tant qu’auteur du billet et committer PostgreSQL, je pense aussi que cette fonctionnalité serait souhaitable. Cela dit, le problème de l’envoi des tuples au client est encore plus délicat que ce qui a été décrit plus haut
    Il n’y a même pas de garantie qu’un nouveau plan renverra les mêmes tuples. Par exemple, avec SELECT * FROM table LIMIT 10, en l’absence de ORDER BY, les tuples retournés sont non déterministes
    Il serait peut-être plus simple d’accumuler X tuples dans une file, puis de ne commencer à les envoyer qu’une fois la file pleine. Une fois cette file remplie, on considérerait qu’il est trop tard pour replanifier et on figerait le plan actuel
    L’utilisateur pourrait ajuster X afin d’avoir plus de temps pour changer de plan, en échange de plus de mémoire consommée et d’un délai accru avant le premier tuple
  • Une autre approche serait d’autoriser des requêtes avec une phase de planification longue. On pourrait permettre d’utiliser 1 seconde ou plusieurs secondes pour choisir le meilleur plan, et pendant ce temps collecter davantage de statistiques ou même exécuter brièvement la requête à titre d’essai
  • Je me demande dans quels cas il est utile que le client puisse inverser le sens au milieu de la requête pour redemander les résultats précédents dans l’ordre inverse
  • Je me demande si, lorsqu’une requête ne détermine pas complètement l’ordre de tri, le plan de requête peut influer sur l’ordre des résultats. Si oui, l’approche proposée pourrait être quasiment impossible
    La nouvelle requête ne pourrait pas simplement sauter les N premiers résultats et devrait comparer chaque ligne déjà envoyée avec un dictionnaire
  • Cet article et les travaux qu’il cite pourraient vous intéresser : https://arxiv.org/pdf/1902.08291

• J’utilise cet outil pour visualiser les requêtes : https://explain.dalibo.com/
  Il y a aussi https://www.pgexplain.dev/, dont la sortie était moins bonne auparavant, mais aujourd’hui les deux me semblent comparables

  • L’outil est excellent et je l’utilise, mais je n’ai pas une compréhension assez approfondie pour savoir comment corriger mon approche en voyant, dans le plan, ce qui semble mauvais
  • En regardant votre profil, vous êtes CTO dans la fintech ; je me demande comment vous gérez l’avertissement de cet outil recommandant de « ne pas envoyer d’informations importantes ou sensibles »
    Je me demande s’il existe un outil d’assainissement de plans d’exécution qui aide dans ce type de situation

• Les améliorations du query planner sont toujours les bienvenues, et c’est une partie extrêmement importante d’une base de données. Bien sûr, c’est surtout quand il ne fait pas ce que je veux que cela saute aux yeux
  Personnellement, ce qui m’a souvent frustré récemment, c’est le JIT dans les versions récentes de PostgreSQL. Les heuristiques qui décident quand l’utiliser ne me semblent pas du tout robustes
  Je l’ai vu sur des requêtes typiquement générées par des ORM : la requête elle-même est simple, mais elle embarque beaucoup de tables via des jointures. Sans JIT, cela se termine en quelques millisecondes, alors qu’avec JIT il ajoute 1 à 1,5 seconde, ce qui rend le tout extrêmement lent même sur de petits volumes de données
  Maintenant, je sais qu’il suffit de désactiver JIT, mais pour un utilisateur qui n’a pas encore compris pourquoi c’est lent, cela peut fortement dégrader son impression de PostgreSQL. J’aime PostgreSQL, mais laisser JIT activé par défaut me semble bien trop risqué

  • En tant qu’auteur du billet et committer PostgreSQL, je suis tout à fait d’accord pour dire que le code qui décide d’utiliser JIT ou non doit être amélioré
    Dans PG16, il ne regarde que le coût total estimé du plan, sans tenir compte du nombre d’expressions à compiler
    Compiler quelques expressions est rapide, mais lorsqu’on interroge une table partitionnée avec des centaines de partitions et qu’elles figurent toutes dans le plan, le compilateur JIT a beaucoup de travail
    Avec un collègue, nous avons du code pour améliorer cela, mais à ce stade il n’est pas certain qu’il entre dans PG17
  • Un autre point étrange avec JIT, c’est que le code généré n’est pas mis en cache. C’est souvent la partie la plus coûteuse de l’exécution d’une requête, donc je ne comprends pas pourquoi il n’est pas mis en cache
    Même en cherchant dans les discussions de la mailing list PostgreSQL à propos de JIT, je n’ai pas trouvé de raison convaincante
    Sur des charges de travail OLTP, il vaut mieux désactiver JIT
  • JIT me paraît pratiquement être un échec. L’intention était bonne, mais LLVM n’est pas l’outil adapté ici. Je l’ai désactivé globalement
    Je n’utilise pas d’ORM, donc ce n’est pas seulement à cause de schémas de requêtes bizarres
    En revanche, la parallélisation des requêtes peut réellement être utile et, surtout, elle ne nuit que rarement
  • J’ai récemment rencontré un bug étrange lié à JIT en production
    Après avoir mis à jour quelques paquets via apt, une grosse requête exécutée toutes les 5 minutes a soudainement commencé à échouer. Plus précisément, PostgreSQL coupait silencieusement la connexion au milieu de l’exécution, sans rien écrire dans les logs
    En testant manuellement avec EXPLAIN, j’ai constaté que seule la variante de la requête qui activait JIT cassait, tandis que celle qui ne l’utilisait pas fonctionnait bien. Une fois JIT désactivé, tout est revenu à la normale
  • Je me demande s’ils ont essayé d’utiliser des prepared statements pour ne compiler qu’une seule fois, puis réutiliser le résultat compilé à chaque exécution de cette requête

• Je me demande à quelle fréquence ces changements ont un effet dans des requêtes réelles. En particulier, le changement « utiliser Limit au lieu de Unique pour implémenter DISTINCT quand c’est possible » donne l’impression de ne s’appliquer qu’à des requêtes vraiment absurdes
  Je me demande si les développeurs de PostgreSQL disposent d’informations pour en juger

  • J’ai l’impression que cela aura un effet assez souvent. DISTINCT est quelque chose que les développeurs peu expérimentés ajoutent souvent pour « corriger » une mauvaise requête, et en général l’une des premières choses qu’on fait quand on commence à améliorer les performances est de réécrire la requête pour le rendre inutile
    Si les améliorations de DISTINCT rendent le système plus robuste face aux mauvaises requêtes, c’est très bénéfique. Cela ne corrigera pas tous les problèmes, mais toute amélioration est bonne à prendre
  • En tant qu’auteur du billet de blog et de cette fonctionnalité, ce cas a effectivement été évoqué sur la mailing list pgsql-hackers
    Je suis d’accord pour dire qu’il est peu probable que cela s’applique souvent, mais l’avantage est que détecter si cela s’applique revenait à quelque chose d’aussi simple que vérifier si un pointeur est NULL
    La détection est très simple et, la plupart du temps, cela ne s’appliquera pas, mais quand c’est applicable, cela peut apporter un gain de performance considérable
  • Le problème, c’est que les ORM ont tendance à produire des requêtes vraiment absurdes, et que les développeurs refusent ensuite de les corriger en écrivant directement du SQL, parce que cela ne leur paraît pas très pur
    Ce n’est sans doute pas un problème extrêmement courant, mais je ne serais pas surpris qu’il apparaisse de temps en temps
  • Dans mon ancien travail, on autorisait les adresses e-mail en double dans la table des utilisateurs pour des raisons legacy, mais on ne voulait pas en ajouter de nouvelles, donc avant de créer un nouvel utilisateur on exécutait la requête select distinct email from users where email = ?
    Je ne pense pas qu’il y ait jamais eu plus de 100 lignes avec la même adresse e-mail. La plupart étaient des utilisateurs de test qu’on aurait pu supprimer, mais je m’égare

• J’aimerais qu’il existe dans PostgreSQL un mode strict pour tester les applications. Un mode qui examine uniquement la requête elle-même et, indépendamment des statistiques, renvoie une erreur si un index absent ferait qu’une requête ne s’améliore asymptotiquement qu’en sa présence
  J’aimerais aussi une commande CREATE INDICES FOR qui crée les index concernés pour les mises à niveau d’application, ainsi qu’un mode de création automatique d’index pour l’usage interactif et le développement
  Plus généralement, le système devrait être conçu de sorte qu’aucune exécution asymptotiquement sous-optimale ne puisse jamais se produire

• Je ne comprends pas pourquoi ils n’implémentent pas les hints

  • Il existe l’extension pg_hint_plan. Le danger des hints, c’est que même s’ils sont corrects au moment où on les écrit, ils peuvent devenir contre-productifs si la taille des tables ou la distribution des données change
    D’après ce dont je me souviens des anciennes discussions sur les hints, il n’y avait pas d’opposition générale tant que cela ne contraignait pas trop fortement le planner et que cela lui permettait de s’adapter aux changements des données sous-jacentes
    Par exemple, au lieu d’indiquer qu’un prédicat donné correspond à 10 lignes, on lui signalerait qu’il existe une corrélation entre deux colonnes
  • Discussion connexe : Why PostgreSQL doesn't have query hints
    https://news.ycombinator.com/item?id=2179433 (60 commentaires, 2011)
    La position officielle sur le wiki PostgreSQL est ici : https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion
    En substance : « nous ne sommes pas intéressés par les hints exactement tels qu’ils sont couramment implémentés dans d’autres bases de données »
    Parmi les problèmes des systèmes de hints existants : ils dégradent la maintenabilité du code applicatif, compliquent les montées de version, encouragent de mauvaises habitudes chez les DBA et ne s’adaptent pas bien aux changements d’échelle des données
    Je ne veux pas vraiment leur reprocher cette position, mais c’est frustrant quand PostgreSQL choisit un plan stupide et qu’on ne peut pas le convaincre de faire un choix raisonnable alors qu’on sait qu’il se trompe

• Un ami, DBA Microsoft pour des entreprises de taille intermédiaire, a dit qu’on ne pouvait pas faire de choses sérieuses avec PostgreSQL. Il aurait même été choqué d’apprendre que PostgreSQL n’avait pas de query planner
  En laissant la moquerie de côté un instant, je me demande s’il y a du vrai dans l’affirmation plus large selon laquelle MSSQL peut gérer des charges à une échelle pour laquelle PostgreSQL ne conviendrait pas. Intuitivement, ça me semble absurde, mais je ne suis pas DBA du tout

  • Il y a un fond de vérité sous cet angle. Si l’on parle de bases de données capables de gérer à peu près tout ce dont on a besoin de façon suffisamment bonne, MSSQL et Oracle ont de fortes chances d’y arriver
    Ils ont historiquement résolu les problèmes en y injectant de l’argent et du matériel — donc encore plus d’argent — jusqu’à ce qu’ils disparaissent. Il y a bien sûr aussi de la technique intelligente, mais au fond ils ont bénéficié de bien plus d’ingénierie pendant bien plus longtemps
    Ils peuvent faire du scale-out horizontal à une échelle plus grande que ce que PostgreSQL peut raisonnablement faire
    Cela dit, PostgreSQL rattrape son retard, et on pourrait aussi dire que MySQL/MariaDB a toujours eu une histoire plutôt correcte sur ce point. Les options de montée en charge horizontale s’améliorent en permanence
    Aujourd’hui, il est devenu plus simple d’exploiter un cluster PostgreSQL de plusieurs téraoctets sur un petit nombre de machines avec un trafic important, tout en plaçant le « big data » dans des bases plus spécialisées. L’ancienne approche qui consistait à tout faire entrer dans MSSQL/Oracle est peut-être un peu datée
  • J’ai beaucoup développé sur MSSQL, et il y a dans PostgreSQL quelques absences de fonctionnalités qui peuvent surprendre
    Ce à quoi ton ami faisait peut-être allusion, c’est au fait que PostgreSQL n’a pas de moyen de mettre en cache ou de figer un plan de requête. PostgreSQL replannifie chaque instruction à moins d’utiliser manuellement des instructions préparées, et encore, cela ne fonctionne qu’au niveau de la connexion
    MSSQL met en cache et réutilise les plans depuis longtemps, ce qui permet au planner de consacrer plus de temps à l’élaboration du plan. Il y a aussi des hints, et on peut figer un plan
    PostgreSQL aurait vraiment besoin de hints. Même si l’optimiseur est excellent, parfois je sais mieux que lui et j’aimerais pouvoir le forcer à m’écouter
    PostgreSQL n’a pas non plus de véritable index clusterisé et toutes les tables sont des heaps. Dans MSSQL, on utilise cela très souvent, en général en définissant la clé primaire comme index clusterisé, si bien que la table elle-même devient l’index et qu’il n’y a pas d’indirection pour les recherches par clé
    Fait intéressant, SQLite est l’inverse : une table y a toujours un index clusterisé, qu’on le crée ou non, tandis que MSSQL permet de choisir entre heap et table organisée par index
  • PostgreSQL a bien un query planner. Cet article tout entier parle justement d’améliorations qui lui sont apportées. Donc soit il y a eu un problème de communication, soit ton ami ne connaît pas du tout PostgreSQL
    Il existe aussi des exemples de très grosses bases PostgreSQL qui fonctionnent bien, donc PostgreSQL peut clairement passer à l’échelle
    Cela dit, SQL Server a des fonctionnalités que PostgreSQL n’a pas, et si elles comptent vraiment, il peut mieux convenir à certains cas d’usage. Au final, ce sont simplement deux bases de données différentes, avec des forces et des faiblesses différentes
  • J’ai utilisé les deux, à la fois pour de l’OLTP et du data warehousing, et les deux sont corrects
    Au départ, j’allais écrire que j’aurais recommandé à mon entreprise de migrer vers PostgreSQL si nous n’avions pas eu des applications fournies par des éditeurs qui exigeaient SQL Server
    Puis j’ai réalisé tout ce qu’il faudrait remplacer de ce que Microsoft inclut : reporting services, integration services, jobs, intégration AD, service broker, etc. notify/listen n’a pas de types de messages
    Nous n’utilisons plus analysis services, mais à l’époque où nous l’utilisions, cela aussi aurait été difficile à remplacer
    C’est ce genre de choses qui retient les gens. Je n’ai aucune idée du temps qu’il faudrait pour remplacer tout cela, et passer un an à remplacer ce qu’on a déjà n’offre pas un bon retour sur investissement
  • Aurora d’AWS semble plutôt bien s’en sortir et vise à être un remplacement drop-in pour PostgreSQL et MySQL

• Je me demande pourquoi cela a été publié chez citusdata plutôt que sur postgresql.org. Je ne sais pas si c’est réservé à des fonctionnalités payantes ou si c’est un ajout open source

  • Parce que l’auteur travaille chez Citus Data et a lui-même écrit une partie de ces optimisations

• À quand l’utilisation d’un index pour accélérer les requêtes IS NOT DISTINCT FROM ? ;)