Home • Explore • Blog • CI
☰



postgresql
52th chunk of `doc/src/sgml/config.sgml`
0495da4a6f3a747cf8e6818702fd7a82acded99a76c629fc0000000100000fa0
 selects a size equal to
        1/32nd (about 3%) of <xref linkend="guc-shared-buffers"/>, but not less
        than <literal>64kB</literal> nor more than the size of one WAL
        segment, typically <literal>16MB</literal>.  This value can be set
        manually if the automatic choice is too large or too small,
        but any positive value less than <literal>32kB</literal> will be
        treated as <literal>32kB</literal>.
        If this value is specified without units, it is taken as WAL blocks,
        that is <symbol>XLOG_BLCKSZ</symbol> bytes, typically 8kB.
        This parameter can only be set at server start.
       </para>

       <para>
        The contents of the WAL buffers are written out to disk at every
        transaction commit, so extremely large values are unlikely to
        provide a significant benefit.  However, setting this value to at
        least a few megabytes can improve write performance on a busy
        server where many clients are committing at once.  The auto-tuning
        selected by the default setting of -1 should give reasonable
        results in most cases.
       </para>

      </listitem>
     </varlistentry>

     <varlistentry id="guc-wal-writer-delay" xreflabel="wal_writer_delay">
      <term><varname>wal_writer_delay</varname> (<type>integer</type>)
      <indexterm>
       <primary><varname>wal_writer_delay</varname> configuration parameter</primary>
      </indexterm>
      </term>
      <listitem>
      <para>
        Specifies how often the WAL writer flushes WAL, in time terms.
        After flushing WAL the writer sleeps for the length of time given
        by <varname>wal_writer_delay</varname>, unless woken up sooner
        by an asynchronously committing transaction. If the last flush
        happened less than <varname>wal_writer_delay</varname> ago and less
        than <varname>wal_writer_flush_after</varname> worth of WAL has been
        produced since, then WAL is only written to the operating system, not
        flushed to disk.
        If this value is specified without units, it is taken as milliseconds.
        The default value is 200 milliseconds (<literal>200ms</literal>).  Note that
        on some systems, the effective resolution of sleep delays is 10
        milliseconds; setting <varname>wal_writer_delay</varname> to a value that is
        not a multiple of 10 might have the same results as setting it to the
        next higher multiple of 10. This parameter can only be set in the
        <filename>postgresql.conf</filename> file or on the server command line.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry id="guc-wal-writer-flush-after" xreflabel="wal_writer_flush_after">
      <term><varname>wal_writer_flush_after</varname> (<type>integer</type>)
      <indexterm>
       <primary><varname>wal_writer_flush_after</varname> configuration parameter</primary>
      </indexterm>
      </term>
      <listitem>
      <para>
        Specifies how often the WAL writer flushes WAL, in volume terms.
        If the last flush happened less
        than <varname>wal_writer_delay</varname> ago and less
        than <varname>wal_writer_flush_after</varname> worth of WAL has been
        produced since, then WAL is only written to the operating system, not
        flushed to disk.  If <varname>wal_writer_flush_after</varname> is set
        to <literal>0</literal> then WAL data is always flushed immediately.
        If this value is specified without units, it is taken as WAL blocks,
        that is <symbol>XLOG_BLCKSZ</symbol> bytes, typically 8kB.
        The default is <literal>1MB</literal>.
        This parameter can only be set in the
        <filename>postgresql.conf</filename> file or on the server command line.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry id="guc-wal-skip-threshold" xreflabel="wal_skip_threshold">
      <term><varname>wal_skip_threshold</varname> (<type>integer</type>)
Title: WAL Buffer Size and WAL Writer Configuration in PostgreSQL
Summary
This section delves into the configuration of WAL buffer size and the WAL writer process in PostgreSQL. It further explains `wal_buffers`, detailing the shared memory allocation for WAL data and its impact on write performance, noting that auto-tuning usually provides reasonable results. It describes `wal_writer_delay`, which sets the frequency for the WAL writer to flush WAL data based on time, and `wal_writer_flush_after`, which determines the flush frequency based on the volume of WAL produced.