0

postrgresql noob, PG 9.4.x, no access to application code, developers, anyone knowledgeable about it

User database CT has 427GB pg_largeobject (PGLOB) table, next largest table is 500ish MB.

Per this post (Does Postgresql use PGLOB internally?) a very reputable member said postgresql does not use PGLOB internally.

I have reviewed the schema of all user tables in the database, and none of them are of type OID (or lo) - which is the value used for PGLOB rows to tie the collection of blob chunks back to a referencing table row. I think this means I cannot use vacuumlo (vacuumlo) to delete orphaned PGLOB rows because that utility searches user objects for those two data types in user tables.

I HAVE identified a table with an integer field type that has int values that match LOID values in PGLOB. This seems to indicate that the developers somehow got their blobs into PGLOB using the integer value stored in a user table row.

QUESTION: Is that last statement possible?

A) If it is not, what could be adding all this data to PGLOB table?

B) If it is possible, is there a way I can programatically search ALL tables for integer values that might represent rows in PGLOB?

NEED: I DESPERATELY need to reduce the size of the PGLOB table, as we are running out of disk space. And no, we cannot add space to existing disk per admin. So I somehow need to determine if there are LOID values in PGLOB that do NOT exist in ANY user tables as integer-type fields and then run lo_unlink to remove the rows. This could get me more usable 8K pages in the table.

BTW, I have also run pg_freespace on PGLOB, and it identified that most of the pages in PGLOB did not contain enough space in which to insert another blob chunk.

THANKS FOR THE ASSISTANCE!

TheSQLGuru
  • 11
  • 1
  • My apologies, but this post seems to be mostly duplicated from additions to this post: [link]https://dba.stackexchange.com/questions/308340/free-space-in-pg-largeobject-not-being-reused/308377#308377[/link] – TheSQLGuru Mar 10 '22 at 18:21

2 Answers2

0

Not really an answer but thinking out loud:

As you found all large objects are stored in a single table. The oid field you refer to is something you add to a table so you can have a pointer to a particular LO oid in pg_largeobject. That being said there is nothing compelling you to store that info in a table, you can just create LO's in pg_largeobject. From the looks of it, and just a guess, the developers stored the oid's as integer's with the intent of doing integer::oid to get a particular LO back as needed. I would look at other information is stored in that table to see if helps determine what the LO's are for?

Also you might join the integer::oid values to the oid(loid) in pg_catalog to see if that table accounts for all of them?

Adrian Klaver
  • 15,886
  • 2
  • 17
  • 28
0

I was able to do a detailed analysis of all user tables in the database, find all columns that contained numeric data with no decimals, and then do a a query from pg_largeobject with a NOT EXISTS clause for every table matching pglob.loid against the appropriate field(s) in the user tables.

I found 25794 LOIDs that could be DELETEd from the PGLOB table, totaling 3.4M rows.

select distinct loid
into OrphanedBLOBs
from pg_largeobject l
where NOT exists (select * from tbl1 cn where cn.noteid = l.loid)
and not  exists (select * from tbl1 d where d.document = l.loid)
and not  exists (select * from tbl1 d where d.reportid = l.loid)

I used that table to execute lo_unlink(loid) for each of the LOIDs.

TheSQLGuru
  • 11
  • 1