Re: Request for further clarification on synchronous_commit - Mailing list pgsql-docs

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@momjian.us>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

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