Re: Is there a peer-to-peer server solution with PG?

On Fri, 4 Feb 2005, Mike Nolan wrote:

>> If you have so much update load that one server cannot accomodate that
>> load, then you should wonder why you'd expect that causing every one
>> of these updates to be applied to (say) 3 servers would "diminish"
>> this burden.
>
> The update/query load isn't the real issue here, it's that these two
> servers will be 800 miles apart and there are some advantages in having
> each office connect to its local database rather than having one of
> them connect to the remote master.

Considering that the two masters need to be connected, I don't see the
advantage. Any write on the _local_ master will have to wait for the
_remote_ master to complete as well.

> The Slony-1 approach will work, assuming I've got suffient network
> bandwidth to support it plus the traffic from the remote office plus
> exixting outside traffic from our public website.

Local read-only access won't travel on the network, both with multi-master
and with master-slave.

Write traffic _will_ be transmitted over the wire, both with multi-master
and with master-slave. With multi-master _every write operation_ will be
remotely duplicated, _both ways_, _synchronously_. That is, master-1 has
to wait for master-2 and vice versa. If you're concerned with network
performances, multi-master will only make it worse.

With master-slave, _only_ the write operations performed on
the slave side need to travel, since clients will perform them directly
on the master. On the master side writes are only local.
Of course, the _results_ of the writes will have to be propagated to the
slave (and thus they travel on the network as well), but that's another
matter (delay is usually acceptable, and fits MVCC - the semantics not
broken).

If you're willing to break semantics, you may run two splitted masters
and find a way to synchronize them. But that requires application level
knowledge, if not human intervention, to resolve conflicts.


> That's one of those things you just don't know will work until you
> have it built, so I'm looking for other options now while I have time
> to consider them.  Once I get on-site in two weeks it'll a lot more hectic.
> --
> Mike Nolan
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>      joining column's datatypes do not match
>

.TM.
--
       ____/  ____/   /
      /      /       /            Marco Colombo
     ___/  ___  /   /              Technical Manager
    /          /   /             ESI s.r.l.
  _____/ _____/  _/               Colombo@ESI.it