Re: High-Concurrency GiST in postgreSQL

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