Re: Postgresql replication

Chris Travers wrote:
> Why not have the people who have rights to review this all write to the
> master database and have that replicated back? It seems like latency is
> not really an issue. Replication here is only going to complicate

What master database? Having a single master defeats the purpose of load
balancing to handle more users.

> things. If it were me, I would be having my approval app pull data
> from *all* of the databases independently and not rely on the
> replication for this part. The replication could then be used to
> replicate *approved* data back to the slaves.

If your app client happens to have high speed access to all servers,
fine. And you can guarantee uptime connections to all servers except for
the rare cases of hardware failure. The problem is if you don't, you end
up with every transaction running at the speed of the slowest connection
between a client and the farthest DB. While the final status of a
transaction does not need to show up anytime soon on a user's screen,
there still needs to be fast response for each individual user action.

How bad does the response get? I've done some simple tests comparing APP
<-LAN-> DB versus APP <-cross country VPN-> DB. Even simple actions like
inserting a recording and checking for a dupe key violation (e.g. almost
no bandwidth needed) takes about 10,000 times longer than over a 100mbit
LAN.

> I still don't understand the purpose of replicating the pending data...

Imagine a checking account. A request to make an online payment can be
made on any server. The moment the user submits a request, he sees it on
his screen. This request is considered pending and not a true
transaction yet. All requests are collected together via replication and
the "home" server for that account then can check the account balance
and decide whether there's enough funds to issue those payments.