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

Re: COPY Fillfactor patch

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Hannu Krosing <hannu(at)tm(dot)ee>
Cc: Simon Riggs <simon(at)2ndquadrant(dot)com>,Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-patches(at)postgresql(dot)org,Neil Conway <neilc(at)samurai(dot)com>
Subject: Re: COPY Fillfactor patch
Date: 2005-04-20 13:03:42
Message-ID: 200504201303.j3KD3g625132@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-patches
My guess is that the FILLFACTOR will actually be a GUC variable rather
than a clause to CREATE INDEX or REINDEX or CLUSTER.

---------------------------------------------------------------------------

Hannu Krosing wrote:
> On T, 2005-04-12 at 18:38 +0100, Simon Riggs wrote:
> > On Tue, 2005-04-12 at 09:56 -0400, Tom Lane wrote:
> 
> (Neil, I added you to CC: to show you at least two more places where
> sparse heaps can be generally useful and not tweaks for single
> benchmark)
> 
> > > Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> > > > During recent tuning of the TPC-C workload, I produced the following
> > > > patch to force COPY to leave some space in each data block when it loads
> > > > data into heap relations.
> 
> When I comtemplated a similar idea some time ago, my primary goal was
> reducing disk head movement during massive updates. 
> 
> At that time it seemed to me cheaper to not leave space in each page,
> but to leave each Nth page empty, as more new tuples will be punt on the
> same page and thus cause less WAL writes. 
> 
> Warning: This may be a false assumption - I did not check from code, if
> this is actually so for any or even a significant number of cases.
> 
> > > Put the info into the Relation
> > > structure instead of cluttering heap_insert's API.  (This would mean
> > > that *every* heap_insert is affected, not only COPY, which is what you
> > > want anyway I would think.)
> > 
> > Well, I intended it to work *only* for copy, not for insert, which is
> > why I did it that way. 
> 
> To be more generally useful similar thing should be added to VACUUM FULL
> and CLUSTER.
> 
> And perhaps some weird LAZY version of "VACUUM EXPAND" could be written
> as well, for keeping the holes from filling up in constantly growing
> databases.
> 
> Having these holes is also essential, if we want a cheap way to keep
> data in CLUSTER order after initial CLUSTER command - if we do have free
> space everywhere in the file, we can just put each new tuple on the
> first page with free space on or after its preceeding tuple in cluster
> index.
> 
> > Anyway, when I get time, I'll finish off the patch. Unless other readers
> > would like to follow on.
> 
> I hope you will you will get that time before 8.1 ships :)
> 
> -- 
> Hannu Krosing <hannu(at)tm(dot)ee>
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman(at)candle(dot)pha(dot)pa(dot)us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

In response to

Responses

pgsql-patches by date

Next:From: Simon RiggsDate: 2005-04-20 13:07:53
Subject: Re: COPY Fillfactor patch
Previous:From: Simon RiggsDate: 2005-04-20 08:27:51
Subject: Re: WIP XLog Switch

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