postgresql hstore key/value vs traditional SQL performance

Question

I need to develop a key/value backend, something like this:

Table T1 id-PK, Key - string, Value - string
INSERT into T1('String1', 'Value1')
INSERT INTO T1('String1', 'Value2')

Table T2 id-PK2, id2->external key to id
some other data in T2, which references data in T1 (like users which have those K/V etc)

I heard about PostgreSQL hstore with GIN/GIST. What is better (performance-wise)? Doing this the traditional way with SQL joins and having separate columns(Key/Value) ? Does PostgreSQL hstore perform better in this case?

The format of the data should be any key=>any value. I also want to do text matching e.g. partially search for (LIKE % in SQL or using the hstore equivalent). I plan to have around 1M-2M entries in it and probably scale at some point.

What do you recommend ? Going the SQL traditional way/PostgreSQL hstore or any other distributed key/value store with persistence?

If it helps, my server is a VPS with 1-2GB RAM, so not a pretty good hardware. I was also thinking to have a cache layer on top of this, but I think it rather complicates the problem. I just want good performance for 2M entries. Updates will be done often but searches even more often.

Thanks.

Answer 1

Your question is unclear because your not clear about your objective.

The key here is the index (pun intended) - if your dealing with a large amount of keys you want to be able to retrieve them with a the least lookups and without pulling up unrelated data.

Short answer is you probably don't want to use hstore, but lets look into more detail...

• Does each id have many key/value pairs (hundreds+)? Don't use hstore.
• Will any of your values contain large blocks of text (4kb+)? Don't use hstore.
• Do you want to be able to search by keys in wildcard expressions? Don't use hstore.
• Do you want to do complex joins/aggregation/reports? Don't use hstore.
• Will you update the value for a single key? Don't use hstore.
• Multiple keys with the same name under an id? Can't use hstore.

So what's the use of hstore? Well, one good scenario would be if you wanted to hold key/value pairs for an external application where you know you always want to retrive all key/values and will always save the data back as a block (ie, it's never edited in-place). At the same time you do want some flexibility to be able to search this data - albiet very simply - rather than storing it in say a block of XML or JSON. In this case since the number of key/value pairs are small you save on space because your compressing several tuples into one hstore.

Consider this as your table:

CREATE TABLE kv (
  id /* SOME TYPE */ PRIMARY KEY,
  key_name TEXT NOT NULL,
  key_value TEXT,
  UNIQUE(id, key_name)
);