Skip site navigation (1) Skip section navigation (2)

Re: Pre-allocated free space for row updating (like

From: Simon Riggs <simon(at)2ndquadrant(dot)com>
To: josh(at)agliodbs(dot)com
Cc: pgsql-hackers(at)postgresql(dot)org,Satoshi Nagayasu <nagayasus(at)nttdata(dot)co(dot)jp>,"Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: Pre-allocated free space for row updating (like
Date: 2005-08-30 09:39:00
Message-ID: 1125394740.4010.436.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-hackers
On Wed, 2005-08-24 at 17:24 -0700, Josh Berkus wrote:
> Satoshi,
> 
> > I've created a new patch which can be applied to the current cvs tree.
> >
> > http://dpsql.sourceforge.net/pctfree.cvs.diff
> 
> Hmmm ... I don't see where I set the GUC.   How am I supposed to vary the 
> PCTFREE amount?
> 

This is strikingly similar to a patch I wrote in February and submitted
in March for performance prototyping (pgsql-patches). We followed up on
that patch with a detailed discussion on how we would implement that
feature. My patch was slated in just the same way this has been (and
rightfully so...).

The summary was:

1. Have a PCTFREE column added on a table by table basis
2. Apply PCTFREE for Inserts only
3. Allow Updates to use the full space in the block.

Having PCTFREE set for all tables will not produce a good performance
result. This definitely needs to be on a table by table basis because
different tables have different ratios of INSERT/UPDATE/DELETEs.

If you look at DBT-2, you'll see that only the STOCK table would benefit
from this optimization, since it has 100% UPDATEs and is also the
heaviest hit table in the workload. Other tables would not benefit at
all from having PCTFREE set... for example the HISTORY table which has
100% INSERTs would see a drop in performance as a result.

Best Regards, Simon Riggs



In response to

Responses

pgsql-hackers by date

Next:From: Mario WeilguniDate: 2005-08-30 09:58:37
Subject: Re: VACUUM/t_ctid bug (was Re: GiST concurrency commited)
Previous:From: Teodor SigaevDate: 2005-08-30 09:25:52
Subject: Re: VACUUM/t_ctid bug (was Re: GiST concurrency commited)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group