Re: Sync Rep Design

On Fri, 2010-12-31 at 09:27 +0100, Stefan Kaltenbrunner wrote:

> Maybe it has been discussed but I still don't see way it makes any
> sense. If I declare a standby a sync standby I better want it sync - not
> "maybe sync". consider the case of a 1 master and two identical sync
> standbys - one sync standby is in the same datacenter the other is in a
> backup location say 15km away.
> Given there is a small constant latency to the second box (even if you
> have fast networks) the end effect is that the second standby will NEVER
> be sync (because the local one will always be faster) and you end up
> with an async slave that cannot be used per your business rules?

Your picture above is a common misconception. I will add something to
the docs to explain this.

1. "sync" is a guarantee about how we respond to the client when we
commit. If we wait for more than one response that slows things down,
makes the cluster more fragile, complicates the code and doesn't
appreciably improve the guarantee.

2. "sync" does not guarantee that the updates to the standbys are in any
way coordinated. You can run a query on one standby and get one answer
and at the exact same time run the same query on another standby and get
a different answer (slightly ahead/behind). That also means that if the
master crashes one of the servers will be ahead or behind. You can use
pg_last_xlog_receive_location() to check which one that is.

When people say they want *all* servers to respond, its usually because
they want (2), but that is literally impossible in a distributed system.

--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services