Re: Fillfactor in postgresql 9.2

Поиск
Список
Период
Сортировка
От Pavan Deolasee
Тема Re: Fillfactor in postgresql 9.2
Дата
Msg-id CABOikdPKuQuRJcThLnRrmpzoVuAgghBrGEpKbEDet-6qO7aSZQ@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Fillfactor in postgresql 9.2  (Albe Laurenz <laurenz.albe@wien.gv.at>)
Список 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 по дате отправления:

Предыдущее
От: Albe Laurenz
Дата:
Сообщение: Re: Fillfactor in postgresql 9.2
Следующее
От: Andrea Suisani
Дата:
Сообщение: [OT] A flash filesystem tuning guide