Home Explore Blog CI



postgresql

47th chunk of `doc/src/sgml/config.sgml`
4f780fcb6b16bed8ff0af88fa878f285b0e43083812100780000000100000fa0
 <literal>remote_write</literal> and <literal>local</literal>
        all provide the same local synchronization level
        as <literal>on</literal>.  The local behavior of all
        non-<literal>off</literal> modes is to wait for local flush of WAL
        to disk.  In <literal>off</literal> mode, there is no waiting,
        so there can be a delay between when success is reported to the
        client and when the transaction is later guaranteed to be safe
        against a server crash.  (The maximum
        delay is three times <xref linkend="guc-wal-writer-delay"/>.)  Unlike
        <xref linkend="guc-fsync"/>, setting this parameter to <literal>off</literal>
        does not create any risk of database inconsistency: an operating
        system or database crash might
        result in some recent allegedly-committed transactions being lost, but
        the database state will be just the same as if those transactions had
        been aborted cleanly.  So, turning <varname>synchronous_commit</varname> off
        can be a useful alternative when performance is more important than
        exact certainty about the durability of a transaction.  For more
        discussion see <xref linkend="wal-async-commit"/>.
       </para>

       <para>
        If <xref linkend="guc-synchronous-standby-names"/> is non-empty,
        <varname>synchronous_commit</varname> also controls whether
        transaction commits will wait for their WAL records to be
        processed on the standby server(s).
       </para>

       <para>
        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.  This will
        cause much larger commit delays than previous settings since
        it waits for WAL replay.  When set to <literal>on</literal>,
        commits wait until replies
        from the current synchronous standby(s) indicate they have received
        the commit record of the transaction and flushed it to durable storage.  This
        ensures the transaction will not be lost unless both the primary and
        all synchronous standbys suffer corruption of their database storage.
        When set to <literal>remote_write</literal>, commits will wait until replies
        from the current synchronous standby(s) indicate they have
        received the commit record of the transaction and written it to
        their file systems. This setting ensures data preservation if a standby instance of
        <productname>PostgreSQL</productname> crashes, but not if the standby
        suffers an operating-system-level crash because the data has not
        necessarily reached durable storage on the standby.
        The setting <literal>local</literal> causes commits to wait for
        local flush to disk, but not for replication.  This is usually not
        desirable when synchronous replication is in use, but is provided for
        completeness.
       </para>

       <para>
        This parameter can be changed at any time; the behavior for any
        one transaction is determined by the setting in effect when it
        commits.  It is therefore possible, and useful, to have some
        transactions commit synchronously and others asynchronously.
        For example, to make a single multistatement transaction commit
        asynchronously when the default is the opposite, issue <command>SET
        LOCAL synchronous_commit TO OFF</command> within the transaction.
       </para>

       <para>
        <xref linkend="synchronous-commit-matrix"/> summarizes the
        capabilities of the <varname>synchronous_commit</varname> settings.
       </para>

       <table id="synchronous-commit-matrix">
        <title>synchronous_commit Modes</title>
      

Title: Synchronous Commit Modes and Standby Considerations
Summary
This section details the behavior of different `synchronous_commit` settings. When `synchronous_standby_names` is empty, only 'on' and 'off' are relevant, with 'off' allowing for faster commits but risking transaction loss in case of a crash. With non-empty `synchronous_standby_names`, the parameter dictates whether commits wait for WAL records to be processed on standby servers. Specifically, `remote_apply` waits for replay and storage on standbys, `on` waits for durable storage, `remote_write` waits for file system writes on standbys, and `local` waits only for local disk flush. The parameter can be changed at any time, and the behavior is determined at commit time, allowing a mix of synchronous and asynchronous commits. A table summarizing the capabilities of each `synchronous_commit` setting is also referenced.