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>
In-Reply-To: <289f005c-4a80-4098-bf58-f53e06bb7f8c@aklaver.com>
From: veem v <veema0000@gmail.com>
Date: Sun, 20 Jul 2025 02:49:14 +0530
Message-ID: <CAB+=1TVy8p8dAaTECQS+2+S-zrcVTtmV2bqBUPTTR2UAW+9=Qg@mail.gmail.com>
Subject: Re: Performance of JSON type in postgres
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: Merlin Moncure <mmoncure@gmail.com>, "David G. Johnston" <david.g.johnston@gmail.com>, 
	pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000001018c4063a4ecc1b"
Archived-At: <https://www.postgresql.org/message-id/CAB%2B%3D1TVy8p8dAaTECQS%2B2%2BS-zrcVTtmV2bqBUPTTR2UAW%2B9%3DQg%40mail.gmail.com>
Precedence: bulk

--0000000000001018c4063a4ecc1b
Content-Type: text/plain; charset="UTF-8"

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, 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
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.

Regards
Veem

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><div class=3D"gmail_quote gmail=
_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Sun, 20 Jul 2025=
 at 02:29, Adrian Klaver &lt;<a href=3D"mailto:adrian.klaver@aklaver.com">a=
drian.klaver@aklaver.com</a>&gt; wrote:<br></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.<br><div>=C2=A0</div><div>Regards</div><div>V=
eem</div></div></div>

--0000000000001018c4063a4ecc1b--