Re: Best practice when reindexing in production

От: Jesper Krogh
Тема: Re: Best practice when reindexing in production
Дата: ,
Msg-id: 51A63717.7070301@krogh.cc
(см: обсуждение, исходный текст)
Ответ на: Best practice when reindexing in production  (Niels Kristian Schjødt)
Ответы: Re: Best practice when reindexing in production  (Daniele Varrazzo)
Список: pgsql-performance

Скрыть дерево обсуждения

Best practice when reindexing in production  (Niels Kristian Schjødt, )
 Re: Best practice when reindexing in production  (Magnus Hagander, )
  Re: Best practice when reindexing in production  (Armand du Plessis, )
   Re: Best practice when reindexing in production  (Niels Kristian Schjødt, )
  Re: Best practice when reindexing in production  (Niels Kristian Schjødt, )
   Re: Best practice when reindexing in production  (Magnus Hagander, )
   Re: Best practice when reindexing in production  (Matheus de Oliveira, )
    Re: Best practice when reindexing in production  (Igor Neyman, )
     Re: Best practice when reindexing in production  (Matheus de Oliveira, )
      Re: Best practice when reindexing in production  (Igor Neyman, )
 Re: Best practice when reindexing in production  (Jesper Krogh, )
  Re: Best practice when reindexing in production  (Daniele Varrazzo, )
   Re: Best practice when reindexing in production  (Alan Hodgson, )
    Re: Best practice when reindexing in production  (Daniele Varrazzo, )
 Re: Best practice when reindexing in production  (Jeff Janes, )

On 29/05/13 14:24, Niels Kristian Schjødt wrote:On 29/05/13 14:24, Niels
Kristian Schjødt wrote:
> Hi,
>
> I have a database with quite some data (millions of rows), that is heavily updated all the time. Once a day I would
liketo reindex my database (and maybe re cluster it - don't know if that's worth it yet?). I need the database to be
usablewhile doing this (both read and write). I see that there is no way to REINDEX CONCURRENTLY - So what approach
wouldyou suggest that I take on this? 

Hi.

Since you still dont know wether it is worth it or not, I would strongly
suggest that you test this out before. Simply just creating an index
next to the old one with the same options (but different name) and
compare sizes would be simple.

Second, if the new index is significantly smaller than the old on, I
suggest that you try to crank up the autovacuum daemon instead of
blindly dropping and creating indexes, this will help to mitigate the
bloat you're seeing accumulating in above test.

Cranking up autovacuum is going to have significan less impact on the
concurrent queries while doing it and can help to maintain the database
in a shape where regular re-indexings shouldnt be nessesary. Autovacuum
has build in logic to sleep inbetween operations in order to reduce the
IO-load of you system for the benefit of concurrent users. The approach
of duplicate indices will pull all the resources it can get and
concurrent users may suffer while you do it..

Jesper

--
Jesper



В списке pgsql-performance по дате сообщения:

От: Daniele Varrazzo
Дата:
Сообщение: Re: Best practice when reindexing in production
От: Josh Berkus
Дата:
Сообщение: Performance bug in prepared statement binding in 9.2?