Обсуждение: Storing large large JSON objects in JSONB

On Sat, 2022-07-09 at 12:37 -0700, Wells Oliver wrote:
> We have a couple of tables where we like to store very large JSON objects
> in a JSONB column. We don't use these in, err, real time kind of situations,
> intensive queries, etc, so I'm not concerned about their performance
> necessarily, it's more just to store vendor data that's parsed as we go into
> smaller more usable tables, and so they serve as the historical record,
> and occasionally we re-process.
> 
> I'm wondering if there's some smarter solution with Postgres, these usually
> end up in TOAST tables as I understand it, so the actual table is quite small
> and then there's some associated TOAST thing that grows and grows (the given
> one right now is 500GB).
> 
> I don't.. really... care, I guess, that the TOAST tables are that large,
> but I thought I might do the diligence here and ask if there's some different
> solution, or if this is fine, or some kind of ticking time bomb I am
> understanding poorly.
> 
> We like these in the database as we have SQL scripts to parse them, so saving
> them as files in S3 and rewriting ETL in python or whatever kind sounds awful.

I know of no problem this could cause, other than that if your database grows
very large, it may be a pain to backup.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Wells Oliver schrieb am 09.07.2022 um 21:37:
> We have a couple of tables where we like to store very large JSON
> objects in a JSONB column.
Note, that the upper limit for a single jsonb value is 255MB
Not 1GB as it is e.g. the case for text or json.

Wells Oliver schrieb am 11.07.2022 um 18:25:
>> On Mon, Jul 11, 2022 at 5:11 AM Thomas Kellerer <shammat@gmx.net <mailto:shammat@gmx.net>> wrote:
>>
>>     Wells Oliver schrieb am 09.07.2022 um 21:37:
>>      > We have a couple of tables where we like to store very large JSON
>>      > objects in a JSONB column.
>>     Note, that the upper limit for a single jsonb value is 255MB
>>     Not 1GB as it is e.g. the case for text or json.

> What exactly does that mean? The upper limit at which TOAST is used
> for storage, or what? We certainly have objects larger than 256MB in
> JSONB columns.
Hmm, that's strange, because when I try to create a JSONB value that is
larger than 256MB I get the following error:

    ERROR: total size of jsonb object elements exceeds the maximum of 268435455 bytes

Are you sure you are using jsonb, not json maybe?
To my knowledge the json data type does not have this limit.

On 07/11/22 20:49, Thomas Kellerer wrote:
>
> Wells Oliver schrieb am 11.07.2022 um 18:25:
>>> On Mon, Jul 11, 2022 at 5:11 AM Thomas Kellerer <shammat@gmx.net 
>>> <mailto:shammat@gmx.net>> wrote:
>>>
>>>     Wells Oliver schrieb am 09.07.2022 um 21:37:
>>>      > We have a couple of tables where we like to store very large 
>>> JSON
>>>      > objects in a JSONB column.
>>>     Note, that the upper limit for a single jsonb value is 255MB
>>>     Not 1GB as it is e.g. the case for text or json.
>
>> What exactly does that mean? The upper limit at which TOAST is used
>> for storage, or what? We certainly have objects larger than 256MB in
>> JSONB columns.
> Hmm, that's strange, because when I try to create a JSONB value that is
> larger than 256MB I get the following error:
>
>    ERROR: total size of jsonb object elements exceeds the maximum of 
> 268435455 bytes
>
> Are you sure you are using jsonb, not json maybe?
> To my knowledge the json data type does not have this limit.
>
>
>
>
Wow! We are using citus for storing likely objects. 256MB. My opinion is 
you are doing something wrong.