Inheritance pg_largeobject table

On 12/8/22 23:08, Zhao, Bing wrote:

*Categorization: Unclassified *

We are running PG11.11, and have more than 50T LO data about load into the
pg_largeobject table. But 32T is the limitation.

We have created couple child tables that using inheritance to
pg_largeobject, and we have tried use triggers(insert or update) to put
into child tables. It doesn’t work by use LO functions, ex lo_put,
lo_create…seems never got triggered. But works with insert statement into
the pg_largeobject.

If we move LO data from root table to child, lo_get will break.

Any ideas?

Out of curiosity, why inheritance instead of declarative partitioning?

--
Angular momentum makes the world go 'round.

Le ven. 9 déc. 2022 à 06:27, Ron <ronljohnsonjr@gmail.com> a écrit :

On 12/8/22 23:08, Zhao, Bing wrote:

*Categorization: Unclassified *

We are running PG11.11, and have more than 50T LO data about load into the
pg_largeobject table. But 32T is the limitation.

We have created couple child tables that using inheritance to
pg_largeobject, and we have tried use triggers(insert or update) to put
into child tables. It doesn’t work by use LO functions, ex lo_put,
lo_create…seems never got triggered. But works with insert statement into
the pg_largeobject.

If we move LO data from root table to child, lo_get will break.

Any ideas?

Out of curiosity, why inheritance instead of declarative partitioning?

Hello,

Pg_largeobject is a system table belonging to the pg_catalog schema.

You cannot customize it(partition,reindex concurrently...) like any other
table.
Maintenance operations on pg_largeobject are also limited, repack extension
cannot work on pg_catalog schema.

We encounter a problem with largeobjects on production databases, a few
weeks ago. My colleague sent a message to the community, and most of the
first feedback that he has received were : "why did you still use this
technology from the last century? "
After this adventure, we have decided to stop using them in the months to
come. It will help us to use logical replication for our next technical
migrations.

Alternatives to pg_largeobject can be:
- using byteA columns, largeobjects will be stored in a toast structure,
linked to the corresponding table. If your largeobjects are stored in
different tables, your data will be spread into different objects, it could
be easier to maintain.
- store binary data outside the database (if it is possible)