Re: How often do I need to reindex tables?

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. 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 :)

--
Bill Moran
Collaborative Fusion Inc.