Re: VACUUM optimization ideas.

Alfred Perlstein wrote:

> The idea is that since rows are marked deleted it's ok for the
> vacuum to fill them with data from the tail of the table as
> long as no transaction is in progress that has started before
> the row was deleted.

Well, isn't one of the advantages of vacuuming in the reordering it
does? With a "fill deleted chunks" logic, we'd have far less order in
the databases.

> This may allow the vacuum process to copyback all the data without
> a lock,

Nope. Another process might update the values in between move and mark,
if the record is not locked. We'd either have to write-lock the entire
table for that period, write lock every item as it is moved, or lock,
move and mark on a per-record base. The latter would be slow, but it
could be done in a permanent low priority background process, utilizing
empty CPU cycles. Besides, it probably could not only be done simply
filling from the tail, but also moving up the records in a sorted
fashion.

> #2
>
> Reducing the amount of scanning a vaccum must do:
>
> It would make sense that if a value of the earliest deleted chunk
> was kept in a table then vacuum would not have to scan the entire
> table in order to work, it would only need to start at the 'earliest'
> invalidated row.

Trivial to do. But of course #1 may imply that the physical ordering is
even less likely to be related to the logical ordering in a way where
this helps.

> The utility of this (at least for us) is that we have several tables
> that will grow to hundreds of megabytes, however changes will only
> happen at the tail end (recently added rows).

The tail is a relative position - except for the case where you add
temporary records to a constant default set, everything in the tail will
move, at least relatively, to the head after some time.

> If we could reduce the
> amount of time spent in a vacuum state it would help us a lot.

Rather: If we can reduce the time spent in a locked state while
vacuuming, it would help a lot. Being in a vacuum is not the issue -
even permanent vacuuming need not be an issue, if the locks it uses are
suitably short-time.

Sevo

--
sevo(at)ip23(dot)net