Re: [Pgsphere-dev] GIST index concurrency concern - Mailing list pgsql-hackers

Oleg,Daniel and I have both been collaborating on this structure for a while now. 
We are aware that GiST reads work very fast. But won't they be "paralyzed" 
when there are writes? Both of us are working on dating sites, and the main 
problem that concerns us is a very heavy traffic load. At this point I am 
planning to queue all changes to a GiST index and commit them every 10-15 
minutes. Is that really necessary? It's realistic to assume here that if 
there is a problem with locking the table for writes, it will be a problem in 
this situation because this structure is going to be hit VERY hard (and 
Daniel's situation is on an even larger scale). We hope that we can alleviate 
that with a "transaction queue", but this is not a simple fix. Have you seen 
any projects that were under a heavy load using a GiST index, and were they 
able to avoid being "paralyzed" somehow?

Thanks in advance,
Patrick

On Tuesday 09 November 2004 22:08, Oleg Bartunov wrote:
> Oleg Bartunov <oleg@sai.msu.su>

> 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
> >
>