Thread: [DOCS] Synchronous logical replication?

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.
1) I failed to find in the docs whether logical replication supports
   synchronous mode at all and how it is configured.
2) Assuming it is supported, how setting synchronous_commit to off on
   replica (subscriber) can increase COMMIT latency on master?
   Obviously synchronous mode itself does increase it, but
     - In physical replication (docs for synchronous_commit in section
       19.5.1) only master's synchronous_commit value determines what
       it waits for (e.g. 'remote_write' means writing to replica's OS),
       and it seems natural to expect the same behaviour from logical
       replication
     - Even if replica's synchronous_commit somehow influences this,
       'off' value is the lowest and fastest, isn't it?


Arseny Sher
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

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

Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:

> 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.

And another, if I get it right, is that while asynchronous commit makes
server report 'success' faster, it also makes the actual WAL writing to
the disk *slower* in terms of latency, because this action is delayed
for some arbitrary time (well, to be exactly, up to 3*wal_writer_delay
milliseconds). As for synchronous commit, its WAL is written as soon as
possible, though I am not sure which guarantees exist here -- walwriter
is woken up each wal_writer_delay ms, but it seems to write only fully
completed pages under heavy load. Please correct me if I am wrong.

In general, it's clear now, thanks.

--
Arseny Sher