Re: Request for further clarification on synchronous_commit

On Tue, Aug 18, 2020 at 12:50:34PM +0200, Kasper Kondzielski wrote:
> Hi, thanks for the reply.
>
> To be honest I don't think it is better. Previously paragraph about
> remote_apply was after paragraph about `on` and before remote_write which
> followed natural order in terms of how strict these parameters are (i.e. how
> strong are the guarantees they provide). Because of that I think that
> remote_apply should return to its previous position.

Uh, not really --- see below.

> My original concern was about the fact that the difference between `on`,
> remote_write and remote_apply wasn't perfectly clear.
> I am not sure if I understand this difference correctly but maybe such a table
> could be helpful to me and others:
>
> +-----------------------------+-------------------------------------------+
> | | synchronous_commit |
> +-----------------------------+-----+--------------+--------------+-------+
> | operation on standby server | on | remote_apply | remote_write | local |
> +-----------------------------+-----+--------------+--------------+-------+
> | written to WAL | Yes | Yes | Yes | No |
> +-----------------------------+-----+--------------+--------------+-------+
> | commit transaction | Yes | Yes | No | No |
> +-----------------------------+-----+--------------+--------------+-------+
> | fsync | Yes | No | No | No |
> +-----------------------------+-----+--------------+--------------+-------+
>
> From which we can clearly see that only `on` option guarantees fsync, and the
> only difference between remote_write and remote_apply is the visibility of
> transaction results to the queries.

Un, 'on' does _not_ apply the WAL data, and remote_apply does do remote
fsync. If you want to go in order of severity, with the most severe
first, it is:

remote_apply
on
remote_write
local

This is seen in the C enum ordering for synchronous_commit, but in
reverse order:

typedef enum
{
SYNCHRONOUS_COMMIT_OFF, /* asynchronous commit */
SYNCHRONOUS_COMMIT_LOCAL_FLUSH, /* wait for local flush only */
SYNCHRONOUS_COMMIT_REMOTE_WRITE, /* wait for local flush and remote
* write */
SYNCHRONOUS_COMMIT_REMOTE_FLUSH, /* wait for local and remote flush */
SYNCHRONOUS_COMMIT_REMOTE_APPLY /* wait for local flush and remote apply */
} SyncCommitLevel;

and this defines the 'on' behavior:

/* Define the default setting for synchronous_commit */
#define SYNCHRONOUS_COMMIT_ON SYNCHRONOUS_COMMIT_REMOTE_FLUSH

I will clarify this comment, and the docs, to say that remote_apply
includes remote flush.

Obviously these docs need improvement. Updated patch attached. I have
to admit I was kind of confused if remote_apply did remote fsync, but
never had the time to research it until you asked. remote_apply is so
different from the rest, and so heavy, that I put it last in its own
paragraph.

> +         Finally, when set to <literal>remote_apply</literal>, commits will
> +         wait until replies from the current synchronous standby(s) indicate
> +         they have received the commit record of the transaction and applied
> +         it, so that it has become visible to queries on the standby(s).
> +         This can cause much larger commit delays than previous settings
> +         since it involves WAL replay.
> 'This can cause much' - What does it mean that it can cause? Under what
> circumstances it will/won't cause it?

Uh, I think we can change this to "will cause", because I can't think of
a case where it will not.

> "since it involves WAL replay" - What is a WAL replay? 

Well, there is a doc section that talks about WAL:

https://www.postgresql.org/docs/12/wal.html

and other parts of the config docs that talk about WAL.

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EnterpriseDB https://enterprisedb.com

The usefulness of a cup is in its emptiness, Bruce Lee