Subject: Re: Performance of JSON type in postgres

From: Adrian Klaver <[email-masked]>
Date: Sat, 19 Jul 2025 13:59:36 -0700

On 7/19/25 13:39, veem v wrote:

> Thank you.
> 
> I tested below for sample data. I see loading or serialization seems a 
> lot slower(twice as slower) in JSONB as compared to JSON. Whereas 
> storage looks efficient in JSONB. and reading performance of nested 
> fields are 7-8 times slower in JSON as compared to JSONB(and ofcourse 
> index support makes it a better choice here). Hope i am testing it 
> correctly here.
> 
> https://dbfiddle.uk/6P7sjL22 <https://dbfiddle.uk/6P7sjL22>
> 
> So I am a bit confused here . Also one of our use case is, along with 
> persisting this data and querying it in postgres database, We are also 
> going to move this data from postgres (which is a upstream OLTP system) 
> to a downstream OLAP system ,which is in Snowflake database which is 
> having data types like Variant or Varchar types. So, will it create a 
> significant difference if we store it in JSON vs JSONB in our postgres 
> i.e the source/upstream database?

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.

-- 
Adrian Klaver
[email-masked]

---

From: veem v <[email-masked]>
Date: Sun, 20 Jul 2025 02:49:14 +0530

On Sun, 20 Jul 2025 at 02:29, Adrian Klaver <[email-masked]>
wrote:

> 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

---

From: Ron Johnson <[email-masked]>
Date: Sat, 19 Jul 2025 17:31:35 -0400

On Sat, Jul 19, 2025 at 5:19 PM veem v <[email-masked]> wrote:

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

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!

---

From: Adrian Klaver <[email-masked]>
Date: Sat, 19 Jul 2025 14:34:11 -0700

On 7/19/25 14:19, veem v wrote:
> 
> On Sun, 20 Jul 2025 at 02:29, Adrian Klaver <[email-masked] 
> <mailto:[email-masked]>> wrote:
> 
> specific format makes a difference in such a situation(mainly 
> considering JSONB seems postgres native only).
> 

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

> 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

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

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

> constraints within a JSONB column to enforce referential integrity with 

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.