Re: Fillfactor in postgresql 9.2

On Tue, Jul 2, 2013 at 5:15 PM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>wrote:

> Niels Kristian Schjødt wrote:
> > I am experiencing a similar issue as the one mentioned in this post
> >
> http://stackoverflow.com/questions/3100072/postgresql-slow-on-a-large-table-with-arrays-and-lots-of-
> > updates/3100232#3100232
> > However the post is written for a 8.3 installation, so I'm wondering if
> the fillfactor problem is
> > still roughly the same in 9.2, and hence would have a similar effect
> when adjusted?
>
> Yes, lowering the fillfactor for a table will still
> increase the chances of HOT updates, improving performance
> and reducing the need for maintenance.
>

Our experience while testing HOT was a bit different (and I think that's
why the default fillfactor was left unchanged). Even if you start at 100,
the system quickly stabilizes after first update on every page. Even though
the first update is a non-HOT update, the subsequent update on the same
page will be a HOT update assuming there are no long running transactions
and HOT gets a chance to clean up the dead space left by previous update.

Having said that, it may not be a bad idea to start with a small free space
in each page, may be just enough to hold one more row (plus a few more
bytes for the line pointers etc).

Also, you need to be careful about updating many rows on the same page in a
single transaction or having an open long running transaction. They can
easily stop HOT's ability to aggressively clean up dead space and stop the
bloat.

Thanks,
Pavan
--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee