public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Klaver <[email protected]>
To: veem v <[email protected]>
To: Merlin Moncure <[email protected]>
To: David G. Johnston <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Performance of JSON type in postgres
Date: Sat, 19 Jul 2025 13:59:36 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAB+=1TWwerQMVQvOP0oAVunTUz8tnX425Jn-84DBYbOD+us9Eg@mail.gmail.com>
References: <CAB+=1TU2bi8UkdD94kMizsTrTBUgqbdWKtM4Lq67+BMi6Vt1qA@mail.gmail.com>
	<[email protected]>
	<CAKFQuwarbmz7=yg14h6u-UhGPfbT56TsX8S6w9wVdMHGiWxbYg@mail.gmail.com>
	<CAHyXU0xQGXFBZ10GtqTkXL3_b8FbB79qP+XS2XCfxp+6WuH1Cg@mail.gmail.com>
	<CAB+=1TWwerQMVQvOP0oAVunTUz8tnX425Jn-84DBYbOD+us9Eg@mail.gmail.com>

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]

view thread (4+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Performance of JSON type in postgres
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox