Re: Issues with two-server Synch Rep

On Mon, 2010-10-11 at 16:07 -0400, Robert Haas wrote:
> > I was initially taken aback by the word "useless" as well. However, I
> > had trouble thinking of a use case that isn't better solved by sync rep
> > without HS, or async rep. I don't have the numbers either though, so
> > perhaps someone does have a use case.
>
> The main use cases for synchronous replication seem to be (1) high
> availability and (2) read scalability. That is, if you have 99%
> writes and 1% reads, you can round-robin the reads and do all the
> writes on the master. But I think we are quite a way from making (2)
> work well enough to get excited about.

[ I assume you meant "99% reads and 1% writes" ]

Wouldn't the snapshot publication (as Josh called it) back to the master
work better for that use case?

I'm not even sure that it's the ratio that matters, but rather how
constant the writes are. 1% writes does not necessarily mean that a
random 1% of read queries fail on the standby. I don't have the numbers,
but SR + query cancel seems like the standby system would effectively be
down during write activity. I wouldn't be surprised if SR + query cancel
resulted in some frustrated users; but perhaps "useless" is too strong a
word.

> >> It would be far better if we could decouple master cleanup from
> >> standby cleanup, so that only the machine that actually has the old
> >> query gets bloated. However, no one seems excited about writing that
> >> code.
> >
> > That doesn't seem just a matter of code, it seems like a major design
> > conflict.
>
> Yes. I had the idea of trying to fix this by allowing the standby to
> retain old versions of entire pages that got cleaned up on the master,
> until the transactions that might want to read the old pages were
> gone. But that may be prohibitively difficult, not sure.

I think you'd end up having a notion of a snapshot of block information
(like a FS with snapshots) inside of postgres.

Sounds like a lot of complexity to me, and the only benefit I see is
moving bloat from the primary to the standby. Granted, that would be
nice, but I would expect some costs aside from just the complexity.

Regards,
Jeff Davis