On Fri, 2007-03-02 at 16:39 -0500, Tom Lane wrote: > Jeff Davis <pgsql@xxxxxxxxxxx> 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
