From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Wells Oliver <wells.oliver@gmail.com>
To: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: Storing large large JSON objects in JSONB
Date: Mon, 11 Jul 2022 08:13:16 +0200
Message-ID: <2b5df78128c02a4389f4fb9a05c0fd8084583c6a.camel@cybertec.at> (raw)
In-Reply-To: <CAOC+FBWRavHQWtQRJ_suSRjtDR9PrnDeRm9CWojr2ogYgDA7Ww@mail.gmail.com>
References: <CAOC+FBWRavHQWtQRJ_suSRjtDR9PrnDeRm9CWojr2ogYgDA7Ww@mail.gmail.com>
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
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: pgsql-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, wells.oliver@gmail.com
Subject: Re: Storing large large JSON objects in JSONB
In-Reply-To: <2b5df78128c02a4389f4fb9a05c0fd8084583c6a.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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