Re: Avoiding second heap scan in VACUUM

[Simon Riggs <simon(at)2ndquadrant(dot)com>]

On Fri, 2008-05-30 at 14:22 +0530, Pavan Deolasee wrote:

> For large tables, two heap scans along with several additional page
> writes doesn't seem to the cost we can afford, especially in IO-bound
> application. IMHO a timed wait is not such a bad thing. Note that its
> all about VACUUM which is a background, maintenance activity and it
> won't harm to delay it by few seconds or even minutes. Also, as I said
> earlier "waiting" is a minor detail, may be there is a better way to
> do things.
> 
> Unless there are some strong objections, I would like to give it a
> shot and see if there are any real benefits. We can then fix any
> regression cases. Let me know if somebody thinks there are certain
> show stoppers or the benefits of avoiding a second scan on a large
> table is not worth. I personally have a strong feeling that it's worth
> the efforts.

I'm not really clear what you intend to do now.

We both agreed that avoiding a second pass is a good thing. What I still
don't accept is that an unconstrained wait is justifiable. You've just
said its a minor detail, but that's not the way I see it. It might be a
second, but it might be an hour or more. If I run a VACUUM at 0700,
thinking it will finish by 0900 before my database gets busy, it is a
very bad thing to find that it only started at 0900 and is now
interfering with my business workload.

A non-waiting solution seems like the only way to proceed.

Is this a non-issue anyway, with DSM?

-- 
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support