On Tue, Jul 16, 2013 at 10:33 AM, Andrew Dunstan <andrew@xxxxxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
