Re: KVP table vs. hstore - hstore performance (Was: Postgres NoSQL emulation) - Mailing list pgsql-performance

From Jim Nasby
Subject Re: KVP table vs. hstore - hstore performance (Was: Postgres NoSQL emulation)
Date
Msg-id 1AE9B4D5-9369-4A58-9041-55AC7A1061CC@nasby.net
Whole thread Raw
In response to Re: KVP table vs. hstore - hstore performance (Was: Postgres NoSQL emulation)  (Merlin Moncure <mmoncure@gmail.com>)
Responses Re: KVP table vs. hstore - hstore performance (Was: Postgres NoSQL emulation)  (Stefan Keller <sfkeller@gmail.com>)
List pgsql-performance
On May 16, 2011, at 8:47 AM, Merlin Moncure wrote:
> On Sat, May 14, 2011 at 5:10 AM, Stefan Keller <sfkeller@gmail.com> wrote:
>> Hi,
>>
>> I am conducting a benchmark to compare KVP table vs. hstore and got
>> bad hstore performance results when the no. of records is greater than
>> about 500'000.
>>
>> CREATE TABLE kvp ( id SERIAL PRIMARY KEY, key text NOT NULL, value text );
>> -- with index on key
>> CREATE TABLE myhstore ( id SERIAL PRIMARY KEY, obj hstore NOT NULL );
>> -- with GIST index on obj
>>
>> Does anyone have experience with that?
>
> hstore is not really designed for large-ish sets like that.

And KVP is? ;)

IIRC hstore ends up just storing everything as text, with pointers to know where things start and end. There's no real
indexinginside hstore, so basically the only thing it can do is scan the entire hstore. 

That said, I would strongly reconsider using KVP for anything except the most trivial of data sets. It is *extremely*
inefficient.Do you really have absolutely no idea what *any* of your keys will be? Even if you need to support a
certainamount of non-deterministic stuff, I would put everything you possibly can into real fields and only use KVP or
hstorefor things that you really didn't anticipate. 

Keep in mind that for every *value*, your overhead is 24 bytes for the heap header, 2+ varlena bytes in the heap, plus
thelength of the key. In the index you're looking at 6+ bytes of overhead, 1+ byte for varlena, plus the length of the
key.The PK will cost you an additional 16-24 bytes, depending on alignment. So that's a *minimum* of ~50 bytes per
value,and realistically the overhead will be closer to 65-70 bytes, *per value*. Unless your values are decent-sized
strings,the overhead is going to be many times larger than the actual data! 
--
Jim C. Nasby, Database Architect                   jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net



pgsql-performance by date:

Previous
From: Jim Nasby
Date:
Subject: Re: reducing random_page_cost from 4 to 2 to force index scan
Next
From: Stefan Keller
Date:
Subject: Re: KVP table vs. hstore - hstore performance (Was: Postgres NoSQL emulation)