Re: [HACKERS] Index recreation in vacuum

Hiroshi Inoue wrote:
> > > Yes,I believe so. It's necessary to keep consistency between heap
> > > table and indexes even in case of abort/crash.
> > > As far as I see,it has been a big charge for vacuum.
> >
> > OK, how about making a copy of the heap table before starting vacuum,
> > moving all the tuples in that copy, create new index, and then move the
> > new heap and indexes over the old version. We already have an exclusive
> > lock on the table. That would be 100% reliable, with the disadvantage
> > of using 2x the disk space. Seems like a big win.
> >
>
> I heard from someone that old vacuum had been like so.
> Probably 2x disk space for big tables was a big disadvantage.

Yes, It is critical.

How about sequence like this:

* Drop indices (keeping somewhere index descriptions)
* vacuuming table
* recreate indices

If something crash, user have been noticed
to re-run vacuum or recreate indices by hand
when system restarts.

I use script like described above for vacuuming
- it really increase vacuum performance for large table.

--
Dmitry Samersoff, DM\S
dms(at)wplus(dot)net http://devnull.wplus.net
* there will come soft rains