Re: Vacuum questions...

["Jim C. Nasby" <jnasby(at)pervasive(dot)com>]

On Tue, Sep 27, 2005 at 02:47:46PM -0400, Jan Wieck wrote:
> On 9/24/2005 8:17 PM, Jim C. Nasby wrote:
> 
> >Would it be difficult to vacuum as part of a dump? The reasoning behind
> >this is that you have to read the table to do the dump anyway, 
> 
> I think aside from what's been said so far, it would be rather difficult 
> anyway. pg_dump relies on MVCC and requires to run in one transaction to 
> see a consistent snapshot while vacuum jiggles around with transactions 
> in some rather non-standard way.

Is this true even if they were in different connections?

My (vague) understanding of the vacuum process is that it first vacuums
indexes, and then vacuums the heap. Since we don't dump indexes, there's
nothing for backup to do while those are vacuumed, so my idea is:

pg_dump:
foreach (table)
    spawn vacuum
    wait for vacuum to hit heap
    start copy
    wait for analyze to finish
next;

dump_vacuum (table):
foreach (index on table)
    vacuum index
next;

notify pg_dump we're going to start vacuum of heap
vacuum heap

if we should analyze {
    analyze table
}

notify pg_dump analyze is done
exit

AFAIK, this should allow both to run in seperate transactions. Granted,
it would slow down the dump, since it would have to wait while indexes
were being vacuumed, but it would win when it came to the heap.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby(at)pervasive(dot)com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461