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)
--
Angular momentum makes the world go 'round.