Thread: REINDEX Issue - DB Management

- How often do you REINDEX your databases?
- What do you verify on the database to know that it is time to reindex?

I currently have a PostgreSQL 7.4.7 server with a 500 MB database
(about 200 tables). I made a Shell script that calls
/contrib/reindexdb. Then I put it on crontab, the way it is executed
once a week and mails me the results after the operation. Do you think
it is reasonable?

--

Regards,

Rodrigo Hjort
GTI - Projeto PostgreSQL
CELEPAR - Cia de Informática do Paraná
http://www.pr.gov.br

"Rodrigo Hjort" <rodrigo.hjort@gmail.com> wrote
>- How often do you REINDEX your databases?
> - What do you verify on the database to know that it is time to reindex?
>

There is no unique answer to the first one. You can binary search your
optimal reindex rate. Suppose the workload is steady, then you first set
reindex rate at, say, one time per week, then try twice per week, if it gets
better, then try more frequent reindex. If not, then you can try 1 time per
two-weeks and so on.

Regards,
Qingqing