Re: BUG #5946: Long exclusive lock taken by vacuum (not full)

On Fri, Mar 25, 2011 at 7:43 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I don't recall any particular discussion of making the user contend with
> that.  My thought would be to do something like enlarging the table by
> 10% anytime we need to extend it.  The main issue here is where to cause
> that to happen.  An individual backend that just wants to store one more
> tuple probably shouldn't have to do that much work.

Just for reference this is how Oracle *used* to behave. It was widely
hated and led to all sorts of problems. Best practice was to pick a
reasonable size for your tablespace and pre-allocate that size and set
future increments to be that size with 0% growth.

Otherwise the problem with growing 10% is that it's hard for a DBA to
know how much space headroom he needs on the drive. The database might
grow at any time by a hard to predict amount of space which isn't
proportional or connected in any way with the usage. If your database
starts out small and you load a terabyte into it then by the time it's
full that 10% exponential growth is 90GB and adding one more row might
trigger that allocation at any time.

And at the same time the first few hundred gigabytes are still
fragmented and allocated in small chunks due to the initial load
starting slowly.

It was considered much better to get predictable behaviour by setting
the initial extent size to something like 1GB and then setting it to
grow by 1GB with no growth.

--
greg