Feedback-ID: i76984098:Fastmail
Message-ID: <b369880b-677b-4bf0-8d2a-7939dc1007bc@aklaver.com>
Date: Sat, 19 Jul 2025 14:34:11 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Performance of JSON type in postgres
To: veem v <veema0000@gmail.com>
Cc: Merlin Moncure <mmoncure@gmail.com>,
 "David G. Johnston" <david.g.johnston@gmail.com>,
 pgsql-general <pgsql-general@lists.postgresql.org>
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>
 <CAB+=1TVy8p8dAaTECQS+2+S-zrcVTtmV2bqBUPTTR2UAW+9=Qg@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAB+=1TVy8p8dAaTECQS+2+S-zrcVTtmV2bqBUPTTR2UAW+9=Qg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/b369880b-677b-4bf0-8d2a-7939dc1007bc%40aklaver.com>
Precedence: bulk

On 7/19/25 14:19, veem v wrote:
> 
> On Sun, 20 Jul 2025 at 02:29, Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto: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,

Again as I mentioned at the beginning of this thread many of the answers 
can be found here:

https://www.postgresql.org/docs/current/datatype-json.html

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

8.14.4. jsonb Indexing

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

"By contrast, jsonb does not preserve white space, does not preserve the 
order of object keys, and does not keep duplicate object keys. If 
duplicate keys are specified in the input, only the last value is kept."

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

"JSON data types are for storing JSON (JavaScript Object Notation) data, 
as specified in RFC 7159. Such data can also be stored as text, but the 
JSON data types have the advantage of enforcing that each stored value 
is valid according to the JSON rules. "

There is no schema validation.


> 
> 4)No Native Foreign Key Support: You cannot directly define foreign key 
> constraints within a JSONB column to enforce referential integrity with 
> other tables.

Table 8.23. JSON Primitive Types and Corresponding PostgreSQL Types

If you look at that you will see that JSON(B) types do not cover the 
range that Postgres types do, making it difficult to map directly to 
Postgres tables even if there where FK's allowed.

To me it looks like you really need to store the data in defined 
Postgres tables.


> Regards
> Veem

-- 
Adrian Klaver
adrian.klaver@aklaver.com