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

On Wed, Aug 19, 2020 at 11:39:53AM +0200, Kasper Kondzielski wrote:
> > 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.
> 
> Ok, I see, thanks. Shouldn't we then stick to this order whenever possible
> (might be sometimes reversed).
> So, in the proposed patch I would suggest putting remote_apply first. (Of
> course, before that we can mention that the default option is `on`, but without
> going to much into the details.)

Well, it is kind of confusing.  I wanted to put remote_apply in its own
paragraph because it only applies to standbys, and because it is much
heavier and in a different scope (replay) than the others.  Frankly,
remote_apply is realated to synchronicity only to the extent it allows
consistent/synchronous results from standbys, not related to syncing
data to the kernel or durable storage.

> 
> > 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
> 
> Wouldn't the table be beneficial when it comes to highlighting these
> differences?

Uh, I don't think we list a table like this anywhere else for config
options.  I would be interested if others think it would be helpful.

> +-----------------------------+---------------------------------------------------------+
> |                             | synchronous_commit                                      |
> +-----------------------------+--------------+-------------------+--------------+-------+
> | operation on standby server | remote_apply | on (remote_flush) | remote_write | local |
> +-----------------------------+--------------+-------------------+--------------+-------+
> | write to WAL                | Yes          | Yes               | Yes          | No    |
> +-----------------------------+--------------+-------------------+--------------+-------+
> | fsync                       | Yes          | Yes               | No           | No    |
> +-----------------------------+--------------+-------------------+--------------+-------+
> | apply WAL data              | Yes          | No                | No           | No    |
> +-----------------------------+--------------+-------------------+--------------+-------+
> 
> 
> > and this defines the 'on' behavior:
> >
> >        /* Define the default setting for synchronous_commit */
> >        #define SYNCHRONOUS_COMMIT_ON   SYNCHRONOUS_COMMIT_REMOTE_FLUSH
> 
> Is there any valid reason to hide this behavior under `on` alias? In my opinion
> `remote_flush` does much better job with describing what it does. Maybe we
> could rename `on` to `remote_flush` but also create an alias `on=remote_flush`
> to keep backward compatibility? 

Well, I think we originally only had 'on', and later added the others. 
Also, 'on' is also local flush.  We don't support local _write_ where we
only write it to the kernel.  We support fysync off, which I think is
the local behavior of remote_write.  I think remote_write is saying we
want local fsync but no fsync for remote.  Is that even correct?

This is certainly confusing.  Maybe we do need a chart, but we need to
list local and standby behavior.

> +         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), and also written to durable storage on the standbys.
> 
> "and also written to durable storage on the standbys." -> You mean flushed?
> Maybe it should be better to stick to cohesive terminology to not introduce any
> confusion.

Yes, I mean written to durable storage.  I don't think you can use
"flushed" alone since you could be flusing the WAL buffers to the file
system.

> > 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.
> 
> Yes, I know what is WAL for. I only don't get what kind of operation do you
> mean by 'WAL replay'. The only one thing which I can think of is the process of
> restoring database after a crash, when we apply changes from WAL to the data
> pages which haven't been flushed to the disk, but I don't think that this is
> that. Basically what I wonder is how can a WAL replay influence the transaction
> commit?

Well, WAL reply is how replication works.  Pretty much the same thing
that happens during crash recovery, but it happens continually.

Someone just wrote this blog entry, which I think helps explain what we
are talking about:

    https://www.percona.com/blog/2020/08/21/postgresql-synchronous_commit-options-and-synchronous-standby-replication/

How is this for a table?

                  -- local --   ------------------- standbys ------------------
                   durable          query      durable commit  durable commit
                   commit        consistency   after OS crash  after PG crash
    remote_apply      X               X               X              X
    on                X                               X              X
    remote_write      X                                              X
    local             X
    off

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

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