Agreed on the importance of understanding the transaction modes. I was specifically pointing to the potential latency of blocked reads during splitting nodes on inserting when rebalancing. But as Paul points out, postgres does Ang/Tan splits. While less optimal than R* splits, Ang/Tan is faster as I recall. So it might not be so bad overall.
And I appreciate the tip to look at pgPool which I didn't know about and will read up. Thanks, Carlos On Dec 5, 2011 3:26 PM, "Andy Colson" <a...@squeakycode.net> wrote: > On 12/5/2011 3:41 PM, John R Pierce wrote: > >> On 12/05/11 1:34 PM, C. Mundi wrote: >> >>> So that's my concern. I'm doing 80% reads which are all non-blocking >>> with 20% writes mixed in, and I need to avoid the effect of writes >>> blocking queries which do not need to traverse branches affected by >>> the write. >>> >> >> postgres does no blocking on inserts/updates. the commonest lock is if >> you're doing a transaction, and need to select something prior to >> updating it, then you use a SELECT ... FOR UPDATE; this locks just the >> rows you're going to update so noone else can update them (but other >> clients can still read the existing value prior to your COMMIT). >> >> As an addition to this, Reads and Writes wont block each other, but > you'll need to watch the overlap if its a problem. There are many ways to > go about it depending on what you want (transaction isolation levels, > locking, etc). > > In general, I think it might look like: > connection1: > start transaction > select * from table where the_geom && POINT(a b) > > connection2: > start transaction > update table set the_geom = POLYGON(a b c d) where rowid = 5; > > connection1: (in the same transaction it started above) > select the_geom from table where rowid = 5; > -- gets the origional geom, NOT the one from connection2! > > There are transaction options for read committed, read un-committed, etc, > etc. I don't rightly understand them all, but it sounds like you'll want > to. > > > > traverse branches affected by the write > > I assume that's a reference to building an underlying tree structure. You > wont need to worry about it. On the other hand, if that's a reference to > some geo-boxing thing where one row is included in another and you need to > update multiple rows, and I'm starting to confuse myself, then you might > have a problem. > > Also, as John points out, you'll want a connection pooler. I've heard > good things about pgPool. It'll also spread read's across multiple > computers just incase you need a faster response. (writes go to all > computers, read's round-robin). > > -Andy >
