pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedStoring large large JSON objects in JSONB
7+ messages / 4 participants
[nested] [flat]
* Storing large large JSON objects in JSONB
@ 2022-07-09 19:37 Wells Oliver <wells.oliver@gmail.com>
2022-07-11 06:13 ` Re: Storing large large JSON objects in JSONB Laurenz Albe <laurenz.albe@cybertec.at>
2022-07-11 12:11 ` Re: Storing large large JSON objects in JSONB Thomas Kellerer <shammat@gmx.net>
0 siblings, 2 replies; 7+ messages in thread
From: Wells Oliver @ 2022-07-09 19:37 UTC (permalink / raw)
To: pgsql-admin
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.
Anyway, thanks for any insight I'm missing on this front.
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Storing large large JSON objects in JSONB
2022-07-09 19:37 Storing large large JSON objects in JSONB Wells Oliver <wells.oliver@gmail.com>
@ 2022-07-11 06:13 ` Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 0 replies; 7+ messages in thread
From: Laurenz Albe @ 2022-07-11 06:13 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Storing large large JSON objects in JSONB
2022-07-09 19:37 Storing large large JSON objects in JSONB Wells Oliver <wells.oliver@gmail.com>
@ 2022-07-11 12:11 ` Thomas Kellerer <shammat@gmx.net>
2022-07-11 16:25 ` Re: Storing large large JSON objects in JSONB Wells Oliver <wells.oliver@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Thomas Kellerer @ 2022-07-11 12:11 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Storing large large JSON objects in JSONB
2022-07-09 19:37 Storing large large JSON objects in JSONB Wells Oliver <wells.oliver@gmail.com>
2022-07-11 12:11 ` Re: Storing large large JSON objects in JSONB Thomas Kellerer <shammat@gmx.net>
@ 2022-07-11 16:25 ` Wells Oliver <wells.oliver@gmail.com>
2022-07-11 17:49 ` Re: Storing large large JSON objects in JSONB Thomas Kellerer <shammat@gmx.net>
0 siblings, 1 reply; 7+ messages in thread
From: Wells Oliver @ 2022-07-11 16:25 UTC (permalink / raw)
To: Thomas Kellerer <shammat@gmx.net>; +Cc: pgsql-admin@lists.postgresql.org
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.
On Mon, Jul 11, 2022 at 5:11 AM Thomas Kellerer <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.
>
>
>
>
>
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Storing large large JSON objects in JSONB
2022-07-09 19:37 Storing large large JSON objects in JSONB Wells Oliver <wells.oliver@gmail.com>
2022-07-11 12:11 ` Re: Storing large large JSON objects in JSONB Thomas Kellerer <shammat@gmx.net>
2022-07-11 16:25 ` Re: Storing large large JSON objects in JSONB Wells Oliver <wells.oliver@gmail.com>
@ 2022-07-11 17:49 ` Thomas Kellerer <shammat@gmx.net>
2022-07-11 17:58 ` Re: Storing large large JSON objects in JSONB Wells Oliver <wells.oliver@gmail.com>
2022-07-12 06:02 ` Re: Storing large large JSON objects in JSONB Aleksey M Boltenkov <padrebolt@yandex.ru>
0 siblings, 2 replies; 7+ messages in thread
From: Thomas Kellerer @ 2022-07-11 17:49 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org; +Cc: Wells Oliver <wells.oliver@gmail.com>
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Storing large large JSON objects in JSONB
2022-07-09 19:37 Storing large large JSON objects in JSONB Wells Oliver <wells.oliver@gmail.com>
2022-07-11 12:11 ` Re: Storing large large JSON objects in JSONB Thomas Kellerer <shammat@gmx.net>
2022-07-11 16:25 ` Re: Storing large large JSON objects in JSONB Wells Oliver <wells.oliver@gmail.com>
2022-07-11 17:49 ` Re: Storing large large JSON objects in JSONB Thomas Kellerer <shammat@gmx.net>
@ 2022-07-11 17:58 ` Wells Oliver <wells.oliver@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Wells Oliver @ 2022-07-11 17:58 UTC (permalink / raw)
To: Thomas Kellerer <shammat@gmx.net>; +Cc: pgsql-admin@lists.postgresql.org
Eh, I might be wrong, these are some very large objects, but maybe they are
below the 256MB threshold.
On Mon, Jul 11, 2022 at 10:49 AM Thomas Kellerer <shammat@gmx.net> 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.
>
>
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Storing large large JSON objects in JSONB
2022-07-09 19:37 Storing large large JSON objects in JSONB Wells Oliver <wells.oliver@gmail.com>
2022-07-11 12:11 ` Re: Storing large large JSON objects in JSONB Thomas Kellerer <shammat@gmx.net>
2022-07-11 16:25 ` Re: Storing large large JSON objects in JSONB Wells Oliver <wells.oliver@gmail.com>
2022-07-11 17:49 ` Re: Storing large large JSON objects in JSONB Thomas Kellerer <shammat@gmx.net>
@ 2022-07-12 06:02 ` Aleksey M Boltenkov <padrebolt@yandex.ru>
1 sibling, 0 replies; 7+ messages in thread
From: Aleksey M Boltenkov @ 2022-07-12 06:02 UTC (permalink / raw)
To: Thomas Kellerer <shammat@gmx.net>; pgsql-admin@lists.postgresql.org; +Cc: Wells Oliver <wells.oliver@gmail.com>
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2022-07-12 06:02 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-07-09 19:37 Storing large large JSON objects in JSONB Wells Oliver <wells.oliver@gmail.com>
2022-07-11 06:13 ` Laurenz Albe <laurenz.albe@cybertec.at>
2022-07-11 12:11 ` Thomas Kellerer <shammat@gmx.net>
2022-07-11 16:25 ` Wells Oliver <wells.oliver@gmail.com>
2022-07-11 17:49 ` Thomas Kellerer <shammat@gmx.net>
2022-07-11 17:58 ` Wells Oliver <wells.oliver@gmail.com>
2022-07-12 06:02 ` Aleksey M Boltenkov <padrebolt@yandex.ru>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox