Multi-Versions and Vacuum

From: Anthony Berglas <anthony(dot)berglas(at)lucida(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Multi-Versions and Vacuum
Date: 2002-07-19 04:23:48
Message-ID: E6062644D006474BAEB2A3933C1C459C22FE4C@lucidamail.lucidainc.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Thanks to all the people that responed. Summaries and notes:-

VACUUM

The consensus seems to be that old copies are not removed until a Vacuum is
performed. When a Vacuum is performed then only versions older than the
start of the oldest transaction are removed.

(Compare with Oracle -- a certain amount of space is allocated for multi
versions ("Rollback Segments"). Old versions are moved there, I think.
When the space is exhausted then then old versions are dropped
automatically. Occasionally this can cause transactions to be aborted
because the old versions they need are no longer available.)

What actually is the difference between Vaccum and Vacuum Full? I assume
that the former must release some disk space, otherwise it would be useless?

STORAGE

It appears that the versions are stored in the same blocks as the normal
data. Also, seems that the whole row is copied (correct?). This will
reduce the efficiency of the cache to some extent by having blocks
containing non-pertinent data.

LOCKING

The docs mentioned phantoms in the Serializable section, implying that they
were not trapped by Read Committed. But they are, good.

My note about Oracle is that in its Read Committed mode transactions are
safer without being Serializable. It is half way between what Postgres call
Read Committed and Serializable. And if I think that in that regard Oracle
got it right. Safer transactions without Serializable rollback problems
with no real downside. Particularly for reports.

USING THE LOGS FOR MULTI VERSIONS

If anyone know why this is a bad idea I'd be interested. Seems to be a win
win to me. No need to vacuum, no additional overhead during update (you
need the logs anyway), fast queries, less disk space. (This is not what
Oracle does.)

TIME TRAVEL

Sounds fun. But hard to get right.

EXISTING DOCS

When this thread settles I'll add a note to the interactive docs.

(Thanks Neil for taking the trouble to provide doc fragments. But if you
read them again you will note that they are not at all clear on the actual
question that I asked. Which is why I asked them.)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Neil Conway 2002-07-19 04:51:38 Re: Multi-Versions and Vacuum
Previous Message pgsql-gen Newsgroup (@Basebeans.com) 2002-07-19 03:55:02 new MVC with Standard Tags and DB sample (of course in pgSQL 7.2)