pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Inheritance pg_largeobject table
3+ messages / 3 participants
[nested] [flat]

* Inheritance pg_largeobject table
@ 2022-12-09 05:08 Zhao, Bing <Bing.Zhao@cra-arc.gc.ca>
  2022-12-09 05:27 ` Re: Inheritance pg_largeobject table Ron <ronljohnsonjr@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Zhao, Bing @ 2022-12-09 05:08 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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?

Is there a way store them into pg_largeobject? expand this table? Or we have to consider toast tables.


Thanks,

~Bing

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Inheritance pg_largeobject table
  2022-12-09 05:08 Inheritance pg_largeobject table Zhao, Bing <Bing.Zhao@cra-arc.gc.ca>
@ 2022-12-09 05:27 ` Ron <ronljohnsonjr@gmail.com>
  2022-12-09 06:37   ` Re: Inheritance pg_largeobject table Thomas Boussekey <thomas.boussekey@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Ron @ 2022-12-09 05:27 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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.

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Inheritance pg_largeobject table
  2022-12-09 05:08 Inheritance pg_largeobject table Zhao, Bing <Bing.Zhao@cra-arc.gc.ca>
  2022-12-09 05:27 ` Re: Inheritance pg_largeobject table Ron <ronljohnsonjr@gmail.com>
@ 2022-12-09 06:37   ` Thomas Boussekey <thomas.boussekey@gmail.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Thomas Boussekey @ 2022-12-09 06:37 UTC (permalink / raw)
  To: Ron <ronljohnsonjr@gmail.com>; +Cc: PostgreSQL General <pgsql-general@lists.postgresql.org>

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)


> --
> Angular momentum makes the world go 'round.
>

^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2022-12-09 06:37 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-12-09 05:08 Inheritance pg_largeobject table Zhao, Bing <Bing.Zhao@cra-arc.gc.ca>
2022-12-09 05:27 ` Ron <ronljohnsonjr@gmail.com>
2022-12-09 06:37   ` Thomas Boussekey <thomas.boussekey@gmail.com>

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