On Fri, 26 Apr 2024 at 10:54, Yugo NAGATA <nag...@sraoss.co.jp> wrote: > > On Wed, 24 Apr 2024 16:08:39 -0500 > Nathan Bossart <nathandboss...@gmail.com> wrote: > > > On Tue, Apr 23, 2024 at 11:47:38PM -0400, Tom Lane wrote: > > > On the whole I find this proposed feature pretty unexciting > > > and dubiously worthy of the implementation/maintenance effort. > > > > I don't have any particularly strong feelings on $SUBJECT, but I'll admit > > I'd be much more interested in resolving any remaining reasons folks are > > using large objects over TOAST. I see a couple of reasons listed in the > > docs [0] that might be worth examining. > > > > [0] https://www.postgresql.org/docs/devel/lo-intro.html > > If we could replace large objects with BYTEA in any use cases, large objects > would be completely obsolete. However, currently some users use large objects > in fact, so improvement in this feature seems beneficial for them. > > > Apart from that, extending TOAST to support more than 1GB data and > stream-style access seems a good challenge. I don't know if there was a > proposal for this in past. This is just a thought, for this purpose, we > will need a new type of varlena that can contains large size information, > and a new toast table schema that can store offset information or some way > to convert a offset to chunk_seq.
If you're interested in this, you may want to check out [0] and [1] as threads on the topic of improving TOAST handling of large values ([1] being a thread where the limitations of our current external TOAST pointer became clear once more), and maybe talk with Aleksander Alekseev and Nikita Malakhov. They've been working closely with systems that involve toast pointers and their limitations. The most recent update on the work of Nikita (reworking TOAST handling) [2] is that he got started adapting their externally pluggable toast into type-internal methods only, though I've not yet noticed any updated patches appear on the list. As for other issues with creating larger TOAST values: TOAST has a value limit of ~1GB, which means a single large value (or two, for that matter) won't break anything in the wire protocol, as DataRow messages have a message size field of uint32 [^3]. However, if we're going to allow even larger values to be stored in table's attributes, we'll have to figure out how we're going to transfer those larger values to (and from) clients. For large objects, this is much less of an issue because the IO operations are already chunked by design, but this may not work well for types that you want to use in your table's columns. Kind regards, Matthias van de Meent [0] https://postgr.es/m/flat/CAN-LCVMq2X%3Dfhx7KLxfeDyb3P%2BBXuCkHC0g%3D9GF%2BJD4izfVa0Q%40mail.gmail.com [1] https://postgr.es/m/flat/CAJ7c6TOtAB0z1UrksvGTStNE-herK-43bj22%3D5xVBg7S4vr5rQ%40mail.gmail.com [2] https://postgr.es/m/CAN-LCVOgMrda9hOdzGkCMdwY6dH0JQa13QvPsqUwY57TEn6jww%40mail.gmail.com [^3] Most, if not all PostgreSQL wire protocol messages have this uint32 message size field, but the DataRow one is relevant here as it's the one way users get their data out of the database.
