Skip site navigation (1) Skip section navigation (2)

Re: Reducing relation locking overhead

From: Simon Riggs <simon(at)2ndquadrant(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Greg Stark <gsstark(at)mit(dot)edu>,Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>,pgsql-hackers(at)postgresql(dot)org
Subject: Re: Reducing relation locking overhead
Date: 2005-12-02 14:53:27
Message-ID: 1133535207.2906.582.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-hackers
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....

I know at least one other RDBMS that uses optimistic locking when
creating indexes. It checks the table description, builds the index with
a read lock, then checks the table description again before attempting
to lock the catalog, "create" the index and then complete. There is a
risk of getting a "table restructured error" after the build is nearly
complete. If we did that, then we wouldn't need to lock the indexes
because you wouldn't be able to see an index until it was built. Doing
something similar might allow us to have online CREATEs yet without a
locking overhead. 

24x7 operation is actually fairly common. Maybe not with a strong SLA
for availability, but many websites and embedded apps are out there all
the time. The PostgreSQL claim to fame has concurrency at the top of the
list, so we should assume that in all we do.

Best Regards, Simon Riggs


In response to

Responses

pgsql-hackers by date

Next:From: Philip WarnerDate: 2005-12-02 14:55:09
Subject: Optimizer oddness, possibly compounded in 8.1
Previous:From: luckyghio@katamail.comDate: 2005-12-02 14:44:41
Subject: postgres questions (semi-joins, algebraic space)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group