Re: Vacuum and Large Objects

On 12/15/2011 04:01 AM, Simon Windsor wrote:

Hi

 

I am having problems recovering storage from a Postgres 9.05 database that is used to hold large XML blocks for a week, before they are archived off line.

 

The main tables are partitioned in daily partitions, and these are easy to manage, however the DB keeps growing despite using Vacuum (daily at 0700) and autovacuum (this does not seem to run, although the process is running). The system is insert only, and partitions are dropped when over 7 days of age.

 

I believe the issue lies with pg_largeobject, it is split between 88 files of approx. 1G each.

Are you using pg_largeobject via the lo_ functions, or via the `lo' datatype?

If you're using it via the `lo' type then certain actions can IIRC cause large object leaks. Try vacuumlo.

http://www.postgresql.org/docs/current/static/vacuumlo.html

vacuumlo is **NOT** suitable for use on databases where you use the lo_ functions directly.

See also the `lo' module:

http://www.postgresql.org/docs/current/static/lo.html


If you're using the lo_ functions directly and still seeing excessive space consumption in pg_largeobject then beyond a CLUSTER or VACUUM FULL run I'm not sure what to advise.

--
Craig Ringer