Re: Large Objects and and Vacuum

Please don't send HTML mail to this list.

Simon Windsor wrote:
> I am struggling with the volume and number of XML files a new
application is storing. The table
> pg_largeobjects is growing fast, and despite the efforts of vacuumlo,
vacuum and auto-vacuum it keeps
> on growing in size.

Have you checked if the number of large objects in the database
is growing as well?

Check the result of
SELECT count(DISTINCT loid) FROM pg_largeobject;
over time, or before and after "vacuumlo".

> The main tables that hold large objects are partitioned and every few
days I drop partition tables
> older than seven days, but despite all this, the system is growing in
size and not releasing space
> back to the OS.

Do you also delete the large objects referenced in these dropped tables?
They won't vanish automatically.

If you use large objects in a partitioned table, you probably have
a design problem. Having to clean up after orphaned large objects
will mitigate the performance gain by dropping partitions instead
of deleting data. You might be better off with bytea.

> Using either vacuum full or cluster to fix pg_largeobjects will
require a large amount of work space
> which I do not have on this server.
>
> Is there another method of scanning postgres tables, moving active
blocks and releasing store back to
> the OS?

If VACUUM does not keep pg_largeobject from growing, VACUUM FULL or
something else will not help either.
You have to figure out why your large objects don't get deleted.
Only after they are deleted, VACUUM can free the space.

> Failing this, I can see an NFS mount being required.

Beg your pardon?

Yours,
Laurenz Albe