Re: How often do I need to reindex tables?

On Fri, 2007-03-02 at 16:39 -0500, Tom Lane wrote:
> Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> > Isn't a REINDEX still needed in the case of monotonically increasing
> > keys, such as in a sequence or timestamp index? I also delete tuples, so
> > that results in a forward-shifting range of keys.
>
> No, that shouldn't be a problem, if you're maintaining a constant key
> range width (that is, *all* the old entries get deleted). The only
> pattern I'm aware of that causes a problem is if you leave a small
> subset of the keys behind, for instance insert every few minutes and
> then later delete all but one entry per day. In this situation you may
> end up with an index containing as few as one entry per page. We
> don't have any mechanism short of REINDEX to collapse nonempty index
> pages together, so that way lies bloat. But if you delete all the old
> entries then the pages get recycled and there shouldn't be a problem.
>

You just described this particular table, so I will need to continue
REINDEXing. It's getting maybe 10-50 inserts per second, and most expire
in an day. However, a small percentage hang around for much longer.

REINDEX isn't a problem for me, because there are periods of low usage.

I think if I really wanted to eliminate REINDEX I could move the few
remaining records into another table and have a view accross them.

Regards,
Jeff Davis