In PostgresQL 16, how to detect where "Large Objects" are used?

Question

I have a large database that I need to migrate from postgresql 16 to 17. But when programming a logical replication, I receive a message saying that a large object has been modified. But I don't know where are those large objects used.

--> How to list fields in all tables that use these larges objects?

And is there another flow that lead to the creation of large objects?

The database is an AWS RDS.

Answer 1

Large objects are exactly that: objects; they all are stored in a single table, pg_largeobject, and their metadata in pg_largeobject_metadata. References to their OIDs can be stored in user tables, but there is no guaranteed method to find exactly what those tables are. You will have to study your application code or log DML statements to see what tables are updated whenever large objects are created or modified.

• jehon: So you mean that it is postgres that decide when to use large objects or not? It is not related to the table structure?
• mustaccio: No, it's the application developers that decide when to use LOs. They then use special functions to create and manage LOs, which Postgres stores in pg_largeobject. The developers then may, or may not, store references to LOs somewhere else.