CODEWITHSUNDEEP
postgresqlhstore
Natalie Perret
Natalie Perret

Reputation: 8997

Benefits of hstore over a key/value table with PostgreSQL?

In one of our applications, we need to have some key/value pairs that we will periodically update.

We initially thought about a table which would be something like:

CREATE TABLE "KeyValue" (
    "Key"   TEXT UNIQUE,
    "Value" TEXT,
    PRIMARY KEY("Key")
);

Which perfectly fits our needs.

But while digging if there was something already builtin I found out about the hstore module, but I can't really get my head around about the benefits of using that hstore instead of my old-fashion table.

In addition the syntax seems a bit weird to me, when looking at examples I can't find a simple example where it would be explained how to create a pair (e.g. "StartDate": "2012/12/25"), read it (e.g. "2012/12/25"), then update it ("StartDate": "2012/12/26"), and potentially delete it.

Is there someone who could explain the benefits of using the hstore approach over the traditional table I just made up above?

Upvotes: 3

Views: 2377

Answers (1)

joonas
joonas

Reputation: 135

I've used hstore previously through Hibernate/Java way back. I did it because the total size of the per row map was small, but the table like you describe it with row specific key-value pairs would had had really many rows. I might be mistaken but I recall the hstore may have originally been designed for small maps but there is no longer mention of such in the documentation pages. Also, my requirements for the hstore column were only related to the row, any "reporting" which was done for all of the column values was for development only.

Though, looking at your example table, it would appear you are looking for globally unique keys where as I think hstore is most useful when you have one hstore per row. I don't think you should solve the "globally unique keys" with a hstore and some index while it's possible you could.

Note that you linked to an unsupported version of the postgresql, here's the latest hstore documentation. It does have examples of instantiation (SELECT 'a=>1,a=>2'::hstore; or hstore('c', '3')) and the examples section contains the rest of the examples you asked.

It's been a while since I last developed or maintained anything on postgresql, but what I remember, the json support was coming up fast and looked interesting. If you are storing anything except opaque strings in your Value column, it might be worth your while to look at the json support as it can support the json datatypes with some limitations.

Upvotes: 4

Related Questions