# PostgreSQL : Optimisations SQL > Axes d’optimisation, écriture des requêtes, accès aux données et transactions. --- Index LLMS : [llms.txt](/fr/llms.txt) --- # PostgreSQL : Optimisations SQL {#devpg-ch4} ### Introduction {#devpg-4-0-1} L’optimisation doit porter sur : - Le matériel - serveur, distribution, kernel, stockage, réseau… - Le moteur de la base : - postgresql.conf & co - l’organisation des fichiers de PostgreSQL - L’application - schéma, requêtes, vues… Les bases de données sont des systèmes très complexes. Afin d’en tirer toutes les performances possibles, l’optimisation doit porter sur un très grand nombre de composants différents : le serveur qui héberge la base de données, et de manière générale tout l’environnement matériel, les processus faisant fonctionner la base de données, les fichiers et disques servant à son stockage, le partitionnement, mais aussi, et surtout, l’application elle-même. C’est sur ce dernier point que les gains sont habituellement les plus importants. (Les autres sont traités entre autres dans le module de formation J1 1 .) Ce module se focalise sur ce dernier point. Il n’aborde pas les plans d’exécution à proprement parler, ceux-ci étant traités ailleurs. 1 https://dali.bo/j1_html **Figure 4-157-f01.** Figure 4\-157\-f01 \- Diapositive d’ouverture du chapitre 4\. ![Diapositive d’ouverture du chapitre 4\.](/images/devpg/manual/p157-image-01.png) ## Axes d’optimisation {#devpg-4-1} « 80% des effets sont produits par 20% des causes. » (Principe de Pareto) - Il est illusoire d’optimiser une application sans connaître les sources de ralentissement - Cibler l’optimisation : - trouver ces « 20% » - ne pas micro-optimiser ce qui n’a pas d’influence Le principe de Pareto et la loi de Pareto sont des constats empiriques. On peut définir mathématiquement une distribution vérifiant le principe de Pareto 2 . Le pourcentage de la population dont la richesse est supérieure à une valeur x est proportionnel **Équation 4-158-q01.** Formule extraite du PDF\, page 158\. $$ A / x^{\alpha} $$ Vilfredo Pareto a , économiste du XIXe siècle a https://fr.wikipedia.org/wiki/Vilfredo_Pareto De nombreux phénomènes suivent cette distribution. Dans le cadre de PostgreSQL, cela se vérifie souvent. Une poignée de requêtes peut être responsable de l’essentiel du ralentissement. Une table mal conçue peut être responsable de la majorité de vos problèmes. Le temps de développement n’était pas infini, il ne sert à rien de passer beaucoup de temps à optimiser chaque paramètre sans avoir identifié les principaux consommateurs de ressource. ### Quelles requêtes optimiser? {#devpg-4-1-1} Seul un certain nombre de requêtes sont critiques - Identification (outil de profiling) - à optimiser prioritairement - Différencier - longues en temps cumulé = coûteuses en ressources serveur - longues et interactives = mauvais ressenti des utilisateurs Toutes les requêtes ne sont pas critiques, seul un certain nombre d’entre elles méritent une attention particulière. Il y a deux façon de déterminer les requêtes qui nécessitent d’être travaillées. La première dépend du ressenti utilisateur : un utilisateur devant son écran est notoirement impatient. Il faudra donc en priorité traiter les requêtes interactives. Certaines auront déjà d’excellents temps de réponse, d’autres pourront être améliorées. 2 https://fr.wikipedia.org/wiki/Principe_de_Pareto L’autre méthode pour déterminer les requêtes à optimiser consiste à utiliser les outils de profiling habituels, dont nous allons voir quelques exemples. Ces outils permettent de déterminer les requêtes les plus fréquemment exécutées, et d’établir un classement des requêtes qui ont nécessité le plus de temps cumulé à leur exécution (voir l’onglet Time consuming queries (N) d’un rapport pgBadger). Les requêtes les plus fréquemment exécutées méritent également qu’on leur porte attention. Même si leur temps d’exécution cumulé est acceptable, leur optimisation peut permettre d’économiser quelques ressources du serveur, et une dérive peut avoir vite de gros impacts. ### Recherche des axes d’optimisation {#devpg-4-1-2} Quelques profilers : - pgBadger - https://pgbadger.darold.net - pg_stat_statements : - https://dali.bo/x2_html#pg_stat_statements - PoWA 3 - https://powa.readthedocs.io/en/latest/ Trois outils courants permettent d’identifier rapidement les requêtes les plus consommatrices sur un serveur. Les outils pour PostgreSQL ont le fonctionnement suivant : pgBadger : pgBadger est un analyseur de fichiers de traces PostgreSQL. Il nécessite de tracer dans les journaux applicatifs de PostgreSQL toutes les requêtes et leur durée. L’outil sait repérer des requêtes identiques avec des paramètres différents. Il les analyse et retourne les requêtes les plus fréquemment exécutées, les plus gourmandes unitairement, les plus gourmandes en temps cumulé (somme des temps unitaires). Pour plus de détails, voir https://dali.bo/h1_html#pgbadger. pg_stat_statements : L’extension pg_stat_statements 4 est livrée avec PostgreSQL. Elle trace pour chaque ordre (même SQL, paramètres différents) exécuté sur l’instance son nombre d’exécutions, sa durée cumulée, et un certain nombre d’autres statistiques très utiles. Si elle est présente, la requête suivante permet de déterminer les requêtes dont les temps d’exécution cumulés sont les plus importants : **Exemple 4-159-e01.** Exemple ou sortie extraite du PDF\, page 159\. ```text SELECT r.rolname, d.datname, s.calls, s.total_exec_time, ``` s.total_exec_time / s.calls AS avg_time, s.query **Exemple 4-159-e02.** Exemple ou sortie extraite du PDF\, page 159\. ```text FROM pg_stat_statements s JOIN pg_roles r ON (s.userid=r.oid) JOIN pg_database d ON (s.dbid = d.oid) ORDER BY s.total_exec_time DESC ``` LIMIT 10 ; 4 https://dali.bo/x2_html#pg_stat_statements Et la requête suivante permet de déterminer les requêtes les plus fréquemment appelées : **Exemple 4-160-e01.** Exemple ou sortie extraite du PDF\, page 160\. ```text SELECT r.rolname, d.datname, s.calls, s.total_exec_time, ``` s.total_exec_time / s.calls AS avg_time, s.query **Exemple 4-160-e02.** Exemple ou sortie extraite du PDF\, page 160\. ```text FROM pg_stat_statements s JOIN pg_roles r ON (s.userid=r.oid) JOIN pg_database d ON (s.dbid = d.oid) ORDER BY s.calls DESC ``` LIMIT 10; Pour plus de détails sur les métriques relevées, voir https://dali.bo/h2_html#pg_stat_statements, et pour l’installation et des exemples de requêtes, voir https://dali.bo/x2_html#pg_stat_statements, ou encore la documentation officielle 5 PoWA : PoWA est une extension disponible dans les dépôts du PGDG, qui s’appuie sur pg_stat_statements pour historiser l’activité du serveur. Une interface web permet ensuite de visualiser l’activité ainsi historisée et repérer les requêtes problématiques avec les fonctionnalités de drill-down de l’interface. 5 https://docs.postgresql.fr/current/pgstatstatements.html ## SQL et requêtes {#devpg-4-2} Le SQL : - est un langage déclaratif : - on décrit le résultat, pas la façon de l’obtenir - c’est le travail de la base de déterminer le traitement à effectuer - décrit un traitement ensembliste : - ≠ traitement procédural - « on effectue des opérations sur des relations pour obtenir des relations » - est normalisé Le langage SQL 6 a été normalisé par l’ANSI en 1986 et est devenu une norme ISO internationale en 1987. Il s’agit de la norme ISO 9075. Elle a subi plusieurs évolutions dans le but d’ajouter des fonctionnalités correspondant aux attentes de l’industrie logicielle. Parmi ces améliorations, notons l’intégration de quelques fonctionnalités objet pour le modèle relationnel-objet. La dernière version stable de la norme est SQL :2023 7 (juin 2023). ### Opérateurs relationnels {#devpg-4-2-1} Les opérateurs purement relationnels : - Projection = SELECT - choix des colonnes - Sélection = WHERE - choix des enregistrements - Jointure = FROM/JOIN - choix des tables - Bref : tout ce qui détermine sur quelles données on travaille Tous ces opérateurs sont optimisables : il y a 40 ans de théorie mathématique développée afin de permettre l’optimisation de ces traitements. L’optimiseur fera un excellent travail sur ces opérations, et les organisera de façon efficace. Par exemple : a JOIN b JOIN c WHERE c.col=constante sera très probablement réordonné en c JOIN b JOIN a WHERE c.col=constante ou c JOIN a JOIN b WHERE c.col=constante . Le moteur se débrouillera aussi pour choisir le meilleur algorithme de jointure pour chacune, suivant les volumétries ramenées. 6 https://fr.wikipedia.org/wiki/Structured_Query_Language 7 https://en.wikipedia.org/wiki/SQL:2023 ### Opérateurs non-relationnels {#devpg-4-2-2} Les autres opérateurs sont non-relationnels : - ORDER BY - GROUP BY/DISTINCT - HAVING - sous-requête, vue - fonction (classique, d’agrégat, analytique) - jointure externe Ceux-ci sont plus difficilement optimisables : ils introduisent par exemple des contraintes d’ordre dans l’exécution : **Exemple 4-162-e01.** Exemple ou sortie extraite du PDF\, page 162\. ```text SELECT * FROM table1 WHERE montant > ( SELECT avg(montant) FROM table1 WHERE departement='44' ); ``` On doit exécuter la sous-requête avant la requête. Les jointures externes sont relationnelles, mais posent tout de même des problèmes et doivent être traitées prudemment. **Exemple 4-162-e02.** Exemple ou sortie extraite du PDF\, page 162\. ```text SELECT * FROM t1 LEFT JOIN t2 on (t1.t2id=t2.id) JOIN t3 on (t1.t3id=t3.id) ; ``` Il faut faire les jointures dans l’ordre indiqué : joindre t1 à t3 puis le résultat à t2 pourrait ne pas amener le même résultat (un LEFT JOIN peut produire des NULL ). Il est donc préférable de toujours mettre les jointures externes en fin de requête, sauf besoin précis : on laisse bien plus de liberté à l’optimiseur. Le mot clé DISTINCT ne doit être utilisé qu’en dernière extrémité. On le rencontre très fréquemment dans des requêtes mal écrites qui produisent des doublons, afin de corriger le résultat - souvent en passant par un tri de l’ensemble du résultat, ce qui est coûteux. ### Données utiles {#devpg-4-2-3} Le volume de données récupéré a un impact sur les performances. - N’accéder qu’aux tables nécessaires - N’accéder qu’aux colonnes nécessaires - viser Index Only Scan - se méfier : stockage TOAST - Plus le volume de données à traiter est élevé, plus les opérations seront lentes : - tris et Jointures - éventuellement stockage temporaire sur disque pour certains algorithmes Éviter les SELECT * : C’est une bonne pratique, car la requête peut changer de résultat suite à un changement de schéma, ce qui risque d’entraîner des conséquences sur le reste du code. Ne récupérer que les colonnes utilisées : Dans beaucoup de cas, PostgreSQL sait repérer des colonnes qui figurent dans la requête mais sont finalement inutiles. Mais il n’est pas parfait. Surtout, il ne pourra pas repérer que vous n’avez pas réellement besoin d’une colonne issue d’une requête. En précisant uniquement les colonnes nécessaires, le moteur peut parfois utiliser des parcours plus simples, notamment des Index Only Scans. Il peut aussi éviter de lire les colonnes à gros contenu qui sont généralement déportés dans la partie TOAST 8 d’une table (des fichiers séparés de la table principale pour certains grands champs, transparents à l’utilisation mais dont l’accès n’est pas gratuit). Éviter les jointures sur des tables inutiles : Il n’y a que peu de cas où l’optimiseur peut supprimer de lui-même l’accès à une table inutile. Notamment, PostgreSQL le fait dans le cas d’un LEFT JOIN sur une table inutilisée dans le SELECT , au travers d’une clé étrangère, car on peut garantir que cette table est effectivement inutile. ### Limiter le nombre de requêtes {#devpg-4-2-4} SQL : langage ensembliste - Ne pas faire de traitement unitaire par enregistrement - Utiliser les jointures, ne pas accéder à chaque table une par une - Une seule requête, parcours de curseur - Fréquent avec les ORM Les bases de données relationnelles sont conçues pour manipuler des relations, pas des enregistrements unitaires. Le langage SQL (et même les autres langages relationnels qui ont existé comme QUEL, SEQUEL) est conçu pour permettre la manipulation d’un gros volume de données, et la mise en correspondance (jointure) d’informations. Une base de données relationnelle n’est pas une simple couche de persistance. Le fait de récupérer en une seule opération l’ensemble des informations pertinentes est aussi, indépendamment du langage, un gain de performance énorme, car il permet de s’affranchir en grande partie des latences de communication entre la base et l’application. Préparons un jeu de test : **Exemple 4-163-e01.** Exemple ou sortie extraite du PDF\, page 163\. ```text CREATE TABLE test (id int, valeur varchar); INSERT INTO test SELECT i,chr(i%94+32) FROM generate_series (1,1000000) g(i); ALTER TABLE test ADD PRIMARY KEY (id); VACUUM ANALYZE test ; ``` 8 [https://dali.bo/m4_html#mécanisme-toast](https://dali.bo/m4_html#m%C3%A9canisme-toast) Le script perl génère 1000 ordres pour récupérer des enregistrements un par un, avec une requête préparée pour être dans le cas le plus efficace : **Exemple 4-164-e01.** Exemple ou sortie extraite du PDF\, page 164\. ```text #!/usr/bin/perl -w print "PREPARE ps (int) AS SELECT * FROM test WHERE id=\$1;\n"; for (my $i=0;$i<=1000;$i++) { print "EXECUTE ps($i);\n"; } ``` Exécutons ce code : **Exemple 4-164-e02.** Exemple ou sortie extraite du PDF\, page 164\. ```text time perl demo.pl | psql > /dev/null ``` **Exemple 4-164-e03.** Exemple ou sortie extraite du PDF\, page 164\. ```text real 0m44,476s user 0m0,137s sys 0m0,040s ``` La durée totale de ces 1000 requêtes dépend fortement de la distance au serveur de base de données. Si le serveur est sur le même sous-réseau, on peut descendre à une seconde. Noter que l’établissement de la connexion au serveur n’a lieu qu’une fois. Voici maintenant la même chose, en un seul ordre SQL, avec la même volumétrie en retour : **Exemple 4-164-e04.** Exemple ou sortie extraite du PDF\, page 164\. ```text time psql -c "SELECT * FROM test WHERE id BETWEEN 0 AND 1000" > /dev/null ``` **Exemple 4-164-e05.** Exemple ou sortie extraite du PDF\, page 164\. ```text real 0m0,129s user 0m0,063s sys 0m0,018s ``` Les allers-retours au serveur sont donc très coûteux dès qu’ils se cumulent. Le problème se rencontre assez fréquemment avec des ORM. La plupart d’entre eux fournissent un moyen de traverser des liens entre objets. Par exemple, si une commande est liée à plusieurs articles, un ORM permettra d’écrire un code similaire à celui-ci (exemple en Java avec Hibernate) : **Exemple 4-164-e06.** Exemple ou sortie extraite du PDF\, page 164\. ```text List commandes = sess.createCriteria(Commande.class); ``` **Exemple 4-164-e07.** Exemple ou sortie extraite du PDF\, page 164\. ```text for(Commande cmd : commandes) { // Un traitement utilisant les produits // Génère une requête par commande !! System.out.println(cmd.getProduits()); } ``` Tel quel, ce code générera N+1 requête, N étant le nombre de commandes. En effet, pour chaque accès à l’attribut “produits”, l’ORM générera une requête pour récupérer les produits correspondants à la commande. Le SQL généré sera alors similaire à celui-ci : **Exemple 4-164-e08.** Exemple ou sortie extraite du PDF\, page 164\. ```text SELECT * FROM commande; SELECT * from produits where commande_id = 1; SELECT * from produits where commande_id = 2; SELECT * from produits where commande_id = 3; SELECT * from produits where commande_id = 4; ``` **Exemple 4-165-e01.** Exemple ou sortie extraite du PDF\, page 165\. ```text SELECT * from produits where commande_id = 5; SELECT * from produits where commande_id = 6; ... ``` La plupart des ORM proposent des options pour personnaliser la stratégie d’accès aux b collections. Il est extrêmement important de connaître celles-ci afin de permettre à l’ORM de générer des requêtes optimales. Par exemple, dans le cas précédent, nous savons que tous les produits de toutes les commandes seront utilisés. Nous pouvons donc informer l’ORM de ce fait : **Exemple 4-165-e02.** Exemple ou sortie extraite du PDF\, page 165\. ```text List commandes = sess.createCriteria(Commande.class) ``` .setFetchMode('produits', FetchMode.EAGER); **Exemple 4-165-e03.** Exemple ou sortie extraite du PDF\, page 165\. ```text for(Commande cmd : commandes) { // Un traitement utilisant les produits System.out.println(cmd.getProduits()); } ``` Ceci générera une seule et unique requête du type : **Exemple 4-165-e04.** Exemple ou sortie extraite du PDF\, page 165\. ```text SELECT * FROM commandes LEFT JOIN produits ON commandes.id = produits.commande_id; ``` ### Sous-requêtes dans un IN {#devpg-4-2-5} Un Semi Join peut être très efficace (il ne lit pas tout) **Exemple 4-165-e05.** Exemple ou sortie extraite du PDF\, page 165\. ```text SELECT * FROM t1 WHERE val1 IN ( SELECT val2 … ) ``` - Sinon attention s’il y a beaucoup de valeurs dans la sous-requête! - dédoublonner : **Exemple 4-165-e06.** Exemple ou sortie extraite du PDF\, page 165\. ```text SELECT * FROM t1 WHERE val1 IN ( SELECT DISTINCT val2 … ) ``` - surtout : réécriture avec EXISTS (si index disponible) De la même manière que pour la clause EXISTS , un des intérêts du IN est de savoir quand il n’y a pas besoin de lire toute la sous-requête, comme ici, où seuls 5 blocs de la grande table lots sont lus, qui contiennent déjà toutes les valeurs possibles de transporteur_id : **Exemple 4-165-e07.** Exemple ou sortie extraite du PDF\, page 165\. ```text EXPLAIN (ANALYZE,BUFFERS) SELECT nom FROM transporteurs WHERE transporteur_id IN ( ``` **Exemple 4-166-e01.** Exemple ou sortie extraite du PDF\, page 166\. ```text SELECT transporteur_id FROM lots WHERE date_depot IS NOT NULL ) ; ``` **Exemple 4-166-e02.** Exemple ou sortie extraite du PDF\, page 166\. ```text QUERY PLAN ------------------------------------------------------------------------------- Nested Loop Semi Join (cost=0.00..19478.49 rows=5 width=12) (actual time=0.032..0.042 rows=5.00 loops=1) Join Filter: (transporteurs.transporteur_id = lots.transporteur_id) Rows Removed by Join Filter: 13 Buffers: shared hit=5 read=1 -> Seq Scan on transporteurs (cost=0.00..1.05 rows=5 width=20) (actual time=0.004..0.005 rows=5.00 loops=1) Buffers: shared hit=1 -> Seq Scan on lots (cost=0.00..19476.04 rows=1006704 width=8) (actual time=0.005..0.006 rows=4.00 loops=5) Filter: (date_depot IS NOT NULL) Buffers: shared hit=4 read=1 Planning: Buffers: shared hit=173 read=1 ``` Planning Time: 0.664 ms **Exemple 4-166-e03.** Exemple ou sortie extraite du PDF\, page 166\. ```text Execution Time: 0.069 ms ``` Mais la requête dans le IN peut être arbitrairement complexe, l’optimisation peut échouer et PostgreSQL peut basculer sur une forme de jointure plus lourde avec un regroupement des valeurs de la sous-requête : **Exemple 4-166-e04.** Exemple ou sortie extraite du PDF\, page 166\. ```text EXPLAIN (ANALYZE,COSTS OFF) SELECT nom FROM transporteurs WHERE transporteur_id IN ( SELECT transporteur_id + 0 /* modification du critère */ FROM lots WHERE date_depot IS NOT NULL ) ; ``` **Exemple 4-166-e05.** Exemple ou sortie extraite du PDF\, page 166\. ```text QUERY PLAN ------------------------------------------------------------------------------- Hash Join (actual time=139.280..139.283 rows=5.00 loops=1) Hash Cond: (transporteurs.transporteur_id = (lots.transporteur_id + 0)) -> Seq Scan on transporteurs (actual time=0.010..0.011 rows=5.00 loops=1) -> Hash (actual time=139.265..139.265 rows=5 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> HashAggregate (actual time=139.259..139.261 rows=5.00 loops=1) Group Key: (lots.transporteur_id + 0) Batches: 1 Memory Usage: 24kB -> Seq Scan on lots (actual time=0.010..70.184 rows=1006704.00 loops=1) Filter: (date_depot IS NOT NULL) ``` Planning Time: 0.201 ms **Exemple 4-166-e06.** Exemple ou sortie extraite du PDF\, page 166\. ```text Execution Time: 139.325 ms ``` Le dédoublonnage explicite au sein même de la sous-requête est alors parfois une bonne idée, même s’il vient forcément avec un certain coût : **Exemple 4-166-e07.** Exemple ou sortie extraite du PDF\, page 166\. ```text EXPLAIN (ANALYZE,BUFFERS) SELECT nom ``` **Exemple 4-167-e01.** Exemple ou sortie extraite du PDF\, page 167\. ```text FROM transporteurs WHERE transporteur_id IN ( SELECT DISTINCT transporteur_id + 0 FROM lots WHERE date_depot IS NOT NULL ) ; ``` **Exemple 4-167-e02.** Exemple ou sortie extraite du PDF\, page 167\. ```text QUERY PLAN ------------------------------------------------------------------------------- Hash Join (actual time=46.385..48.943 rows=5.00 loops=1) Hash Cond: (transporteurs.transporteur_id = ((lots.transporteur_id + 0))) -> Seq Scan on transporteurs (actual time=0.005..0.006 rows=5.00 loops=1) -> Hash (actual time=46.375..48.931 rows=5 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Unique (actual time=46.367..48.925 rows=5.00 loops=1) -> Sort (actual time=46.366..48.922 rows=15.00 loops=1) Sort Key: ((lots.transporteur_id + 0)) Sort Method: quicksort Memory: 25kB -> Gather (actual time=46.306..48.914 rows=15.00 loops=1) Workers Planned: 2 Workers Launched: 2 -> HashAggregate (actual time=44.707..44.708 rows=5.00 ``` loops=3) **Exemple 4-167-e03.** Exemple ou sortie extraite du PDF\, page 167\. ```text Group Key: (lots.transporteur_id + 0) Batches: 1 Memory Usage: 24kB Worker 0: Batches: 1 Memory Usage: 24kB Worker 1: Batches: 1 Memory Usage: 24kB -> Parallel Seq Scan on lots (actual time=0.008..23.330 rows=335568.00 loops=3) Filter: (date_depot IS NOT NULL) ``` Planning Time: 0.096 ms **Exemple 4-167-e04.** Exemple ou sortie extraite du PDF\, page 167\. ```text Execution Time: 48.979 ms ``` Mais on ne retrouve pas les performances de la première version. Une autre possibilité est de réécrire la requête avec EXISTS mais cela n’a d’intérêt ici que si on peut indexer le champ calculé; auquel cas les requêtes ci-dessus peuvent redevenir efficaces. **Exemple 4-167-e05.** Exemple ou sortie extraite du PDF\, page 167\. ```text SELECT nom FROM transporteurs t WHERE EXISTS ( SELECT 'ok' FROM lots l WHERE date_depot IS NOT NULL AND l.transporteur_id+0 /* à indexer */ = t.transporteur_id ) ; ``` ### Sous-requêtes liées {#devpg-4-2-6} À éviter : **Exemple 4-168-e01.** Exemple ou sortie extraite du PDF\, page 168\. ```text SELECT a,b FROM t1 WHERE val IN ( SELECT f(b) ) ``` - un appel de fonction ou sous-requête par ligne ! - est-ce voulu? - transformer en clause WHERE - penser à la clause LATERAL Dans l’exemple ci-dessus, le résultat de la sous-requête dépend de la valeur b de chaque ligne de t1 . On a donc autant d’appels, ce qui peut être une catastrophe. L’expérience montre que ce n’est parfois pas vraiment voulu… Selon la complexité de la sous-requête, elle peut être réécrite en une simple clause WHERE . Il faut connaître aussi la clause LATERAL 9 dédiée à ce genre de chose et qui a au moins le mérite d’être lisible et explicite. ### Sous-requêtes : équivalences IN/EXISTS/LEFT JOIN {#devpg-4-2-7} Ces sous-requêtes sont strictement équivalentes (Semi-join) : **Exemple 4-168-e02.** Exemple ou sortie extraite du PDF\, page 168\. ```text SELECT * FROM t1 WHERE fk IN ( SELECT pk FROM t2 WHERE … ) ``` **Exemple 4-168-e03.** Exemple ou sortie extraite du PDF\, page 168\. ```text SELECT * FROM t1 WHERE EXISTS ( SELECT 1 FROM t2 WHERE t2.pk = t1.fk AND … ) ``` **Exemple 4-168-e04.** Exemple ou sortie extraite du PDF\, page 168\. ```text SELECT t1.* FROM t1 LEFT JOIN t2 ON (t1.fk=t2.pk) WHERE t2.id IS NULL ``` (Et Anti-join pour les variantes avec NOT ) - Attention à NOT IN : préférer NOT EXISTS Les seules sous-requêtes sans danger sont celles qui retournent un ensemble constant et ne sont exécutés qu’une fois, ou celles qui expriment un Semi-Join (test d’existence) ou Anti-Join (test de nonexistence), qui sont presque des jointures : la seule différence est qu’elles ne récupèrent pas l’enregistrement de la table cible. 9 https://docs.postgresql.fr/current/queries-table-expressions.html#QUERIES-LATERAL Attention toutefois à l’utilisation du prédicat NOT IN , ils peuvent générer des plans d’exécution catastrophiques, avec une exécution de la sous-requête par ligne. **Exemple 4-169-e01.** Exemple ou sortie extraite du PDF\, page 169\. ```text EXPLAIN SELECT * FROM commandes c WHERE numero_commande NOT IN (SELECT l.numero_commande FROM lignes_commandes l ); ``` **Exemple 4-169-e02.** Exemple ou sortie extraite du PDF\, page 169\. ```text QUERY PLAN ------------------------------------------------------------------------------- Gather (cost=1000.00..22803529388.17 rows=500000 width=51) ``` Workers Planned: 2 **Exemple 4-169-e03.** Exemple ou sortie extraite du PDF\, page 169\. ```text -> Parallel Seq Scan on commandes c (cost=0.00..22803478388.17 rows=208333 width=51) Filter: (NOT (SubPlan 1)) ``` SubPlan 1 **Exemple 4-169-e04.** Exemple ou sortie extraite du PDF\, page 169\. ```text -> Materialize (cost=0.00..101602.11 rows=3141807 width=8) -> Seq Scan on lignes_commandes l (cost=0.00..73620.07 rows=3141807 width=8) ``` La raison est la suivante : si un NULL est présent dans la liste du NOT IN , NOT IN vaut systématiquement false. Or, nous savons qu’il n’y aura pas de numero_commandes à NULL . (Dans cette requête précise, PostgreSQL aurait pu le deviner car le champ lignes_commandes.numero_commande est NOT NULL , mais il n’en est pas encore capable.) Une réécriture avec EXISTS est strictement équivalente et produit un plan d’exécution largement plus intéressant avec un Hash Right Anti Join : **Exemple 4-169-e05.** Exemple ou sortie extraite du PDF\, page 169\. ```text EXPLAIN SELECT * FROM commandes WHERE NOT EXISTS ( SELECT 1 FROM lignes_commandes l WHERE l.numero_commande = commandes.numero_commande ); ``` **Exemple 4-169-e06.** Exemple ou sortie extraite du PDF\, page 169\. ```text QUERY PLAN ------------------------------------------------------------------------------ Gather (cost=24604.00..148053.15 rows=419708 width=51) ``` Workers Planned: 2 **Exemple 4-169-e07.** Exemple ou sortie extraite du PDF\, page 169\. ```text -> Parallel Hash Right Anti Join (cost=23604.00..105082.35 rows=174878 width=51) Hash Cond: (l.numero_commande = commandes.numero_commande) -> Parallel Seq Scan on lignes_commandes l (cost=0.00..55292.86 rows=1309086 width=8) -> Parallel Hash (cost=14325.67..14325.67 rows=416667 width=51) -> Parallel Seq Scan on commandes (cost=0.00..14325.67 rows=416667 width=51) ``` ### Les vues {#devpg-4-2-8} Une vue est une requête pré-déclarée en base. - Équivalent relationnel d’une fonction - Si utilisée dans une autre requête, elle est traitée comme une sous-requête - et inlinée - Pas de problème si elle est relationnelle… Les vues sont des requêtes dont le code peut être inclus dans une autre requête, comme s’il s’agissait d’une sous-requête. Les vues sont très pratiques en SQL et, en théorie, permettent de séparer le modèle physique (les tables) de ce que voient les développeurs, et donc de faire évoluer le modèle physique sans impact pour le développement. Elles sont surtout très pratiques pour rendre les requêtes plus lisibles, permettre la réutilisation de code SQL, et masquer la complexité à des utilisateurs peu avertis. Dans le cas idéal, une vue reste relationnelle et donc ne contient que SELECT , FROM et WHERE , et elle peut être fusionnée avec le reste de la requête; y compris avec des vues basées sur des vues. Ici les critères de deux vues imbriquées se retrouvent dans un même nœud : **Exemple 4-170-e01.** Exemple ou sortie extraite du PDF\, page 170\. ```text CREATE OR REPLACE VIEW v_test_az ``` AS SELECT * FROM test **Exemple 4-170-e02.** Exemple ou sortie extraite du PDF\, page 170\. ```text WHERE valeur BETWEEN 'A' AND 'Z' ; ``` **Exemple 4-170-e03.** Exemple ou sortie extraite du PDF\, page 170\. ```text CREATE OR REPLACE VIEW v_test_1000_az ``` AS SELECT * FROM v_test_az **Exemple 4-170-e04.** Exemple ou sortie extraite du PDF\, page 170\. ```text WHERE id < 100 ; ``` **Exemple 4-170-e05.** Exemple ou sortie extraite du PDF\, page 170\. ```text EXPLAIN SELECT * FROM v_test_1000_az ; ``` **Exemple 4-170-e06.** Exemple ou sortie extraite du PDF\, page 170\. ```text QUERY PLAN ------------------------------------------------------------------------------ Index Scan using test_pkey on test (cost=0.42..10.68 rows=54 width=6) Index Cond: (id < 100) Filter: (((valeur)::text >= 'A'::text) AND ((valeur)::text <= 'Z'::text)) ``` ### Éviter les vues non-relationnelles {#devpg-4-2-9} - Attention aux vues avec DISTINCT , GROUP BY etc. - tous les problèmes des sous-requêtes déjà vus - impossible de l’inliner - barrière d’optimisation - …et mauvaises performances - Les vues sont dangereuses en termes de performance - masquent la complexité - Penser aux vues matérialisées si la requête est lourde En pratique, les vues sont souvent sources de ralentissement : elles masquent la complexité, et l’utilisateur crée alors sans le savoir des requêtes très complexes, mettant en jeu des dizaines de tables (voire des dizaines de fois les mêmes tables !). Avant d’utiliser une vue, il faut s’intéresser un peu à son contenu et ce qu’elle fait. On retrouve aussi toute la complexité liée aux sous-requêtes, puisqu’une vue en est l’équivalent. En particulier, il faut se méfier des vues contenant des opérations non-relationnelles, qui peuvent empêcher de nombreuses optimisations. En voici un exemple simple : **Exemple 4-171-e01.** Exemple ou sortie extraite du PDF\, page 171\. ```text CREATE OR REPLACE VIEW v_test_did ``` AS SELECT DISTINCT ON (id) id,valeur FROM test ; **Exemple 4-171-e02.** Exemple ou sortie extraite du PDF\, page 171\. ```text EXPLAIN (ANALYZE, COSTS OFF) SELECT id,valeur FROM v_test_did WHERE valeur='b' ; ``` **Exemple 4-171-e03.** Exemple ou sortie extraite du PDF\, page 171\. ```text QUERY PLAN -------------------------------------------------------------------------------- Subquery Scan on v_test_did (actual time=0.070..203.584 rows=10638.00 loops=1) Filter: ((v_test_did.valeur)::text = 'b'::text) Rows Removed by Filter: 989362 -> Unique (actual time=0.017..158.458 rows=1000000.00 loops=1) -> Index Scan using test_pkey on test (actual time=0.015..98.200 rows=1000000.00 loops=1) ``` Planning Time: 0.202 ms **Exemple 4-171-e04.** Exemple ou sortie extraite du PDF\, page 171\. ```text Execution Time: 203.872 ms ``` On constate que la condition de filtrage sur b n’est appliquée qu’à la fin. C’est normal : à cause du DISTINCT ON , l’optimiseur ne peut pas savoir si l’enregistrement qui sera retenu dans la sousrequête vérifiera valeur='b' ou pas, et doit donc attendre l’étape suivante pour filtrer. Le coût en performances, même avec un volume de données raisonnable, peut être astronomique. ## Accès aux données {#devpg-4-3} L’accès aux données est coûteux. - Quelle que soit la base - Dialogue entre client et serveur - plusieurs aller/retours potentiellement - Analyse d’un langage complexe - SQL PostgreSQL : gram.y de 19000 lignes - Calcul de plan : - langage déclaratif => converti en impératif à chaque exécution Dans les captures réseau ci-dessous, le serveur est sur le port 5932. **Exemple 4-172-e01.** Exemple ou sortie extraite du PDF\, page 172\. ```text SELECT * FROM test , 0 enregistrement : ``` 10:57:15.087777 IP 127.0.0.1.39508 > 127.0.0.1.5932: Flags [P.], seq 109:134, ack 226, win 350, options [nop,nop,TS val 2270307 ecr 2269578], length 25 10:57:15.088130 IP 127.0.0.1.5932 > 127.0.0.1.39508: Flags [P.], seq 226:273, ack 134, win 342, options [nop,nop,TS val 2270307 ecr 2270307], length 47 10:57:15.088144 IP 127.0.0.1.39508 > 127.0.0.1.5932: Flags [.], ack 273, win 350, options [nop,nop,TS val 2270307 ecr 2270307], length 0 **SELECT * FROM test, 1000 enregistrements :** 10:58:08.542660 IP 127.0.0.1.39508 > 127.0.0.1.5932: Flags [P.], seq 188:213, ack 298, win 350, options [nop,nop,TS val 2286344 ecr 2285513], length 25 10:58:08.543281 IP 127.0.0.1.5932 > 127.0.0.1.39508: Flags [P.], seq 298:8490, ack 213, win 342, options [nop,nop,TS val 2286344 ecr 2286344], length 8192 10:58:08.543299 IP 127.0.0.1.39508 > 127.0.0.1.5932: Flags [.], ack 8490, win 1002, options [nop,nop,TS val 2286344 ecr 2286344], length 0 10:58:08.543673 IP 127.0.0.1.5932 > 127.0.0.1.39508: Flags [P.], seq 8490:14241, ack 213, win 342, options [nop,nop,TS val 2286344 ecr 2286344], length 5751 10:58:08.543682 IP 127.0.0.1.39508 > 127.0.0.1.5932: Flags [.], ack 14241, win 1012, options [nop,nop,TS val 2286344 ecr 2286344], length 0 Un client JDBC va habituellement utiliser un aller/retour de plus, en raison des requêtes préparées : un dialogue pour envoyer la requête et la préparer, et un autre pour envoyer les paramètres. Le problème est la latence du réseau, habituellement : de 50 à 300 μs. Cela limite à 3 000 à 20 000 le nombre d’opérations maximum par seconde par socket. On peut bien sûr paralléliser sur plusieurs sessions, mais cela complique le traitement. En ce qui concerne le parser : comme indiqué dans ce message 10 : gram.o , le parser fait 1 Mo une fois compilé! ### Coût des connexions {#devpg-4-3-1} Se connecter coûte cher : - Authentification, permissions - Latence réseau - Négociation SSL - Création de processus & contexte d’exécution - Acquisition de verrous **Exemple 4-173-e01.** Exemple ou sortie extraite du PDF\, page 173\. ```text -> Maintenir les connexions côté applicatif, ou utiliser un pooler. ``` L’établissement d’une connexion client au serveur est coûteuse, en temps comme ressource. Il faut plusieurs allers-retours réseau pour établir la connexion. Il y a souvent une négociation pour le chiffrement SSL. PostgreSQL doit authentifier l’utilisateur, puis créer son processus, le contexte d’exécution, poser quelques verrous, avant que les requêtes puissent arriver. Pour des requêtes répétées, il est beaucoup plus efficace d’ouvrir une connexion et de la réutiliser pour de nombreuses requêtes. On peut tester l’impact d’une connexion/déconnexion avec pgbench , dont l’option -C lui demande de se connecter à chaque requête : **Exemple 4-173-e02.** Exemple ou sortie extraite du PDF\, page 173\. ```text $ pgbench pgbench -T 20 -c 10 -j5 -S -C ``` starting vacuum...end. transaction type: scaling factor: 2 query mode: simple number of clients: 10 number of threads: 5 duration: 20 s number of transactions actually processed: 16972 latency average = 11.787 ms tps = 848.383850 (including connections establishing) tps = 1531.057609 (excluding connections establishing) Sans se reconnecter à chaque requête : **Exemple 4-173-e03.** Exemple ou sortie extraite du PDF\, page 173\. ```text $ pgbench pgbench -T 20 -c 10 -j5 -S ``` starting vacuum...end. transaction type: scaling factor: 2 query mode: simple number of clients: 10 number of threads: 5 duration: 20 s 10 https://www.postgresql.org/message-id/CA+TgmoaaYvJ7yDKJHrWN1BVk_7fcV16rvc93udSo59gfxG_t7A@mail.gmail.com number of transactions actually processed: 773963 latency average = 0.258 ms tps = 38687.524110 (including connections establishing) tps = 38703.239556 (excluding connections establishing) On passe de 900 à 38 000 transactions par seconde. Un pooler est souvent intégré d’office aux serveurs d’applications (par exemple Tomcat). Sinon, PgBouncer 11 est l’outil généralement recommandé. Pour les détails, voir notre module de formation W6 12 . ### Penser relationnel {#devpg-4-3-2} - Les spécifications sont souvent procédurales, voire objet! - Prendre du recul - Réfléchir de façon ensembliste - on travaille sur des ensembles de données - penser aux CTE ( WITH ) Si les spécifications disent (version simplifiée bien sûr) : - vérifier la présence du client ; - s’il est présent, mettre à jour son adresse; - sinon, créer le client avec la bonne adresse, on peut être tenter d’écrire (pseudo-code client) : **Exemple 4-174-e01.** Exemple ou sortie extraite du PDF\, page 174\. ```text SELECT count(*) from clients where client_name = 'xxx' INTO compte ``` **Exemple 4-174-e02.** Exemple ou sortie extraite du PDF\, page 174\. ```text IF compte > 0 UPDATE clients set adresse='yyy' WHERE client_name='xxx' ELSE INSERT client SET client_name='xxx', adresse='yyy' END IF ``` D’où 3 requêtes, systématiquement 2 appels à la base. On peut facilement économiser une requête : **Exemple 4-174-e03.** Exemple ou sortie extraite du PDF\, page 174\. ```text UPDATE clients set adresse='yyy' WHERE client_name='xxx' IF NOT FOUND INSERT client SET client_name='xxx', adresse='yyy' END IF ``` Les versions modernes de PostgreSQL permettent de tout faire en un seul ordre. L’exemple suivant utilise une fusion des enregistrements dans PostgreSQL avec des CTE 13 . 11 https://www.pgbouncer.org/ 12 https://dali.bo/w6_html 13 https://vibhorkumar.wordpress.com/2011/10/26/upsertmerge-using-writable-cte-in-postgresql-9-1/ WITH enregistrements_a_traiter AS ( **Exemple 4-175-e01.** Exemple ou sortie extraite du PDF\, page 175\. ```text SELECT * FROM (VALUES ('toto' , 'adresse1' ),('tata','adresse2')) ``` AS val(nom_client,adresse) ), mise_a_jour AS ( **Exemple 4-175-e02.** Exemple ou sortie extraite du PDF\, page 175\. ```text UPDATE client SET adresse=enregistrements_a_traiter.adresse FROM enregistrements_a_traiter WHERE enregistrements_a_traiter.nom_client=client.nom_client RETURNING client.nom_client ) INSERT INTO client (nom_client,adresse) SELECT nom_client,adresse from enregistrements_a_traiter WHERE NOT EXISTS ( SELECT 1 FROM mise_a_jour WHERE mise_a_jour.nom_client=enregistrements_a_traiter.nom_client ); ``` Dans beaucoup de cas on peut faire encore plus simple grâce à la clause ON CONFLICT … DO UPDATE (« upsert 14 ») : **Exemple 4-175-e03.** Exemple ou sortie extraite du PDF\, page 175\. ```text INSERT INTO client (nom_client,adresse) VALUES ('toto' , 'adresse1' ), ('tata','adresse2') ON CONFLICT (nom_client) DO UPDATE SET adresse = EXCLUDED.adresse WHERE client.nom_client = EXCLUDED.nom_client; ``` PostgreSQL 15 apporte même une commande MERGE 15 . Par rapport à INSERT ON CONFLICT , MERGE permet aussi des suppressions, et possède un mécanisme différent. ### Pas de DDL applicatif {#devpg-4-3-3} Le schéma est la modélisation des données - Une application n’a pas à y toucher lors de son fonctionnement normal + exception : tables temporaires - SQL manipule les données en flux continu : - chaque étape d’un plan d’exécution n’attend pas la fin de la précédente - donc : une table temporaire est souvent une perte de temps Il est fortement déconseillé qu’une application modifie le schéma de données pendant son fonctionnement, notamment qu’elle crée des tables ou ajoute des colonnes. Une exception fréquente concerne les tables « temporaires », qui n’existent que le temps d’une session. Elles sont inévitables dans certaines circonstances, assez courantes pendant des batchs, mais dans le flux normal de l’applicatif l’utilisation de tables temporaires ne sert généralement qu’à multiplier les étapes et à 14 https://docs.postgresql.fr/current/sql-insert.html 15 https://public.dalibo.com/exports/formation/workshops/fr/ws15/150-postgresql_15.handout.html#ajout-de-la-commande-sql-merge poser des sortes de points d’arrêt artificiels dans le maniement des données. C’est très net sur cette réécriture de l’exemple précédent : **Exemple 4-176-e01.** Exemple ou sortie extraite du PDF\, page 176\. ```text CREATE TEMP TABLE temp_a_inserer (nom_client text, adresse text); ``` **Exemple 4-176-e02.** Exemple ou sortie extraite du PDF\, page 176\. ```text INSERT INTO temp_a_inserer SELECT * FROM (VALUES ('toto' , 'adresse1' ), ('tata','adresse2')) AS tmp; ``` **Exemple 4-176-e03.** Exemple ou sortie extraite du PDF\, page 176\. ```text UPDATE client SET adresse=temp_a_inserer.adresse FROM temp_a_inserer WHERE temp_a_inserer.nom_client=client.nom_client; ``` **Exemple 4-176-e04.** Exemple ou sortie extraite du PDF\, page 176\. ```text INSERT INTO client (nom_client,adresse) SELECT nom_client,adresse from temp_a_inserer WHERE NOT EXISTS ( SELECT 1 FROM client WHERE client.nom_client=temp_a_inserer.nom_client ); DROP TABLE temp_a_inserer; ``` ### Optimiser chaque accès {#devpg-4-3-4} Les moteurs SQL sont très efficaces, et évoluent en permanence - Ils ont de nombreuses méthodes de tri, de jointure, choisies en fonction du contexte - En SQL : - optimisation selon volume & configuration - évolution avec le moteur - Dans l’application cliente : vous devrez le maintenir et l’améliorer - Faites le maximum côté SQL : - agrégats, fonctions analytiques, tris, numérotations, CASE , etc. - Commentez avec -- et /* */ L’avantage du code SQL est, encore une fois, qu’il est déclaratif. Il aura donc de nombreux avantages sur un code procédural côté applicatif, quel que soit le langage. L’exécution par le moteur évoluera pour prendre en compte les variations de volumétrie des différentes tables. Les optimiseurs sont la partie la plus importante d’un moteur SQL. Ils progressent en permanence. Chaque nouvelle version va donc potentiellement améliorer vos performances. Si vous écrivez du procédural avec des appels unitaires à la base dans des boucles, le moteur ne pourra rien optimiser. Si vous faites vos tris ou regroupements côté client, vous êtes limités aux algorithmes fournis par vos langages, voire à ceux que vous aurez écrit manuellement à un moment donné. Alors qu’une base de données bascule automatiquement entre une dizaine d’algorithmes différents suivant le volume, le type de données à trier, ce pour quoi le tri est ensuite utilisé, etc., voire évite de trier en utilisant des tables de hachage ou des index disponibles. La migration à une nouvelle version du moteur peut vous apporter d’autres techniques prises alors en compte de manière transparente. ### Ne faire que le nécessaire {#devpg-4-3-5} Prendre de la distance vis-à-vis des spécifications fonctionnelles (bis) : - Ex : mise à jour ou insertion ? - tenter la mise à jour, et regarder combien d’enregistrements ont été mis à jour - surtout pas de COUNT(*) - éventuellement un test de l’existence d’un seul enregistrement - gérer les exceptions plutôt que de vérifier préalablement que les conditions sont remplies (si l’exception est rare) - et se renseigner sur la syntaxe Toujours coder les accès aux données pour que la base fasse le maximum de traitement, mais uniquement les traitements nécessaires : l’accès aux données est coûteux, il faut l’optimiser. Et le gros des pièges peut être évité avec les quelques règles d’« hygiène » simples qui viennent d’être énoncées. ## Index {#devpg-4-4} - Objets destinés à l’optimisation des accès - À poser par les développeurs : **Exemple 4-178-e01.** Exemple ou sortie extraite du PDF\, page 178\. ```text CREATE INDEX ON ma_table (nom colonne) ; ``` Les index sont des objets uniquement destinés à accélérer les requêtes (filtrage mais aussi jointures et tris, ou respect des contraintes d’unicité). Ils ne modifient jamais le résultat d’une requête (tout au plus : ils peuvent changer l’ordre des lignes résultantes si celui-ci est indéfini.) Il est capital pour un développeur d’en maîtriser les bases, car il est celui qui sait quels sont les champs interrogés dans son application. Les index sont un sujet en soi qui sera traité par ailleurs. ## Impact des transactions {#devpg-4-5} - Verrous : relâchés à la fin de la transaction - COMMIT - ROLLBACK - Validation des données sur le disque au COMMIT - écriture synchrone : coûteux - Contournements : - tables temporaires/unlogged? - parfois : synchronous_commit = off (…si perte possible) - -> Faire des transactions qui correspondent au fonctionnel - pas trop nombreuses - courtes, pas de travail inutile une fois des verrous posés Réaliser des transactions permet de garantir l’atomicité des opérations : toutes les modifications sont validées ( COMMIT ), ou tout est annulé ( ROLLBACK ). Il n’y a pas d’état intermédiaire. Le COMMIT garantit aussi la durabilité des opérations : une fois que le COMMIT a réussi, la base de données garantit que les opérations ont bien été stockées, et ne seront pas perdues… sauf perte du matériel (disque) sur lequel ont été écrites ces opérations bien sûr. L’opération COMMIT a bien sûr un coût : il faut garantir que les données sont bien écrites sur le disque, il faut les écrire sur le disque (évidemment), mais aussi attendre la confirmation du disque, voire de serveurs répliqués distants parfois. Même avec des disques SSD, plus performants que les disques classiques, cette opération reste coûteuse. Un disque dur classique doit attendre la rotation du disque et placer sa tête au bon endroit (dans le journal de transaction), écrire la donnée, et confirmer au système que c’est fait. Un disque SSD doit écrire réellement le bloc demandé, c’est-à-dire l’effacer (relativement lent) puis le réécrire. Dans les deux cas, il faut compter de l’ordre de la milliseconde. Il est parfois acceptable de perdre les dernières données en cas de panne de courant b (par défaut, celles committées pendant les derniers 300 ms). On peut donc réduire la fréquence de la synchronisation des journaux avec : **Exemple 4-179-e01.** Exemple ou sortie extraite du PDF\, page 179\. ```text SET synchronous_commit TO off ; /* pour une session */ SET LOCAL synchronous_commit TO off ; /* pour une transaction */ ``` La synchronisation n’aura plus lieu aussi fréquemment. L’impact peut être énorme sur les petites transactions nombreuses. De plus, les transactions devant garantir l’atomicité des opérations, il est nécessaire qu’elles prennent des verrous : sur les enregistrements modifiés, sur les tables accédées (pour éviter les changements de structure pendant leur manipulation), sur des prédicats (dans certains cas compliqués comme le niveau d’isolation serializable)… Tout ceci a un impact : - par le temps d’acquisition des verrous, bien sûr ; - par la contention entre sessions : certaines risquent d’en bloquer d’autres. Les verrous étant posés lors des ordres d’écriture et relâchés en fin de transaction, on fera en sorte que la transaction fasse le minimum de choses après les premières écritures. Il est donc très difficile de déterminer la bonne durée d’une transaction. Trop courte : on génère beaucoup d’opérations synchrones. Trop longue : on risque de bloquer d’autres sessions. Le mieux (et le plus important en fait) est de coller au besoin fonctionnel. ### Verrouillage et contention {#devpg-4-5-1} - Chaque transaction prend des verrous : - sur les objets (tables, index, etc.) pour empêcher au moins leur suppression ou modification de structure pendant leur travail - sur les enregistrements - libérés à la fin de la transaction : les transactions très longues peuvent donc être problématiques - Sous PostgreSQL, on peut quand même lire un enregistrement en cours de modification : on voit l’ancienne version (MVCC) Afin de garantir une isolation correcte entre les différentes sessions, le SGBD a besoin de protéger certaines opérations. On ne peut par exemple pas autoriser une session à modifier le même enregistrement qu’une autre, tant qu’on ne sait pas si cette dernière a validé ou annulé sa modification. On a donc un verrouillage des enregistrements modifiés. Certains SGBD verrouillent totalement l’enregistrement modifié. Celui-ci n’est plus accessible même en lecture tant que la modification n’a pas été validée ou annulée. Cela a l’avantage d’éviter aux sessions en attente de voir une ancienne version de l’enregistrement, mais le défaut de les bloquer, et donc de fortement dégrader les performances. PostgreSQL, comme Oracle, utilise un modèle dit MVCC (Multi-Version Concurrency Control), qui permet à chaque enregistrement de cohabiter en plusieurs versions simultanées en base. Cela permet d’éviter que les écrivains ne bloquent les lecteurs ou les lecteurs ne bloquent les écrivains. Cela permet aussi de garantir un instantané de la base à une requête, sur toute sa durée, voire sur toute la durée de sa transaction si la session le demande ( BEGIN ISOLATION LEVEL REPEATABLE READ ). Dans le cas où il est réellement nécessaire de verrouiller un enregistrement sans le mettre à jour immédiatement (pour éviter une mise à jour concurrente), il faut utiliser l’ordre SQL SELECT FOR UPDATE . Ce dernier possède une très intéressante option SKIP LOCKED 16 pour ne pas être bloqué par une ligne déjà verrouillée. 16 https://docs.postgresql.fr/current/sql-select.html#SQL-FOR-UPDATE-SHARE ### Deadlocks {#devpg-4-5-2} « Verrous mortels » : comment les éviter? - Théorie : prendre toujours les verrous dans le même ordre - Pratique, ça n’est pas toujours possible ou commode - Conséquence : une des transactions est tuée - erreurs, ralentissements Les deadlocks se produisent quand plusieurs sessions acquièrent simultanément des verrous et s’interbloquent. Par exemple : **Tableau 4-181-t01.** Exemple d’interblocage entre deux sessions\, page PDF 181\. | Session 1 | Session 2 | | --- | --- | | `BEGIN` | `BEGIN` | | `UPDATE demo SET a=10 WHERE a=1;` | | | `UPDATE demo SET a=11 WHERE a=2;` | `UPDATE demo SET a=11 WHERE a=2;` — session 1 bloquée, attend session 2. | | | `UPDATE demo SET a=10 WHERE a=1;` — session 2 bloquée, attend session 1. | Bien sûr, la situation ne reste pas en l’état. Une session qui attend un verrou appelle au bout d’un temps court (une seconde par défaut sous PostgreSQL) le gestionnaire de deadlocks, qui finira par tuer une des deux sessions. Dans cet exemple, il sera appelé par la session 2, ce qui débloquera la situation. Une application qui a beaucoup de deadlocks a plusieurs problèmes : - les transactions, mêmes celles qui réussissent, attendent beaucoup (ce qui bride l’utilisation de toutes les ressources machine) ; - certaines finissent annulées et doivent donc être rejouées (travail supplémentaire). Dans notre exemple, on aurait pu éviter le problème, en définissant une règle simple : toujours verrouiller par valeurs de a croissante. Dans la pratique, sur des cas complexes, c’est bien sûr bien plus difficile à faire. Par ailleurs, un deadlock peut impliquer plus de deux transactions. Mais simplement réduire le volume de deadlocks aura toujours un impact très positif sur les performances. On peut aussi déclencher plus rapidement le gestionnaire de deadlock . 1 seconde, c’est quelquefois une éternité dans la vie d’une application. Sous PostgreSQL, il suffit de modifier le paramètre deadlock_timeout . Plus cette variable sera basse, plus le traitement de détection de deadlock sera déclenché souvent. Et celui-ci peut être assez gourmand si de nombreux verrous sont présents, puisqu’il s’agit de détecter des cycles dans les dépendances de verrous. ### Quelle durée pour les sessions & transactions? {#devpg-4-5-3} Divers seuils possibles, jamais globalement. **Exemple 4-182-e01.** Exemple ou sortie extraite du PDF\, page 182\. ```text SET …_timeout TO '5s' ; ALTER ROLE … IN DATABASE … SET ..._timeout TO '…s' ``` Paramètre Cible du seuil lock_timeout Attente de verrou statement_timeout Ordre en cours idle_session_timeout Session inactive idle_in_transaction_session_timeout Transaction en cours, inactive transaction_timeout (v17) Transaction en cours PostgreSQL possède divers seuils pour éviter des ordres, transactions ou sessions trop longues. Le dépassement d’un seuil provoque la fin et l’annulation de l’ordre, transaction ou session. Par défaut, aucun n’est activé. Ne définissez jamais des paramètres globalement, les ordres de maintenance ou les > batchs de nuit seraient aussi concernés, tout comme les superutilisateurs. Utilisez un ordre SET dans la session, ou ALTER ROLE … IN DATABASE … SET … . Il est important que l’application sache gérer l’arrêt de connexion ou l’annulation d’un > ordre ou d’une transaction et réagir en conséquence (nouvelle tentative, abandon avec erreur…). Dans le cas contraire, l’application pourrait rester déconnectée, et suivant les cas des données pourraient être perdues. lock_timeout permet d’interrompre une requête qui mettrait trop de temps à acquérir son verrou. Il faut l’activer par précaution avant une opération lourde (un VACUUM FULL notamment) pour éviter un « empilement des verrous ». En effet, si l’ordre ne peut pas s’exécuter immédiatement, il bloque toutes les autres requêtes sur la table. statement_timeout est la durée maximale d’un ordre. Défini au niveau d’un utilisateur ou d’une session, cet ordre permet d’éviter que des requêtes aberrantes chargent la base pendant des heures ou des jours; ou encore que des requêtes « s’empilent » sur une base totalement saturée ou avec trop de contention. Autre exemple : les utilisateurs de supervision et autres utilisateurs non critiques. À noter : l’extrait suivant d’une sauvegarde par pg_dump montre que l’outil inhibe ces paramètres par précaution, afin que la restauration n’échoue pas. **Exemple 4-183-e01.** Exemple ou sortie extraite du PDF\, page 183\. ```text -- Dumped from database version 17.2 (Debian 17.2-1.pgdg120+1) -- Dumped by pg_dump version 17.2 (Debian 17.2-1.pgdg120+1) ``` **Exemple 4-183-e02.** Exemple ou sortie extraite du PDF\, page 183\. ```text SET statement_timeout = 0; SET lock_timeout = 0; SET idle_in_transaction_session_timeout = 0; SET transaction_timeout = 0; … ``` L’idée peut être reprise pour des scripts batchs, par exemple. ### Quelle durée pour une session ? {#devpg-4-5-4} - Courte - coût & temps des connexions - pooler ? - Longue - risque de saturation du nombre de connexions - (rare) gaspillage mémoire par les backends Il est généralement conseillé d’utiliser des sessions assez longues, car la création d’une connexion à PostgreSQL est une opération lourde. Si l’applicatif ne cesse de se connecter/déconnecter, il faudra penser à un pooler (pgBouncer, ou côté applicatif). Des sessions très longues et inactives ne sont en général pas un souci. Un pooler garde justement ses sessions longtemps. Mais si les sessions ne se ferment jamais, le nombre maximal de sessions ( max_connections ) peut être atteint, et de nouvelles connexions refusées. De plus, pour les performances, il n’est pas très bon qu’il y ait des milliers de sessions ouvertes, malgré les progrès des versions récentes de PostgreSQL. Enfin, si la consommation mémoire du backend associé à une session est raisonnable, il arrive, exceptionnellement, qu’il se mette à occuper durablement de la mémoire (par exemple en cas d’accès à des milliers de tables et index durant son existence). La durée maximale des sessions peut être réglée par idle_session_timeout , ou au niveau du pooler s’il y en a un. ### Quelle durée pour une transaction ? {#devpg-4-5-5} - Courte - synchronisation fréquente coûteuse - Longue - verrous bloquants Les transactions ouvertes et figées durablement sans faire de COMMIT ou ROLLBACK sont un problème sérieux, car l’autovacuum ne pourra pas nettoyer de ligne que cette session pourrait encore voir. Si l’applicatif ne peut être corrigé, ou si des utilisateurs ouvrent un outil quelconque sans le refermer, une solution est de définir idle_in_transaction_session_timeout , par exemple à une ou plusieurs heures. Pour les performances, il faut éviter les sessions trop courtes car le coût de la synchronisation sur disque des journaux lors d’un COMMIT est coûteux (sauf table unlogged ou synchronous_commit ). Typiquement, un traitement de batch ou de chargement regroupera de nombreuses opérations en une seule transaction. D’un autre côté, chaque transaction maintient ses verrous jusqu’à sa fin, et peut donc bloquer d’autres transactions et ralentir toute l’application. En utilisation transactionnelle, il vaut donc mieux que les transactions soient courtes et n’en fassent pas plus que ce qui est dicté par le besoin fonctionnel, et si possible le plus tard possible dans la transaction. transaction_timeout (à partir de PostgreSQL 17) peut alors servir comme sécurité en cas de problème. À noter que cette limite ne concerne pas les transactions préparées, liées au transactions en deux phases 17 , et dont la longueur est parfois un souci. 17 https://docs.postgresql.fr/current/two-phase.html ## Base distribuée {#devpg-4-6} Écrire sur plusieurs nœuds ? - Complexité (applicatif/exploitation) - -> risque d’erreur (programmation, fausse manipulation) - reprise d’incident complexe - Essayez avec un seul serveur plus gros - après avoir optimisé bien sûr - PostgreSQL peut vous étonner Il y a plusieurs variantes de l’utilisation de plusieurs nœuds. PostgreSQL permet nativement la lecture sur des réplicas d’une instance (et la technologie est fiable), mais pas l’écriture. Il est cependant possible de faire du sharding en répartissant les données sur plusieurs instances indépendantes (sharding). L’extension Citus 18 permet de faire cela de manière transparente. Mais il ne faut surtout pas négliger tous les coûts de cette solution : non seulement le coût du matériel, mais aussi les coûts humains : procédures d’exploitation, de maintenance, complexité accrue de développement, etc. Performance et robustesse peuvent être des objectifs contradictoires. Avant de complexifier votre système, pensez à augmenter les ressources du serveur b (après avoir optimisé autant que possible et identifié quelle ressource pose problème : RAM, CPU, disque… ?) 18 https://www.citusdata.com/product ## Bibliographie {#devpg-4-7} - Quelques références : - The Art of SQL, Stéphane Faroult - Refactoring SQL Applications, Stéphane Faroult - SQL Performance Explained, Markus Winand - Introduction aux bases de données, Chris Date - The Art of PostgreSQL, Dimitri Fontaine - Vidéos de Stéphane Faroult sous Youtube Il existe bien des livres sur le développement en SQL. Voici quelques sources intéressantes parmi bien d’autres : Livres pratiques non propres à PostgreSQL : - The Art of SQL, Stéphane Faroult, 2006 (ISBN-13 : 978-0596008949) - Refactoring SQL Applications, Stéphane Faroult, 2008 (ISBN-13 : 978-0596514976) - SQL Performance Explained, Markus Winand, 2012 : même si ce livre ne tient pas compte des dernières nouveautés des index de PostgreSQL, il contient l’essentiel de ce qu’un développeur doit savoir sur les index B-tree sur diverses bases de données courantes - site internet (fr) 19 - ISBN-13 en français : 978-3950307832, en anglais : 978-3950307825 En vidéos : - Stéphane Faroult (roughsealtd sur Youtube 20 ) : SQL Best Practices in less than 20 minutes partie 1 21 , partie 2 22 , partie 3 23 . Livres spécifiques à PostgreSQL : - The Art of PostgreSQL 24 de Dimitri Fontaine (2020); l’ancienne édition de 2017 se nommait Mastering PostgreSQL in Application Development. Sur la théorie des bases de données : - An Introduction to Database Systems, Chris Date (8è édition de 2003, ISBN-13 en français : 978- 2711748389; en anglais : 978-0321197849) ; - The World and the Machine, Michael Jackson (version en ligne 25 . 19 https://use-the-index-luke.com/fr 20 https://www.youtube.com/channel/UCW6zsYGFckfczPKUUVdvYjg 21 https://www.youtube.com/watch?v=40Lnoyv-sXg&list=PL767434BC92D459A7 22 https://www.youtube.com/watch?v=GbZgnAINjUw&list=PL767434BC92D459A7 23 https://www.youtube.com/watch?v=y70FmugnhPU&list=PL767434BC92D459A7 24 https://theartofpostgresql.com/ 25 https://dl.acm.org/doi/10.1145/225014.225041 ## Quiz {#devpg-4-8} https://dali.bo/j3_quiz Pour poursuivre la lecture, voir [le chapitre suivant](/fr/devpg/05-techniques-indexation/#devpg-ch5). Pour revenir au chapitre précédent, voir [le chapitre précédent](/fr/devpg/03-introduction-plans-execution/#devpg-ch3).