How to identify tables that are using large objects in postgres?

Question

I am an oracle dba and recently started learning postgres database administration. In one of our postgres databases, pg_largeobject table is of 30G and I want to reclaim disk space by unlinking and vacuuming the large objects which are not used by any tables in the database. For this, I would like to know the tables in the database which are using these large objects. Is there any query or procedure to achieve this?

Update: I tried using vacuumlo but it returned 0 objects to be removed. It means that the large objects are not orphaned but we don't have any table in the database which references majority of the objects in the pg_largeobject table. Then how come these objects are not orphaned?

Answer 1

Normally, applications should unlink the large objects that are no longer needed, but in the case they don't, PostgreSQL ships with an utility named vacuumlo that does exactly what you're asking for.

Description:

vacuumlo is a simple utility program that will remove any “orphaned” large objects from a PostgreSQL database. An orphaned large object (LO) is considered to be any LO whose OID does not appear in any oid or lo data column of the database.

---

If there's a single table with an oid pointing to large objects, the orphaned large objects can be found directly with this query (PostgreSQL 9.0 or newer):

select oid from pg_largeobject_metadata m where not exists
 (select 1 from name_of_table  where m.oid=name_of_oid_column);