Re: VACUUM FULL performance issues with pg_largeobject table

PG User 2010 <pguser2010(at)gmail(dot)com> writes:
> 1) is there any easy way to fiddle with the vacuum process so that it is not
> CPU bound and doing very little I/O? Why would vacuum full be CPU bound
> anyway???

The only part of VAC FULL that seems like it could be CPU-bound is index
cleanup. If the table is sufficiently bloated with dead tuples, that
could take awhile. It might be useful to try this:

1. REINDEX TABLE pg_largeobject;
2. VACUUM pg_largeobject;
3. VACUUM FULL pg_largeobject;

I have never tried this in a serious bloat situation, but in principle
I think it should improve matters. The idea is to get rid of as many dead
index and heap entries as you can before letting VAC FULL loose on it, and
also do as much of the work as possible with a less-than-exclusive lock.
Don't forget that large maintenance_work_mem will help the first two
steps, as long as you don't set it so high as to drive the machine into
swapping.

> 2) is it possible to interrupt VACUUM FULL, then re-start it later on and
> have it pick up where it was working before?

NO. Doing that will in fact make things worse --- a failed VAC FULL
results in even more dead entries to be cleaned up.

regards, tom lane