Re: Horizontal scalability/sharding

On 09/03/2015 03:57 AM, Bruce Momjian wrote:
>> >
>> > Yes, the logical replication has similar syncrep properties as the
>> > binary one (feedback works same way).

Oh? What does UDR/BDR currently support for sync?

> Yes, I assumed that. Logical replication uses WAL, so if you are
> synchronous with WAL, logical replication is synchronous too. However,
> of course, it is synchronous in being durable, not synchronous in terms
> of applying the WAL. This is true of binary and logical replication.

Well, there's no such thing as simultaneity in scalable architectures.
But users are already used to that ... anybody who load-balances to read
slaves knows about lag. The only way* to ensure near-simultenaity is to
have some kind of single-node, single-process GTM for the cluster, and
then your actual scalability goes bye-bye.

The bigger issue we'll need to address with this is the fight between
lag and load-balancing, which would become a much worse issue with
read-load-balanced shards which are transparent to the user. They'd see
the effects of lag, without having actually chosen to use this or that
replica. This is the other reason to look at logical replication;
presumably with logrep, we can be more discriminating about what
activities cause lag (for one thing, vacuum won't).

Also:
On 09/03/2015 07:00 AM, Kevin Grittner wrote:
> There is another approach to this that we should consider how (if?)
> we are going to cover: database affinity. I have seen cases where
> there are multiple databases which are targets of asynchronous
> replication, with a web application load balancing among them. The
> application kept track of which copy each connection was using, so
> that if when they were not exactly in sync the user never saw "time
> moving backward". Two different users might see versions of the
> data from different points in time, but that generally doesn't
> matter, especially if the difference is just a few minutes. If one
> copy got too far behind for some reason, they would load-shift to
> the other servers (time still moves forward, only there is a "jump"
> forward at the shift). This would allow the tardy database to be
> dedicated to catching up again.
>
> Bottom line is that this very smooth behavior required two features
> -- the ability for the application to control database affinity,
> and the ability to shift that affinity gracefully (with no down
> time).

Yes. Frankly, it would be *easier* to code things so that the same
session always gets its requests load balanced to the same copies;
making that a feature, too, is nice.

(* there are actually other ways to come close to simultaneity, but they
are much more complicated)

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com