Thread: vacuum full in the background

hi,

i'm happy wih autovacuum and cluster.
but now I ask myself, why i can't move the last table rows
to the beginning step by step?
vacuum-full needs an exclusive lock.
that is okay for me, but if a query arrives, that is waiting
for my vacuum-full, i would like to save my vacuum-work
and pause.

when there comes an bulk-import, i know that my vacuum
will not modify the contents of the table.
so it would be great, if the import can append to the table
normally.
when my vacuum finishes, it would be great, if the new
free space in the middle could be eliminated by just
modifiing one inode-pointer.

are there reasons, why this could not be done?

regards, maletin.

Martin von Oertzen  wrote:

> why i can't move the last table rows to the beginning step by step?

I believe that if you search the hackers list from somewhere around
six to twelve months ago, someone posted a client program which did
that.  I think it wasn't pursued for lack of interest -- it's pretty
rare that it's a net win to give space back to the OS after a VACUUM,
since you're likely to need it again.  It's faster to re-use space
already allocated to the table than to give it to the OS and request
it back from the OS.

> vacuum-full needs an exclusive lock.
> that is okay for me, but if a query arrives, that is waiting
> for my vacuum-full, i would like to save my vacuum-work
> and pause.

Why are you doing VACUUM FULL?  In most environments that should only
be needed if there is unusual activity or normal maintenance fails
for some reason.

> when there comes an bulk-import, i know that my vacuum
> will not modify the contents of the table.
> so it would be great, if the import can append to the table
> normally.

I'm not following -- what would you like it to do differently?

> when my vacuum finishes, it would be great, if the new
> free space in the middle could be eliminated by just
> modifiing one inode-pointer.
>
> are there reasons, why this could not be done?

For approximately the same reasons why you can't slice out two or
three feet from the middle of your car when the back seat is empty.

-Kevin