public inbox for [email protected]help / color / mirror / Atom feed
Re: Performance of JSON type in postgres 4+ messages / 3 participants [nested] [flat]
* Re: Performance of JSON type in postgres @ 2025-07-19 20:59 Adrian Klaver <[email protected]> 2025-07-19 21:19 ` Re: Performance of JSON type in postgres veem v <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Adrian Klaver @ 2025-07-19 20:59 UTC (permalink / raw) To: veem v <[email protected]>; Merlin Moncure <[email protected]>; David G. Johnston <[email protected]>; +Cc: pgsql-general <[email protected]> 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 protected] ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Performance of JSON type in postgres 2025-07-19 20:59 Re: Performance of JSON type in postgres Adrian Klaver <[email protected]> @ 2025-07-19 21:19 ` veem v <[email protected]> 2025-07-19 21:31 ` Re: Performance of JSON type in postgres Ron Johnson <[email protected]> 2025-07-19 21:34 ` Re: Performance of JSON type in postgres Adrian Klaver <[email protected]> 0 siblings, 2 replies; 4+ messages in thread From: veem v @ 2025-07-19 21:19 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; +Cc: Merlin Moncure <[email protected]>; David G. Johnston <[email protected]>; pgsql-general <[email protected]> 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. Regards Veem ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Performance of JSON type in postgres 2025-07-19 20:59 Re: Performance of JSON type in postgres Adrian Klaver <[email protected]> 2025-07-19 21:19 ` Re: Performance of JSON type in postgres veem v <[email protected]> @ 2025-07-19 21:31 ` Ron Johnson <[email protected]> 1 sibling, 0 replies; 4+ messages in thread From: Ron Johnson @ 2025-07-19 21:31 UTC (permalink / raw) To: pgsql-general <[email protected]> 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! ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Performance of JSON type in postgres 2025-07-19 20:59 Re: Performance of JSON type in postgres Adrian Klaver <[email protected]> 2025-07-19 21:19 ` Re: Performance of JSON type in postgres veem v <[email protected]> @ 2025-07-19 21:34 ` Adrian Klaver <[email protected]> 1 sibling, 0 replies; 4+ messages in thread From: Adrian Klaver @ 2025-07-19 21:34 UTC (permalink / raw) To: veem v <[email protected]>; +Cc: Merlin Moncure <[email protected]>; David G. Johnston <[email protected]>; pgsql-general <[email protected]> On 7/19/25 14:19, veem v wrote: > > On Sun, 20 Jul 2025 at 02:29, Adrian Klaver <[email protected] > <mailto:[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, 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 [email protected] ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-07-19 21:34 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-07-19 20:59 Re: Performance of JSON type in postgres Adrian Klaver <[email protected]> 2025-07-19 21:19 ` veem v <[email protected]> 2025-07-19 21:31 ` Ron Johnson <[email protected]> 2025-07-19 21:34 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox