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

On Wed, 6 Jan 1999, Bruce Momjian wrote:

> > > From: Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us>
> >
> > > > insert a few row in a table (>50000) and do
> > > > delete from mytable;
> > > > vacuum verbose analyze;
> > > >
> > > > Why is this that slow?
> > >
> > > Analyze checks every column in every row.
> >
> > even if you only type "vacuum verbose" it takes _very_ long.
> >
> > I deleted _all_ records with "delete from mytable;" before.
> > A drop and a new create is faster. But what is when you delete (maybe
> > 100000) rows but keep 100 in the table?
> >
> > I use 6.4.2 on NetBSD/i486 (that box makes gets 12MBytes/sec via the
> > filesystem out of the drives)
>
> Not sure what to say. Vacuum does take a while, and it is often faster
> to drop and recreate.

Let's ignore the 'analyze' part first...take a simple 'vacuum'
command...what takes the longest? My understanding is a vacuum
simplistically, takes and moves all rows "up" in the file to fill in any
blanks resulting from updates and deletes, then truncates the end of the
file...

If so, is there no way of having vacuum running on its own?

Basically, if my understanding is remotely correct, vaccum is
defragmenting the table...so why can't the defragmenting be performed
during idle time...or, at least some of it.

Start at the top of the table, go to the first 'blank' section (a deleted
record)...find the next good record that will fit in the space, move it
there...clear out the old space, etc...

if dba issues a 'vacuum', lock the table and do all records at once, but
otherwise try and vacuum the table live...

With the new MVCC serialization, this concept should be less intrusive on
readers, no?

Marc G. Fournier
Systems Administrator @ hub.org
primary: scrappy(at)hub(dot)org secondary: scrappy(at){freebsd|postgresql}.org