Vous trouverez en ligne les différentes versions complètes de ce document.

Vous n’avez pas le droit d’utiliser cette création à des fins commerciales.

Si vous modifiez, transformez ou adaptez cette création, vous n’avez le droit de distribuer la création qui en résulte que sous un contrat identique à celui-ci.

Vous devez citer le nom de l’auteur original de la manière indiquée par l’auteur de l’œuvre ou le titulaire des droits qui vous confère cette autorisation (mais pas d’une manière qui suggérerait qu’ils vous soutiennent ou approuvent votre utilisation de l’œuvre). À chaque réutilisation ou distribution de cette création, vous devez faire apparaître clairement au public les conditions contractuelles de sa mise à disposition. La meilleure manière de les indiquer est un lien vers cette page web. Chacune de ces conditions peut être levée si vous obtenez l’autorisation du titulaire des droits sur cette œuvre. Rien dans ce contrat ne diminue ou ne restreint le droit moral de l’auteur ou des auteurs.

Le texte complet de la licence est disponible sur http://creativecommons.org/licenses/by-nc-sa/2.0/fr/legalcode

Cela inclut les diapositives, les manuels eux-mêmes et les travaux pratiques. Cette formation peut également contenir quelques images et schémas dont la redistribution est soumise à des licences différentes qui sont alors précisées.

Sur les versions précédentes susceptibles d’être encore rencontrées en production, seuls quelques points très importants sont évoqués, en plus éventuellement de quelques éléments historiques.

Sauf précision contraire, le système d’exploitation utilisé est Linux.

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.)

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.

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.

Le pourcentage de la population dont la richesse est supérieure à une valeur x est proportionnel à A/x^α »

Vilfredo Pareto, économiste du XIXe siècle

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.

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.

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.

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 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 :

SELECT r.rolname, d.datname, s.calls, s.total_exec_time,
       s.total_exec_time / s.calls AS avg_time, s.query
  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 ;

Et la requête suivante permet de déterminer les requêtes les plus fréquemment appelées :

SELECT r.rolname, d.datname, s.calls, s.total_exec_time,
       s.total_exec_time / s.calls  AS avg_time, s.query
  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

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.

Le langage SQL 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 (juin 2023).

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.

Ceux-ci sont plus difficilement optimisables : ils introduisent par exemple des contraintes d’ordre dans l’exécution :

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.

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.

É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 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.

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 :

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 ;

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 :

#!/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 :

time perl demo.pl  | psql > /dev/null

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 :

time psql -c "SELECT * FROM test WHERE id BETWEEN 0 AND 1000" > /dev/null

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) :

List commandes = sess.createCriteria(Commande.class);

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 :

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;
SELECT * from produits where commande_id = 5;
SELECT * from produits where commande_id = 6;
...

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 :

List commandes = sess.createCriteria(Commande.class)
  .setFetchMode('produits', FetchMode.EAGER);

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 :

SELECT * FROM commandes
LEFT JOIN produits ON commandes.id = produits.commande_id;

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 :

EXPLAIN (ANALYZE,BUFFERS)
SELECT nom
FROM transporteurs
WHERE transporteur_id IN (
        SELECT transporteur_id
        FROM lots
        WHERE date_depot IS NOT NULL
        ) ;

                               QUERY PLAN
-------------------------------------------------------------------------------
 Nested Loop Semi Join  (cost=0.00..19478.49 rows=5 width=12) (actual time=0.032..0.042 rows=5 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 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 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
 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 :

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
        ) ;

                               QUERY PLAN
-------------------------------------------------------------------------------
 Hash Join (actual time=139.280..139.283 rows=5 loops=1)
   Hash Cond: (transporteurs.transporteur_id = (lots.transporteur_id + 0))
   ->  Seq Scan on transporteurs (actual time=0.010..0.011 rows=5 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 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 loops=1)
                     Filter: (date_depot IS NOT NULL)
 Planning Time: 0.201 ms
 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 :

EXPLAIN (ANALYZE,BUFFERS)
SELECT nom
FROM transporteurs
WHERE transporteur_id IN (
        SELECT   DISTINCT   transporteur_id + 0
        FROM lots
        WHERE date_depot IS NOT NULL
        ) ;

                               QUERY PLAN
-------------------------------------------------------------------------------
 Hash Join (actual time=46.385..48.943 rows=5 loops=1)
   Hash Cond: (transporteurs.transporteur_id = ((lots.transporteur_id + 0)))
   ->  Seq Scan on transporteurs (actual time=0.005..0.006 rows=5 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 loops=1)
               ->  Sort (actual time=46.366..48.922 rows=15 loops=1)
                     Sort Key: ((lots.transporteur_id + 0))
                     Sort Method: quicksort  Memory: 25kB
                     ->  Gather (actual time=46.306..48.914 rows=15 loops=1)
                           Workers Planned: 2
                           Workers Launched: 2
                           ->  HashAggregate (actual time=44.707..44.708 rows=5 loops=3)
                                 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 loops=3)
                                       Filter: (date_depot IS NOT NULL)
 Planning Time: 0.096 ms
 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.

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
        ) ;

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[] (https://docs.postgresql.fr/current/queries-table-expressions.html#QUERIES-LATERAL) dédiée à ce genre de chose et qui a au moins le mérite d’être lisible et explicite.

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 non-existence), qui sont presque des jointures : la seule différence est qu’elles ne récupèrent pas l’enregistrement de la table cible.

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.

EXPLAIN SELECT *
FROM    commandes c
WHERE   numero_commande NOT IN (SELECT l.numero_commande
                                FROM   lignes_commandes l );

                               QUERY PLAN
-------------------------------------------------------------------------------
 Gather  (cost=1000.00..22803529388.17 rows=500000 width=51)
   Workers Planned: 2
   ->  Parallel Seq Scan on commandes c  (cost=0.00..22803478388.17 rows=208333 width=51)
         Filter: (NOT (SubPlan 1))
         SubPlan 1
           ->  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 :

EXPLAIN SELECT *
FROM commandes
WHERE NOT EXISTS ( SELECT 1
                   FROM lignes_commandes l
                   WHERE l.numero_commande = commandes.numero_commande );

                                 QUERY PLAN
------------------------------------------------------------------------------
 Gather  (cost=24604.00..148053.15 rows=419708 width=51)
   Workers Planned: 2
   ->  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 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 :

CREATE OR REPLACE VIEW v_test_az
AS SELECT * FROM test
WHERE valeur BETWEEN 'A' AND 'Z' ;

CREATE OR REPLACE VIEW v_test_1000_az
AS SELECT * FROM v_test_az
WHERE id < 100 ;

EXPLAIN SELECT * FROM v_test_1000_az ;

                                 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))

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 :

CREATE OR REPLACE VIEW v_test_did
AS SELECT DISTINCT ON (id) id,valeur FROM test ;

EXPLAIN (ANALYZE, COSTS OFF)
  SELECT id,valeur
  FROM v_test_did
  WHERE valeur='b' ;

                                   QUERY PLAN
--------------------------------------------------------------------------------
 Subquery Scan on v_test_did (actual time=0.070..203.584 rows=10638 loops=1)
   Filter: ((v_test_did.valeur)::text = 'b'::text)
   Rows Removed by Filter: 989362
   ->  Unique (actual time=0.017..158.458 rows=1000000 loops=1)
         ->  Index Scan using test_pkey on test (actual time=0.015..98.200 rows=1000000 loops=1)
 Planning Time: 0.202 ms
 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 sous-requê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.

Dans les captures réseau ci-dessous, le serveur est sur le port 5932.

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 : gram.o, le parser fait 1 Mo une fois compilé !

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 :

$ pgbench pgbench -T 20 -c 10 -j5 -S -C

starting vacuum...end.
transaction type: <builtin: select only>
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 :

$ pgbench pgbench -T 20 -c 10 -j5 -S

starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 2
query mode: simple
number of clients: 10
number of threads: 5
duration: 20 s
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 est l’outil généralement recommandé. Pour les détails, voir notre module de formation W6.

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) :

SELECT count(*) from clients where client_name = 'xxx'
INTO compte

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 :

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.

WITH
  enregistrements_a_traiter AS (
    SELECT * FROM (VALUES ('toto' , 'adresse1' ),('tata','adresse2'))
    AS val(nom_client,adresse)
  ),
  mise_a_jour AS (
    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 ») :

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. Par rapport à INSERT ON CONFLICT, MERGE permet aussi des suppressions, et possède un mécanisme différent.

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 à 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 :

CREATE TEMP TABLE temp_a_inserer  (nom_client text, adresse text);

INSERT INTO temp_a_inserer
  SELECT * FROM (VALUES ('toto' , 'adresse1' ), ('tata','adresse2')) AS tmp;

UPDATE client SET adresse=temp_a_inserer.adresse
FROM temp_a_inserer
WHERE temp_a_inserer.nom_client=client.nom_client;

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;

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.

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.

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.

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.

SET synchronous_commit TO off ;          /* pour une session */
SET LOCAL synchronous_commit TO off ;    /* pour une transaction */

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.

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 pour ne pas être bloqué par une ligne déjà verrouillée.

Les deadlocks se produisent quand plusieurs sessions acquièrent simultanément des verrous et s’interbloquent. Par exemple :

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.

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é.

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.

-- 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)

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.

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.

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, et dont la longueur est parfois un souci.

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 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.

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)
  • ISBN-13 en français : 978-3950307832, en anglais : 978-3950307825

En vidéos :

• Stéphane Faroult (roughsealtd sur Youtube) : SQL Best Practices in less than 20 minutes partie 1, partie 2, partie 3.

Livres spécifiques à PostgreSQL :

• The Art of PostgreSQL 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 .