Aller au contenu

Hstore et données non structurées

Principe

Il est fréquent de devoir stocker des données non structurées ou partiellement structurées dans une base de données. La méthode traditionnelle consiste à créer une table entité/clé/valeur. Cependant, cette solution est assez limitée car elle est à la fois difficile à interroger et très lente : les clés d’une même entité peuvent être disséminées dans toute la table des entités. De plus, ce modèle génère beaucoup de jointures, complexifie les requêtes CRUD et les rends difficile à optimiser.

Le module hstore apporte une solution à ce problème en introduisant un nouveau type, également nommé hstore, qui permet de stocker des paires clé/valeur dans un champ de table, l’entité étant évidemment l’enregistrement de la table.

L’indexation peut s’opérer avec des index btree, GiST ou GIN.

Note

hstore était très utile tant que le JSON était mal supporté par PostgreSQL. hstore reste intéressant de par sa simplicité, mais le JSON est de nos jours plus flexible et plus connu pour le même cas d’usage.

Exemple d’utilisation

Mise en place : 

CREATE EXTENSION hstore;
CREATE TABLE test2 (a hstore);
INSERT INTO test2 VALUES ('a=>1,b=>2');
INSERT INTO test2 VALUES('a=>1,b=>3');
INSERT INTO test2 VALUES('a=>1,b=>3,c=>4');
CREATE INDEX test2_gist ON test2 USING gist (a);
CREATE INDEX test2_btree ON test2 USING gist (a);

Prédicat indexable avec test2_btree : 

SELECT * FROM test2 WHERE b -> '2';

         a                                                                                                                                                                   
--------------------                                                                                                                                                         
 "a"=>"1", "b"=>"2"                                                                                                                                                          
(1 ligne)

Prédicats indexable avec l’indextest2_gist (ou un gin) : 

SELECT * FROM test2 WHERE a @> 'c=>4';

              a               
------------------------------
 "a"=>"1", "b"=>"3", "c"=>"4"
(1 ligne)

UPDATE test2 SET a=a||'d=>5'::hstore;
SELECT * FROM test2;

                   a                    
----------------------------------------
 "a"=>"1", "b"=>"2", "d"=>"5"
 "a"=>"1", "b"=>"3", "d"=>"5"
 "a"=>"1", "b"=>"3", "c"=>"4", "d"=>"5"

marc=# SELECT * FROM test2 WHERE a ? 'c';

                   a                    
----------------------------------------
 "a"=>"1", "b"=>"3", "c"=>"4", "d"=>"5"
(1 ligne)

Explications des opérateurs :

  • @> : opérateur d’inclusion (vérifie si l’élément de droite est contenu dans l’élément de gauche)
  • ? : vérifie la présence d’une clé
  • || : concatène deux valeurs de type hstore

Toutes ces opérations peuvent utiliser l’index gist défini dans l’exemple.

-> qui permet d’accès à une clé du hstore pour récupérer sa valeur, est indexable avec un btree.

Références