pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Greg Stark <gsstark@mit.edu>
To: Bruce Momjian <pgman@candle.pha.pa.us>
Cc: Adi Alurkar <adi@sf.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Equivalent praxis to CLUSTERED INDEX?
Date: 27 Aug 2004 15:31:22 -0400
Message-ID: <87fz68ml85.fsf@stark.xeocode.com> (raw)
In-Reply-To: <200408271748.i7RHmfJ15766@candle.pha.pa.us>
References: <200408271748.i7RHmfJ15766@candle.pha.pa.us>

Bruce Momjian <pgman@candle.pha.pa.us> writes:

> Agreed.  What I am wondering is with our system where every update gets
> a new row, how would this help us?  I know we try to keep an update on
> the same row as the original, but is there any significant performance
> benefit to doing that which would offset the compaction advantage?

Hm. Posit a system where all transactions are short updates executed in
autocommit mode. 

In such a system as soon as a transaction commits it would take a very short
time before the previous record was a dead tuple.

If every backend kept a small list of tuples it had marked deleted and
whenever it was idle checked to see if they were dead yet, it might avoid much
of the need for vacuum. And in such a circumstance I think you wouldn't need
more than a pctfree of 50% even on a busy table. Every tuple would need about
one extra slot.

This would only be a reasonable idea if a) if the list of potential dead
tuples is short and if it overflows it just forgets them leaving them for
vacuum to deal with. and b) It only checks the potentially dead tuples when
the backend is otherwise idle.

Even so it would be less efficient than a batch vacuum, and it would be taking
up i/o bandwidth (to maintain indexes even if the heap buffer is in ram), even
if that backend is idle it doesn't mean other backends couldn't have used that
i/o bandwidth.

But I think it would deal with a lot of the complaints about vacuum and it
would make it more feasible to use a pctfree parameter to make clustering more
effective.

-- 
greg




view thread (41+ messages)  latest in thread

Message-ID: <87fz68ml85.fsf@stark.xeocode.com>
Permalink:  ../87fz68ml85.fsf@stark.xeocode.com/
Also on:    postgresql.org/message-id/87fz68ml85.fsf@stark.xeocode.com

 · 

reply

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: gsstark@mit.edu, pgman@candle.pha.pa.us, adi@sf.net
  Subject: Re: Equivalent praxis to CLUSTERED INDEX?
  In-Reply-To: <87fz68ml85.fsf@stark.xeocode.com>

* 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