Re: How often do I need to reindex tables?

Bill Moran wrote:
> In response to "Ezequias Rodrigues da Rocha" <ezequias(dot)rocha(at)gmail(dot)com>:
>> 2007/2/27, Jim C. Nasby <jim(at)nasby(dot)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/