Re: Fillfactor in postgresql 9.2 - Mailing list pgsql-performance

From Pavan Deolasee
Subject Re: Fillfactor in postgresql 9.2
Date
Msg-id CABOikdPKuQuRJcThLnRrmpzoVuAgghBrGEpKbEDet-6qO7aSZQ@mail.gmail.com
Whole thread Raw
In response to Re: Fillfactor in postgresql 9.2  (Albe Laurenz <laurenz.albe@wien.gv.at>)
List pgsql-performance



On Tue, Jul 2, 2013 at 5:15 PM, Albe Laurenz <laurenz.albe@wien.gv.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

pgsql-performance by date:

Previous
From: Albe Laurenz
Date:
Subject: Re: Fillfactor in postgresql 9.2
Next
From: Andrea Suisani
Date:
Subject: [OT] A flash filesystem tuning guide