December 30th, 2013, 11:53 AM
Finding out what is writing to pg_largeobject
I am using PostgreSQL x64 9.2.6 on Windows 2008 Server Standard R2 with IBM Rational Focal Point application. The backup of the DB is taken on daily basis at 5 in the morning. The backup file size is growing by 30MB every day, even on the days when there is almost no activity on the application.
On investigation, I found out that the table pg_largeobject is growing on daily basis. I now want to find out what writes to this table, which particular query or which particular objects are being stored in this table. How can I enable audit or some other function through which I can find out what is causing this table to grow?
I have also used vacuumlo.exe, but even that does not claim the size any more than few MBs.
December 31st, 2013, 12:43 PM
You can always enable the PostgreSQL query log, and filter that for "lo_" function calls.
But large objects are usually binary data and that suggests something like backups, daily reports etc.
Comments on this post
January 4th, 2014, 03:04 PM
Thanks Vinny42. Your suggestion worked, though I am still struggling to find out what these queries mean. You have mentioned backup. Does this mean that taking the DB backup causes LOs to be written to this table? This particular DB is backed up daily, using batch script.
Originally Posted by Vinny42
January 5th, 2014, 02:29 AM
No,takng a backup will not write LO's. It may increase the size of the db slightly if you issue a "backup start" command because that causes the DB tostart tracking cahnges to the data, but if you use pg_dump your database will not grow by taking a backup.
What I meant is that the application itself may create a backup of some data, or a report of the day's activity.It may even generate a report of changes, anything really.
January 11th, 2014, 12:23 PM
Do you know of a way to find out which tables from the main schema actually refer to the tuples in the pg_largeobject? There is no direct referential constraint. Based on your suggestion, I see a lot of open write and close calls on lo_, however I have not been able to figure out what data is being written there.
I have a feeling that the application is causing writes to the pg_largeobject table, and though the original tuples get deleted by the app, the pg_largeobject tuples remain orphan. I have used vacuumlo, but it does not claim any space other than ~ 20 MB.
There should be a way to find out who is referencing the pg_largeobject tuples.
January 11th, 2014, 12:56 PM
Large objects are not necessarily linked to tables.
I think you should probably ask this question on one of the mailinglists: http://www.postgresql.org/list/
Those are monitorred by the lead programmers,if anybody knows how to work out where the objects are coming from,it's them.