I'm trying to find out which Table (and possibly which column) is overflowing data into the pg_largeobject table that has grown to an huge size. Our current schema has several applications which store in the DB data such as attachments or text messages. From what I can see, once the data is stored in pg_largeobject table is a sort of BLOB:

Is there a table which logs the source table when it goes into pg_largeobject ? (the source table) or maybe a trigger that I can activate to capture this event ? Thanks

CodePudding user response：

You are confusing large objects with TOAST. Large objects are only used when someone explicitly uses them. TOAST is used automatically when row sizes 'overflow'. Those are quite different things.

If your different apps login as different users, then you might get some insight be looking into pg_largeobject_metadata to see which user owns the large objects.

If that doesn't work because they all use the same login, the large object pointers are generally stored in tables with columns of type 'oid' or 'lo', so you could look for those column types. But that is not fool proof, as you could store them in type int, or I guess bytea or anything else if you really wanted to be obscurant. Or you could even store them in an Excel file or a sticky note if you wanted.

If you turn log_statement to all, you should get messages something like LOG: fastpath function call: "lo_creat" (OID 957) when large objects are used, but that would not be my first choice of investigation methods.