On Tue, Jul 2, 2013 at 5:15 PM, Albe Laurenz <laurenz.albe@xxxxxxxxxx> wrote:
Niels Kristian Schjødt wrote:Yes, lowering the fillfactor for a table will still
> 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?
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
