Re: Replication & web apps

Well, you've hit upon the difference between synchronous replication and
asynchronous replication (and touched on the idea of clustering/load
balancing)
In a synchronous replication scheme, the database update is made to BOTH
master and slave simultaneously....the transactions must commit to both
or it is rolled back.
In asynchronous replication, there can indeed be a delay between when
the master makes update and the slave receives the notification of it.

pg_cluster is an example of a synchronous replication method (although
it's really considered multi-master...not master-slave)
Slony is an example of asynchronous.

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.

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