Cas d’usage

Dans cet article, nous allons présenter un cas d’usage un peu particulier, dans le sens où nous allons nous intéresser à un faible volume de données pour lequel il n’y a pas de problème de performance à résoudre.

Le point de départ, c’est la détection d’un grand nombre d’opérations d’entrées sorties disque (« IO ») et de tâches d’autovacuum sur des tables dont l’analyse montre qu’elles partagent deux caractéristiques communes :

• un petit nombre constant de lignes, moins de mille, et qui ne change pas ;
• un grand nombre de mises à jour (UPDATE) de ces lignes.

VACUUM

La quantité de mises à jour induit le fait que les seuils de déclenchement des tâches d’autovacuum sont très rapidement atteints, d’où le grand nombre de tâches détectées. Une des conséquences problématiques de ce grand nombre de tâches est la quantité de données lues et écrites lors de ces tâches de maintenances, pour parcourir les tables et index associés.

Ce point est facile à contourner : il suffit d’augmenter les valeurs des seuils de déclenchement pour avoir moins de tâches. En limitant le nombre de tâches, on limite les parcours et donc les quantités de données lues et écrites.

La commande suivante permet d’établir un seuil, en valeur absolue du nombre de lignes modifiiées. Par exemple, lorsque la table a 600 lignes, que ces lignes sont mises à jour toutes les minutes, alors un seuil de 6000 doit déclencher une opération d’autovacuum toutes les 10 minutes :

ALTER TABLE <matable>
    SET ( autovacuum_vacuum_threshold = 6000
          , autovacuum_vacuum_scale_factor = 0 );

Ceci ne dit rien de l’efficacité, ou non, de la maintenance effectuée sur la table et les index : avec ce type d’utilisation, il est fréquent de constater que le volume occupé par les fichiers de la table et des index associés dépasse largement le volume réel des données. Ce phénomène d’enflement (« bloat ») peut prendre des proportions conséquentes : parfois plus de 90% du volume physique des tables et des index est en fait inutile, correspondant à d’anciennes versions des enregistrements, devenues invisibles.

Cet enflement est la conséquence du comportement normal de PostgreSQL sur les tables : lors d’un UPDATE, l’ancienne version de la ligne est conservée, et reste visible aux transactions plus anciennes, et une nouvelle version de la ligne est créée.

L’ancienne version de la ligne doit pouvoir être réutilisée. C’est normalement le cas une fois que la commande VACUUM est passée. Mais la très grande fréquence des mises à jour et des VACUUM rendent le processus peu efficace.

Mise en place

Une fois ce constat fait, on comprend que l’essentiel des lectures de données se passe dans des volumes qui sont en fait devenus invisibles, car correspondant aux anciennes versions des lignes, y compris dans les index.

Normalement, un index est créé pour minimiser les parcours de données, en permettant de trouver les données rapidement. Mais dans le cas de figure qui nous intéresse, la présence d’index volumineux produit une quantité de données lues très importante, ce qui est contraire à l’effet recherché lors de la création d’un index. Il est donc intéressant de se poser la question de la pertinence des index pour ce type de tables :

• Est-il possible d’obtenir des performances similaires sans index ?
• Est-il possible de limiter significativement les volumes de données ?

Le test suivant tente de mettre en évidence la pertinence, ou non, des index sur une petite table régulièrement mise à jour.

La table utilisée pour le test est relativement simple, avec néanmoins un attribut de type JSONB qui permet d’utiliser un index GIN :

CREATE TABLE millelignes
  ( id int,
  ev timestamptz not null default now(),
  m float,
  n float,
  z numeric,
  d text,
  p int8range,
  j jsonb
);

L’ensemble des scripts est disponible à l’adresse suivante : https://gitlab.com/loxo-articles/millelignes

Requêtes

Une fois que la table est alimentée avec mille lignes, les requêtes SELECT suivantes permettent de tester les performances :

SELECT count(*)
    FROM millelignes
    WHERE m BETWEEN 1000 AND 2000;

et :

SELECT id, ev, n
    FROM millelignes
    WHERE j @@ '$.active == true'
    AND j @? '$.balance ? (@ < 1000)' ;

Les index testés sont les suivants :

ALTER TABLE millelignes add primary key (id );
CREATE INDEX on millelignes (m);
CREATE INDEX on millelignes USING gin ( j jsonb_path_ops ) ;

Les deux requêtes SELECT sont en mesure d’utiliser les index, avec des différences de temps d’exécution peu significatives par rapport aux mêmes requêtes sans index.

Dans le même temps, des mises à jour des lignes sont faites pour simuler l’activité en écriture :

UPDATE millelignes set ev = now()
   , m = random() * 10000
   , d = md5((random() * 10000)::text)
   , j = jsonb_set( j, '{0,balance}', (random()*10000)::text::jsonb , false )
   where id = :id  ;

UPDATE millelignes set ev = now()
   , j = jsonb_set( j, '{0,active}', (case when random() > 0.5 then true else false end)::text::jsonb , false )
   where id = :id ;

Lancement du test

Dans les deux cas de figure, avec et sans index, les requêtes SELECT et UPDATE sont lancées par l’outil pgbench, avec la commande suivante :

pgbench -d $DBNAME -f selects.sql -f updates.sql -T 240 -j 10 -c 10 2> /dev/null

Le script init.sh encapsule les différents appels pour créer une base de données, la table, les données, les index et collecter les statistiques dans l’extension pg_stat_statements.

Résultats

Le premier élément de comparaison est le nombre de transactions mesurées par l’outil pgbench :

• sans index : 2295 transactions par seconde
• avec index : 2020 transactions par seconde

Pour ces requêtes, les parcours de données sont :

• sans index : 1101750 SeqScan, ce qui est normal
• avec index : 278083 SeqScan et 691521 IdxScan (total 969604), donc les index sont effectivement utilisés.

Puis, le pourcentage de « bloat » mesuré, pour la table :

• sans index : 27,9%
• avec index : 87,2%

Les index contiennent eux aussi beaucoup d’espace perdu, dans les mêmes proportions que la table. Toutefois, ces pourcentages dépendent beaucoup de l’efficacité de l’autovacuum et peuvent varier. Le volume de la table est donc bien plus important en présence d’index :

• sans index : 384 kB
• avec index : 1984 kB + 6928 kB pour les index (total : 8912 kB)

soit un rapport de plus de 20 entre les deux.

En termes de performance, les temps moyens d’exécution des requêtes SELECT et UPDATE sont aussi en faveur de l’absence d’index :

• sans index : 0.72 milliseconde
• avec index : 0.82 milliseconde

ainsi, même avec des parcours d’index optimisant les lectures, les temps sont légèrement plus longs. En effet, le « bloat » étant beaucoup plus important, le nombre de pages de données à lire est lui aussi beaucoup plus important.

Le nombre de pages de données lues en mémoire en témoigne :

• sans index : 47912065 pages de données lues
• avec index : 86424826 pages de données lues

donc, presque le double de pages de données lues en mémoire, ce qui implique une consommation mémoire plus importante.

Enfin, en termes d’écriture, l’extension pg_stat_statements de la version 13 de PostgreSQL ajoute la quantité de données écrite dans les journaux de transactions :

• sans index : 178 MB
• avec index : 251 MB

Conclusion

Le tableau suivant reprend les éléments ci-dessus :

Le résultat est sans appel, pour ce cas d’usage, la présence d’index n’améliore pas les performances et amène un volume beaucoup plus important, en lecture comme en écriture.

Il faut bien sûr pondérer cela, par exemple lorsqu’un index implémente une contrainte d’unicité, ou lorsque la table fait partie d’un modèle plus large où ces index apportent la performance nécessaire à d’autres requêtes, plus critiques.

De plus, une autre possibilité existe : lorsque seul un petit nombre de champs doit être mis à jour, il est souvent plus efficace de les mettre dans une table de faits, dédiés à l’insertion de ces nouvelles valeurs, qui sont par la suite jointes et agrégées.