From: Tom Lane <tgl@sss.pgh.pa.us>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: Tatsuo Ishii <t-ishii@sra.co.jp>
Cc: marty@outputservices.com
Cc: herve@elma.fr
Cc: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL clustering VS MySQL clustering
Date: Sun, 23 Jan 2005 15:40:03 -0500
Message-ID: <6346.1106512803@sss.pgh.pa.us> (raw)
In-Reply-To: <1106511352.31592.112.camel@localhost.localdomain>
References: <41F144FE.30902@outputservices.com>
<41F14758.4030602@outputservices.com>
<20050122.121300.41631131.t-ishii@sra.co.jp>
<25335.1106428223@sss.pgh.pa.us>
<1106511352.31592.112.camel@localhost.localdomain>
Simon Riggs <simon@2ndquadrant.com> writes:
> Changing the idea slightly might be better: if a row update would cause
> a block split, then if there is more than one row version then we vacuum
> the whole block first, then re-attempt the update.
"Block split"? I think you are confusing tables with indexes.
Chasing down prior versions of the same row is not very practical
anyway, since there is no direct way to find them.
One possibility is, if you tried to insert a row on a given page but
there's not room, to look through the other rows on the same page to see
if any are deletable (xmax below the GlobalXmin event horizon). This
strikes me as a fairly expensive operation though, especially when you
take into account the need to get rid of their index entries first.
Moreover, the check would often be unproductive.
The real issue with any such scheme is that you are putting maintenance
costs into the critical paths of foreground processes that are executing
user queries. I think that one of the primary advantages of the
Postgres storage design is that we keep that work outside the critical
path and delegate it to maintenance processes that can run in the
background. We shouldn't lightly toss away that advantage.
There was some discussion in Toronto this week about storing bitmaps
that would tell VACUUM whether or not there was any need to visit
individual pages of each table. Getting rid of useless scans through
not-recently-changed areas of large tables would make for a significant
reduction in the cost of VACUUM.
regards, tom lane
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: tgl@sss.pgh.pa.us, simon@2ndquadrant.com, t-ishii@sra.co.jp, marty@outputservices.com, herve@elma.fr
Subject: Re: PostgreSQL clustering VS MySQL clustering
In-Reply-To: <6346.1106512803@sss.pgh.pa.us>
* 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