Re: Performance of JSON type in postgres

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.

Regards
Veem