Skip to content

Commit 3c4b520

Browse files
bmomjianbmomjian
committed
doc: improve description of synchronous_commit modes
Previously it wasn't clear exactly what each of the synchronous_commit modes accomplished. This clarifies that, and adds a table describing it. Only backpatched through 9.6 since 9.5 doesn't have all the options. Reported-by: kghost0@gmail.com Discussion: https://postgr.es/m/159741195522.14321.13812604195366728976@wrigleys.postgresql.org Backpatch-through: 9.6
1 parent 8583402 commit 3c4b520

File tree

2 files changed

+116
-31
lines changed

2 files changed

+116
-31
lines changed

‎doc/src/sgml/config.sgml

Lines changed: 114 additions & 30 deletions
Original file line numberDiff line numberDiff line change
@@ -2432,14 +2432,26 @@ include_dir 'conf.d'
24322432
</term>
24332433
<listitem>
24342434
<para>
2435-
Specifies whether transaction commit will wait for WAL records
2436-
to be written to disk before the command returns a <quote>success</quote>
2437-
indication to the client. Valid values are <literal>on</literal>,
2438-
<literal>remote_apply</literal>, <literal>remote_write</literal>, <literal>local</literal>,
2439-
and <literal>off</literal>. The default, and safe, setting
2440-
is <literal>on</literal>. When <literal>off</literal>, there can be a delay between
2441-
when success is reported to the client and when the transaction is
2442-
really guaranteed to be safe against a server crash. (The maximum
2435+
Specifies how much WAL processing must complete before
2436+
the database server returns a <quote>success</quote>
2437+
indication to the client. Valid values are
2438+
<literal>remote_apply</literal>, <literal>on</literal>
2439+
(the default), <literal>remote_write</literal>,
2440+
<literal>local</literal>, and <literal>off</literal>.
2441+
</para>
2442+
2443+
<para>
2444+
If <varname>synchronous_standby_names</varname> is empty,
2445+
the only meaningful settings are <literal>on</literal> and
2446+
<literal>off</literal>; <literal>remote_apply</literal>,
2447+
<literal>remote_write</literal> and <literal>local</literal>
2448+
all provide the same local synchronization level
2449+
as <literal>on</literal>. The local behavior of all
2450+
non-<literal>off</literal> modes is to wait for local flush of WAL
2451+
to disk. In <literal>off</literal> mode, there is no waiting,
2452+
so there can be a delay between when success is reported to the
2453+
client and when the transaction is later guaranteed to be safe
2454+
against a server crash. (The maximum
24432455
delay is three times <xref linkend="guc-wal-writer-delay"/>.) Unlike
24442456
<xref linkend="guc-fsync"/>, setting this parameter to <literal>off</literal>
24452457
does not create any risk of database inconsistency: an operating
@@ -2451,38 +2463,40 @@ include_dir 'conf.d'
24512463
exact certainty about the durability of a transaction. For more
24522464
discussion see <xref linkend="wal-async-commit"/>.
24532465
</para>
2466+
24542467
<para>
2455-
If <xref linkend="guc-synchronous-standby-names"/> is non-empty, this
2456-
parameter also controls whether or not transaction commits will wait
2457-
for their WAL records to be replicated to the standby server(s).
2458-
When set to <literal>on</literal>, commits will wait until replies
2468+
If <xref linkend="guc-synchronous-standby-names"/> is non-empty,
2469+
<varname>synchronous_commit</varname> also controls whether
2470+
transaction commits will wait for their WAL records to be
2471+
processed on the standby server(s).
2472+
</para>
2473+
2474+
<para>
2475+
When set to <literal>remote_apply</literal>, commits will wait
2476+
until replies from the current synchronous standby(s) indicate they
2477+
have received the commit record of the transaction and applied
2478+
it, so that it has become visible to queries on the standby(s),
2479+
and also written to durable storage on the standbys. This will
2480+
cause much larger commit delays than previous settings since
2481+
it waits for WAL replay. When set to <literal>on</literal>,
2482+
commits wait until replies
24592483
from the current synchronous standby(s) indicate they have received
2460-
the commit record of the transaction and flushed it to disk. This
2484+
the commit record of the transaction and flushed it to durable storage. This
24612485
ensures the transaction will not be lost unless both the primary and
24622486
all synchronous standbys suffer corruption of their database storage.
2463-
When set to <literal>remote_apply</literal>, commits will wait until replies
2464-
from the current synchronous standby(s) indicate they have received the
2465-
commit record of the transaction and applied it, so that it has become
2466-
visible to queries on the standby(s).
24672487
When set to <literal>remote_write</literal>, commits will wait until replies
24682488
from the current synchronous standby(s) indicate they have
2469-
received the commit record of the transaction and written it out to
2470-
their operating system. This setting is sufficient to
2471-
ensure data preservation even if a standby instance of
2472-
<productname>PostgreSQL</productname> were to crash, but not if the standby
2473-
suffers an operating-system-level crash, since the data has not
2489+
received the commit record of the transaction and written it to
2490+
their file systems. This setting ensures data preservation if a standby instance of
2491+
<productname>PostgreSQL</productname> crashes, but not if the standby
2492+
suffers an operating-system-level crash because the data has not
24742493
necessarily reached durable storage on the standby.
2475-
Finally, the setting <literal>local</literal> causes commits to wait for
2476-
local flush to disk, but not for replication. This is not usually
2494+
The setting <literal>local</literal> causes commits to wait for
2495+
local flush to disk, but not for replication. This is usually not
24772496
desirable when synchronous replication is in use, but is provided for
24782497
completeness.
24792498
</para>
2480-
<para>
2481-
If <varname>synchronous_standby_names</varname> is empty, the settings
2482-
<literal>on</literal>, <literal>remote_apply</literal>, <literal>remote_write</literal>
2483-
and <literal>local</literal> all provide the same synchronization level:
2484-
transaction commits only wait for local flush to disk.
2485-
</para>
2499+
24862500
<para>
24872501
This parameter can be changed at any time; the behavior for any
24882502
one transaction is determined by the setting in effect when it
@@ -2492,6 +2506,76 @@ include_dir 'conf.d'
24922506
asynchronously when the default is the opposite, issue <command>SET
24932507
LOCAL synchronous_commit TO OFF</command> within the transaction.
24942508
</para>
2509+
2510+
<para>
2511+
<xref linkend="synchronous-commit-matrix"/> summarizes the
2512+
capabilities of the <varname>synchronous_commit</varname> settings.
2513+
</para>
2514+
2515+
<table id="synchronous-commit-matrix">
2516+
<title>synchronous_commit Modes</title>
2517+
<tgroup cols="5">
2518+
<colspec colname="col1" colwidth="1.1*"/>
2519+
<colspec colname="col2" colwidth="1*"/>
2520+
<colspec colname="col3" colwidth="1*"/>
2521+
<colspec colname="col4" colwidth="1*"/>
2522+
<colspec colname="col5" colwidth="1*"/>
2523+
<thead>
2524+
<row>
2525+
<entry>synchronous_commit setting</entry>
2526+
<entry>local durable commit</entry>
2527+
<entry>standby durable commit after PG crash</entry>
2528+
<entry>standby durable commit after OS crash</entry>
2529+
<entry>standby query consistency</entry>
2530+
</row>
2531+
</thead>
2532+
2533+
<tbody>
2534+
2535+
<row>
2536+
<entry>remote_apply</entry>
2537+
<entry align="center">&bull;</entry>
2538+
<entry align="center">&bull;</entry>
2539+
<entry align="center">&bull;</entry>
2540+
<entry align="center">&bull;</entry>
2541+
</row>
2542+
2543+
<row>
2544+
<entry>on</entry>
2545+
<entry align="center">&bull;</entry>
2546+
<entry align="center">&bull;</entry>
2547+
<entry align="center">&bull;</entry>
2548+
<entry align="center"></entry>
2549+
</row>
2550+
2551+
<row>
2552+
<entry>remote_write</entry>
2553+
<entry align="center">&bull;</entry>
2554+
<entry align="center">&bull;</entry>
2555+
<entry align="center"></entry>
2556+
<entry align="center"></entry>
2557+
</row>
2558+
2559+
<row>
2560+
<entry>local</entry>
2561+
<entry align="center">&bull;</entry>
2562+
<entry align="center"></entry>
2563+
<entry align="center"></entry>
2564+
<entry align="center"></entry>
2565+
</row>
2566+
2567+
<row>
2568+
<entry>off</entry>
2569+
<entry align="center"></entry>
2570+
<entry align="center"></entry>
2571+
<entry align="center"></entry>
2572+
<entry align="center"></entry>
2573+
</row>
2574+
2575+
</tbody>
2576+
</tgroup>
2577+
</table>
2578+
24952579
</listitem>
24962580
</varlistentry>
24972581

‎src/include/access/xact.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -68,7 +68,8 @@ typedef enum
6868
SYNCHRONOUS_COMMIT_REMOTE_WRITE, /* wait for local flush and remote
6969
* write */
7070
SYNCHRONOUS_COMMIT_REMOTE_FLUSH, /* wait for local and remote flush */
71-
SYNCHRONOUS_COMMIT_REMOTE_APPLY /* wait for local flush and remote apply */
71+
SYNCHRONOUS_COMMIT_REMOTE_APPLY /* wait for local and remote flush
72+
and remote apply */
7273
} SyncCommitLevel;
7374

7475
/* Define the default setting for synchronous_commit */

0 commit comments

Comments
 (0)