MIME-Version: 1.0
References: <CAB+=1TU2bi8UkdD94kMizsTrTBUgqbdWKtM4Lq67+BMi6Vt1qA@mail.gmail.com>
 <8efa554c-ba95-43d5-953c-def0d53dca9e@aklaver.com> <CAKFQuwarbmz7=yg14h6u-UhGPfbT56TsX8S6w9wVdMHGiWxbYg@mail.gmail.com>
 <CAHyXU0xQGXFBZ10GtqTkXL3_b8FbB79qP+XS2XCfxp+6WuH1Cg@mail.gmail.com>
 <CAB+=1TWwerQMVQvOP0oAVunTUz8tnX425Jn-84DBYbOD+us9Eg@mail.gmail.com>
 <289f005c-4a80-4098-bf58-f53e06bb7f8c@aklaver.com> <CAB+=1TVy8p8dAaTECQS+2+S-zrcVTtmV2bqBUPTTR2UAW+9=Qg@mail.gmail.com>
In-Reply-To: <CAB+=1TVy8p8dAaTECQS+2+S-zrcVTtmV2bqBUPTTR2UAW+9=Qg@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Sat, 19 Jul 2025 17:31:35 -0400
Message-ID: <CANzqJaCj2Lsy_W2CUts2yYsQVkBRS1gLeXOPPqUeF_dX1mZVaw@mail.gmail.com>
Subject: Re: Performance of JSON type in postgres
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000003a31e6063a4ef83c"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaCj2Lsy_W2CUts2yYsQVkBRS1gLeXOPPqUeF_dX1mZVaw%40mail.gmail.com>
Precedence: bulk

--0000000000003a31e6063a4ef83c
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Sat, Jul 19, 2025 at 5:19=E2=80=AFPM veem v <veema0000@gmail.com> wrote:

>
> On Sun, 20 Jul 2025 at 02:29, Adrian Klaver <adrian.klaver@aklaver.com>
> wrote:
>
>> On 7/19/25 13:39, veem v wrote:
>> >
>>
>> I thought you are answered that with your tests above? At least for the
>> Postgres end. As to the Snowflake end you will need to do comparable
>> tests for fetching the data from Postgres and transforming it.
>>
>> Thank you Adrian.
> Yes will try to test the load from postgres to snowflake to see if any
> specific format makes a difference in such a situation(mainly considering
> JSONB seems postgres native only).
>
> Additionally I am unable to test upfront, but few teammates are saying
> below. Are these really true? Wants to know from experts here,
>
> 1)The lack of detailed statistics on data distribution within JSONB
> columns can hinder the query planner from making optimal choices, sometim=
es
> leading to slower execution or a reliance on sequential scans even when
> indexes exist. Storing extensive or deeply nested structures within a
> single JSONB document can lead to document bloat.
>
> 2)Loss of Formatting and Order: The binary format of JSONB doesn't
> preserve the original order of keys, whitespace, or duplicate keys in the
> JSON input.
>
> 3)Lack of Type Safety and Schema Enforcement: JSONB provides no inherent
> schema or type validation. This means you can easily insert inconsistent
> data types for the same key across different rows, making data management
> and querying challenging.
>
> 4)No Native Foreign Key Support: You cannot directly define foreign key
> constraints within a JSONB column to enforce referential integrity with
> other tables.
>

The answer to all of these questions is: normalize your data structures.

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

--0000000000003a31e6063a4ef83c
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Sat, Jul 19, 2025 at 5:19=E2=80=AFPM v=
eem v &lt;<a href=3D"mailto:veema0000@gmail.com">veema0000@gmail.com</a>&gt=
; wrote:</div><div class=3D"gmail_quote gmail_quote_container"><blockquote =
class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px sol=
id rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr"><br=
></div><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On =
Sun, 20 Jul 2025 at 02:29, Adrian Klaver &lt;<a href=3D"mailto:adrian.klave=
r@aklaver.com" target=3D"_blank">adrian.klaver@aklaver.com</a>&gt; wrote:<b=
r></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex=
;border-left:1px solid rgb(204,204,204);padding-left:1ex">On 7/19/25 13:39,=
 veem v wrote:<br>
&gt; <br><br>
I thought you are answered that with your tests above? At least for the <br=
>
Postgres end. As to the Snowflake end you will need to do comparable <br>
tests for fetching the data from Postgres and transforming it.<br><br></blo=
ckquote><div>Thank you Adrian.</div><div>Yes will try to test the load from=
 postgres to snowflake to see if any specific format makes a difference in =
such a situation(mainly considering JSONB seems postgres native only).</div=
><br>Additionally I am unable to test upfront, but few teammates are saying=
 below. Are these really true? Wants to know from experts here,<br><br>1)Th=
e lack of detailed statistics on data distribution within JSONB columns can=
 hinder the query planner from making optimal choices, sometimes leading to=
 slower execution or a reliance on sequential scans even when indexes exist=
. Storing extensive or deeply nested structures within a single JSONB docum=
ent can lead to document bloat.<br><br>2)Loss of Formatting and Order:=C2=
=A0The binary format of JSONB doesn&#39;t preserve the original order of ke=
ys, whitespace, or duplicate keys in the JSON input.<br><br>3)Lack of Type =
Safety and Schema Enforcement:=C2=A0JSONB provides no inherent schema or ty=
pe validation. This means you can easily insert inconsistent data types for=
 the same key across different rows, making data management and querying ch=
allenging.<br><br>4)No Native Foreign Key Support:=C2=A0You cannot directly=
 define foreign key constraints within a JSONB column to enforce referentia=
l integrity with other tables.</div></div></blockquote><div>=C2=A0</div></d=
iv><div>The answer to all of these questions is: normalize your data struct=
ures.</div><div><br></div><span class=3D"gmail_signature_prefix">-- </span>=
<br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">Death to &l=
t;Redacted&gt;, and butter sauce.<div>Don&#39;t boil me, I&#39;m still aliv=
e.<br><div><div>&lt;Redacted&gt; lobster!</div></div></div></div></div></di=
v>

--0000000000003a31e6063a4ef83c--