Re: Replication & web apps - Mailing list pgsql-general

> There are other techniques to balance the load of the database calls so that
> some go to one box and some to others, yet keep the data in synch...
> Continuent makes a commercial p/cluster product  as well as an open source
> product called Sequoia that sit in the JDBC layer and direct traffic and
> control the load balancing.

We use continuent at work (albeit for mysql...) on a three node cluster.

In their default setup they use what's called "strict" querying.  By which
they mean any read to any node will first check to make sure there aren't
any pending writes before continuing.

The other mode is "weak".  Where reads don't bother checking to see if
there are any pending writes before doing their read.  In this mode, we
run the risk that a read might happen before a previous write it's
depending on.  In our tests though the time for replication was really
small (don't remember offhand) but certainly much much smaller than a
round trip request for an end user would be.

That all said, we had to go with weak because strict killed performance.
Absolutely killed it.

Anyway, just something to keep in mind.  Kind of like raid... you lose in
one area to gain in another....

-philip

>
> Jeff Amiel
>
> Leonardo Francalanci wrote:
>> Hi,
>>
>> I still don't understand how replication can be used in web applications.
>> Given this scenario:
>>
>> 1) user updates his profile -> update to the db (master)
>> 2) web app redirects to the "profile page" -> select from db (slave)
>>
>> Since (2) is a select it is issued to the slave.
>>
>> How can one be sure that the master propagates the update (1) to the slave
>> before data is requested from the slave (2)?
>> And: suppose there is a method to understand that the user made a change to
>> the db in the web request (as above) so that we have to issue all queries
>> of the same web request to the master, that is:
>>
>> 1) user updates his profile -> update to the db (master)
>> 2) web app redirects to the "profile page" -> select from db (master again
>> because in this web-request user made a change to the db)
>>
>> what if the user ask AGAIN for the "profile page" BEFORE write propagates
>> to the slave:
>>
>> 3) User ask for a refresh of the "profile page" -> select -> slave (because
>> user didn't make any change during THIS web request)
>>
>> ???
>>
>> In other words: how can asynchronous replication be used in an
>> application???
>>
>>
>>
>>
>>
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>>       choose an index scan if your joining column's datatypes do not
>>       match
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>              http://www.postgresql.org/docs/faq
>