Re: Performance of JSON type in postgres

On Sat, Jul 19, 2025 at 5:19 PM veem v <veema0000(at)gmail(dot)com> wrote:

>
> On Sun, 20 Jul 2025 at 02:29, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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.
>

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!