Re: COPY Fillfactor patch

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>