Re: How often do I need to reindex tables? - Mailing list pgsql-general

Bill Moran wrote:
> In response to "Ezequias Rodrigues da Rocha" <ezequias.rocha@gmail.com>:
>> 2007/2/27, Jim C. Nasby <jim@nasby.net>:
>>> On Tue, Feb 27, 2007 at 11:26:02AM -0800, Dhaval Shah wrote:
>>>> I am planning to use 8.2 and the average inserts/deletes and updates
>>>> across all tables is moderate. That is, it is a moderate sized
>>>> database with moderate usage of tables.
>>>>
>>>> Given that, how often do I need to reindex the tables? Do I need to do
>>>> it everyday?
>>> No, you should very rarely if ever need to do it.
>
> I don't agree.  I think that regular indexing is mandatory under some
> workloads.


Bill, you are right but I believe Jim was speaking from a general
perspective. Generally speaking you should not have to reindex, or if
you do very rarely.

I too have a couple of databases we manage that require a reindex more
often than what would be considered normal, but a reindex is far from
the norm itself.

Joshua D. Drake


 Example:
> bacula=# select relname, relpages from pg_class where relkind='i' and relname not like 'pg_%' order by relname;
>             relname            | relpages
> -------------------------------+----------
>  basefiles_pkey                |        1
>  cdimages_pkey                 |        1
>  client_name_idx               |        2
>  client_pkey                   |        2
>  counters_pkey                 |        1
>  device_pkey                   |        1
>  file_fp_idx                   |    41212
> [...]
>
> bacula=# reindex database bacula;
> [...]
>
>             relname            | relpages
> -------------------------------+----------
>  basefiles_pkey                |        1
>  cdimages_pkey                 |        1
>  client_name_idx               |        2
>  client_pkey                   |        2
>  counters_pkey                 |        1
>  device_pkey                   |        1
>  file_fp_idx                   |    21367
> [...]
>
> There are some additional indexes that I've snipped from the output that also
> saw some benefit from reindexing, but let's just focus on file_fp_idx.
>
> Please note that the database you're looking at is reindexed _weekly_ by a
> cron job, which means the index bloat you're seeing in the above example is
> the result of normal activity since last Saturday.
>
> I've brought this up before, and I want to point it out again.  I really
> think there are certain workloads that require reindexing.  Luckily for
> this particular workload, it's easy to schedule a job to do so, since I
> know when the backups aren't running :)
>


--

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/