From: Alvaro Herrera <alvherre@commandprompt.com>
To: Ivan Voras <ivoras@fer.hr>
Cc: pgsql-performance@postgresql.org
Subject: Re: MVCC & indexes?
Date: Tue, 31 Oct 2006 19:36:32 -0300
Message-ID: <20061031223632.GG12008@alvh.no-ip.org> (raw)
In-Reply-To: <4547C65C.6090208@fer.hr>
References: <4547C65C.6090208@fer.hr>
Ivan Voras wrote:
> Ok, so MVCC is the best thing since a guy put a round stone on a stick
> and called it "the wheel", but I've seen several references on this list
> about "indexes not being under MVCC" - at least that's how I read it,
> the original posts were explaining why indexes can't be used for solving
> MIN()/MAX()/COUNT() aggregates. Is this correct?
>
> In particular, I'm trying to find out is there (b)locking involved when
> concurrently updating and/or inserting records in an indexed table. My
> guess is that, since PG does copy+delete on updating, even updating a
> non-indexed field will require fixups in the index tree (to point to the
> new record) and thus (b)locking.
Well, there certainly is locking involved in inserting index entries,
but it's more fine-grained than you seem to think. Only one page of the
index is locked at any time, resulting in that typically there's very
little blocking involved. Two processes can be inserting into the same
index concurrently (btree and GiST indexes at least; GiST only gained
concurrency in a recent release, I don't remember if it was 8.0 or 8.1).
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: alvherre@commandprompt.com, ivoras@fer.hr
Subject: Re: MVCC & indexes?
In-Reply-To: <20061031223632.GG12008@alvh.no-ip.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox