Re: Reducing relation locking overhead

On Sat, 2005-12-03 at 08:47 -0500, Robert Treat wrote:
> On Friday 02 December 2005 09:53, Simon Riggs wrote:
> > On Fri, 2005-12-02 at 02:14 -0500, Tom Lane wrote:
> > > Greg Stark <gsstark(at)mit(dot)edu> writes:
> > > > It was a *major* new feature that many people were waiting for when
> > > > Oracle finally implemented live CREATE INDEX and REINDEX. The ability
> > > > to run create an index without blocking any operations on a table, even
> > > > updates, was absolutely critical for 24x7 operation.
> > >
> > > Well, we're still not in *that* ballpark and I haven't seen any serious
> > > proposals to make us so. How "absolutely critical" is it really?
> > > Is REINDEX-in-parallel-with-reads-but-not-writes, which is what we
> > > actually have at the moment, an "absolutely critical" facility?
> >
> > REINDEX isn't run that regularly, so perhaps might warrant special
> > attention. (I think there are other things we could do to avoid ever
> > needing to run a REINDEX.)
> >
> > CREATE/DROP INDEX is important however, since we may want to try out new
> > index choices without stopping access altogether. But we do also want
> > the locking contention to be reduced also....
> >
>
> Just thought I'd toss in this random data point... I know I still have a least
> one 7.3 system running were reindexes are a part of the regular routine and
> the ability to query against the table simultaneously is certainly
> approaching "absolutly critical" territory. Hoping to get that system
> upgraded by the end of the month, at which point the frequency of reindex
> will surely decrease, but I'm not sure it's going to go away completly. I
> could probably get by at that point with DROP/CREATE INDEX but it wouldn't be
> my preferred way to do it.

Understood. At 7.3, REINDEXing is essential, since rows never got
deleted and space was not reused. That is not the case now, hence a
REINDEX is less often required.

Best Regards, Simon Riggs