Re: [Pgsphere-dev] GIST index concurrency concern

Daniel,

concurrency is a big issue of current implementation of GiST.
But it should don't bite you for READ ops !
-hackers mailing list is a very relevant mailing list for GiST
discussions. It's pity we several times claimed to work on GiST
concurrency and recovery, but never got a chance :)
I see Neil become interested in GiST concurrency, though.

Oleg
On Tue, 9 Nov 2004, Daniel Ceregatti wrote:

> Hi,
>
> It's recently come to my attention that GIST indices suffer from
> concurrency issues. I have already developed a dating sites using GIST
> for use with attributes using the intarray contrib, and for Earth
> distance/radius calculations using pg_sphere.
>
> I'm wondering if I haven't shot myself in the foot here. So far, I
> understand that a GIST index will be locked by a backend for any DML.
> Basically I'm concerned that my database will not scale in the manner
> that I was hoping, because the sites that access the database are to be
> used by many multiple concurrent users, doing some DML.
>
> I expect my site to sustain something around 1000-3000 new user
> acquisitions per day, all of which will account for an insert into 3
> GIST indices. Additionally there will be people that will be updating
> their attributes and locations as well, but this will probably only
> account for a small fraction of the DML. We don't allow people to delete
> stuff.
>
> My concern now is this concurrency issue. My question is: Is there
> anyone out there using a GIST index on a database where there's a lot of
> DML? Should I be concerned with this issue at all?
>
> If so, what can be done to minimize the impact of heavy DML on a GIST
> index? I've pondered rolling all DML into queues via triggers and then
> de-queuing them in one transaction every so often, like 15 minutes, via
> cron. Any other suggestions?
>
> I'm posting to this list because I understand that both Oleg and Teodor
> read it, and I found no other relevant list. If I've misposted, please
> accept my apology and please direct me to the appropriate list.
>
> Thanks,
>
> Daniel
>
>

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83