Après un succès de la troisième bêta en août 2024, le groupe de développement de PostgreSQL a publié la version GA le 26 septembre. Récemment, j’ai écrit un blog sur certaines des principales fonctionnalités de réplication logique que vous verrez dans PostgreSQL 17.
PostgreSQL a grandi considérablement au fil des ans, et à chaque nouvelle version majeure, il devient une base de données plus robuste, fiable et réactive pour les applications enterprise tant critiques que non critiques. La communauté mondiale et dynamique de PostgreSQL contribue à son succès, en vérifiant scrupuleusement et en examinant soigneusement toutes les modifications avant de les ajouter au code source du projet. Il est également encourageant de voir des grands noms de la technologie comme Microsoft, Google, Apple et d’autres investir dans Postgres en développant des compétences interne et en retournant au communauté open source.
Les améliorations apportées à la réplication logique ouvrent la voie pour ajouter le support de PostgreSQL distribué à la fonctionnalité de base. PostgreSQL distribué fait référence à l’implémentation de PostgreSQL dans une architecture distribuée, permettant une meilleure scalabilité, une tolérance aux pannes et une amélioration des performances sur plusieurs nœuds.
Maintenant, sans plus attendre, let’s discute des fonctionnalités de performance de PostgreSQL 17.
Amélioration de la performance des requêtes avec les CTEs Materialized.
Expressions Table Communes (CTEs) dans PostgreSQL sont des ensembles de résultats temporaires qui peuvent être référencés à l’intérieur d’une instruction SELECT, INSERT, UPDATE ou DELETE. Elles améliorent la lisibilité et l’organisation des requêtes complexes et peuvent être récursives, ce qui les rend particulièrement utiles pour les données hiérarchisées. La syntaxe de base d’une requête CTE est la suivante :
WITH cte_names AS
(– QUERY here )
Select * from cte_names;Incluez le mot-clé WITH dans une requête pour créer la CTE ; la requête parente (qui définit l’ensemble de résultats) suit l’expression AS après le nom de la CTE. Après avoir défini la CTE, vous pouvez vous référer à celle-ci par son nom pour référencer l’ensemble de résultats de la CTE et effectuer d’autres opérations sur cet ensemble de résultats dans la même requête.
PostgreSQL 17 continue d’améliorer les performances et les capacités autour des CTEs, y compris les améliorations dans la planification des requêtes et leur exécution. Les anciennes versions de Postgres traitaient les CTEs comme des clôtures d’optimisation, ce qui signifie que le planificateur ne pouvait pas pousser les prédicats vers elles. Cependant, à partir de PostgreSQL 12, vous pouvez définir des plans d’exécution plus efficaces. Vous devez toujours analyser vos requêtes et considérer les plans d’exécution lorsque la performance est critique.
Astuce performance : Si vous serez confronté à la nécessité de référencer le même ensemble de résultats plusieurs fois, créez le CTE en utilisant le mot-clé MATERIALIZED. Lorsque vous créez un CTE matérialisé, Postgres calcule et stocke les résultats de la requête parente. Par la suite, les requêtes subséquentes n’ont pas besoin d’effectuer des calculs complexes plusieurs fois si vous référencez le CTE plusieurs fois.
Extraire les statistiques de colonnes des références CTE, Postgres 17 améliore les CTE matérialisées
Une CTE matérialisée agit essentiellement comme un mur d’optimisation, ce qui signifie que la requête extérieure n’influera pas sur le plan de la sous-requête une fois que ce plan a été choisi. La requête extérieure a la visibilité de l’estimation de la largeur et du nombre de lignes du jeu de résultats de la CTE, donc il convient de propager les statistiques de colonnes de la sous-requête vers l’ordonnanceur pour la requête extérieure. La requête extérieure peut utiliser tous les informations disponibles, ce qui permet la propagation des informations statistiques de colonne vers le plan de la requête extérieure mais pas vers le plan de la CTE.
This bug reported to the community contains a simple test case that can demonstrate the improvement and effect on the query planner as a result of this improvement.
Exemple : Comparaison du comportement de Postgres 16 à celui de Postgres 17
Premièrement, nous créons notre espace de travail dans Postgres 16 et exécutons ANALYZE contre lui ; deux tables et des indexs :
postgres=# create table t1(a int);
CREATE TABLE
postgres=# create table t2(b int);
CREATE TABLE
postgres=# create index my_index on t1 using btree (a);
CREATE INDEX
postgres=# insert into t1 select generate_series(1, 100000) from generate_series(1, 3);
INSERT 0 300000
postgres=# insert into t2 select generate_series(1, 100) from generate_series(1, 10);
INSERT 0 1000
postgres=# analyze t1;
ANALYZE
postgres=# analyze t2;
ANALYZE
Then, we create our materialized CTE:
postgres=# explain analyze with my_cte as materialized (select b from t2) select *
from t1 where t1.a in (select b from my_cte);
The query plan from our Postgres 16 code sample contains:
QUERY PLAN
----------------------------------------------------------------------
Nested Loop (cost=37.92..856.50 rows=2966 width=4) (actual time=0.574..0.722 rows=300 loops=1)
CTE my_cte
-> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=4) (actual time=0.038..0.161 rows=1000 loops=1)
-> HashAggregate (cost=22.50..24.50 rows=200 width=4) (actual time=0.449..0.461 rows=100 loops=1)
Group Key: my_cte.b
Batches: 1 Memory Usage: 40kB
-> CTE Scan on my_cte (cost=0.00..20.00 rows=1000 width=4) (actual time=0.046..0.322 rows=1000 loops=1)
-> Index Only Scan using my_index on t1 (cost=0.42..4.06 rows=3 width=4) (actual time=0.002..0.002 rows=3 loops=1
00)
Index Cond: (a = my_cte.b)
Heap Fetches: 0
Planning Time: 1.242 ms
Execution Time: 1.051 ms
(12 rows)
Comme vous pouvez le voir dans le plan de requête, les statistiques de colonne de 200 lignes provenant de la sous-requête sont erronées, ce qui impact le plan global.
-> HashAggregate (cost=22.50..24.50 rows=200 width=4) (actual time=0.449..0.461 rows=100 loops=1)
Group Key: my_cte.b
Ensuite, nous testons la même configuration et la même requête sur PostgreSQL 17 :
postgres=# explain analyze with my_cte as materialized (select b from t2) select *
from t1 where t1.a in (select b from my_cte);
QUERY PLAN
-------------------------------------------------------------------------------------------------
---------------------------------
Merge Join (cost=42.25..54.29 rows=302 width=4) (actual time=0.627..0.712 rows=300 loops=1)
Merge Cond: (t1.a = my_cte.b)
CTE my_cte
-> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=4) (actual time=0.031..0.134 rows=1000
loops=1)
-> Index Only Scan using my_index on t1 (cost=0.42..7800.42 rows=300000 width=4) (actual tim
e=0.027..0.049 rows=301 loops=1)
Heap Fetches: 0
-> Sort (cost=26.82..27.07 rows=100 width=4) (actual time=0.598..0.604 rows=100 loops=1)
Sort Key: my_cte.b
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=22.50..23.50 rows=100 width=4) (actual time=0.484..0.494 rows=1
00 loops=1)
Group Key: my_cte.b
Batches: 1 Memory Usage: 24kB
-> CTE Scan on my_cte (cost=0.00..20.00 rows=1000 width=4) (actual time=0.033..0
.324 rows=1000 loops=1)
Planning Time: 1.066 ms
Execution Time: 0.946 ms
(15 rows)Comme vous pouvez le voir dans le plan de requête de Postgres 17, les statistiques de colonne de la sous-requête sont correctement propagées à l’ordonnanceur supérieur de la requête externe. Cela aide PostgreSQL à choisir un meilleur plan qui améliore le temps d’exécution de la requête.
C’est une requête simple, mais avec des requêtes plus grandes et plus complexes, cette modification peut entraîner une différence de performance majeure.
Propagation de Pathkeys d’une CTE vers une requête externe
Une autre amélioration intéressante des fonctionnalités de CTE dans Postgres 17 est la propagation de pathkeys de la sous-requête vers la requête externe. Dans PostgreSQL, les pathkeys font partie du processus de planification d’exécution de la requête utilisé principalement pour trier et ordonner les lignes dans les requêtes qui nécessitent des résultats ordonnés, telles que les requêtes avec une clause ORDER BY, ou lorsque le tri est nécessaire pour d’autres opérations telles que les jointures de fusion.
Auparavant, dans Postgres 17, l’ordre de tri de la sous-requête de CTE matérialisée n’était pas partagé avec la requête externe, même si l’ordre de tri était garanti soit par un nœud d’analyse d’index soit par un nœud de tri. Ne pas avoir un ordre de tri garanti permet au planificateur de PostgreSQL de choisir un plan moins optimisé, tandis qu’avoir un ordre de tri garanti rend plus probable le choix d’un plan optimisé.
Avec PostgreSQL 17, si une CTE est matérialisée et possède un ordre de tri précis, l’ordonnanceur peut réutiliser cette information dans la requête externe, améliorant ainsi la performance en évitant le tri redondant ou en permettant des méthodes de jointure plus efficientes. Comme indiqué dans les commentaires de commit de Tom Lane :
« Le code pour remonter les clés de chemin dans la requête externe existe déjà pour les sous-requêtes de type
RTE_SUBQUERY, mais il n’était pas utilisé pour les CTE, peut-être à cause du souci de maintenir une clôture d’optimisation entre la CTE et la requête externe. »
Cette modification simple du code source de Postgres devrait entraîner des améliorations de performance pour les requêtes impliquant des CTE complexes, en particulier celles où le tri ou les jointures fusionnelles peuvent être optimisées en fonction de l’ordre naturel des résultats de la CTE.
Voici un exemple utilisant les données dans la régression de PostgreSQL :
postgres=# CREATE TABLE tenk1 (
postgres(# unique1 int4,
postgres(# unique2 int4,
postgres(# two int4,
postgres(# four int4,
postgres(# ten int4,
postgres(# twenty int4,
postgres(# hundred int4,
postgres(# thousand int4,
postgres(# twothousand int4,
postgres(# fivethous int4,
postgres(# tenthous int4,
postgres(# odd int4,
postgres(# even int4,
postgres(# stringu1 name,
postgres(# stringu2 name,
postgres(# string4 name
postgres(# );
CREATE TABLE
postgres=# CREATE INDEX tenk1_unique1 ON tenk1 USING btree(unique1 int4_ops);
CREATE INDEX
postgres=# \copy tenk1 FROM '~/projects/postgres/src/test/regress/data/tenk.data';
COPY 10000
postgres=# VACUUM ANALYZE tenk1;
VACUUM
Le plan de requête de notre exemple de code de Postgres 16 contient les éléments suivants :
postgres=# explain analyze with x as materialized (select unique1 from tenk1 b order by unique1)
select count(*) from tenk1 a
where unique1 in (select * from x);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
---
Aggregate (cost=764.29..764.30 rows=1 width=8) (actual time=21.592..21.593 rows=1 loops=1)
CTE x
-> Index Only Scan using tenk1_unique1 on tenk1 b (cost=0.29..306.29 rows=10000 width=4) (actual time=0.046..1.415 rows=10000 loops=
1)
Heap Fetches: 0
-> Nested Loop (cost=225.28..445.50 rows=5000 width=0) (actual time=7.545..20.911 rows=10000 loops=1)
-> HashAggregate (cost=225.00..227.00 rows=200 width=4) (actual time=7.535..9.051 rows=10000 loops=1)
Group Key: x.unique1
Batches: 1 Memory Usage: 929kB
-> CTE Scan on x (cost=0.00..200.00 rows=10000 width=4) (actual time=0.070..3.933 rows=10000 loops=1)
-> Index Only Scan using tenk1_unique1 on tenk1 a (cost=0.29..1.08 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=10000)
Index Cond: (unique1 = x.unique1)
Heap Fetches: 0
Planning Time: 0.806 ms
Execution Time: 21.890 ms
(14 rows)
Le plan de requête de notre exemple de code de Postgres 17 contient les éléments suivants :
postgres=# explain analyze with x as materialized (select unique1 from tenk1 b order by unique1)
select count(*) from tenk1 a
where unique1 in (select * from x);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------- Aggregate (coût=987,55..987,56 lignes=1 largeur=8) (temps réel=8,777..8,778 lignes=1 boucles=1)
CTE x
-> Index Only Scan using tenk1_unique1 on tenk1 b (cost=0.29..306.29 rows=10000 width=4) (actual time=0.010..1.095 rows=100
00 loops=1)
Heap Fetches: 0
-> Merge Semi Join (cost=0.31..656.26 rows=10000 width=0) (actual time=0.037..8.024 rows=10000 loops=1)
Merge Cond: (a.unique1 = x.unique1)
-> Index Only Scan using tenk1_unique1 on tenk1 a (cost=0.29..306.29 rows=10000 width=4) (actual time=0.013..1.262 rows
=10000 loops=1)
Heap Fetches: 0
-> CTE Scan on x (cost=0.00..200.00 rows=10000 width=4) (actual time=0.016..3.678 rows=10000 loops=1)
Planning Time: 0.800 ms
Execution Time: 8.899 ms
(11 rows)Les plans de requête dans Postgres 16 et Postgres 17 sont significativement différents à cause de cette amélioration de la version 17. Voici un petit exemple ; vous pouvez voir que les gains de performance seront importants pour les requêtes plus grandes. Veuillez noter que cette amélioration n’est efficace que si la sous-requête de la variable de calculée (CTE) contient une clause ORDER BY.
Scans d’index B-Tree rapides pour les expressions d’opérations à valeur scalaire
Dans PostgreSQL, ScalarArrayOpExpr est un type de nœud dans le plan d’exécution qui gère les requêtes impliquant des opérations telles que IN ou ANY avec des tableaux ou des listes de valeurs. Il est particulièrement utile pour les requêtes où vous comparez une colonne à un ensemble de valeurs, par exemple : SELECT * FROM table WHERE column = ANY(ARRAY[1, 2, 3]);.
ScalarArrayOpExpr permet à PostgreSQL d’optimiser les requêtes impliquant plusieurs comparaisons utilisant IN ou ANY. PostgreSQL 17 a introduit de nouvelles améliorations de performance pour rendre ces opérations encore plus rapides.
Dans PostgreSQL 17, des améliorations significatives ont été faites aux scans d’index B-Tree, optimisant ainsi le performance, en particulier pour les requêtes avec de grands listes IN ou des conditions ANY. Ces améliorations réduisent le nombre de scans d’index effectués par le système, diminuant ainsi la contention en CPU et sur les pages de tampon, ce qui entraîne une exécution de requête plus rapide.
L’une des améliorations clés se situe dans le traitement des expressions d’opérations sur les tableaux scalaires (SAOP), ce qui permet un parcours plus efficient des index de B-tree, en particulier pour les requêtes multidimensionnelles. Par exemple, lorsque vous avez plusieurs colonnes d’index (chaque colonne avec sa propre liste IN), PostgreSQL 17 peut désormais traiter ces opérations de manière plus efficiente en une seule lecture d’index, plutôt que de plusieurs lectures comme dans les anciennes versions. Cela peut conduire à une augmentation de la performance de 20 à 30 % dans les charges de travail avec des limites CPU où les accès à la page étaient auparavant un goulot d’étranglement.
De plus, PostgreSQL 17 introduit une meilleure gestion des verrous internes, améliorant davantage la performance pour les charges de travail à haute concurrence, en particulier lorsque vous effectuez plusieurs lectures dans plusieurs dimensions dans un index de B-tree.
Nous pouvons démontrer cela avec un exemple simple. Nous utiliserons la même table tenk1 et les mêmes données que nous avons utilisées dans l’exemple précédent de la suite de tests de régression de Postgres.
Notre exemple, lancé sous Postgres 16 :
CREATE TABLE tenk1 (
postgres(# unique1 int4,
postgres(# unique2 int4,
postgres(# two int4,
postgres(# four int4,
postgres(# ten int4,
postgres(# twenty int4,
postgres(# hundred int4,
postgres(# thousand int4,
postgres(# twothousand int4,
postgres(# fivethous int4,
postgres(# tenthous int4,
postgres(# odd int4,
postgres(# even int4,
postgres(# stringu1 name,
postgres(# stringu2 name,
postgres(# string4 name
postgres(# );
CREATE TABLE
postgres=# CREATE INDEX tenk1_unique1 ON tenk1 USING btree(unique1 int4_ops);
CREATE INDEX
postgres=# \copy tenk1 FROM '~/projects/postgres/src/test/regress/data/tenk.data';
COPY 10000
postgres=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tenk1 WHERE unique1 IN (1, 2, 3);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=14.20..330.12 rows=176 width=244) (actual time=0.138..0.153 rows=3 loops=1)
Recheck Cond: (unique1 = ANY ('{1,2,3}'::integer[]))
Heap Blocks: exact=3
Buffers: shared hit=9
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..14.16 rows=176 width=0) (actual time=0.102..0.102 rows=3 loops=1)
Index Cond: (unique1 = ANY ('{1,2,3}'::integer[]))
Buffers: shared hit=6
Planning:
Buffers: shared hit=2
Planning Time: 0.900 ms
Execution Time: 0.242 ms
(11 rows)
postgres=# SELECT idx_scan, idx_tup_fetch FROM pg_stat_user_tables WHERE relname = 'tenk1';
idx_scan | idx_tup_fetch
----------+---------------
3 | 3
(1 row)
Dans la requête précédente, vous pouvez voir que le taux de succès du buffer partagé pour la requête IN était de 9 et qu’il fallait 3 lectures d’index pour obtenir les résultats de l’index scan. Dans PostgreSQL, le terme « shared hit » se rapporte à un type spécifique de succès du cache lié à la gestion des buffers. Un « shared hit » se produit lorsque PostgreSQL accède à un bloc ou page de données du pool de buffers partagés plutôt que depuis le disque, améliorant ainsi la performance des requêtes.
Le même exemple, cette fois-ci lancé sous Postgres 17 :
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tenk1 WHERE unique1 IN (1, 2, 3);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=12.88..24.08 rows=3 width=244) (actual time=0.043..0.054 rows=3 loops=1)
Recheck Cond: (unique1 = ANY ('{1,2,3}'::integer[]))
Heap Blocks: exact=3
Buffers: shared hit=5
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..12.88 rows=3 width=0) (actual time=0.026..0.026 rows=3 loops=1)
Index Cond: (unique1 = ANY ('{1,2,3}'::integer[]))
Buffers: shared hit=2
Planning:
Buffers: shared hit=59
Planning Time: 0.479 ms
Execution Time: 0.116 ms
(11 rows)
postgres=# SELECT idx_scan, idx_tup_fetch FROM pg_stat_user_tables WHERE relname = 'tenk1';
idx_scan | idx_tup_fetch
----------+---------------
1 | 3
(1 row)
Comme vous pouvez le voir, avec Postgres 17, la réussite partagée de buffer est réduite à 5, et surtout, il n’effectue qu’une seule lecture d’index (au lieu de 3 lectures dans le cas de Postgres 16). Avec cette amélioration de Postgres 17, les performances des opérations scalaires sur les tableaux sont grandement améliorées, et Postgres peut choisir des plans de requête optimisés de meilleure manière.
Gestion des slots de réplication logique et des abonnements pendant la mise à niveau
La gestion des slots de réplication logique et la migration des dépendances d’abonnement pendant le processus de mise à niveau majeure est une autre fonctionnalité de réplication logique ajoutée à PostgreSQL 17. Veuillez noter que cette fonctionnalité sera utile uniquement lors de la mise à niveau de PostgreSQL 17 vers d’autres versions ultérieures, elle n’est pas supportée pour une mise à niveau antérieure à Postgres 17. Les slots de réplication et les origines de réplication sont générés lors de la création d’un environnement de réplication logique. Cependant, cette information est spécifique à l’itérateur afin d’enregistrer l’état de la réplication, de l’application et de l’état du transfert de WAL, de sorte qu’elles ne sont pas mises à jour pendant le processus de mise à niveau. Une fois que l’itérateur publié est mis à niveau, l’utilisateur doit reconstruire manuellement ces objets.
Le processus pg_upgrade est amélioré dans PostgreSQL 17 pour référencer et reconstruire ces objets internes ; cette fonctionnalité permet à la réplication de reprendre automatiquement lors de la mise à niveau d’un noeud doté de réplication logique. Précédemment, lors de la mise à niveau d’une version majeure, les utilisateurs devaient supprimer les slots de réplication logique, les obligeant à synchroniser les données avec les abonnés après la mise à niveau. Cela ajoutait de la complexité et augmentait la période de non-service pendant les mises à niveau.
Vous devez suivre les étapes suivantes lors de la mise à niveau du cluster de publication :
- Assurez-vous d’avoir désactivé temporairement tout abonnement au publisher en effectuant une
ALTER SUBSCRIPTION….DISABLE. Ces abonnements sont activés après que le processus de mise à niveau soit terminé. - Définissez le
wal_leveldu nouveau cluster surlogical. - Le
max_replication_slotsdu nouveau cluster doit être défini sur une valeur supérieure ou égale à celle des slots de réplication sur l’ancien cluster. - Les plugins d’extraction utilisés par les slots doivent être installés dans le nouveau cluster.
- Toutes les modifications de l’ancien cluster sont déjà répliquées sur le cluster cible avant la mise à niveau.
- Tous les slots de l’ancien cluster doivent être utilisables ; vous pouvez vous assurer de cela en vérifiant les colonnes en conflit dans la vue pg_replication_slots. Le conflit doit être faux pour tous les slots de l’ancien cluster.
- Aucun des slots du nouveau cluster ne doit avoir la valeur
falsedans la colonneTemporaryde la vuepg_replication_slots. Il ne doit pas y avoir de slots de réplication logique permanents dans le nouveau cluster.
Le processus pg_upgrade de mise à niveau des slots de réplication génère une erreur si l’une ou l’autre des préalables ci-dessus n’est pas remplie.
Conclusion
Avec PostgreSQL 17, l’attention de la communauté est toujours axée sur améliorer les performances, la scalabilité, la sécurité et rendre PostgreSQL prête aux besoins des entreprises. Postgres 17 améliore également l’expérience des développeurs en ajoutant de nouvelles fonctionnalités pour la compatibilité et en rendant les fonctionnalités existantes plus puissantes et robustes.
Au-delà de la version 17, PostgreSQL continuera de s’améliorer et de gagner en performance pour répondre aux besoins d’applications entreprises exigeantes de bases de données plus scalables. La scalabilité (horizontale et verticale) a été améliorée au fil des ans, mais il y a certes de la place pour l’amélioration de la capacité horizontale en ajoutant des fonctionnalités de sharding à PostgreSQL. Nous verrons davantage d’améliorations dans la réplication logique, avec plus à venir sur le domaine de la réplication DDL ou de la réplication des objets manquants (comme les séquences) et un meilleur gestionnaire de nœuds. La communauté reconnaît également la nécessité d’améliorer la compatibilité de PostgreSQL, d’où les améliorations du commande MERGE dans Postgres 17, et des plans pour d’autres fonctionnalités de compatibilité postérieures à Postgres 17.
Source:
https://dzone.com/articles/postgresql-17-a-major-step-forward-in-performance