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?

I don't even know if there's a facility to do that, but there's no need -- standard table maintenance (vacuuming) will attend to both the core table and the TOAST objects attached to it, transparently. — jmelesky
– jmelesky, Commented May 15, 2019 at 16:40
What is the DDL for the pg_largeobject table? How many FK are there and how is the FK delete action defined? — Antoan Milkov
– Antoan Milkov, Commented May 15, 2019 at 17:39
@AntoanMilkov pg_largeobject is kind of postgres maintained system table for all large objects of that are in the database.It only has three columns: large object id of type oid, page no of type integer and data of type bytea. — Ravi Teja Adabala
– Ravi Teja Adabala, Commented May 15, 2019 at 19:06

Daniel Vérité · Accepted Answer · 2019-05-17 08:59:47Z

5

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);

edited May 17, 2019 at 8:59

answered May 15, 2019 at 22:40

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ravi Teja Adabala Over a year ago

Thank you. I have used vacuumlo and it is showing 0 objects to be removed. With this I'm sure that these objects in pg_largeobject table are not orphaned. But we do have a small number of tables, so I went through the description of each and every table but I found only once column of type oid and it has relatively small number of rows. So in conclusion I don't have any table in the database that references majority of the large objects in the pg_largeobject table and yet they are not orphaned. Any ideas on how to proceed?

Daniel Vérité Over a year ago

@RaviTejaAdabala: each large object has many entries in pg_largeobject because it holds one row per segment, not one row per object. Maybe that's what makes you overevaluate the number of large objects. See count(*) from pg_largeobject_metadata for the real number.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related