Re: partial vacuum

Tom Lane wrote:
> I think the major problem with this is the (untenable) assumption that
> the user is keeping track of the table size accurately. It'd be very
> likely that portions of the table get missed if someone tries to
> maintain a table using only partial vacuums specified in this way.
>
> I thought about specifying the range using percentages instead of raw
> block numbers, but that's got equally bad problems of its own. (If
> the table size changes, then successive vacuums from 0-10 and 10-20%
> could miss a few blocks in between.)

Yes. If the user want to use partial vacuum efficiently,
partial vacuum will need some supporting tools to keep track
of the table size changes.
For example, (intelligent) pg_autovacuum or something like that.

> More generally, any sort of partial vacuum operation is going to be
> inherently inefficient because of excessive index scanning --- if
> you chop the table into tenths, say, you are probably doing five or
> so extra index scans to complete the operation, because of scans forced
> with only partially full vacuum memory. Unless you want to redesign
> the way index cleanup is done, you won't be able to use this feature
> with a scan size small enough that it really makes a meaningful
> reduction in the system load produced by a vacuum.

Yes. It's a difficult point for me.
Now, I have no idea to handle indexes in partial vacuum,
but I want to find the answer...

> Have you looked at the vacuum cost delay features present in 8.0?
> On the whole that seems like a better solution for reducing the impact
> of routine vacuuming than trying to manage partial vacuuming with an
> approach like this.

I've not tried yet.

I guess the postgres is going to have a background process to reduce
the vacuum impact in the system load, as we got the bgwriter on
checkpoint handling. Right?

Thanks for comments.

--
NAGAYASU Satoshi <nagayasus(at)nttdata(dot)co(dot)jp>
OpenSource Development Center,
NTT DATA Corp. http://www.nttdata.co.jp