Re: vacuum, performance, and MVCC

On 6/24/06, Mark Woodward <pgsql(at)mohawksoft(dot)com> wrote:
> Currently it looks like this:
>
> ver001->ver002->ver003->...-verN
>
> That's what t_ctid does now, right? Well, that's sort of stupid. Why not
> have it do this:
>
> ver001->verN->...->ver003->ver002->|

Heh, because that's crazy. The first time you insert a key into the
index it will point to v1 of a tuple... say after 5 updates you have
v2,v3,v4,v5... your c_tid pointer chain looks like v1
(original)->v2->v3->v4-v5 (newest). However, your whole idea is based
on not having to do another index insert for unchanged keys, so the
index still points to v1... which means you have to follow the c_tid
chain to get to the newest version just like a sequential scan. I
don't see how you think you can reverse pointer it.

> This will speed up almost *all* queries when there are more than two
> version of rows.

Nope.

> When you vacuum, simply make the latest version (verN) the key row (ver001).

How are you going to do this without a ton of locking... remember, the
index is pointing to v1 with a tid... so you'll have to physically
move the newest version v5 to v1's tid from wherever it was... like a
vacuum full on steroids. Unless of course, you rebuild the index...
but that's not a solution either.

--
Jonah H. Harris, Software Architect | phone: 732.331.1300
EnterpriseDB Corporation | fax: 732.331.1301
33 Wood Ave S, 2nd Floor | jharris(at)enterprisedb(dot)com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/