Re: Mulit-Vesions and Vacuum

On Thu, Jul 18, 2002 at 06:43:03PM -0700, Anthony Berglas wrote:
> Hello All,
>
> Does anyone really know how this multi version concurrency really works?

Somewhat.

> The basic idea is simple, never update anything so that you can always get
> read-consistent queries by reading behind the updates.  But the details are
> missing in the docs.  In particular:-
>
> 1. How long do previous version hang arround?  As long as the oldest
> transaction, or until a Vacuum?

Tuples are valid for as long as a transaction can see them. Vacuum cleans
out any tuples that are no longer valid.

> 2. How expensive are they?  Do the multi-version copies clutter the main
> database blocks, or are they stored elsewhere?  And if I update one field of
> a row, is the entire row copied or just the field?

To store them elsewhere would imply copying them to a new place when you
update them, which could cause performance issues. The entire row is copied
and usually appended to the end of the table.

> 3. What does Vacuum really do?  Will it interfere with open transactions, or
> will it know to leave multi version copies for existing transactions?

Old vacuum used to interfere. There is the new non-blocking vacuum which can
cleanup a lot of stuff but not everything. VACUUM FULL is required to free
up disk-space.

> PS.  I have done a few experiements on how the locking really works, see
> http://www.SimpleORM.org/DBNotes.html.  Note the difference with Oracle,
> which I think is better and easy to implement given the basic MV approach.

If you want serialisable, then you should ask for it (SET TRANSACTION LEVEL
SERIALIZABLE or something like that). The default is not-serializable (as
you saw).

> 1.  Why not just use the transaction log to implement MV?  Each record only
> needs to store a pointer into the logs to the previous update.  And the logs
> would log that pointer, forming a backward pointing linked list.  Most of
> the time this will not be needed, or will refer to a very recently written
> tail of the log which will already be cached in memory.   This approach
> would put an end to Oracle's issus with running out of "Rollback Segments".
> It would also put an end to Postgres performance problems on inserts and
> updates -- why be slower than MySQL?  You need a transaction log anyway, so
> no extra overhead should be added for MV unless a query actually needs to
> read behind updated data.

This bit I'm less sure about. Each tuple does contain a pointer to the
previous incarnation, but I'm not exactly sure what it's used for. The
problem is that only some tuples are usually updated, most are left alone
and they are never moved (except maybe by vacuum). As for insert speed, I
can always saturate the disk subsystem doing straight inserts, so making it
faster doesn't seem too useful. The problem with updates is that you have to
read the old tuple first which ruins the disk performance. Though WAL helps
a lot here IIRC.

> 2.  If you are going to go to the trouble of keeping multi versions for
> locking, why not make them available for the application as well?  One of
> the painful features to implement is audit trails of how records get to be
> the way they are.  The database could do that automatically.

Look in the docs for Time Travel. It used to be supported but was tossed a
while ago since it could be more efficiently implemented using triggers and
it had a tendency to use a lot of disk space.

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.