On Fri, Mar 08, 2002 at 03:46:11PM -0500, Gregory Wood wrote:
> > > > Do sequential scans go over the entire space, including the space not in
> > > > use? It would be great if there was some kind of optimization that could
> > > > move the empty space towards the end. It would probably be an expensive
> > > > operation, but it may be very helpfull on databases with a big turnaround.
> > >
> > > The only difference between doing that and doing a VACUUM FULL would be that
> > > the disk usage would remain the same.
> >
> > There is one other extremely important difference. VACUUM FULL locks the
> > table/database.
>
> But to move around records, you *would* have to lock the table. This could
> be an incorrect assumption, but I believe that you would need to aquire an
> AccessExclusiveLock to rearrange the contents of the table, and that's the
> same lock aquired by VACUUM FULL.
Surely you only need to lock the actual pages being changed. Actually, you
have this tiny little problem with sequential and index scan currently in
progress.
> To put it another way, when you delete (or update) the first record in a
> particular table, to move that record to the end would require moving *all*
> the records up by one. This would destroy the existing MVCC system. You
> would essentially be VACUUM FULLing every time you did a DELETE or UPDATE.
Wouldn't you only need to move the last record to be the first one. Destroys
order, but does pack the database.
Actually, it seems to me you could get this to play with MVCC by treating
the move as a UPDATE that does nothing. Copy the tuple from the end to the
beginning, mark the end one as deleted and the beginning one as new. Voila,
tuple moved and everything will eventually use the new one and ignore the
old one.
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
> If the company that invents a cure for AIDS is expected to make their
> money back in 17 years, why can't we ask the same of the company that
> markets big-titted lip-syncing chicks and goddamn cartoon mice?
