pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedHstore VS. JSON
3+ messages / 3 participants
[nested] [flat]
* Hstore VS. JSON
@ 2013-07-16 15:05 Niels Kristian Schjødt <nielskristian@autouncle.com>
2013-07-16 15:33 ` Re: Hstore VS. JSON Andrew Dunstan <andrew@dunslane.net>
0 siblings, 1 reply; 3+ messages in thread
From: Niels Kristian Schjødt @ 2013-07-16 15:05 UTC (permalink / raw)
To: pgsql-performance
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.
- 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?
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Hstore VS. JSON
2013-07-16 15:05 Hstore VS. JSON Niels Kristian Schjødt <nielskristian@autouncle.com>
@ 2013-07-16 15:33 ` Andrew Dunstan <andrew@dunslane.net>
2013-07-16 15:47 ` Re: Hstore VS. JSON Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Andrew Dunstan @ 2013-07-16 15:33 UTC (permalink / raw)
To: Niels Kristian Schjødt <nielskristian@autouncle.com>; +Cc: pgsql-performance
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.
cheers
andrew
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Hstore VS. JSON
2013-07-16 15:05 Hstore VS. JSON Niels Kristian Schjødt <nielskristian@autouncle.com>
2013-07-16 15:33 ` Re: Hstore VS. JSON Andrew Dunstan <andrew@dunslane.net>
@ 2013-07-16 15:47 ` Merlin Moncure <mmoncure@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Merlin Moncure @ 2013-07-16 15:47 UTC (permalink / raw)
To: Andrew Dunstan <andrew@dunslane.net>; +Cc: Niels Kristian Schjødt <nielskristian@autouncle.com>; pgsql-performance
On Tue, Jul 16, 2013 at 10:33 AM, Andrew Dunstan <andrew@dunslane.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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2013-07-16 15:47 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-07-16 15:05 Hstore VS. JSON Niels Kristian Schjødt <nielskristian@autouncle.com>
2013-07-16 15:33 ` Andrew Dunstan <andrew@dunslane.net>
2013-07-16 15:47 ` Merlin Moncure <mmoncure@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox