Re: Hstore VS. JSON

On Tue, Jul 16, 2013 at 10:33 AM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>
> On 07/16/2013 11:05 AM, Niels Kristian Schjødt wrote:
>>
>> Hi,
>>
>> I'm in the process of implementing a table for storing some raw data in
>> the format of a hash containing one level of keys and values. The hash can
>> be quite big (up to 50 keys pointing at values varying from one to several
>> hundred characters)
>>
>> Now, I'm in doubt whether to use JSON or Hstore for this task. Here is the
>> facts:
>>
>> - I'm not going to search a lot (if any) in the data stored in the column,
>> i'm only going to load it out.
>> - The data is going to be heavily updated (not only inserted). Keys and
>> values are going to be added/overwritten quite some times.
>
>
>
> In both cases, each hstore/json is a single datum, and updating it means
> writing out the whole datum - in fact the whole row containing the datum.
>
>
>> - My database's biggest current issue is updates, so i don't want that to
>> be a bottle neck.
>> - I'm on postgresql 9.2
>>
>> So, question is: Which will be better performance wise, especially for
>> updates? Does the same issues with updates on the MVCC structure apply to
>> updates in Hstore? What is taking up most space on the HDD?
>>
>
> MVCC applies to all updates on all kinds of data. Hstore and JSON are not
> different in this respect.
>
> You should test the storage effects with your data. On 9.2 for your data
> hstore might be a better bet, since in 9.2 hstore has more operators
> available natively.

yeah.

hstore pros:
*) GIST/GIN access
*) good searching operators, in particular @>

json pros:
*) nested, supports fancier structures
*) limited type support (json numerics, etc)

I don't know which is more compact when storing similar data. My
guess is they are pretty close. Since json is something of a standard
for data serialization though I expect it will largely displace hstore
over time.

merlin