Home Explore Blog CI



postgresql

29th chunk of `doc/src/sgml/config.sgml`
e67d8a687f0d4ce518f3b7b4d8b5b4b4b9f297a51a1f2e940000000100000fc2
 multiplying
        <varname>work_mem</varname> by
        <varname>hash_mem_multiplier</varname>.  This makes it
        possible for hash-based operations to use an amount of memory
        that exceeds the usual <varname>work_mem</varname> base
        amount.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry id="guc-hash-mem-multiplier" xreflabel="hash_mem_multiplier">
      <term><varname>hash_mem_multiplier</varname> (<type>floating point</type>)
      <indexterm>
       <primary><varname>hash_mem_multiplier</varname> configuration parameter</primary>
      </indexterm>
      </term>
      <listitem>
       <para>
        Used to compute the maximum amount of memory that hash-based
        operations can use.  The final limit is determined by
        multiplying <varname>work_mem</varname> by
        <varname>hash_mem_multiplier</varname>.  The default value is
        2.0, which makes hash-based operations use twice the usual
        <varname>work_mem</varname> base amount.
       </para>
       <para>
        Consider increasing <varname>hash_mem_multiplier</varname> in
        environments where spilling by query operations is a regular
        occurrence, especially when simply increasing
        <varname>work_mem</varname> results in memory pressure (memory
        pressure typically takes the form of intermittent out of
        memory errors).  The default setting of 2.0 is often effective with
        mixed workloads.  Higher settings in the range of 2.0 - 8.0 or
        more may be effective in environments where
        <varname>work_mem</varname> has already been increased to 40MB
        or more.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry id="guc-maintenance-work-mem" xreflabel="maintenance_work_mem">
      <term><varname>maintenance_work_mem</varname> (<type>integer</type>)
      <indexterm>
       <primary><varname>maintenance_work_mem</varname> configuration parameter</primary>
      </indexterm>
      </term>
      <listitem>
       <para>
        Specifies the maximum amount of memory to be used by maintenance
        operations, such as <command>VACUUM</command>, <command>CREATE
        INDEX</command>, and <command>ALTER TABLE ADD FOREIGN KEY</command>.
        If this value is specified without units, it is taken as kilobytes.
        It defaults
        to 64 megabytes (<literal>64MB</literal>).  Since only one of these
        operations can be executed at a time by a database session, and
        an installation normally doesn't have many of them running
        concurrently, it's safe to set this value significantly larger
        than <varname>work_mem</varname>.  Larger settings might improve
        performance for vacuuming and for restoring database dumps.
       </para>
       <para>
        Note that when autovacuum runs, up to
        <xref linkend="guc-autovacuum-max-workers"/> times this memory
        may be allocated, so be careful not to set the default value
        too high.  It may be useful to control for this by separately
        setting <xref linkend="guc-autovacuum-work-mem"/>.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry id="guc-autovacuum-work-mem" xreflabel="autovacuum_work_mem">
      <term><varname>autovacuum_work_mem</varname> (<type>integer</type>)
      <indexterm>
       <primary><varname>autovacuum_work_mem</varname> configuration parameter</primary>
      </indexterm>
      </term>
      <listitem>
       <para>
        Specifies the maximum amount of memory to be used by each
        autovacuum worker process.
        If this value is specified without units, it is taken as kilobytes.
        It defaults to -1, indicating that
        the value of <xref linkend="guc-maintenance-work-mem"/> should
        be used instead.  The setting has no effect on the behavior of
        <command>VACUUM</command> when run in other contexts.
        This parameter can only be set in the
        <filename>postgresql.conf</filename>

Title: Configuration Parameters: Hash Memory Multiplier, Maintenance Work Memory, and Autovacuum Work Memory
Summary
This section describes three PostgreSQL configuration parameters related to memory management. It elaborates on the `hash_mem_multiplier` parameter and how to adjust it in environments experiencing query operation spilling. Then, it explains `maintenance_work_mem`, defining the memory limit for maintenance operations like VACUUM, CREATE INDEX, and ALTER TABLE, noting its relation to vacuuming performance and database dump restoration. Lastly, it discusses `autovacuum_work_mem`, controlling the memory allocation for each autovacuum worker process, linking it to `maintenance_work_mem` and highlighting its effect on VACUUM commands in different contexts.