Re: [HACKERS] pgsphere
Hi Oleg, On Sun, Jan 8, 2012 at 1:19 PM, Oleg Bartunov o...@sai.msu.su wrote: Dave, The situation with pgshpere is so, that I think we need new developer, since Janko keep silence :) I wrote him several time, since I wanted pgsphere now could benefit very much from our KNNGiST feature. This is number one development from my point of view. I and Teodor have no time to work on pgsphere, sorry. But, there are some astronomers I'm working with, who can take part in this. Sergey Karpov has done extensive benchmarks of q3c, rtree and pgsphere and found the latter still has some benefits in some workload, so we are interesting in development. Regards, Oleg So where do we go from here ? Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On Fri, 6 Jan 2012, Andrew Dunstan wrote: On 01/06/2012 12:32 PM, Dave Cramer wrote: I've been asked by someone to support pgshpere. It would appear that the two project owners are MIA. If anyone knows different can they let me know ? Does anyone have any objection to me taking over the project? One of the owners is Teodor, who is a core committer ... I hope he's not MIA. cheers andrew Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgsphere
On 01/10/2012 09:04 AM, Dave Cramer wrote: So where do we go from here ? First, please note that -hackers is not the right place for this discussion. pgsphere is a pgfoundry project, which is not the province of -hackers. As I suggested, the best solution is for Teodor to add you as a project admin. Oleg, could you please follow this up? Speaking with my pgfoundry admin hat on, I am extremely reluctant to take control of a project away from its owners. If you don't get any action in a week or so, you can approach the pgfoundry admins for help. In the meantime you might want to fork the code onto github or bitbucket. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgsphere
I think Dave you fork project, I suggest different name pgsphere-2 to avoid confusion. github would be ok. Teodor and I are really busy guys, so I don't believe we could participate much, except discussion and testing. We implemented KNNGiST, you add neighbourhood search support to pgsphere :) Oleg On Tue, 10 Jan 2012, Dave Cramer wrote: Hi Oleg, On Sun, Jan 8, 2012 at 1:19 PM, Oleg Bartunov o...@sai.msu.su wrote: Dave, The situation with pgshpere is so, that I think we need new developer, since Janko keep silence :) I wrote him several time, since I wanted pgsphere now could benefit very much from our KNNGiST feature. This is number one development from my point of view. I and Teodor have no time to work on pgsphere, sorry. But, there are some astronomers I'm working with, who can take part in this. Sergey Karpov has done extensive benchmarks of q3c, rtree and pgsphere and found the latter still has some benefits in some workload, so we are interesting in development. Regards, Oleg So where do we go from here ? Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On Fri, 6 Jan 2012, Andrew Dunstan wrote: On 01/06/2012 12:32 PM, Dave Cramer wrote: I've been asked by someone to support pgshpere. It would appear that the two project owners are MIA. If anyone knows different can they let me know ? Does anyone have any objection to me taking over the project? One of the owners is Teodor, who is a core committer ... I hope he's not MIA. cheers andrew Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgsphere
On Sun, 2012-01-08 22:19:53 +0400, Oleg Bartunov o...@sai.msu.su wrote: pgsphere now could benefit very much from our KNNGiST feature. This is number one development from my point of view. I and Teodor have no time to work on pgsphere, sorry. But, there are some astronomers I'm working with, who can take part in this. Sergey Karpov has done extensive benchmarks of q3c, rtree and pgsphere and found the latter still has some benefits in some workload, so we are interesting in development. Could the PostGIS stuff be abused for stellar coordinates? MfG, JBG -- Jan-Benedict Glaw jbg...@lug-owl.de +49-172-7608481 Signature of: Träume nicht von Deinem Leben: Lebe Deinen Traum! the second : signature.asc Description: Digital signature
Re: [HACKERS] pgsphere
On Tue, 10 Jan 2012, Jan-Benedict Glaw wrote: On Sun, 2012-01-08 22:19:53 +0400, Oleg Bartunov o...@sai.msu.su wrote: pgsphere now could benefit very much from our KNNGiST feature. This is number one development from my point of view. I and Teodor have no time to work on pgsphere, sorry. But, there are some astronomers I'm working with, who can take part in this. Sergey Karpov has done extensive benchmarks of q3c, rtree and pgsphere and found the latter still has some benefits in some workload, so we are interesting in development. Could the PostGIS stuff be abused for stellar coordinates? There is no principal difference between celestial sphere and earth, it's a matter of conversion between coordinates. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgsphere
On 1/10/12 9:54 AM, Andrew Dunstan wrote: Speaking with my pgfoundry admin hat on, I am extremely reluctant to take control of a project away from its owners. If you don't get any action in a week or so, you can approach the pgfoundry admins for help. In the meantime you might want to fork the code onto github or bitbucket. Man, the pgfoundry admin hat has to be one of the least collectible ones around. Not exactly a lot of demand for them, and everyone who has one would happily give theirs away. I don't want to drag this off-topic thread on, but it's worth mentioning that http://wiki.postgresql.org/wiki/Project_Hosting has started a small migration guide of sorts for where else you might host this sort of project at. It's not necessarily obvious what pgfoundry provides relative to other sites, or what the trade-offs in the other possibilities are. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgsphere
On Wed, 2012-01-11 00:43:25 +0400, Oleg Bartunov o...@sai.msu.su wrote: On Tue, 10 Jan 2012, Jan-Benedict Glaw wrote: On Sun, 2012-01-08 22:19:53 +0400, Oleg Bartunov o...@sai.msu.su wrote: pgsphere now could benefit very much from our KNNGiST feature. This is number one development from my point of view. I and Teodor have no time to work on pgsphere, sorry. But, there are some astronomers I'm working with, who can take part in this. Sergey Karpov has done extensive benchmarks of q3c, rtree and pgsphere and found the latter still has some benefits in some workload, so we are interesting in development. Could the PostGIS stuff be abused for stellar coordinates? There is no principal difference between celestial sphere and earth, it's a matter of conversion between coordinates. I'm a hobby astronomer myself--so I'm asking myself what is actually needed. I had a look at pgsphere some weeks ago, but didn't use it, because it seemed to be somewhat dead. My next approach was to load a dataset as-is (with floting point RA and dec) and then created a new table selecting all data and converted the coordinates to POINTs. Even with a combined index on (RA,Dec) (as well as one on the POINT column of the new PostGIS enabled table), the later was quite faster when searching for specific areas etc. One important thing that's needed is transformation between equatorial, ecliptic, galactic and probably (local, incorporating current local longitude/latitude and time) horizontal coordinates. What might be important, too, is to be able to change between J2000.0 and B1950.0 etc. The probably easiest thing is to change the (printable) representation of coordinates, because (depending on the people you talk to), especially for RA (equivalent to the earth's longitude), there are at least two totally different notations used: 2h 30min 4.3sec = 2.501194h = 37.5179° The other axis is usually simply written in degree. MfG, JBG -- Jan-Benedict Glaw jbg...@lug-owl.de +49-172-7608481 Signature of: Wenn ich wach bin, träume ich. the second : signature.asc Description: Digital signature
Re: [HACKERS] pgsphere
Dave, The situation with pgshpere is so, that I think we need new developer, since Janko keep silence :) I wrote him several time, since I wanted pgsphere now could benefit very much from our KNNGiST feature. This is number one development from my point of view. I and Teodor have no time to work on pgsphere, sorry. But, there are some astronomers I'm working with, who can take part in this. Sergey Karpov has done extensive benchmarks of q3c, rtree and pgsphere and found the latter still has some benefits in some workload, so we are interesting in development. Regards, Oleg On Fri, 6 Jan 2012, Andrew Dunstan wrote: On 01/06/2012 12:32 PM, Dave Cramer wrote: I've been asked by someone to support pgshpere. It would appear that the two project owners are MIA. If anyone knows different can they let me know ? Does anyone have any objection to me taking over the project? One of the owners is Teodor, who is a core committer ... I hope he's not MIA. cheers andrew Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgsphere
Yes, I returned from Nepal a month ago :) I've sent my opinion about pgsphere in separate message. Oleg On Sat, 7 Jan 2012, Jan Urbaski wrote: - Original message - On Saturday, January 07, 2012 04:43:43 PM Dave Cramer wrote: Well I've sent Teodor a personal email asking him if he was interested and so far no response, so I interpret that as he no longer has interest in the project. I dimly remember him mentioning traveling/hiking planning to travel around the Himalayas somewhere on -hackers. That sounds more like Oleg :) Jan Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgsphere
On 01/08/2012 01:19 PM, Oleg Bartunov wrote: Dave, The situation with pgshpere is so, that I think we need new developer, since Janko keep silence :) I wrote him several time, since I wanted pgsphere now could benefit very much from our KNNGiST feature. This is number one development from my point of view. I and Teodor have no time to work on pgsphere, sorry. But, there are some astronomers I'm working with, who can take part in this. Sergey Karpov has done extensive benchmarks of q3c, rtree and pgsphere and found the latter still has some benefits in some workload, so we are interesting in development. I suggest you just have Teodor add Dave as one of the project admins on pgfoundry. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgsphere
Well I've sent Teodor a personal email asking him if he was interested and so far no response, so I interpret that as he no longer has interest in the project. Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On Fri, Jan 6, 2012 at 12:40 PM, Andrew Dunstan and...@dunslane.net wrote: On 01/06/2012 12:32 PM, Dave Cramer wrote: I've been asked by someone to support pgshpere. It would appear that the two project owners are MIA. If anyone knows different can they let me know ? Does anyone have any objection to me taking over the project? One of the owners is Teodor, who is a core committer ... I hope he's not MIA. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgsphere
On Saturday, January 07, 2012 04:43:43 PM Dave Cramer wrote: Well I've sent Teodor a personal email asking him if he was interested and so far no response, so I interpret that as he no longer has interest in the project. I dimly remember him mentioning traveling/hiking planning to travel around the Himalayas somewhere on -hackers. Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgsphere
- Original message - On Saturday, January 07, 2012 04:43:43 PM Dave Cramer wrote: Well I've sent Teodor a personal email asking him if he was interested and so far no response, so I interpret that as he no longer has interest in the project. I dimly remember him mentioning traveling/hiking planning to travel around the Himalayas somewhere on -hackers. That sounds more like Oleg :) Jan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgsphere
On Saturday, January 07, 2012 05:09:41 PM Jan Urbański wrote: - Original message - On Saturday, January 07, 2012 04:43:43 PM Dave Cramer wrote: Well I've sent Teodor a personal email asking him if he was interested and so far no response, so I interpret that as he no longer has interest in the project. I dimly remember him mentioning traveling/hiking planning to travel around the Himalayas somewhere on -hackers. That sounds more like Oleg :) Oh. Yes. I remembered Oleg writing that module... ;) Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pgsphere
I've been asked by someone to support pgshpere. It would appear that the two project owners are MIA. If anyone knows different can they let me know ? Does anyone have any objection to me taking over the project? Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgsphere
On 01/06/2012 12:32 PM, Dave Cramer wrote: I've been asked by someone to support pgshpere. It would appear that the two project owners are MIA. If anyone knows different can they let me know ? Does anyone have any objection to me taking over the project? One of the owners is Teodor, who is a core committer ... I hope he's not MIA. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pgsphere
I've been asked by someone to support pgshpere. It would appear that the two project owners are MIA. If anyone knows different can they let me know ? Does anyone have any objection to me taking over the project? Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [Pgsphere-dev] GIST index concurrency concern
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. Most people when they talk about a large load on a DBMS system talk about transactins per second. As in 100 per second Even if we only assume 12 hour days, 3000 per day is only one transaction every 14 seconds. That's a triveal rate that could be handled on an older Pentium II PC. Assume the system runs for five years at 3000/day. That's only only about 500,000 rows. In database terms that's not much. Don't worry you have a problem well within the limits of a small PC runnig PostgreSQL. You want to of course place the intire process of adding a new user inside a begin/commit transaction. This will provide the type of queue you want. All of the inserts will get done when the commit happens. Also you will likely want to run the user interface in its own process or thread. Those two things will be all you need as long as your average transaction rate remains so low. If there are ANY locks done in your code you need to remove them and re-think the design. Everyone always thinks they have a large database project. Even a 200,000 row table is small enough that it and its index files can be cached in RAM. Where you might run into the kinds of problems you are thinking about is if you had automated sensor systems (looking either down at the Earth or up at the sky) and software to automatically extract features and catlog those in to a DBMS. Then if you have several of those sensors running you get to the high rates that drive concurrentcy issues. But if you only have four or five users each doing a transaction per second it's not an issue. After you get past the 100 transacton per second rates you are looking at Ocacle on Sun hardware and terrabyte sized disk arrays Like we have down in the lab here. BUt belleive my you need automated data collection systems to gemerate enough data to get you into trouble But I run low-end stuff on my very old 500Mhz PIII = Chris Albertson Home: 310-376-1029 [EMAIL PROTECTED] Cell: 310-990-7550 Office: 310-336-5189 [EMAIL PROTECTED] KG6OMK __ Do you Yahoo!? Check out the new Yahoo! Front Page. www.yahoo.com ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [Pgsphere-dev] GIST index concurrency concern
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 [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [Pgsphere-dev] GIST index concurrency concern
Patrick, you didn't say us about your setup. Have you proved you've seen locking issue for reading ? Are you sure you have no any locks in your code ? Any tests demonstrated your problem would be great. Oleg On Tue, 9 Nov 2004, Patrick Clery wrote: 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 [EMAIL PROTECTED] 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 ___ Pgsphere-dev mailing list [EMAIL PROTECTED] http://gborg.postgresql.org/mailman/listinfo/pgsphere-dev Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [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: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
