CINXE.COM

<!doctype html> <html lang="en"> <head> <title>PostgreSQL: Documentation: 17: 19.4. Resource Consumption</title> <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no"> <meta http-equiv="Content-Type" content="text/xhtml; charset=utf-8" /> <meta name="theme-color" content="#336791"/> <meta name="copyright" content="The PostgreSQL Global Development Group" /> <meta property="og:url" content="https://www.postgresql.org/docs/17/runtime-config-resource.html" /> <meta property="og:type" content="article" /> <meta property="article:published_time" content="2024-11-21T14:03:57" /> <meta property="og:image" content="https://www.postgresql.org/media/img/about/press/elephant.png" /> <meta property="og:title" content="19.4. Resource Consumption" /> <meta property="og:description" content="19.4.&nbsp;Resource Consumption # 19.4.1. Memory 19.4.2. Disk 19.4.3. Kernel Resource Usage 19.4.4. Cost-based Vacuum Delay 19.4.5. Background Writer 19.4.6. Asynchronous …" /> <meta property="og:site_name" content="PostgreSQL Documentation" /> <link rel="canonical" href="https://www.postgresql.org/docs/current/runtime-config-resource.html" /> <link href="/media/css/fontawesome.css?18db9df6" rel="stylesheet"> <link rel="stylesheet" href="/media/css/bootstrap-4.4.1.min.css"> <link rel="shortcut icon" href="/favicon.ico" /> <link rel="stylesheet" type="text/css" href="/dyncss/base.css?18db9df6"> <script src="/media/js/theme.js"></script> </head> <body> <div class="container-fluid"> <div class="row justify-content-md-center"> <div class="col">  <nav class="navbar navbar-expand-lg navbar-light bg-light"> <a class="navbar-brand p-0" href="/"> <img class="logo" src="/media/img/about/press/elephant.png" alt="PostgreSQL Elephant Logo"> </a> <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#pgNavbar" aria-controls="pgNavbar" aria-expanded="false" aria-label="Toggle navigation"> <span class="navbar-toggler-icon"></span> </button> <div class="collapse navbar-collapse" id="pgNavbar"> <ul class="navbar-nav mr-auto"> <li class="nav-item p-2"><a href="/" title="Home">Home</a></li> <li class="nav-item p-2"><a href="/about/" title="About">About</a></li> <li class="nav-item p-2"><a href="/download/" title="Download">Download</a></li> <li class="nav-item p-2"><a href="/docs/" title="Documentation">Documentation</a></li> <li class="nav-item p-2"><a href="/community/" title="Community">Community</a></li> <li class="nav-item p-2"><a href="/developer/" title="Developers">Developers</a></li> <li class="nav-item p-2"><a href="/support/" title="Support">Support</a></li> <li class="nav-item p-2"><a href="/about/donate/" title="Donate">Donate</a></li> <li class="nav-item p-2"><a href="/account/" title="Your account">Your account</a></li> </ul> <form role="search" method="get" action="/search/"> <div class="input-group"> <input id="q" name="q" type="text" size="20" maxlength="255" accesskey="s" class="form-control" placeholder="Search for..."> <span class="input-group-btn"> <button class="btn btn-default" type="submit"><i class="fas fa-search"></i></button> </span> </div> </form> <form id="form-theme" class="form-inline d-none"> <button id="btn-theme" class="btn btn-default ml-1" type="button"></button> </form> </div> </nav> </div> </div> <div class="row justify-content-center pg-shout-box"> <div class="col text-white text-center">November 21, 2024: <a href="/about/news/postgresql-172-166-1510-1415-1318-and-1222-released-2965/"> PostgreSQL 17.2, 16.6, 15.10, 14.15, 13.18, and 12.22 Released! </a> </div> </div> </div> <div class="container-fluid margin"> <div class="row"> <div id="pgContentWrap" class="col-11"> <div class="row"> <div class="col-md-6 mb-2"> <div class="row"> <div class="col"> <div> <a href="/docs/" title="Documentation">Documentation</a> → <a href="/docs/17/index.html">PostgreSQL 17</a> </div> </div> </div> <div class="row"> <div class="col"> Supported Versions: <a href="/docs/current/runtime-config-resource.html" title="PostgreSQL 17 - 19.4. Resource Consumption" class="docs-version-selected">Current</a> (<a href="/docs/17/runtime-config-resource.html" title="PostgreSQL 17 - 19.4. Resource Consumption" class="docs-version-selected">17</a>) / <a href="/docs/16/runtime-config-resource.html" title="PostgreSQL 16 - 19.4. Resource Consumption" >16</a> / <a href="/docs/15/runtime-config-resource.html" title="PostgreSQL 15 - 19.4. Resource Consumption" >15</a> / <a href="/docs/14/runtime-config-resource.html" title="PostgreSQL 14 - 19.4. Resource Consumption" >14</a> / <a href="/docs/13/runtime-config-resource.html" title="PostgreSQL 13 - 19.4. Resource Consumption" >13</a> </div> </div> <div class="row"> <div class="col"> Development Versions: <a href="/docs/devel/runtime-config-resource.html" title="PostgreSQL devel - 19.4. Resource Consumption" rel="nofollow">devel</a> </div> </div> <div class="row"> <div class="col-12"> Unsupported versions: <a href="/docs/12/runtime-config-resource.html" title="PostgreSQL 12 - 19.4. Resource Consumption" rel="nofollow">12</a> / <a href="/docs/11/runtime-config-resource.html" title="PostgreSQL 11 - 19.4. Resource Consumption" rel="nofollow">11</a> / <a href="/docs/10/runtime-config-resource.html" title="PostgreSQL 10 - 19.4. Resource Consumption" rel="nofollow">10</a> / <a href="/docs/9.6/runtime-config-resource.html" title="PostgreSQL 9.6 - 19.4. Resource Consumption" rel="nofollow">9.6</a> / <a href="/docs/9.5/runtime-config-resource.html" title="PostgreSQL 9.5 - 19.4. Resource Consumption" rel="nofollow">9.5</a> / <a href="/docs/9.4/runtime-config-resource.html" title="PostgreSQL 9.4 - 19.4. Resource Consumption" rel="nofollow">9.4</a> / <a href="/docs/9.3/runtime-config-resource.html" title="PostgreSQL 9.3 - 19.4. Resource Consumption" rel="nofollow">9.3</a> / <a href="/docs/9.2/runtime-config-resource.html" title="PostgreSQL 9.2 - 19.4. Resource Consumption" rel="nofollow">9.2</a> / <a href="/docs/9.1/runtime-config-resource.html" title="PostgreSQL 9.1 - 19.4. Resource Consumption" rel="nofollow">9.1</a> / <a href="/docs/9.0/runtime-config-resource.html" title="PostgreSQL 9.0 - 19.4. Resource Consumption" rel="nofollow">9.0</a> / <a href="/docs/8.4/runtime-config-resource.html" title="PostgreSQL 8.4 - 19.4. Resource Consumption" rel="nofollow">8.4</a> / <a href="/docs/8.3/runtime-config-resource.html" title="PostgreSQL 8.3 - 19.4. Resource Consumption" rel="nofollow">8.3</a> / <a href="/docs/8.2/runtime-config-resource.html" title="PostgreSQL 8.2 - 19.4. Resource Consumption" rel="nofollow">8.2</a> / <a href="/docs/8.1/runtime-config-resource.html" title="PostgreSQL 8.1 - 19.4. Resource Consumption" rel="nofollow">8.1</a> </div> </div> </div> <div class="col-md-6 col-lg-5 offset-lg-1"> <form id="docSearchForm" role="search" method="get" action="/search/"> <input type="hidden" name="u" value="/docs/17/" /> <div class="input-group"> <input type="text" id="q" name="q" class="form-control" placeholder="Search the documentation for..."/> <span class="input-group-btn"> <button class="btn btn-default" type="submit"><i class="fas fa-search"></i></button> </span> </div> </form> </div> </div> <div id="docContent"> <div class="navheader"> <table width="100%" summary="Navigation header"> <tr> <th colspan="5" align="center">19.4. Resource Consumption</th> </tr> <tr> <td width="10%" align="left"><a accesskey="p" href="runtime-config-connection.html" title="19.3. Connections and Authentication">Prev</a> </td> <td width="10%" align="left"><a accesskey="u" href="runtime-config.html" title="Chapter 19. Server Configuration">Up</a></td> <th width="60%" align="center">Chapter 19. Server Configuration</th> <td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 17.2 Documentation">Home</a></td> <td width="10%" align="right"> <a accesskey="n" href="runtime-config-wal.html" title="19.5. Write Ahead Log">Next</a></td> </tr> </table> <hr /> </div> <div class="sect1" id="RUNTIME-CONFIG-RESOURCE"> <div class="titlepage"> <div> <div> <h2 class="title" style="clear: both">19.4. Resource Consumption <a href="#RUNTIME-CONFIG-RESOURCE" class="id_link">#</a></h2> </div> </div> </div> <div class="toc"> <dl class="toc"> <dt><span class="sect2"><a href="runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-MEMORY">19.4.1. Memory</a></span></dt> <dt><span class="sect2"><a href="runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-DISK">19.4.2. Disk</a></span></dt> <dt><span class="sect2"><a href="runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-KERNEL">19.4.3. Kernel Resource Usage</a></span></dt> <dt><span class="sect2"><a href="runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST">19.4.4. Cost-based Vacuum Delay</a></span></dt> <dt><span class="sect2"><a href="runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-BACKGROUND-WRITER">19.4.5. Background Writer</a></span></dt> <dt><span class="sect2"><a href="runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-ASYNC-BEHAVIOR">19.4.6. Asynchronous Behavior</a></span></dt> </dl> </div> <div class="sect2" id="RUNTIME-CONFIG-RESOURCE-MEMORY"> <div class="titlepage"> <div> <div> <h3 class="title">19.4.1. Memory <a href="#RUNTIME-CONFIG-RESOURCE-MEMORY" class="id_link">#</a></h3> </div> </div> </div> <div class="variablelist"> <dl class="variablelist"> <dt id="GUC-SHARED-BUFFERS"><span class="term"><code class="varname">shared_buffers</code> (<code class="type">integer</code>) <a id="id-1.6.6.7.2.2.1.1.3" class="indexterm" name="id-1.6.6.7.2.2.1.1.3"></a></span> <a href="#GUC-SHARED-BUFFERS" class="id_link">#</a></dt> <dd> <p>Sets the amount of memory the database server uses for shared memory buffers. The default is typically 128 megabytes (<code class="literal">128MB</code>), but might be less if your kernel settings will not support it (as determined during <span class="application">initdb</span>). This setting must be at least 128 kilobytes. However, settings significantly higher than the minimum are usually needed for good performance. If this value is specified without units, it is taken as blocks, that is <code class="symbol">BLCKSZ</code> bytes, typically 8kB. (Non-default values of <code class="symbol">BLCKSZ</code> change the minimum value.) This parameter can only be set at server start.</p> <p>If you have a dedicated database server with 1GB or more of RAM, a reasonable starting value for <code class="varname">shared_buffers</code> is 25% of the memory in your system. There are some workloads where even larger settings for <code class="varname">shared_buffers</code> are effective, but because <span class="productname">PostgreSQL</span> also relies on the operating system cache, it is unlikely that an allocation of more than 40% of RAM to <code class="varname">shared_buffers</code> will work better than a smaller amount. Larger settings for <code class="varname">shared_buffers</code> usually require a corresponding increase in <code class="varname">max_wal_size</code>, in order to spread out the process of writing large quantities of new or changed data over a longer period of time.</p> <p>On systems with less than 1GB of RAM, a smaller percentage of RAM is appropriate, so as to leave adequate space for the operating system.</p> </dd> <dt id="GUC-HUGE-PAGES"><span class="term"><code class="varname">huge_pages</code> (<code class="type">enum</code>) <a id="id-1.6.6.7.2.2.2.1.3" class="indexterm" name="id-1.6.6.7.2.2.2.1.3"></a></span> <a href="#GUC-HUGE-PAGES" class="id_link">#</a></dt> <dd> <p>Controls whether huge pages are requested for the main shared memory area. Valid values are <code class="literal">try</code> (the default), <code class="literal">on</code>, and <code class="literal">off</code>. With <code class="varname">huge_pages</code> set to <code class="literal">try</code>, the server will try to request huge pages, but fall back to the default if that fails. With <code class="literal">on</code>, failure to request huge pages will prevent the server from starting up. With <code class="literal">off</code>, huge pages will not be requested. The actual state of huge pages is indicated by the server variable <a class="xref" href="runtime-config-preset.html#GUC-HUGE-PAGES-STATUS">huge_pages_status</a>.</p> <p>At present, this setting is supported only on Linux and Windows. The setting is ignored on other systems when set to <code class="literal">try</code>. On Linux, it is only supported when <code class="varname">shared_memory_type</code> is set to <code class="literal">mmap</code> (the default).</p> <p>The use of huge pages results in smaller page tables and less CPU time spent on memory management, increasing performance. For more details about using huge pages on Linux, see <a class="xref" href="kernel-resources.html#LINUX-HUGE-PAGES" title="18.4.5. Linux Huge Pages">Section 18.4.5</a>.</p> <p>Huge pages are known as large pages on Windows. To use them, you need to assign the user right <span class="quote">“<span class="quote">Lock pages in memory</span>”</span> to the Windows user account that runs <span class="productname">PostgreSQL</span>. You can use Windows Group Policy tool (gpedit.msc) to assign the user right <span class="quote">“<span class="quote">Lock pages in memory</span>”</span>. To start the database server on the command prompt as a standalone process, not as a Windows service, the command prompt must be run as an administrator or User Access Control (UAC) must be disabled. When the UAC is enabled, the normal command prompt revokes the user right <span class="quote">“<span class="quote">Lock pages in memory</span>”</span> when started.</p> <p>Note that this setting only affects the main shared memory area. Operating systems such as Linux, FreeBSD, and Illumos can also use huge pages (also known as <span class="quote">“<span class="quote">super</span>”</span> pages or <span class="quote">“<span class="quote">large</span>”</span> pages) automatically for normal memory allocation, without an explicit request from <span class="productname">PostgreSQL</span>. On Linux, this is called <span class="quote">“<span class="quote">transparent huge pages</span>”</span><a id="id-1.6.6.7.2.2.2.2.5.5" class="indexterm" name="id-1.6.6.7.2.2.2.2.5.5"></a> (THP). That feature has been known to cause performance degradation with <span class="productname">PostgreSQL</span> for some users on some Linux versions, so its use is currently discouraged (unlike explicit use of <code class="varname">huge_pages</code>).</p> </dd> <dt id="GUC-HUGE-PAGE-SIZE"><span class="term"><code class="varname">huge_page_size</code> (<code class="type">integer</code>) <a id="id-1.6.6.7.2.2.3.1.3" class="indexterm" name="id-1.6.6.7.2.2.3.1.3"></a></span> <a href="#GUC-HUGE-PAGE-SIZE" class="id_link">#</a></dt> <dd> <p>Controls the size of huge pages, when they are enabled with <a class="xref" href="runtime-config-resource.html#GUC-HUGE-PAGES">huge_pages</a>. The default is zero (<code class="literal">0</code>). When set to <code class="literal">0</code>, the default huge page size on the system will be used. This parameter can only be set at server start.</p> <p>Some commonly available page sizes on modern 64 bit server architectures include: <code class="literal">2MB</code> and <code class="literal">1GB</code> (Intel and AMD), <code class="literal">16MB</code> and <code class="literal">16GB</code> (IBM POWER), and <code class="literal">64kB</code>, <code class="literal">2MB</code>, <code class="literal">32MB</code> and <code class="literal">1GB</code> (ARM). For more information about usage and support, see <a class="xref" href="kernel-resources.html#LINUX-HUGE-PAGES" title="18.4.5. Linux Huge Pages">Section 18.4.5</a>.</p> <p>Non-default settings are currently supported only on Linux.</p> </dd> <dt id="GUC-TEMP-BUFFERS"><span class="term"><code class="varname">temp_buffers</code> (<code class="type">integer</code>) <a id="id-1.6.6.7.2.2.4.1.3" class="indexterm" name="id-1.6.6.7.2.2.4.1.3"></a></span> <a href="#GUC-TEMP-BUFFERS" class="id_link">#</a></dt> <dd> <p>Sets the maximum amount of memory used for temporary buffers within each database session. These are session-local buffers used only for access to temporary tables. If this value is specified without units, it is taken as blocks, that is <code class="symbol">BLCKSZ</code> bytes, typically 8kB. The default is eight megabytes (<code class="literal">8MB</code>). (If <code class="symbol">BLCKSZ</code> is not 8kB, the default value scales proportionally to it.) This setting can be changed within individual sessions, but only before the first use of temporary tables within the session; subsequent attempts to change the value will have no effect on that session.</p> <p>A session will allocate temporary buffers as needed up to the limit given by <code class="varname">temp_buffers</code>. The cost of setting a large value in sessions that do not actually need many temporary buffers is only a buffer descriptor, or about 64 bytes, per increment in <code class="varname">temp_buffers</code>. However if a buffer is actually used an additional 8192 bytes will be consumed for it (or in general, <code class="symbol">BLCKSZ</code> bytes).</p> </dd> <dt id="GUC-MAX-PREPARED-TRANSACTIONS"><span class="term"><code class="varname">max_prepared_transactions</code> (<code class="type">integer</code>) <a id="id-1.6.6.7.2.2.5.1.3" class="indexterm" name="id-1.6.6.7.2.2.5.1.3"></a></span> <a href="#GUC-MAX-PREPARED-TRANSACTIONS" class="id_link">#</a></dt> <dd> <p>Sets the maximum number of transactions that can be in the <span class="quote">“<span class="quote">prepared</span>”</span> state simultaneously (see <a class="xref" href="sql-prepare-transaction.html" title="PREPARE TRANSACTION"><span class="refentrytitle">PREPARE TRANSACTION</span></a>). Setting this parameter to zero (which is the default) disables the prepared-transaction feature. This parameter can only be set at server start.</p> <p>If you are not planning to use prepared transactions, this parameter should be set to zero to prevent accidental creation of prepared transactions. If you are using prepared transactions, you will probably want <code class="varname">max_prepared_transactions</code> to be at least as large as <a class="xref" href="runtime-config-connection.html#GUC-MAX-CONNECTIONS">max_connections</a>, so that every session can have a prepared transaction pending.</p> <p>When running a standby server, you must set this parameter to the same or higher value than on the primary server. Otherwise, queries will not be allowed in the standby server.</p> </dd> <dt id="GUC-WORK-MEM"><span class="term"><code class="varname">work_mem</code> (<code class="type">integer</code>) <a id="id-1.6.6.7.2.2.6.1.3" class="indexterm" name="id-1.6.6.7.2.2.6.1.3"></a></span> <a href="#GUC-WORK-MEM" class="id_link">#</a></dt> <dd> <p>Sets the base maximum amount of memory to be used by a query operation (such as a sort or hash table) before writing to temporary disk files. If this value is specified without units, it is taken as kilobytes. The default value is four megabytes (<code class="literal">4MB</code>). Note that a complex query might perform several sort and hash operations at the same time, with each operation generally being allowed to use as much memory as this value specifies before it starts to write data into temporary files. Also, several running sessions could be doing such operations concurrently. Therefore, the total memory used could be many times the value of <code class="varname">work_mem</code>; it is necessary to keep this fact in mind when choosing the value. Sort operations are used for <code class="literal">ORDER BY</code>, <code class="literal">DISTINCT</code>, and merge joins. Hash tables are used in hash joins, hash-based aggregation, memoize nodes and hash-based processing of <code class="literal">IN</code> subqueries.</p> <p>Hash-based operations are generally more sensitive to memory availability than equivalent sort-based operations. The memory limit for a hash table is computed by multiplying <code class="varname">work_mem</code> by <code class="varname">hash_mem_multiplier</code>. This makes it possible for hash-based operations to use an amount of memory that exceeds the usual <code class="varname">work_mem</code> base amount.</p> </dd> <dt id="GUC-HASH-MEM-MULTIPLIER"><span class="term"><code class="varname">hash_mem_multiplier</code> (<code class="type">floating point</code>) <a id="id-1.6.6.7.2.2.7.1.3" class="indexterm" name="id-1.6.6.7.2.2.7.1.3"></a></span> <a href="#GUC-HASH-MEM-MULTIPLIER" class="id_link">#</a></dt> <dd> <p>Used to compute the maximum amount of memory that hash-based operations can use. The final limit is determined by multiplying <code class="varname">work_mem</code> by <code class="varname">hash_mem_multiplier</code>. The default value is 2.0, which makes hash-based operations use twice the usual <code class="varname">work_mem</code> base amount.</p> <p>Consider increasing <code class="varname">hash_mem_multiplier</code> in environments where spilling by query operations is a regular occurrence, especially when simply increasing <code class="varname">work_mem</code> 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 <code class="varname">work_mem</code> has already been increased to 40MB or more.</p> </dd> <dt id="GUC-MAINTENANCE-WORK-MEM"><span class="term"><code class="varname">maintenance_work_mem</code> (<code class="type">integer</code>) <a id="id-1.6.6.7.2.2.8.1.3" class="indexterm" name="id-1.6.6.7.2.2.8.1.3"></a></span> <a href="#GUC-MAINTENANCE-WORK-MEM" class="id_link">#</a></dt> <dd> <p>Specifies the maximum amount of memory to be used by maintenance operations, such as <code class="command">VACUUM</code>, <code class="command">CREATE INDEX</code>, and <code class="command">ALTER TABLE ADD FOREIGN KEY</code>. If this value is specified without units, it is taken as kilobytes. It defaults to 64 megabytes (<code class="literal">64MB</code>). 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 <code class="varname">work_mem</code>. Larger settings might improve performance for vacuuming and for restoring database dumps.</p> <p>Note that when autovacuum runs, up to <a class="xref" href="runtime-config-autovacuum.html#GUC-AUTOVACUUM-MAX-WORKERS">autovacuum_max_workers</a> 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 <a class="xref" href="runtime-config-resource.html#GUC-AUTOVACUUM-WORK-MEM">autovacuum_work_mem</a>.</p> </dd> <dt id="GUC-AUTOVACUUM-WORK-MEM"><span class="term"><code class="varname">autovacuum_work_mem</code> (<code class="type">integer</code>) <a id="id-1.6.6.7.2.2.9.1.3" class="indexterm" name="id-1.6.6.7.2.2.9.1.3"></a></span> <a href="#GUC-AUTOVACUUM-WORK-MEM" class="id_link">#</a></dt> <dd> <p>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 <a class="xref" href="runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM">maintenance_work_mem</a> should be used instead. The setting has no effect on the behavior of <code class="command">VACUUM</code> when run in other contexts. This parameter can only be set in the <code class="filename">postgresql.conf</code> file or on the server command line.</p> </dd> <dt id="GUC-VACUUM-BUFFER-USAGE-LIMIT"><span class="term"><code class="varname">vacuum_buffer_usage_limit</code> (<code class="type">integer</code>) <a id="id-1.6.6.7.2.2.10.1.3" class="indexterm" name="id-1.6.6.7.2.2.10.1.3"></a></span> <a href="#GUC-VACUUM-BUFFER-USAGE-LIMIT" class="id_link">#</a></dt> <dd> <p>Specifies the size of the <a class="glossterm" href="glossary.html#GLOSSARY-BUFFER-ACCESS-STRATEGY"></a><a class="glossterm" href="glossary.html#GLOSSARY-BUFFER-ACCESS-STRATEGY" title="Buffer Access Strategy">Buffer Access Strategy</a> used by the <code class="command">VACUUM</code> and <code class="command">ANALYZE</code> commands. A setting of <code class="literal">0</code> will allow the operation to use any number of <code class="varname">shared_buffers</code>. Otherwise valid sizes range from <code class="literal">128 kB</code> to <code class="literal">16 GB</code>. If the specified size would exceed 1/8 the size of <code class="varname">shared_buffers</code>, the size is silently capped to that value. The default value is <code class="literal">2MB</code>. If this value is specified without units, it is taken as kilobytes. This parameter can be set at any time. It can be overridden for <a class="xref" href="sql-vacuum.html" title="VACUUM"><span class="refentrytitle">VACUUM</span></a> and <a class="xref" href="sql-analyze.html" title="ANALYZE"><span class="refentrytitle">ANALYZE</span></a> when passing the <code class="option">BUFFER_USAGE_LIMIT</code> option. Higher settings can allow <code class="command">VACUUM</code> and <code class="command">ANALYZE</code> to run more quickly, but having too large a setting may cause too many other useful pages to be evicted from shared buffers.</p> </dd> <dt id="GUC-LOGICAL-DECODING-WORK-MEM"><span class="term"><code class="varname">logical_decoding_work_mem</code> (<code class="type">integer</code>) <a id="id-1.6.6.7.2.2.11.1.3" class="indexterm" name="id-1.6.6.7.2.2.11.1.3"></a></span> <a href="#GUC-LOGICAL-DECODING-WORK-MEM" class="id_link">#</a></dt> <dd> <p>Specifies the maximum amount of memory to be used by logical decoding, before some of the decoded changes are written to local disk. This limits the amount of memory used by logical streaming replication connections. It defaults to 64 megabytes (<code class="literal">64MB</code>). Since each replication connection only uses a single buffer of this size, and an installation normally doesn't have many such connections concurrently (as limited by <code class="varname">max_wal_senders</code>), it's safe to set this value significantly higher than <code class="varname">work_mem</code>, reducing the amount of decoded changes written to disk.</p> </dd> <dt id="GUC-COMMIT-TIMESTAMP-BUFFERS"><span class="term"><code class="varname">commit_timestamp_buffers</code> (<code class="type">integer</code>) <a id="id-1.6.6.7.2.2.12.1.3" class="indexterm" name="id-1.6.6.7.2.2.12.1.3"></a></span> <a href="#GUC-COMMIT-TIMESTAMP-BUFFERS" class="id_link">#</a></dt> <dd> <p>Specifies the amount of memory to use to cache the contents of <code class="literal">pg_commit_ts</code> (see <a class="xref" href="storage-file-layout.html#PGDATA-CONTENTS-TABLE" title="Table 65.1. Contents of PGDATA">Table 65.1</a>). If this value is specified without units, it is taken as blocks, that is <code class="symbol">BLCKSZ</code> bytes, typically 8kB. The default value is <code class="literal">0</code>, which requests <code class="varname">shared_buffers</code>/512 up to 1024 blocks, but not fewer than 16 blocks. This parameter can only be set at server start.</p> </dd> <dt id="GUC-MULTIXACT-MEMBER-BUFFERS"><span class="term"><code class="varname">multixact_member_buffers</code> (<code class="type">integer</code>) <a id="id-1.6.6.7.2.2.13.1.3" class="indexterm" name="id-1.6.6.7.2.2.13.1.3"></a></span> <a href="#GUC-MULTIXACT-MEMBER-BUFFERS" class="id_link">#</a></dt> <dd> <p>Specifies the amount of shared memory to use to cache the contents of <code class="literal">pg_multixact/members</code> (see <a class="xref" href="storage-file-layout.html#PGDATA-CONTENTS-TABLE" title="Table 65.1. Contents of PGDATA">Table 65.1</a>). If this value is specified without units, it is taken as blocks, that is <code class="symbol">BLCKSZ</code> bytes, typically 8kB. The default value is <code class="literal">32</code>. This parameter can only be set at server start.</p> </dd> <dt id="GUC-MULTIXACT-OFFSET-BUFFERS"><span class="term"><code class="varname">multixact_offset_buffers</code> (<code class="type">integer</code>) <a id="id-1.6.6.7.2.2.14.1.3" class="indexterm" name="id-1.6.6.7.2.2.14.1.3"></a></span> <a href="#GUC-MULTIXACT-OFFSET-BUFFERS" class="id_link">#</a></dt> <dd> <p>Specifies the amount of shared memory to use to cache the contents of <code class="literal">pg_multixact/offsets</code> (see <a class="xref" href="storage-file-layout.html#PGDATA-CONTENTS-TABLE" title="Table 65.1. Contents of PGDATA">Table 65.1</a>). If this value is specified without units, it is taken as blocks, that is <code class="symbol">BLCKSZ</code> bytes, typically 8kB. The default value is <code class="literal">16</code>. This parameter can only be set at server start.</p> </dd> <dt id="GUC-NOTIFY-BUFFERS"><span class="term"><code class="varname">notify_buffers</code> (<code class="type">integer</code>) <a id="id-1.6.6.7.2.2.15.1.3" class="indexterm" name="id-1.6.6.7.2.2.15.1.3"></a></span> <a href="#GUC-NOTIFY-BUFFERS" class="id_link">#</a></dt> <dd> <p>Specifies the amount of shared memory to use to cache the contents of <code class="literal">pg_notify</code> (see <a class="xref" href="storage-file-layout.html#PGDATA-CONTENTS-TABLE" title="Table 65.1. Contents of PGDATA">Table 65.1</a>). If this value is specified without units, it is taken as blocks, that is <code class="symbol">BLCKSZ</code> bytes, typically 8kB. The default value is <code class="literal">16</code>. This parameter can only be set at server start.</p> </dd> <dt id="GUC-SERIALIZABLE-BUFFERS"><span class="term"><code class="varname">serializable_buffers</code> (<code class="type">integer</code>) <a id="id-1.6.6.7.2.2.16.1.3" class="indexterm" name="id-1.6.6.7.2.2.16.1.3"></a></span> <a href="#GUC-SERIALIZABLE-BUFFERS" class="id_link">#</a></dt> <dd> <p>Specifies the amount of shared memory to use to cache the contents of <code class="literal">pg_serial</code> (see <a class="xref" href="storage-file-layout.html#PGDATA-CONTENTS-TABLE" title="Table 65.1. Contents of PGDATA">Table 65.1</a>). If this value is specified without units, it is taken as blocks, that is <code class="symbol">BLCKSZ</code> bytes, typically 8kB. The default value is <code class="literal">32</code>. This parameter can only be set at server start.</p> </dd> <dt id="GUC-SUBTRANSACTION-BUFFERS"><span class="term"><code class="varname">subtransaction_buffers</code> (<code class="type">integer</code>) <a id="id-1.6.6.7.2.2.17.1.3" class="indexterm" name="id-1.6.6.7.2.2.17.1.3"></a></span> <a href="#GUC-SUBTRANSACTION-BUFFERS" class="id_link">#</a></dt> <dd> <p>Specifies the amount of shared memory to use to cache the contents of <code class="literal">pg_subtrans</code> (see <a class="xref" href="storage-file-layout.html#PGDATA-CONTENTS-TABLE" title="Table 65.1. Contents of PGDATA">Table 65.1</a>). If this value is specified without units, it is taken as blocks, that is <code class="symbol">BLCKSZ</code> bytes, typically 8kB. The default value is <code class="literal">0</code>, which requests <code class="varname">shared_buffers</code>/512 up to 1024 blocks, but not fewer than 16 blocks. This parameter can only be set at server start.</p> </dd> <dt id="GUC-TRANSACTION-BUFFERS"><span class="term"><code class="varname">transaction_buffers</code> (<code class="type">integer</code>) <a id="id-1.6.6.7.2.2.18.1.3" class="indexterm" name="id-1.6.6.7.2.2.18.1.3"></a></span> <a href="#GUC-TRANSACTION-BUFFERS" class="id_link">#</a></dt> <dd> <p>Specifies the amount of shared memory to use to cache the contents of <code class="literal">pg_xact</code> (see <a class="xref" href="storage-file-layout.html#PGDATA-CONTENTS-TABLE" title="Table 65.1. Contents of PGDATA">Table 65.1</a>). If this value is specified without units, it is taken as blocks, that is <code class="symbol">BLCKSZ</code> bytes, typically 8kB. The default value is <code class="literal">0</code>, which requests <code class="varname">shared_buffers</code>/512 up to 1024 blocks, but not fewer than 16 blocks. This parameter can only be set at server start.</p> </dd> <dt id="GUC-MAX-STACK-DEPTH"><span class="term"><code class="varname">max_stack_depth</code> (<code class="type">integer</code>) <a id="id-1.6.6.7.2.2.19.1.3" class="indexterm" name="id-1.6.6.7.2.2.19.1.3"></a></span> <a href="#GUC-MAX-STACK-DEPTH" class="id_link">#</a></dt> <dd> <p>Specifies the maximum safe depth of the server's execution stack. The ideal setting for this parameter is the actual stack size limit enforced by the kernel (as set by <code class="literal">ulimit -s</code> or local equivalent), less a safety margin of a megabyte or so. The safety margin is needed because the stack depth is not checked in every routine in the server, but only in key potentially-recursive routines. If this value is specified without units, it is taken as kilobytes. The default setting is two megabytes (<code class="literal">2MB</code>), which is conservatively small and unlikely to risk crashes. However, it might be too small to allow execution of complex functions. Only superusers and users with the appropriate <code class="literal">SET</code> privilege can change this setting.</p> <p>Setting <code class="varname">max_stack_depth</code> higher than the actual kernel limit will mean that a runaway recursive function can crash an individual backend process. On platforms where <span class="productname">PostgreSQL</span> can determine the kernel limit, the server will not allow this variable to be set to an unsafe value. However, not all platforms provide the information, so caution is recommended in selecting a value.</p> </dd> <dt id="GUC-SHARED-MEMORY-TYPE"><span class="term"><code class="varname">shared_memory_type</code> (<code class="type">enum</code>) <a id="id-1.6.6.7.2.2.20.1.3" class="indexterm" name="id-1.6.6.7.2.2.20.1.3"></a></span> <a href="#GUC-SHARED-MEMORY-TYPE" class="id_link">#</a></dt> <dd> <p>Specifies the shared memory implementation that the server should use for the main shared memory region that holds <span class="productname">PostgreSQL</span>'s shared buffers and other shared data. Possible values are <code class="literal">mmap</code> (for anonymous shared memory allocated using <code class="function">mmap</code>), <code class="literal">sysv</code> (for System V shared memory allocated via <code class="function">shmget</code>) and <code class="literal">windows</code> (for Windows shared memory). Not all values are supported on all platforms; the first supported option is the default for that platform. The use of the <code class="literal">sysv</code> option, which is not the default on any platform, is generally discouraged because it typically requires non-default kernel settings to allow for large allocations (see <a class="xref" href="kernel-resources.html#SYSVIPC" title="18.4.1. Shared Memory and Semaphores">Section 18.4.1</a>).</p> </dd> <dt id="GUC-DYNAMIC-SHARED-MEMORY-TYPE"><span class="term"><code class="varname">dynamic_shared_memory_type</code> (<code class="type">enum</code>) <a id="id-1.6.6.7.2.2.21.1.3" class="indexterm" name="id-1.6.6.7.2.2.21.1.3"></a></span> <a href="#GUC-DYNAMIC-SHARED-MEMORY-TYPE" class="id_link">#</a></dt> <dd> <p>Specifies the dynamic shared memory implementation that the server should use. Possible values are <code class="literal">posix</code> (for POSIX shared memory allocated using <code class="literal">shm_open</code>), <code class="literal">sysv</code> (for System V shared memory allocated via <code class="literal">shmget</code>), <code class="literal">windows</code> (for Windows shared memory), and <code class="literal">mmap</code> (to simulate shared memory using memory-mapped files stored in the data directory). Not all values are supported on all platforms; the first supported option is usually the default for that platform. The use of the <code class="literal">mmap</code> option, which is not the default on any platform, is generally discouraged because the operating system may write modified pages back to disk repeatedly, increasing system I/O load; however, it may be useful for debugging, when the <code class="literal">pg_dynshmem</code> directory is stored on a RAM disk, or when other shared memory facilities are not available.</p> </dd> <dt id="GUC-MIN-DYNAMIC-SHARED-MEMORY"><span class="term"><code class="varname">min_dynamic_shared_memory</code> (<code class="type">integer</code>) <a id="id-1.6.6.7.2.2.22.1.3" class="indexterm" name="id-1.6.6.7.2.2.22.1.3"></a></span> <a href="#GUC-MIN-DYNAMIC-SHARED-MEMORY" class="id_link">#</a></dt> <dd> <p>Specifies the amount of memory that should be allocated at server startup for use by parallel queries. When this memory region is insufficient or exhausted by concurrent queries, new parallel queries try to allocate extra shared memory temporarily from the operating system using the method configured with <code class="varname">dynamic_shared_memory_type</code>, which may be slower due to memory management overheads. Memory that is allocated at startup with <code class="varname">min_dynamic_shared_memory</code> is affected by the <code class="varname">huge_pages</code> setting on operating systems where that is supported, and may be more likely to benefit from larger pages on operating systems where that is managed automatically. The default value is <code class="literal">0</code> (none). This parameter can only be set at server start.</p> </dd> </dl> </div> </div> <div class="sect2" id="RUNTIME-CONFIG-RESOURCE-DISK"> <div class="titlepage"> <div> <div> <h3 class="title">19.4.2. Disk <a href="#RUNTIME-CONFIG-RESOURCE-DISK" class="id_link">#</a></h3> </div> </div> </div> <div class="variablelist"> <dl class="variablelist"> <dt id="GUC-TEMP-FILE-LIMIT"><span class="term"><code class="varname">temp_file_limit</code> (<code class="type">integer</code>) <a id="id-1.6.6.7.3.2.1.1.3" class="indexterm" name="id-1.6.6.7.3.2.1.1.3"></a></span> <a href="#GUC-TEMP-FILE-LIMIT" class="id_link">#</a></dt> <dd> <p>Specifies the maximum amount of disk space that a process can use for temporary files, such as sort and hash temporary files, or the storage file for a held cursor. A transaction attempting to exceed this limit will be canceled. If this value is specified without units, it is taken as kilobytes. <code class="literal">-1</code> (the default) means no limit. Only superusers and users with the appropriate <code class="literal">SET</code> privilege can change this setting.</p> <p>This setting constrains the total space used at any instant by all temporary files used by a given <span class="productname">PostgreSQL</span> process. It should be noted that disk space used for explicit temporary tables, as opposed to temporary files used behind-the-scenes in query execution, does <span class="emphasis"><em>not</em></span> count against this limit.</p> </dd> <dt id="GUC-MAX-NOTIFY-QUEUE-PAGES"><span class="term"><code class="varname">max_notify_queue_pages</code> (<code class="type">integer</code>) <a id="id-1.6.6.7.3.2.2.1.3" class="indexterm" name="id-1.6.6.7.3.2.2.1.3"></a></span> <a href="#GUC-MAX-NOTIFY-QUEUE-PAGES" class="id_link">#</a></dt> <dd> <p>Specifies the maximum amount of allocated pages for <a class="xref" href="sql-notify.html" title="NOTIFY"><span class="refentrytitle">NOTIFY</span></a> / <a class="xref" href="sql-listen.html" title="LISTEN"><span class="refentrytitle">LISTEN</span></a> queue. The default value is 1048576. For 8 KB pages it allows to consume up to 8 GB of disk space.</p> </dd> </dl> </div> </div> <div class="sect2" id="RUNTIME-CONFIG-RESOURCE-KERNEL"> <div class="titlepage"> <div> <div> <h3 class="title">19.4.3. Kernel Resource Usage <a href="#RUNTIME-CONFIG-RESOURCE-KERNEL" class="id_link">#</a></h3> </div> </div> </div> <div class="variablelist"> <dl class="variablelist"> <dt id="GUC-MAX-FILES-PER-PROCESS"><span class="term"><code class="varname">max_files_per_process</code> (<code class="type">integer</code>) <a id="id-1.6.6.7.4.2.1.1.3" class="indexterm" name="id-1.6.6.7.4.2.1.1.3"></a></span> <a href="#GUC-MAX-FILES-PER-PROCESS" class="id_link">#</a></dt> <dd> <p>Sets the maximum number of simultaneously open files allowed to each server subprocess. The default is one thousand files. If the kernel is enforcing a safe per-process limit, you don't need to worry about this setting. But on some platforms (notably, most BSD systems), the kernel will allow individual processes to open many more files than the system can actually support if many processes all try to open that many files. If you find yourself seeing <span class="quote">“<span class="quote">Too many open files</span>”</span> failures, try reducing this setting. This parameter can only be set at server start.</p> </dd> </dl> </div> </div> <div class="sect2" id="RUNTIME-CONFIG-RESOURCE-VACUUM-COST"> <div class="titlepage"> <div> <div> <h3 class="title">19.4.4. Cost-based Vacuum Delay <a href="#RUNTIME-CONFIG-RESOURCE-VACUUM-COST" class="id_link">#</a></h3> </div> </div> </div> <p>During the execution of <a class="xref" href="sql-vacuum.html" title="VACUUM"><span class="refentrytitle">VACUUM</span></a> and <a class="xref" href="sql-analyze.html" title="ANALYZE"><span class="refentrytitle">ANALYZE</span></a> commands, the system maintains an internal counter that keeps track of the estimated cost of the various I/O operations that are performed. When the accumulated cost reaches a limit (specified by <code class="varname">vacuum_cost_limit</code>), the process performing the operation will sleep for a short period of time, as specified by <code class="varname">vacuum_cost_delay</code>. Then it will reset the counter and continue execution.</p> <p>The intent of this feature is to allow administrators to reduce the I/O impact of these commands on concurrent database activity. There are many situations where it is not important that maintenance commands like <code class="command">VACUUM</code> and <code class="command">ANALYZE</code> finish quickly; however, it is usually very important that these commands do not significantly interfere with the ability of the system to perform other database operations. Cost-based vacuum delay provides a way for administrators to achieve this.</p> <p>This feature is disabled by default for manually issued <code class="command">VACUUM</code> commands. To enable it, set the <code class="varname">vacuum_cost_delay</code> variable to a nonzero value.</p> <div class="variablelist"> <dl class="variablelist"> <dt id="GUC-VACUUM-COST-DELAY"><span class="term"><code class="varname">vacuum_cost_delay</code> (<code class="type">floating point</code>) <a id="id-1.6.6.7.5.5.1.1.3" class="indexterm" name="id-1.6.6.7.5.5.1.1.3"></a></span> <a href="#GUC-VACUUM-COST-DELAY" class="id_link">#</a></dt> <dd> <p>The amount of time that the process will sleep when the cost limit has been exceeded. If this value is specified without units, it is taken as milliseconds. The default value is zero, which disables the cost-based vacuum delay feature. Positive values enable cost-based vacuuming.</p> <p>When using cost-based vacuuming, appropriate values for <code class="varname">vacuum_cost_delay</code> are usually quite small, perhaps less than 1 millisecond. While <code class="varname">vacuum_cost_delay</code> can be set to fractional-millisecond values, such delays may not be measured accurately on older platforms. On such platforms, increasing <code class="command">VACUUM</code>'s throttled resource consumption above what you get at 1ms will require changing the other vacuum cost parameters. You should, nonetheless, keep <code class="varname">vacuum_cost_delay</code> as small as your platform will consistently measure; large delays are not helpful.</p> </dd> <dt id="GUC-VACUUM-COST-PAGE-HIT"><span class="term"><code class="varname">vacuum_cost_page_hit</code> (<code class="type">integer</code>) <a id="id-1.6.6.7.5.5.2.1.3" class="indexterm" name="id-1.6.6.7.5.5.2.1.3"></a></span> <a href="#GUC-VACUUM-COST-PAGE-HIT" class="id_link">#</a></dt> <dd> <p>The estimated cost for vacuuming a buffer found in the shared buffer cache. It represents the cost to lock the buffer pool, lookup the shared hash table and scan the content of the page. The default value is one.</p> </dd> <dt id="GUC-VACUUM-COST-PAGE-MISS"><span class="term"><code class="varname">vacuum_cost_page_miss</code> (<code class="type">integer</code>) <a id="id-1.6.6.7.5.5.3.1.3" class="indexterm" name="id-1.6.6.7.5.5.3.1.3"></a></span> <a href="#GUC-VACUUM-COST-PAGE-MISS" class="id_link">#</a></dt> <dd> <p>The estimated cost for vacuuming a buffer that has to be read from disk. This represents the effort to lock the buffer pool, lookup the shared hash table, read the desired block in from the disk and scan its content. The default value is 2.</p> </dd> <dt id="GUC-VACUUM-COST-PAGE-DIRTY"><span class="term"><code class="varname">vacuum_cost_page_dirty</code> (<code class="type">integer</code>) <a id="id-1.6.6.7.5.5.4.1.3" class="indexterm" name="id-1.6.6.7.5.5.4.1.3"></a></span> <a href="#GUC-VACUUM-COST-PAGE-DIRTY" class="id_link">#</a></dt> <dd> <p>The estimated cost charged when vacuum modifies a block that was previously clean. It represents the extra I/O required to flush the dirty block out to disk again. The default value is 20.</p> </dd> <dt id="GUC-VACUUM-COST-LIMIT"><span class="term"><code class="varname">vacuum_cost_limit</code> (<code class="type">integer</code>) <a id="id-1.6.6.7.5.5.5.1.3" class="indexterm" name="id-1.6.6.7.5.5.5.1.3"></a></span> <a href="#GUC-VACUUM-COST-LIMIT" class="id_link">#</a></dt> <dd> <p>This is the accumulated cost that will cause the vacuuming process to sleep for <code class="varname">vacuum_cost_delay</code>. The default is 200.</p> </dd> </dl> </div> <div class="note"> <h3 class="title">Note</h3> <p>There are certain operations that hold critical locks and should therefore complete as quickly as possible. Cost-based vacuum delays do not occur during such operations. Therefore it is possible that the cost accumulates far higher than the specified limit. To avoid uselessly long delays in such cases, the actual delay is calculated as <code class="varname">vacuum_cost_delay</code> * <code class="varname">accumulated_balance</code> / <code class="varname">vacuum_cost_limit</code> with a maximum of <code class="varname">vacuum_cost_delay</code> * 4.</p> </div> </div> <div class="sect2" id="RUNTIME-CONFIG-RESOURCE-BACKGROUND-WRITER"> <div class="titlepage"> <div> <div> <h3 class="title">19.4.5. Background Writer <a href="#RUNTIME-CONFIG-RESOURCE-BACKGROUND-WRITER" class="id_link">#</a></h3> </div> </div> </div> <p>There is a separate server process called the <em class="firstterm">background writer</em>, whose function is to issue writes of <span class="quote">“<span class="quote">dirty</span>”</span> (new or modified) shared buffers. When the number of clean shared buffers appears to be insufficient, the background writer writes some dirty buffers to the file system and marks them as clean. This reduces the likelihood that server processes handling user queries will be unable to find clean buffers and have to write dirty buffers themselves. However, the background writer does cause a net overall increase in I/O load, because while a repeatedly-dirtied page might otherwise be written only once per checkpoint interval, the background writer might write it several times as it is dirtied in the same interval. The parameters discussed in this subsection can be used to tune the behavior for local needs.</p> <div class="variablelist"> <dl class="variablelist"> <dt id="GUC-BGWRITER-DELAY"><span class="term"><code class="varname">bgwriter_delay</code> (<code class="type">integer</code>) <a id="id-1.6.6.7.6.3.1.1.3" class="indexterm" name="id-1.6.6.7.6.3.1.1.3"></a></span> <a href="#GUC-BGWRITER-DELAY" class="id_link">#</a></dt> <dd> <p>Specifies the delay between activity rounds for the background writer. In each round the writer issues writes for some number of dirty buffers (controllable by the following parameters). It then sleeps for the length of <code class="varname">bgwriter_delay</code>, and repeats. When there are no dirty buffers in the buffer pool, though, it goes into a longer sleep regardless of <code class="varname">bgwriter_delay</code>. If this value is specified without units, it is taken as milliseconds. The default value is 200 milliseconds (<code class="literal">200ms</code>). Note that on some systems, the effective resolution of sleep delays is 10 milliseconds; setting <code class="varname">bgwriter_delay</code> 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 <code class="filename">postgresql.conf</code> file or on the server command line.</p> </dd> <dt id="GUC-BGWRITER-LRU-MAXPAGES"><span class="term"><code class="varname">bgwriter_lru_maxpages</code> (<code class="type">integer</code>) <a id="id-1.6.6.7.6.3.2.1.3" class="indexterm" name="id-1.6.6.7.6.3.2.1.3"></a></span> <a href="#GUC-BGWRITER-LRU-MAXPAGES" class="id_link">#</a></dt> <dd> <p>In each round, no more than this many buffers will be written by the background writer. Setting this to zero disables background writing. (Note that checkpoints, which are managed by a separate, dedicated auxiliary process, are unaffected.) The default value is 100 buffers. This parameter can only be set in the <code class="filename">postgresql.conf</code> file or on the server command line.</p> </dd> <dt id="GUC-BGWRITER-LRU-MULTIPLIER"><span class="term"><code class="varname">bgwriter_lru_multiplier</code> (<code class="type">floating point</code>) <a id="id-1.6.6.7.6.3.3.1.3" class="indexterm" name="id-1.6.6.7.6.3.3.1.3"></a></span> <a href="#GUC-BGWRITER-LRU-MULTIPLIER" class="id_link">#</a></dt> <dd> <p>The number of dirty buffers written in each round is based on the number of new buffers that have been needed by server processes during recent rounds. The average recent need is multiplied by <code class="varname">bgwriter_lru_multiplier</code> to arrive at an estimate of the number of buffers that will be needed during the next round. Dirty buffers are written until there are that many clean, reusable buffers available. (However, no more than <code class="varname">bgwriter_lru_maxpages</code> buffers will be written per round.) Thus, a setting of 1.0 represents a <span class="quote">“<span class="quote">just in time</span>”</span> policy of writing exactly the number of buffers predicted to be needed. Larger values provide some cushion against spikes in demand, while smaller values intentionally leave writes to be done by server processes. The default is 2.0. This parameter can only be set in the <code class="filename">postgresql.conf</code> file or on the server command line.</p> </dd> <dt id="GUC-BGWRITER-FLUSH-AFTER"><span class="term"><code class="varname">bgwriter_flush_after</code> (<code class="type">integer</code>) <a id="id-1.6.6.7.6.3.4.1.3" class="indexterm" name="id-1.6.6.7.6.3.4.1.3"></a></span> <a href="#GUC-BGWRITER-FLUSH-AFTER" class="id_link">#</a></dt> <dd> <p>Whenever more than this amount of data has been written by the background writer, attempt to force the OS to issue these writes to the underlying storage. Doing so will limit the amount of dirty data in the kernel's page cache, reducing the likelihood of stalls when an <code class="function">fsync</code> is issued at the end of a checkpoint, or when the OS writes data back in larger batches in the background. Often that will result in greatly reduced transaction latency, but there also are some cases, especially with workloads that are bigger than <a class="xref" href="runtime-config-resource.html#GUC-SHARED-BUFFERS">shared_buffers</a>, but smaller than the OS's page cache, where performance might degrade. This setting may have no effect on some platforms. If this value is specified without units, it is taken as blocks, that is <code class="symbol">BLCKSZ</code> bytes, typically 8kB. The valid range is between <code class="literal">0</code>, which disables forced writeback, and <code class="literal">2MB</code>. The default is <code class="literal">512kB</code> on Linux, <code class="literal">0</code> elsewhere. (If <code class="symbol">BLCKSZ</code> is not 8kB, the default and maximum values scale proportionally to it.) This parameter can only be set in the <code class="filename">postgresql.conf</code> file or on the server command line.</p> </dd> </dl> </div> <p>Smaller values of <code class="varname">bgwriter_lru_maxpages</code> and <code class="varname">bgwriter_lru_multiplier</code> reduce the extra I/O load caused by the background writer, but make it more likely that server processes will have to issue writes for themselves, delaying interactive queries.</p> </div> <div class="sect2" id="RUNTIME-CONFIG-RESOURCE-ASYNC-BEHAVIOR"> <div class="titlepage"> <div> <div> <h3 class="title">19.4.6. Asynchronous Behavior <a href="#RUNTIME-CONFIG-RESOURCE-ASYNC-BEHAVIOR" class="id_link">#</a></h3> </div> </div> </div> <div class="variablelist"> <dl class="variablelist"> <dt id="GUC-BACKEND-FLUSH-AFTER"><span class="term"><code class="varname">backend_flush_after</code> (<code class="type">integer</code>) <a id="id-1.6.6.7.7.2.1.1.3" class="indexterm" name="id-1.6.6.7.7.2.1.1.3"></a></span> <a href="#GUC-BACKEND-FLUSH-AFTER" class="id_link">#</a></dt> <dd> <p>Whenever more than this amount of data has been written by a single backend, attempt to force the OS to issue these writes to the underlying storage. Doing so will limit the amount of dirty data in the kernel's page cache, reducing the likelihood of stalls when an <code class="function">fsync</code> is issued at the end of a checkpoint, or when the OS writes data back in larger batches in the background. Often that will result in greatly reduced transaction latency, but there also are some cases, especially with workloads that are bigger than <a class="xref" href="runtime-config-resource.html#GUC-SHARED-BUFFERS">shared_buffers</a>, but smaller than the OS's page cache, where performance might degrade. This setting may have no effect on some platforms. If this value is specified without units, it is taken as blocks, that is <code class="symbol">BLCKSZ</code> bytes, typically 8kB. The valid range is between <code class="literal">0</code>, which disables forced writeback, and <code class="literal">2MB</code>. The default is <code class="literal">0</code>, i.e., no forced writeback. (If <code class="symbol">BLCKSZ</code> is not 8kB, the maximum value scales proportionally to it.)</p> </dd> <dt id="GUC-EFFECTIVE-IO-CONCURRENCY"><span class="term"><code class="varname">effective_io_concurrency</code> (<code class="type">integer</code>) <a id="id-1.6.6.7.7.2.2.1.3" class="indexterm" name="id-1.6.6.7.7.2.2.1.3"></a></span> <a href="#GUC-EFFECTIVE-IO-CONCURRENCY" class="id_link">#</a></dt> <dd> <p>Sets the number of concurrent disk I/O operations that <span class="productname">PostgreSQL</span> expects can be executed simultaneously. Raising this value will increase the number of I/O operations that any individual <span class="productname">PostgreSQL</span> session attempts to initiate in parallel. The allowed range is 1 to 1000, or zero to disable issuance of asynchronous I/O requests. Currently, this setting only affects bitmap heap scans.</p> <p>For magnetic drives, a good starting point for this setting is the number of separate drives comprising a RAID 0 stripe or RAID 1 mirror being used for the database. (For RAID 5 the parity drive should not be counted.) However, if the database is often busy with multiple queries issued in concurrent sessions, lower values may be sufficient to keep the disk array busy. A value higher than needed to keep the disks busy will only result in extra CPU overhead. SSDs and other memory-based storage can often process many concurrent requests, so the best value might be in the hundreds.</p> <p>Asynchronous I/O depends on an effective <code class="function">posix_fadvise</code> function, which some operating systems lack. If the function is not present then setting this parameter to anything but zero will result in an error. On some operating systems (e.g., Solaris), the function is present but does not actually do anything.</p> <p>The default is 1 on supported systems, otherwise 0. This value can be overridden for tables in a particular tablespace by setting the tablespace parameter of the same name (see <a class="xref" href="sql-altertablespace.html" title="ALTER TABLESPACE"><span class="refentrytitle">ALTER TABLESPACE</span></a>).</p> </dd> <dt id="GUC-MAINTENANCE-IO-CONCURRENCY"><span class="term"><code class="varname">maintenance_io_concurrency</code> (<code class="type">integer</code>) <a id="id-1.6.6.7.7.2.3.1.3" class="indexterm" name="id-1.6.6.7.7.2.3.1.3"></a></span> <a href="#GUC-MAINTENANCE-IO-CONCURRENCY" class="id_link">#</a></dt> <dd> <p>Similar to <code class="varname">effective_io_concurrency</code>, but used for maintenance work that is done on behalf of many client sessions.</p> <p>The default is 10 on supported systems, otherwise 0. This value can be overridden for tables in a particular tablespace by setting the tablespace parameter of the same name (see <a class="xref" href="sql-altertablespace.html" title="ALTER TABLESPACE"><span class="refentrytitle">ALTER TABLESPACE</span></a>).</p> </dd> <dt id="GUC-IO-COMBINE-LIMIT"><span class="term"><code class="varname">io_combine_limit</code> (<code class="type">integer</code>) <a id="id-1.6.6.7.7.2.4.1.3" class="indexterm" name="id-1.6.6.7.7.2.4.1.3"></a></span> <a href="#GUC-IO-COMBINE-LIMIT" class="id_link">#</a></dt> <dd> <p>Controls the largest I/O size in operations that combine I/O. The default is 128kB.</p> </dd> <dt id="GUC-MAX-WORKER-PROCESSES"><span class="term"><code class="varname">max_worker_processes</code> (<code class="type">integer</code>) <a id="id-1.6.6.7.7.2.5.1.3" class="indexterm" name="id-1.6.6.7.7.2.5.1.3"></a></span> <a href="#GUC-MAX-WORKER-PROCESSES" class="id_link">#</a></dt> <dd> <p>Sets the maximum number of background processes that the cluster can support. This parameter can only be set at server start. The default is 8.</p> <p>When running a standby server, you must set this parameter to the same or higher value than on the primary server. Otherwise, queries will not be allowed in the standby server.</p> <p>When changing this value, consider also adjusting <a class="xref" href="runtime-config-resource.html#GUC-MAX-PARALLEL-WORKERS">max_parallel_workers</a>, <a class="xref" href="runtime-config-resource.html#GUC-MAX-PARALLEL-MAINTENANCE-WORKERS">max_parallel_maintenance_workers</a>, and <a class="xref" href="runtime-config-resource.html#GUC-MAX-PARALLEL-WORKERS-PER-GATHER">max_parallel_workers_per_gather</a>.</p> </dd> <dt id="GUC-MAX-PARALLEL-WORKERS-PER-GATHER"><span class="term"><code class="varname">max_parallel_workers_per_gather</code> (<code class="type">integer</code>) <a id="id-1.6.6.7.7.2.6.1.3" class="indexterm" name="id-1.6.6.7.7.2.6.1.3"></a></span> <a href="#GUC-MAX-PARALLEL-WORKERS-PER-GATHER" class="id_link">#</a></dt> <dd> <p>Sets the maximum number of workers that can be started by a single <code class="literal">Gather</code> or <code class="literal">Gather Merge</code> node. Parallel workers are taken from the pool of processes established by <a class="xref" href="runtime-config-resource.html#GUC-MAX-WORKER-PROCESSES">max_worker_processes</a>, limited by <a class="xref" href="runtime-config-resource.html#GUC-MAX-PARALLEL-WORKERS">max_parallel_workers</a>. Note that the requested number of workers may not actually be available at run time. If this occurs, the plan will run with fewer workers than expected, which may be inefficient. The default value is 2. Setting this value to 0 disables parallel query execution.</p> <p>Note that parallel queries may consume very substantially more resources than non-parallel queries, because each worker process is a completely separate process which has roughly the same impact on the system as an additional user session. This should be taken into account when choosing a value for this setting, as well as when configuring other settings that control resource utilization, such as <a class="xref" href="runtime-config-resource.html#GUC-WORK-MEM">work_mem</a>. Resource limits such as <code class="varname">work_mem</code> are applied individually to each worker, which means the total utilization may be much higher across all processes than it would normally be for any single process. For example, a parallel query using 4 workers may use up to 5 times as much CPU time, memory, I/O bandwidth, and so forth as a query which uses no workers at all.</p> <p>For more information on parallel query, see <a class="xref" href="parallel-query.html" title="Chapter 15. Parallel Query">Chapter 15</a>.</p> </dd> <dt id="GUC-MAX-PARALLEL-MAINTENANCE-WORKERS"><span class="term"><code class="varname">max_parallel_maintenance_workers</code> (<code class="type">integer</code>) <a id="id-1.6.6.7.7.2.7.1.3" class="indexterm" name="id-1.6.6.7.7.2.7.1.3"></a></span> <a href="#GUC-MAX-PARALLEL-MAINTENANCE-WORKERS" class="id_link">#</a></dt> <dd> <p>Sets the maximum number of parallel workers that can be started by a single utility command. Currently, the parallel utility commands that support the use of parallel workers are <code class="command">CREATE INDEX</code> only when building a B-tree index, and <code class="command">VACUUM</code> without <code class="literal">FULL</code> option. Parallel workers are taken from the pool of processes established by <a class="xref" href="runtime-config-resource.html#GUC-MAX-WORKER-PROCESSES">max_worker_processes</a>, limited by <a class="xref" href="runtime-config-resource.html#GUC-MAX-PARALLEL-WORKERS">max_parallel_workers</a>. Note that the requested number of workers may not actually be available at run time. If this occurs, the utility operation will run with fewer workers than expected. The default value is 2. Setting this value to 0 disables the use of parallel workers by utility commands.</p> <p>Note that parallel utility commands should not consume substantially more memory than equivalent non-parallel operations. This strategy differs from that of parallel query, where resource limits generally apply per worker process. Parallel utility commands treat the resource limit <code class="varname">maintenance_work_mem</code> as a limit to be applied to the entire utility command, regardless of the number of parallel worker processes. However, parallel utility commands may still consume substantially more CPU resources and I/O bandwidth.</p> </dd> <dt id="GUC-MAX-PARALLEL-WORKERS"><span class="term"><code class="varname">max_parallel_workers</code> (<code class="type">integer</code>) <a id="id-1.6.6.7.7.2.8.1.3" class="indexterm" name="id-1.6.6.7.7.2.8.1.3"></a></span> <a href="#GUC-MAX-PARALLEL-WORKERS" class="id_link">#</a></dt> <dd> <p>Sets the maximum number of workers that the cluster can support for parallel operations. The default value is 8. When increasing or decreasing this value, consider also adjusting <a class="xref" href="runtime-config-resource.html#GUC-MAX-PARALLEL-MAINTENANCE-WORKERS">max_parallel_maintenance_workers</a> and <a class="xref" href="runtime-config-resource.html#GUC-MAX-PARALLEL-WORKERS-PER-GATHER">max_parallel_workers_per_gather</a>. Also, note that a setting for this value which is higher than <a class="xref" href="runtime-config-resource.html#GUC-MAX-WORKER-PROCESSES">max_worker_processes</a> will have no effect, since parallel workers are taken from the pool of worker processes established by that setting.</p> </dd> <dt id="GUC-PARALLEL-LEADER-PARTICIPATION"><span class="term"><code class="varname">parallel_leader_participation</code> (<code class="type">boolean</code>) <a id="id-1.6.6.7.7.2.9.1.3" class="indexterm" name="id-1.6.6.7.7.2.9.1.3"></a></span> <a href="#GUC-PARALLEL-LEADER-PARTICIPATION" class="id_link">#</a></dt> <dd> <p>Allows the leader process to execute the query plan under <code class="literal">Gather</code> and <code class="literal">Gather Merge</code> nodes instead of waiting for worker processes. The default is <code class="literal">on</code>. Setting this value to <code class="literal">off</code> reduces the likelihood that workers will become blocked because the leader is not reading tuples fast enough, but requires the leader process to wait for worker processes to start up before the first tuples can be produced. The degree to which the leader can help or hinder performance depends on the plan type, number of workers and query duration.</p> </dd> </dl> </div> </div> </div> <div class="navfooter"> <hr /> <table width="100%" summary="Navigation footer"> <tr> <td width="40%" align="left"><a accesskey="p" href="runtime-config-connection.html" title="19.3. Connections and Authentication">Prev</a> </td> <td width="20%" align="center"><a accesskey="u" href="runtime-config.html" title="Chapter 19. Server Configuration">Up</a></td> <td width="40%" align="right"> <a accesskey="n" href="runtime-config-wal.html" title="19.5. Write Ahead Log">Next</a></td> </tr> <tr> <td width="40%" align="left" valign="top">19.3. Connections and Authentication </td> <td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 17.2 Documentation">Home</a></td> <td width="40%" align="right" valign="top"> 19.5. Write Ahead Log</td> </tr> </table> </div> </div> <div id="docComments"> <h2>Submit correction</h2> <p> If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use <a href="/account/comments/new/17/runtime-config-resource.html/" rel="nofollow">this form</a> to report a documentation issue. </p> </div> </div>  </div> </div>  <footer id="footer"> <div class="container"> <div class="row"> <div class="col-md-12"> <ul> <li><a href="https://twitter.com/postgresql"><img src="/media/img/atpostgresql.png" alt="@postgresql"></a></li> <li><a href="https://git.postgresql.org/gitweb/?p=postgresql.git"><img src="/media/img/git.png" alt="Git"></a></li> </ul> </div> </div> </div>  <div class="container"> <a href="/about/privacypolicy">Privacy Policy</a> | <a href="/about/policies/coc/">Code of Conduct</a> | <a href="/about/">About PostgreSQL</a> | <a href="/about/contact/">Contact</a><br/> <p>Copyright © 1996-2024 The PostgreSQL Global Development Group</p> </div> </footer> <script src="/media/js/jquery-3.4.1.slim.min.js"></script> <script src="/media/js/popper-1.16.0.min.js"></script> <script src="/media/js/bootstrap-4.4.1.min.js"></script> <script src="/media/js/main.js?18db9df6"></script> </body> </html>