Re: Synchronous logical replication?

On 6/25/17 15:07, Arseny Sher wrote:
> devel version of the manual (2017-06-25 12:17:02) contains the following
> paragraph documenting the CREATE SUBSCRIPTION command:
>
> synchronous_commit (enum)
>
> ....
>
> A different setting might be appropriate when doing synchronous
> logical replication. The logical replication workers report the
> positions of writes and flushes to the publisher, and when using
> synchronous replication, the publisher will wait for the actual
> flush. This means that setting synchronous_commit for the subscriber
> to off when the subscription is used for synchronous replication
> might increase the latency for COMMIT on the publisher. In this
> scenario, it can be advantageous to set synchronous_commit to local
> or higher.
>
> It seems to me quite confusing.

It is.

> 1) I failed to find in the docs whether logical replication supports
> synchronous mode at all and how it is configured.

I have added some information about that.

> 2) Assuming it is supported, how setting synchronous_commit to off on
> replica (subscriber) can increase COMMIT latency on master?

The synchronous_commit setting for a subscription determines what
happens to the things that the subscription's apply worker writes
locally. The subscription's apply worker operates much like a normal
client backend, and whatever it writes and commits is subject to its
local current synchronous_commit setting.

In most cases, synchronous_commit off is the best setting because it
avoids the flushing work at commit time, and it is safe because in case
of a crash the data can be re-obtained from the publishing server.

But if you use synchronous replication on the publishing server, then
the publishing server will wait for the subscribing server to send
feedback messages when the sent data has been flushed to disk on the
subscribing server (depending on the particular setting). If the
subscriber has synchronous_commit off, then the flushing happens at some
random later time, and then the upstream publisher has to wait for that
to happen. In order to speed that up, you need to make the subscriber
flush stuff faster, and the way to do that is to set synchronous_commit
to a value other than off on the subscriber.

The reason for this confusion is that synchronous_commit has both a
local and a remote meaning, and in this case the local meaning on the
subscriber has an impact on the remote meaning of the publisher.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services