From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Andrew Dunstan <andrew(at)dunslane(dot)net> |
Cc: | Niels Kristian Schjødt <nielskristian(at)autouncle(dot)com>, "pgsql-performance(at)postgresql(dot)org list" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Hstore VS. JSON |
Date: | 2013-07-16 15:47:18 |
Message-ID: | CAHyXU0wB7sVQnmJ=Mw-Qdg_z3A6nLCB4CPyrpQEHTr4oWfyKOg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Brian Fehrle | 2013-07-16 16:51:42 | General key issues when comparing performance between PostgreSQL and oracle |
Previous Message | Andrew Dunstan | 2013-07-16 15:33:28 | Re: Hstore VS. JSON |