`CLUSTER`¶

La commande CLUSTER dans PostgreSQL permet de réorganiser physiquement les données d’une table en suivant l’ordre d’un index spécifié. Cette opération améliore les performances des requêtes, notamment les lectures séquentielles, en réduisant la fragmentation des données. Cependant, elle nécessite un verrouillage exclusif de la table, ce qui la rend temporairement indisponible pour les autres opérations. CLUSTER peut être utilisé ponctuellement pour optimiser les tables fréquemment lues. L’opération peut être coûteuse en termes de temps et de ressources, il est donc recommandé de l’exécuter pendant les périodes de faible activité. Il est également possible de configurer l’exécution automatique de CLUSTER via les paramètres de maintenance.

Comment savoir si un index est “clusterisée” ?¶

La commande CLUSTER n’est pas permamente, c’est à dire que l’ordre des pages données est modifiée dès lors qu’une commande INSERT ou UPDATE est effectuée.

Il est donc important de savoir quelle est la fréquence de modification de la table et de pouvoir mesurer avec quelle régularité la commande CLUSTER doit être lancée.

Pour cela un solution naïve consiste à surveiller le nombre d’UPDATE et d’INSERT mais il existe une méthode plus subtile. En effet PostgreSQL mesure la corrélation statistique entre l’ordre physique des lignes et l’ordre logique des valeurs de la colonne :

Si cet corrélation vaut 1 alors cela signifie que la table est triée selon l’ordre des valeurs de la colonne ;
Si la corrélation est à -1 cela signifie que la table est triée dans l’ordre inverse des valeurs de la colonne ;
Si la valeur est proche de 0, il n’y a aucune corrélation entre l’ordre des valeurs et l’organisation de données sur le disque.

Pour se faire, il faudra lancer la commande ANALYZE sur les tables à monitorer, cela aura pour effet de mettre à jour les statistiques des tables.

Voici comment obtenir le coefficient de corrélation pour une colonne :

ANALYZE ma_table;
SELECT p.correlation
FROM pg_stats p
WHERE p.tablename = 'ma_table'
    AND p.attname = 'ma_colonne';

À partir de cette information il est possible de construire une vue pour afficher le taux de corrélation des tables clusterisées de la base courante.

Par exemple :

CREATE OR REPLACE VIEW v_cluster_correlation
AS
SELECT  s.relname AS relation,
    s.indexrelname AS INDEX,
    a.attname AS COLUMN,
    p.correlation AS correlation
FROM pg_stats p, pg_stat_user_indexes s, pg_index i, pg_attribute a
WHERE i.indisclustered
    AND s.indexrelid = i.indexrelid
    AND p.tablename = s.relname
    AND a.attnum = ANY (indkey)
    AND a.attrelid = i.indrelid
    AND p.attname = a.attname
ORDER BY s.relname, s.indexrelname;

Créons d’abord une table t1 :

CREATE TABLE t1 (c1 TEXT);
ALTER TABLE t1 ADD CONSTRAINT idx_c1 PRIMARY KEY (c1);

Nous marquons l’index comme clustered :

CLUSTER idx_c1 ON t1;

Nous insérons maintenant les données dans un ordre aléatoire :

INSERT INTO t1 VALUES('1');
INSERT INTO t1 VALUES('42');
INSERT INTO t1 VALUES('58');
INSERT INTO t1 VALUES('5');

Puis nous lançons l’ANALYZE pour mettre à jour les statistiques :

ANALYZE;

Et enfin nous pouvons constater la corrélation :

SELECT * FROM v_cluster_correlation;
 relation | index  | column | correlation 
----------+--------+--------+-------------
 t1       | idx_c1 | c1     |         0.8
(1 row)

Nous pouvons constater que la valeur de colonne correlation n’est pas égale à 1. Cela nous indique donc que le tri n’est pas fait par ordre croissant.

Lançons maintenant la commande CLUSTER et ANALYZE pour constater la corrélation :

CLUSTER idx_c1 ON t1;
ANALYZE;
SELECT * FROM v_cluster_correlation;
 relation | INDEX  | COLUMN | correlation
----------+--------+--------+-------------
 t1       | idx_id | id     |       1
(1 ligne)

La corrélation est à 1 donc nous avons un index parfaitement trié par ordre croissant.

CLUSTER¶

Comment savoir si un index est “clusterisée” ?¶

`CLUSTER`¶