Re: 7.3.1 takes long time to vacuum table?

Well, here is another case where partitioning would be usefull.

Lets all agree the that vaccuming a small table should be better done by
copying to a new one.
Now, if a larger table would be partitioned, it would allow vacuuming
one partition at a time.


JLL

P.S. Is there really a need to reorder the vaccumed table???


"Shridhar Daithankar" wrote:
>
[...]
> Well, One thing I can think of is the extra space required. The algo. looks
> good but it would be very difficult to make sure that it works all the time
> especially given that postgresql does not have sophisticated and/or tunable
> storage handling( think of tablespaces ).
>
> It is always space-time trade-off. On one hand we have vacuum which uses a
> constant and may be negiliible space but takes time proportional to amount of
> work. On other hand we have drop/recreate table which takes double the space
> but is extremely fast i.e. proportinal to data size at max. I/O bandwidth
> available..
>
> It would be good if there is in between. Of course it would not be easy to do
> it. But it has to start, isn't it?..:-)
>
> > In fact, my colleague has just done a test with SELECT..INTO on our dev
> > version and it compacted 600Mb -> 400Mb in just 40s(!). We then did a
> > vacuum full on the same original 600Mb table which is still going after
> > 20mins. Difficult choice! So even in a worse case scenario we could have
> > a fully vacuumed table within a day.... we're looking at dropping some
> > indexes in the db to reclaim enough space to be able to fit another copy
> > of the table on the disk... this is looking very tempting at the
> > moment....
>
> I recommend this strategy of "vacuuming" be documented in standard
> documentation and FAQ. Given that postgresql is routinely deployed for
> databases >10GB which is greater than small/medium by any definition today, I
> think this will be a good move.
>
> Furthermore this strategy reduces the down time due to vacuum full locks
> drastically. I would say it is worth buying a 80GB IDE disk for this purpose
> if you have this big database..
>
> Nice to see that my idea helped somebody..:-)
>
>  Shridhar
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org