Re: Reducing relation locking overhead

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.

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL