RE: [HACKERS] Re: [PORTS] vacuum takes too long

> > With MVCC an occasional 'vacuum analyze' should only be
> noticed from the
> > performance improvements.  As far as I can tell most of the
> work done by
> > an analyze is in reading the table data.  If you make sure
> to write the
> > new information at the end of the transaction you only lock
> the indexes
> > for the amount of time it takes to write them.
> >
> > I see a 'vacuum analyze' being less of a problem than 'vacuum'.
> > Any of you experts can contradict my assumptions.
>
> The problem is that vacuum analyze does both vacuum and analyze.
> Analyze takes so long, we figured we might as well vacuum
> too.  Maybe we
> need to change that.
It seems that VACUUM would have to lock most of the rows in a table even
with MVCC; where as ANALYZE can benefit directly from MVCC never
blocking a reader.  I for one agree in the separation (hey I always
thought they were separate).
How hard would it be to have VACUUM cheat on the table write?  Have
VACUUM take advantage of the fact that we are actually using a file
system file as much as is possible in it's VACUUM.  Therefore the actual
moving of the rows could be accomplished in the time it takes to select
all the rows into a new table file and then change the file.  There
might be some issues with file-node trouble in the system catalogs, but
those could be taken care of quickly as well.  The only things that
you'd have to watch for is a write to the table in the middle of your
reading of the rows.  CLUSTER could also use the same system with a
order by on an index.  Let me know what you think.
You know what else...  MVCC would allow us to ignore updating indexes on
a COPY if combined with the new quicker ANALYZE after the COPY.
    Keeping the ideas coming,
    -DEJ