Feedback-ID: i76984098:Fastmail
Message-ID: <289f005c-4a80-4098-bf58-f53e06bb7f8c@aklaver.com>
Date: Sat, 19 Jul 2025 13:59:36 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Performance of JSON type in postgres
To: veem v <veema0000@gmail.com>, Merlin Moncure <mmoncure@gmail.com>,
 "David G. Johnston" <david.g.johnston@gmail.com>
Cc: 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>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAB+=1TWwerQMVQvOP0oAVunTUz8tnX425Jn-84DBYbOD+us9Eg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/289f005c-4a80-4098-bf58-f53e06bb7f8c%40aklaver.com>
Precedence: bulk

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
adrian.klaver@aklaver.com