public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ron Johnson <[email protected]>
To: pgsql-general <[email protected]>
Subject: Re: Performance of JSON type in postgres
Date: Sat, 19 Jul 2025 17:31:35 -0400
Message-ID: <CANzqJaCj2Lsy_W2CUts2yYsQVkBRS1gLeXOPPqUeF_dX1mZVaw@mail.gmail.com> (raw)
In-Reply-To: <CAB+=1TVy8p8dAaTECQS+2+S-zrcVTtmV2bqBUPTTR2UAW+9=Qg@mail.gmail.com>
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]>
<CAB+=1TVy8p8dAaTECQS+2+S-zrcVTtmV2bqBUPTTR2UAW+9=Qg@mail.gmail.com>
On Sat, Jul 19, 2025 at 5:19 PM veem v <[email protected]> wrote:
>
> 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.
>
The answer to all of these questions is: normalize your data structures.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
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]
Subject: Re: Performance of JSON type in postgres
In-Reply-To: <CANzqJaCj2Lsy_W2CUts2yYsQVkBRS1gLeXOPPqUeF_dX1mZVaw@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