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

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