Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Uz7GM-0003jL-0i for pgsql-performance@arkaria.postgresql.org; Tue, 16 Jul 2013 15:34:02 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Uz7GL-0001yS-G3 for pgsql-performance@arkaria.postgresql.org; Tue, 16 Jul 2013 15:34:01 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Uz7GK-0001xR-94 for pgsql-performance@postgresql.org; Tue, 16 Jul 2013 15:34:00 +0000 Received: from relay5-d.mail.gandi.net ([217.70.183.197]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Uz7GD-0005fh-V6 for pgsql-performance@postgresql.org; Tue, 16 Jul 2013 15:33:59 +0000 Received: from mfilter18-d.gandi.net (mfilter18-d.gandi.net [217.70.178.146]) by relay5-d.mail.gandi.net (Postfix) with ESMTP id 6A58841C0B1; Tue, 16 Jul 2013 17:33:32 +0200 (CEST) X-Virus-Scanned: Debian amavisd-new at mfilter18-d.gandi.net Received: from relay5-d.mail.gandi.net ([217.70.183.197]) by mfilter18-d.gandi.net (mfilter18-d.gandi.net [10.0.15.180]) (amavisd-new, port 10024) with ESMTP id 9GehB2JckKFq; Tue, 16 Jul 2013 17:33:30 +0200 (CEST) X-Originating-IP: 98.27.58.255 Received: from [192.168.10.146] (cpe-098-027-058-255.nc.res.rr.com [98.27.58.255]) (Authenticated sender: adsend@dunslane.net) by relay5-d.mail.gandi.net (Postfix) with ESMTPSA id 4792041C073; Tue, 16 Jul 2013 17:33:30 +0200 (CEST) Message-ID: <51E567C8.8090909@dunslane.net> Date: Tue, 16 Jul 2013 11:33:28 -0400 From: Andrew Dunstan User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:17.0) Gecko/20130110 Thunderbird/17.0.2 MIME-Version: 1.0 To: =?ISO-8859-1?Q?Niels_Kristian_Schj=F8dt?= CC: "pgsql-performance@postgresql.org list" Subject: Re: Hstore VS. JSON References: <2BA3AA5A-A83A-4662-A850-4FA830C65A46@autouncle.com> In-Reply-To: <2BA3AA5A-A83A-4662-A850-4FA830C65A46@autouncle.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org On 07/16/2013 11:05 AM, Niels Kristian Schj=F8dt wrote: > Hi, > > I'm in the process of implementing a table for storing some raw data in t= he format of a hash containing one level of keys and values. The hash can b= e quite big (up to 50 keys pointing at values varying from one to several h= undred characters) > > Now, I'm in doubt whether to use JSON or Hstore for this task. Here is th= e 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 v= alues are going to be added/overwritten quite some times. In both cases, each hstore/json is a single datum, and updating it means=20 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 up= dates? Does the same issues with updates on the MVCC structure apply to upd= ates 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=20 not different in this respect. You should test the storage effects with your data. On 9.2 for your data=20 hstore might be a better bet, since in 9.2 hstore has more operators=20 available natively. cheers andrew --=20 Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance