Re: Issues with two-server Synch Rep

On Mon, Oct 11, 2010 at 4:01 PM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> On Mon, 2010-10-11 at 15:22 -0400, Robert Haas wrote:
>> >> This is a completely separate issue from making replication
>> >> synchronous.  And, really?  Useless for running read queries?
>> >
>> > Absolutely.  For a synch standby, you can't tolerate any standby delay
>> > at all.  This means that anywhere from 1/4 to 3/4 of queries on the
>> > standby would be cancelled on any high-traffic OLTP server.  Hence,
>> > "useless".
>>
>> What is your source for those numbers?  They could be right, but I
>> simply don't know.
>
> 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.

>> 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.
Alternatively, you could, as you say, do logical rather than physical
replication.

>> A further grump about our current architecture is that it doesn't seem
>> at all clear how to make it work for partial replication.  I have to
>> wonder whether we are going down the wrong path completely and need to
>> hit the reset button.  But neither this nor the pruning problem are
>> things that we can reasonably expect the sync rep patch to solve, if
>> we want it to get committed this release cycle.
>
> What we have is physical replication, but you seem to be talking about
> logical replication (evidence: Slony solves both the independent cleanup
> problem and partial replication).
>
> Both logical and physical replication have a place, and I don't believe
> either is the "wrong" path. If we'd like to add logical replication, we
> may be better of starting with Slony (or Londiste) and working from
> there.

Yeah, that's possible. Or Mammoth Replicator.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company