Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ubjWX-004YGl-Nw for pgsql-general@arkaria.postgresql.org; Tue, 15 Jul 2025 17:33:01 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1ubjWV-003yHV-QZ for pgsql-general@arkaria.postgresql.org; Tue, 15 Jul 2025 17:33:00 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ubjWV-003yHM-Au for pgsql-general@lists.postgresql.org; Tue, 15 Jul 2025 17:33:00 +0000 Received: from mail-lf1-x134.google.com ([2a00:1450:4864:20::134]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1ubjWU-007RKX-0G for pgsql-general@lists.postgresql.org; Tue, 15 Jul 2025 17:32:58 +0000 Received: by mail-lf1-x134.google.com with SMTP id 2adb3069b0e04-5561d41fc96so6037162e87.1 for ; Tue, 15 Jul 2025 10:32:57 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1752600776; x=1753205576; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=g6YqI9uGRGRlqBDe+3o4YZXTCO4Zahmv5U4geEO6dCY=; b=P2Tb1IFEII0OJyzfTwxcJE+vFEzL3aXz0o/guuelInt3GldK4wzrGzcQz0WowPZgwP i+trLO9KY8z5lhCG8+5N/yf30IwpAyCEQZfO6E6qVwv+pDFfgjtTLbvSJpl1hbTssCmU XJ/6IzSZ8HVYw729xSfLaDi3RU95QjdqSHpYo8XLl4Omqj3xb0dO9yPIdLoRThIZ5Y8n 176+eiydDepVEGEwp6YnD3AJyHKgH/gHlpoWPXnnZ7BDMWgqRHIipfroGSL+v5U5DY9c 4XhvqxBsxQ+5zK4E7JK/cZk2wn5EMX9lfKVQcbniZxS1vxN1YQA3d5PRShaSHvV7ckSu FoVw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1752600776; x=1753205576; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=g6YqI9uGRGRlqBDe+3o4YZXTCO4Zahmv5U4geEO6dCY=; b=ANkR+h7CM6IcXQW69C7lvT3Wj/JBfcMZyoq+YwOHM4orFKkUaIe0/Q8MUAD/ry5jww RmcCFcens+RjJDM4028VvKJrx+3hKNdsMKfvIemdN9x3k7O4GTYHzdP8rsUsm0xUVo44 uQ11nx3SU6qSnOojF50rkG7c8bfuByA7Hy+t7MH9rqiXDY/COxp1yKshNrpkjzhhtC1Z G/5ztatPOZTLoasjtBT7SmmW6qoHuZ1zQ6sX4+fzF3PGTRvqtjFDbEwizyh7o3wv2Nkc dUJSQ6vDt2P5AIwg89tg/msXhF4g0wPfDiupiclgOdGJYqq/x4C3o9YSCojvmCN693A7 DB3A== X-Forwarded-Encrypted: i=1; AJvYcCVNL9vFFo9P8dYRpvPBCiUfIB5peis7xVp1B4VbE1BHLxbEAs8f/WDkfgjd4O3S6ihyQEv7AlVWmxl/koPg@lists.postgresql.org X-Gm-Message-State: AOJu0YxjfRMARrorXm6zmBAiRKDCrSJCab1V/xtZj0U3n4lzZOOzQImB zb+g8DfIy/T+fzj7vWEYQhtfhzd1HXtLvfQlTCMLNM12CGnCI9gx99Isslq0OAFNri7F8GXEg4J 8J6Nb7iZvhfpseQIJowLx7XluEAG/9PGqwv8554G8mQ== X-Gm-Gg: ASbGncvUdT+8+bDL877qonPDIUCdzP5f7CF348VnaRZseGTkN29JP9mBtmBryZYnRm1 rTMq3pCLWzhBGNQMnE4tDRJUxlxwCv30cEIqH72S2dvHGMcsRXmkDQnnDlyBFWAy5mTptGAZXh0 DG5W401b1nM2PvCW9dxZGV/w9gUoz1BTmeOnCTCp6NF5VZu0BQ4udLfLig63n7GP9jajItkVGyz h7m74E= X-Google-Smtp-Source: AGHT+IFl5Utd5putOC5cIChi4hXx9uOUrgvpw/Uhxd6BToEXSPD49krjG/OTqq4ec/TngsDR9B0OGclXrisCI8x0tRw= X-Received: by 2002:a05:6512:3d88:b0:553:24bb:daa1 with SMTP id 2adb3069b0e04-55a2330242dmr142640e87.11.1752600776074; Tue, 15 Jul 2025 10:32:56 -0700 (PDT) MIME-Version: 1.0 References: <8efa554c-ba95-43d5-953c-def0d53dca9e@aklaver.com> In-Reply-To: From: Merlin Moncure Date: Tue, 15 Jul 2025 11:32:42 -0600 X-Gm-Features: Ac12FXw5xO6JNcsVOwXPAvLkOtApb8QRQq7woiWSP784jNS-_WRoNkNWWJ7dzHI Message-ID: Subject: Re: Performance of JSON type in postgres To: "David G. Johnston" Cc: Adrian Klaver , veem v , pgsql-general Content-Type: multipart/alternative; boundary="000000000000b0eda30639fb2a2e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000b0eda30639fb2a2e Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, Jul 14, 2025 at 2:01=E2=80=AFPM David G. Johnston < david.g.johnston@gmail.com> wrote: > On Mon, Jul 14, 2025 at 12:54=E2=80=AFPM Adrian Klaver > wrote: > >> On 7/14/25 12:51, veem v wrote: >> > So I want to >> > understand the experts' opinion on this which I believe will be >> > crucial during design itself. >> >> It is spelled out here: >> >> https://www.postgresql.org/docs/current/datatype-json.html >> >> > I've taken to heart the main takeaway from that page: > > "In general, most applications should prefer to store JSON data as jsonb, > unless there are quite specialized needs, such as legacy assumptions abou= t > ordering of object keys." > I don't think the documentation is accurate at all, unless one of those specialized needs is to 'be faster'. json serialization is more than 2x faster based on simple testing (see below). This is absolutely not a trivial difference. I would say, use json for serialization, use jsonb for data storage, unless the precise structure of the input document is important. merlin leaselock_iam@leaselock_prod=3D> explain analyze select json_agg(l) from ( select l from llcore.lease l limit 10000) q; QUERY PLAN ---------------------------------------------------------------------------= --------------------------------------------------- Aggregate (cost=3D405.52..405.53 rows=3D1 width=3D32) (actual time=3D69.043..69.048 rows=3D1 loops=3D1) -> Limit (cost=3D0.00..380.52 rows=3D10000 width=3D247) (actual time=3D0.017..9.764 rows=3D10000 loops=3D1) -> Seq Scan on lease l (cost=3D0.00..100383.89 rows=3D2638089 width=3D247) (actual time=3D0.016..8.831 rows=3D10000 loops=3D1) Planning Time: 0.109 ms Execution Time: 69.088 ms (5 rows) Time: 160.560 ms leaselock_iam@leaselock_prod=3D> explain analyze select jsonb_agg(l) from ( select l from llcore.lease l limit 10000) q; QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------- Aggregate (cost=3D405.52..405.53 rows=3D1 width=3D32) (actual time=3D146.139..146.141 rows=3D1 loops=3D1) -> Limit (cost=3D0.00..380.52 rows=3D10000 width=3D247) (actual time=3D0.017..20.837 rows=3D10000 loops=3D1) -> Seq Scan on lease l (cost=3D0.00..100383.89 rows=3D2638089 width=3D247) (actual time=3D0.016..19.975 rows=3D10000 loops=3D1) Planning Time: 0.108 ms Execution Time: 152.277 ms --000000000000b0eda30639fb2a2e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Mon, Jul 14, 2025 at 2:01=E2=80=AFPM D= avid G. Johnston <david.g.= johnston@gmail.com> wrote:
On Mon, Jul 14, 202= 5 at 12:54=E2=80=AFPM Adrian Klaver <adrian.klaver@aklaver.com> wrote:=
On 7/14/25 12:51, veem v wrote:
>=C2=A0So I = want to
> understand the experts' opinion on this which=C2=A0I believe=C2=A0= will=C2=A0 be
> crucial=C2=A0during design itself.

It is spelled out here:

https://www.postgresql.org/docs/current/d= atatype-json.html


I've taken to heart the main takeaw= ay from that page:

"In= general, most applications should prefer to store JSON data as jsonb, unle= ss there are quite specialized needs, such as legacy assumptions about orde= ring of object keys."

I don't think the=C2=A0documentation is accurate at all, unless one o= f those specialized needs is to 'be faster'.=C2=A0 =C2=A0json seria= lization is more than 2x faster based on simple testing (see below).=C2=A0 = =C2=A0This is absolutely not a trivial difference.

I would say, use json for serialization, use jsonb for data storage, unles= s the precise structure of the input document is important.

<= /div>
merlin

leaselock_iam@leaselock_prod=3D&g= t; explain analyze select json_agg(l) from ( select l from llcore.lease l l= imit 10000) q;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Q= UERY PLAN
--------------------------------------------------------------= ----------------------------------------------------------------
=C2=A0A= ggregate =C2=A0(cost=3D405.52..405.53 rows=3D1 width=3D32) (actual time=3D6= 9.043..69.048 rows=3D1 loops=3D1)
=C2=A0 =C2=A0-> =C2=A0Limit =C2=A0(= cost=3D0.00..380.52 rows=3D10000 width=3D247) (actual time=3D0.017..9.764 r= ows=3D10000 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Seq= Scan on lease l =C2=A0(cost=3D0.00..100383.89 rows=3D2638089 width=3D247) = (actual time=3D0.016..8.831 rows=3D10000 loops=3D1)
=C2=A0Planning Time:= 0.109 ms
=C2=A0Execution Time: 69.088 ms
(5 rows)

Time: 160.5= 60 ms
leaselock_iam@leaselock_prod=3D> explain analyze select jsonb_a= gg(l) from ( select l from llcore.lease l limit 10000) q;
=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 QUERY PLAN
-------------------= ---------------------------------------------------------------------------= ---------------------------------
=C2=A0Aggregate =C2=A0(cost=3D405.52..= 405.53 rows=3D1 width=3D32) (actual time=3D146.139..146.141 rows=3D1 loops= =3D1)
=C2=A0 =C2=A0-> =C2=A0Limit =C2=A0(cost=3D0.00..380.52 rows=3D1= 0000 width=3D247) (actual time=3D0.017..20.837 rows=3D10000 loops=3D1)
= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Seq Scan on lease l =C2=A0(co= st=3D0.00..100383.89 rows=3D2638089 width=3D247) (actual time=3D0.016..19.9= 75 rows=3D10000 loops=3D1)
=C2=A0Planning Time: 0.108 ms
=C2=A0Execut= ion Time: 152.277 ms
=C2=A0
--000000000000b0eda30639fb2a2e--