Last Updated on February 8, 2020

hstore is a PostgreSQL extension that adds support for a key/value data type, and a set of operator and functions that are useful to work with key/value logics. In practice, hstore makes it possible to store key/value pairs in relational tables. This article is a practical introduction to hstore.

Use cases

If a table has an hstore column, every row can have a set of key/value pairs. You can use them as attributes that don’t have to be the same for all the rows. In other words, the schemaful relational data model becomes partially schemaless when we have hstore columns.

But we will see that key/value pair don’t have to be completely schemaless. Thanks to CHECK constraints and UNIQUE constraints, we can define precise rules about the keys and their values. If we do this, hstore becomes an alternative to table inheritance for storing records with some common columns in the same table. We may want to do this, for example, to store a catalogue of products with some common attributes (name, description, price…) plus a set of attributes that depend on the product type (a car and a telly have indeed different characteristics).

An hstore tutorial

Let’s explore hstore with an introductory tutorial.

Installing hstore

CREATE SCHEMA hstore_example;
SET search_path TO hstore_example;
CREATE EXTENSION hstore SCHEMA hstore_example;

As you can see, we installed hstore with CREATE EXTENSION and we bounded it to the hstore_example schema.

Creating hstore columns

We have now an hstore data type. We can create hstore columns just like we create any other columns, they have nothing special.

CREATE TABLE product (
    id SERIAL,
    name VARCHAR(50) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    type VARCHAR(20) NOT NULL,
    characteristics HSTORE NOT NULL,
    PRIMARY KEY (id)
);

Any number of hstore columns is allowed, an hstore column default value can be specified, hstore arrays are permitted, and so on.

Indexes on hstore

At this point we need to index hstore columns:

CREATE INDEX idx_characteristics
    ON product
    USING GIN
    (characteristics)
;

To index hstore columns we canhstore use GIN or GIST indexes. As a general rule, GIN is usually sensibly faster for reads, but depending on your workload sometimes it could even be slower. Modifying GIN index is slower than modifying a GIN index. If you are not satisfied with your performance, it may be worth testing your workload with both types of indexes.

More about indexes after we discuss the basic hstore operations.

Inserting hstore data

Let’s see the syntax we need to insert some test data:

INSERT INTO product
        (name, price, type, characteristics)
    VALUES
          ('Yellow Shirt', 15.00, 'shirt', 'size => "M", color => "yellow"')
        , ('White Shirt', 15.00, 'shirt', 'size => "S", color => "white"')
        , ('McPiper Biscuits', 4.50, 'shortbread', 'brand => "McPiper", flavor => "Original Highlander"')
        , ('Phone X', 65.00, 'phone', 'brand => "Acme", color => "white", weight => "200", os => "WarnerOS"')
        , ('Phone Y', 70.00, 'phone', 'brand => "HAL Phone", color => "black", weight => "150", os => "Odissey"')
        , ('Mysterious Object', 1.00, '?', '')
;

So the rules are simple:

• hstore values are written as strings;
• The string can be empty (we can avoid NULL here, see What does NULL mean in SQL?);
• Each K/V pair is written as 'key => "value"' ;
• Pairs are separated by commas;
• Spaces outside of values don’t matter, use them if you like them (I do).

Selecting hstore data

We can filter the rows based on its properties:

tutorial=# SELECT *
tutorial-#     FROM product
tutorial-#     WHERE type = 'phone' AND characteristics->'os' = 'Odissey'
tutorial-# ;
 id |  name   | price | type  |                             characteristics                              
----+---------+-------+-------+--------------------------------------------------------------------------
  5 | Phone Y | 70.00 | phone | "os"=>"Odissey", "brand"=>"HAL Phone", "color"=>"black", "weight"=>"150"
(1 row)

All properties are stored in hstore as strings. If we want to test them using the correct data type, we need to convertthem:

tutorial=# SELECT *
tutorial-#     FROM product
tutorial-#     WHERE type = 'phone' AND (characteristics->'weight')::Integer < 200
tutorial-# ;
 id |  name   | price | type  |                             characteristics                              
----+---------+-------+-------+--------------------------------------------------------------------------
  5 | Phone Y | 70.00 | phone | "os"=>"Odissey", "brand"=>"HAL Phone", "color"=>"black", "weight"=>"150"
(1 row)

We can select the rows that have a certain key:

tutorial=# SELECT *
tutorial-#     FROM product
tutorial-#     WHERE characteristics ? 'weight'
tutorial-# ;
 id |  name   | price | type  |                             characteristics                              
----+---------+-------+-------+--------------------------------------------------------------------------
  4 | Phone X | 65.00 | phone | "os"=>"WarnerOS", "brand"=>"Acme", "color"=>"white", "weight"=>"200"
  5 | Phone Y | 70.00 | phone | "os"=>"Odissey", "brand"=>"HAL Phone", "color"=>"black", "weight"=>"150"
(2 rows)

Many more operations are supported, but here we are only covering the most common cases.

Modifying hstore properties

We can completely overwrite an hstore value, just like any other value (numbers, strings…). But in most cases we only want to change, add or delete a specific property. Let’s see how to perform these operations.

Adding a new property or modifying an existing property is done the same way:

UPDATE product
    SET characteristics = characteristics || 'color => "grey"'
    WHERE id = 5
;
UPDATE product
    SET characteristics = characteristics || 'os_version => "10.0"'
    WHERE id = 4
;

To delete a property:

UPDATE product
    SET characteristics = delete(characteristics, 'weight')
    WHERE type = 'phone'
;

Advanced indexes

Let’s see some more advanced indexes, taking advantage of hstore operators and PostgreSQL native features.

Partial indexes

Indexes take space on disk. Bigger indexes can be slower than smaller indexes. If we don’t need to include certain values in an index, it is often a good thing to avoid that.

To only index rows that match a condition:

CREATE INDEX idx_characteristics_with_brand
    ON product
    USING GIN
    (characteristics)
    WHERE characteristics ? 'brand'
;

Functional indexes

The term functional index seems to have disappeared from PostgreSQL documentation some years ago, but I find it very clear (and correct: an expression without side effects is a function).

Here’s how to index a single property:

CREATE INDEX idx_characteristics_color
    ON product
    ((characteristics -> 'color')
;

To index the list of keys present in an hstore value:

CREATE INDEX idx_characteristics_keys
    ON product
    USING GIN
    ((skeys(characteristics)))
;

We are still using GIN because we are indexing an ARRAY.

UNIQUE indexes

It could make sense to build a UNIQUE index on a specific value:

CREATE UNIQUE INDEX unq_characteristics_shirt_color
    ON product
    ((characteristics -> 'color'))
    WHERE type = 'Shirt' 
;

It is also possible (but weird) to build an index on the whole hstore column, just use a normal BTREE:

CREATE UNIQUE INDEX unq_characteristics
    ON product
    (characteristics)
;

CHECK constraints

Adding CHECK constraints to an hstore column:

ALTER TABLE product
    ADD CONSTRAINT chk_characteristics_keys
        CHECK (characteristics->'type' <> 'phone' OR characteristics ? 'size'),
    ADD CONSTRAINT chk_characteristics_weight
        CHECK ((characteristics->'weight')::Integer BETWEEN 50 AND 500)
;

In this case we decide that all objects except for phones must have a size property; and we set a valid range for weight. I believe that CHECK constraints are extremely useful for hstore data.

Foreign keys

We cannot build foreign keys on hstore properties. While indexes can be built on expressions, foreign keys cannot.

References

• hstore documentation
• PostgreSQL anti-patterns: Unnecessary json/hstore dynamic columns, by 2nd Quadrant

Conclusions

Do you have experiences or thoughts to share? Did you find anything wrong in the article? Please comment!

Comments to my articles make this website not just one person’s texts, but also a more collective knowledge base.

Toodle pip,
Federico

Photo credit