public inbox for [email protected]
help / color / mirror / Atom feedFrom: veem v <[email protected]>
To: Adrian Klaver <[email protected]>
Cc: Merlin Moncure <[email protected]>
Cc: David G. Johnston <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Performance of JSON type in postgres
Date: Sun, 20 Jul 2025 02:49:14 +0530
Message-ID: <CAB+=1TVy8p8dAaTECQS+2+S-zrcVTtmV2bqBUPTTR2UAW+9=Qg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAB+=1TU2bi8UkdD94kMizsTrTBUgqbdWKtM4Lq67+BMi6Vt1qA@mail.gmail.com>
<[email protected]>
<CAKFQuwarbmz7=yg14h6u-UhGPfbT56TsX8S6w9wVdMHGiWxbYg@mail.gmail.com>
<CAHyXU0xQGXFBZ10GtqTkXL3_b8FbB79qP+XS2XCfxp+6WuH1Cg@mail.gmail.com>
<CAB+=1TWwerQMVQvOP0oAVunTUz8tnX425Jn-84DBYbOD+us9Eg@mail.gmail.com>
<[email protected]>
On Sun, 20 Jul 2025 at 02:29, Adrian Klaver <[email protected]>
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
view thread (4+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Performance of JSON type in postgres
In-Reply-To: <CAB+=1TVy8p8dAaTECQS+2+S-zrcVTtmV2bqBUPTTR2UAW+9=Qg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox