codekingpro/portable-devtools
115k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>20.4. Resource Consumption</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="runtime-config-connection.html" title="20.3. Connections and Authentication" /><link rel="next" href="runtime-config-wal.html" title="20.5. Write Ahead Log" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">20.4. Resource Consumption</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="runtime-config-connection.html" title="20.3. Connections and Authentication">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="runtime-config.html" title="Chapter 20. Server Configuration">Up</a></td><th width="60%" align="center">Chapter 20. Server Configuration</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="runtime-config-wal.html" title="20.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">20.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">20.4.1. Memory</a></span></dt><dt><span class="sect2"><a href="runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-DISK">20.4.2. Disk</a></span></dt><dt><span class="sect2"><a href="runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-KERNEL">20.4.3. Kernel Resource Usage</a></span></dt><dt><span class="sect2"><a href="runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST">20.4.4. Cost-based Vacuum Delay</a></span></dt><dt><span class="sect2"><a href="runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-BACKGROUND-WRITER">20.4.5. Background Writer</a></span></dt><dt><span class="sect2"><a href="runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-ASYNC-BEHAVIOR">20.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">20.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>)3 <a id="id-1.6.7.7.2.2.1.1.3" class="indexterm"></a>4 </span> <a href="#GUC-SHARED-BUFFERS" class="id_link">#</a></dt><dd><p>5 Sets the amount of memory the database server uses for shared6 memory buffers. The default is typically 128 megabytes7 (<code class="literal">128MB</code>), but might be less if your kernel settings will8 not support it (as determined during <span class="application">initdb</span>).9 This setting must be at least 128 kilobytes. However,10 settings significantly higher than the minimum are usually needed11 for good performance.12 If this value is specified without units, it is taken as blocks,13 that is <code class="symbol">BLCKSZ</code> bytes, typically 8kB.14 (Non-default values of <code class="symbol">BLCKSZ</code> change the minimum15 value.)16 This parameter can only be set at server start.17 </p><p>18 If you have a dedicated database server with 1GB or more of RAM, a19 reasonable starting value for <code class="varname">shared_buffers</code> is 25%20 of the memory in your system. There are some workloads where even21 larger settings for <code class="varname">shared_buffers</code> are effective, but22 because <span class="productname">PostgreSQL</span> also relies on the23 operating system cache, it is unlikely that an allocation of more than24 40% of RAM to <code class="varname">shared_buffers</code> will work better than a25 smaller amount. Larger settings for <code class="varname">shared_buffers</code>26 usually require a corresponding increase in27 <code class="varname">max_wal_size</code>, in order to spread out the28 process of writing large quantities of new or changed data over a29 longer period of time.30 </p><p>31 On systems with less than 1GB of RAM, a smaller percentage of RAM is32 appropriate, so as to leave adequate space for the operating system.33 </p></dd><dt id="GUC-HUGE-PAGES"><span class="term"><code class="varname">huge_pages</code> (<code class="type">enum</code>)34 <a id="id-1.6.7.7.2.2.2.1.3" class="indexterm"></a>35 </span> <a href="#GUC-HUGE-PAGES" class="id_link">#</a></dt><dd><p>36 Controls whether huge pages are requested for the main shared memory37 area. Valid values are <code class="literal">try</code> (the default),38 <code class="literal">on</code>, and <code class="literal">off</code>. With39 <code class="varname">huge_pages</code> set to <code class="literal">try</code>, the40 server will try to request huge pages, but fall back to the default if41 that fails. With <code class="literal">on</code>, failure to request huge pages42 will prevent the server from starting up. With <code class="literal">off</code>,43 huge pages will not be requested.44 </p><p>45 At present, this setting is supported only on Linux and Windows. The46 setting is ignored on other systems when set to47 <code class="literal">try</code>. On Linux, it is only supported when48 <code class="varname">shared_memory_type</code> is set to <code class="literal">mmap</code>49 (the default).50 </p><p>51 The use of huge pages results in smaller page tables and less CPU time52 spent on memory management, increasing performance. For more details about53 using huge pages on Linux, see <a class="xref" href="kernel-resources.html#LINUX-HUGE-PAGES" title="19.4.5. Linux Huge Pages">Section 19.4.5</a>.54 </p><p>55 Huge pages are known as large pages on Windows. To use them, you need to56 assign the user right <span class="quote">“<span class="quote">Lock pages in memory</span>”</span> to the Windows user account57 that runs <span class="productname">PostgreSQL</span>.58 You can use Windows Group Policy tool (gpedit.msc) to assign the user right59 <span class="quote">“<span class="quote">Lock pages in memory</span>”</span>.60 To start the database server on the command prompt as a standalone process,61 not as a Windows service, the command prompt must be run as an administrator or62 User Access Control (UAC) must be disabled. When the UAC is enabled, the normal63 command prompt revokes the user right <span class="quote">“<span class="quote">Lock pages in memory</span>”</span> when started.64 </p><p>65 Note that this setting only affects the main shared memory area.66 Operating systems such as Linux, FreeBSD, and Illumos can also use67 huge pages (also known as <span class="quote">“<span class="quote">super</span>”</span> pages or68 <span class="quote">“<span class="quote">large</span>”</span> pages) automatically for normal memory69 allocation, without an explicit request from70 <span class="productname">PostgreSQL</span>. On Linux, this is called71 <span class="quote">“<span class="quote">transparent huge pages</span>”</span><a id="id-1.6.7.7.2.2.2.2.5.5" class="indexterm"></a> (THP). That feature has been known to72 cause performance degradation with73 <span class="productname">PostgreSQL</span> for some users on some Linux74 versions, so its use is currently discouraged (unlike explicit use of75 <code class="varname">huge_pages</code>).76 </p></dd><dt id="GUC-HUGE-PAGE-SIZE"><span class="term"><code class="varname">huge_page_size</code> (<code class="type">integer</code>)77 <a id="id-1.6.7.7.2.2.3.1.3" class="indexterm"></a>78 </span> <a href="#GUC-HUGE-PAGE-SIZE" class="id_link">#</a></dt><dd><p>79 Controls the size of huge pages, when they are enabled with80 <a class="xref" href="runtime-config-resource.html#GUC-HUGE-PAGES">huge_pages</a>.81 The default is zero (<code class="literal">0</code>).82 When set to <code class="literal">0</code>, the default huge page size on the83 system will be used. This parameter can only be set at server start.84 </p><p>85 Some commonly available page sizes on modern 64 bit server architectures include:86 <code class="literal">2MB</code> and <code class="literal">1GB</code> (Intel and AMD), <code class="literal">16MB</code> and87 <code class="literal">16GB</code> (IBM POWER), and <code class="literal">64kB</code>, <code class="literal">2MB</code>,88 <code class="literal">32MB</code> and <code class="literal">1GB</code> (ARM). For more information89 about usage and support, see <a class="xref" href="kernel-resources.html#LINUX-HUGE-PAGES" title="19.4.5. Linux Huge Pages">Section 19.4.5</a>.90 </p><p>91 Non-default settings are currently supported only on Linux.92 </p></dd><dt id="GUC-TEMP-BUFFERS"><span class="term"><code class="varname">temp_buffers</code> (<code class="type">integer</code>)93 <a id="id-1.6.7.7.2.2.4.1.3" class="indexterm"></a>94 </span> <a href="#GUC-TEMP-BUFFERS" class="id_link">#</a></dt><dd><p>95 Sets the maximum amount of memory used for temporary buffers within96 each database session. These are session-local buffers used only97 for access to temporary tables.98 If this value is specified without units, it is taken as blocks,99 that is <code class="symbol">BLCKSZ</code> bytes, typically 8kB.100 The default is eight megabytes (<code class="literal">8MB</code>).101 (If <code class="symbol">BLCKSZ</code> is not 8kB, the default value scales102 proportionally to it.)103 This setting can be changed within individual104 sessions, but only before the first use of temporary tables105 within the session; subsequent attempts to change the value will106 have no effect on that session.107 </p><p>108 A session will allocate temporary buffers as needed up to the limit109 given by <code class="varname">temp_buffers</code>. The cost of setting a large110 value in sessions that do not actually need many temporary111 buffers is only a buffer descriptor, or about 64 bytes, per112 increment in <code class="varname">temp_buffers</code>. However if a buffer is113 actually used an additional 8192 bytes will be consumed for it114 (or in general, <code class="symbol">BLCKSZ</code> bytes).115 </p></dd><dt id="GUC-MAX-PREPARED-TRANSACTIONS"><span class="term"><code class="varname">max_prepared_transactions</code> (<code class="type">integer</code>)116 <a id="id-1.6.7.7.2.2.5.1.3" class="indexterm"></a>117 </span> <a href="#GUC-MAX-PREPARED-TRANSACTIONS" class="id_link">#</a></dt><dd><p>118 Sets the maximum number of transactions that can be in the119 <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>).120 Setting this parameter to zero (which is the default)121 disables the prepared-transaction feature.122 This parameter can only be set at server start.123 </p><p>124 If you are not planning to use prepared transactions, this parameter125 should be set to zero to prevent accidental creation of prepared126 transactions. If you are using prepared transactions, you will127 probably want <code class="varname">max_prepared_transactions</code> to be at128 least as large as <a class="xref" href="runtime-config-connection.html#GUC-MAX-CONNECTIONS">max_connections</a>, so that every129 session can have a prepared transaction pending.130 </p><p>131 When running a standby server, you must set this parameter to the132 same or higher value than on the primary server. Otherwise, queries133 will not be allowed in the standby server.134 </p></dd><dt id="GUC-WORK-MEM"><span class="term"><code class="varname">work_mem</code> (<code class="type">integer</code>)135 <a id="id-1.6.7.7.2.2.6.1.3" class="indexterm"></a>136 </span> <a href="#GUC-WORK-MEM" class="id_link">#</a></dt><dd><p>137 Sets the base maximum amount of memory to be used by a query operation138 (such as a sort or hash table) before writing to temporary disk files.139 If this value is specified without units, it is taken as kilobytes.140 The default value is four megabytes (<code class="literal">4MB</code>).141 Note that a complex query might perform several sort and hash142 operations at the same time, with each operation generally being143 allowed to use as much memory as this value specifies before144 it starts145 to write data into temporary files. Also, several running146 sessions could be doing such operations concurrently.147 Therefore, the total memory used could be many times the value148 of <code class="varname">work_mem</code>; it is necessary to keep this149 fact in mind when choosing the value. Sort operations are used150 for <code class="literal">ORDER BY</code>, <code class="literal">DISTINCT</code>,151 and merge joins.152 Hash tables are used in hash joins, hash-based aggregation, memoize153 nodes and hash-based processing of <code class="literal">IN</code> subqueries.154 </p><p>155 Hash-based operations are generally more sensitive to memory156 availability than equivalent sort-based operations. The157 memory limit for a hash table is computed by multiplying158 <code class="varname">work_mem</code> by159 <code class="varname">hash_mem_multiplier</code>. This makes it160 possible for hash-based operations to use an amount of memory161 that exceeds the usual <code class="varname">work_mem</code> base162 amount.163 </p></dd><dt id="GUC-HASH-MEM-MULTIPLIER"><span class="term"><code class="varname">hash_mem_multiplier</code> (<code class="type">floating point</code>)164 <a id="id-1.6.7.7.2.2.7.1.3" class="indexterm"></a>165 </span> <a href="#GUC-HASH-MEM-MULTIPLIER" class="id_link">#</a></dt><dd><p>166 Used to compute the maximum amount of memory that hash-based167 operations can use. The final limit is determined by168 multiplying <code class="varname">work_mem</code> by169 <code class="varname">hash_mem_multiplier</code>. The default value is170 2.0, which makes hash-based operations use twice the usual171 <code class="varname">work_mem</code> base amount.172 </p><p>173 Consider increasing <code class="varname">hash_mem_multiplier</code> in174 environments where spilling by query operations is a regular175 occurrence, especially when simply increasing176 <code class="varname">work_mem</code> results in memory pressure (memory177 pressure typically takes the form of intermittent out of178 memory errors). The default setting of 2.0 is often effective with179 mixed workloads. Higher settings in the range of 2.0 - 8.0 or180 more may be effective in environments where181 <code class="varname">work_mem</code> has already been increased to 40MB182 or more.183 </p></dd><dt id="GUC-MAINTENANCE-WORK-MEM"><span class="term"><code class="varname">maintenance_work_mem</code> (<code class="type">integer</code>)184 <a id="id-1.6.7.7.2.2.8.1.3" class="indexterm"></a>185 </span> <a href="#GUC-MAINTENANCE-WORK-MEM" class="id_link">#</a></dt><dd><p>186 Specifies the maximum amount of memory to be used by maintenance187 operations, such as <code class="command">VACUUM</code>, <code class="command">CREATE188 INDEX</code>, and <code class="command">ALTER TABLE ADD FOREIGN KEY</code>.189 If this value is specified without units, it is taken as kilobytes.190 It defaults191 to 64 megabytes (<code class="literal">64MB</code>). Since only one of these192 operations can be executed at a time by a database session, and193 an installation normally doesn't have many of them running194 concurrently, it's safe to set this value significantly larger195 than <code class="varname">work_mem</code>. Larger settings might improve196 performance for vacuuming and for restoring database dumps.197 </p><p>198 Note that when autovacuum runs, up to199 <a class="xref" href="runtime-config-autovacuum.html#GUC-AUTOVACUUM-MAX-WORKERS">autovacuum_max_workers</a> times this memory200 may be allocated, so be careful not to set the default value201 too high. It may be useful to control for this by separately202 setting <a class="xref" href="runtime-config-resource.html#GUC-AUTOVACUUM-WORK-MEM">autovacuum_work_mem</a>.203 </p><p>204 Note that for the collection of dead tuple identifiers,205 <code class="command">VACUUM</code> is only able to utilize up to a maximum of206 <code class="literal">1GB</code> of memory.207 </p></dd><dt id="GUC-AUTOVACUUM-WORK-MEM"><span class="term"><code class="varname">autovacuum_work_mem</code> (<code class="type">integer</code>)208 <a id="id-1.6.7.7.2.2.9.1.3" class="indexterm"></a>209 </span> <a href="#GUC-AUTOVACUUM-WORK-MEM" class="id_link">#</a></dt><dd><p>210 Specifies the maximum amount of memory to be used by each211 autovacuum worker process.212 If this value is specified without units, it is taken as kilobytes.213 It defaults to -1, indicating that214 the value of <a class="xref" href="runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM">maintenance_work_mem</a> should215 be used instead. The setting has no effect on the behavior of216 <code class="command">VACUUM</code> when run in other contexts.217 This parameter can only be set in the218 <code class="filename">postgresql.conf</code> file or on the server command219 line.220 </p><p>221 For the collection of dead tuple identifiers, autovacuum is only able222 to utilize up to a maximum of <code class="literal">1GB</code> of memory, so223 setting <code class="varname">autovacuum_work_mem</code> to a value higher than224 that has no effect on the number of dead tuples that autovacuum can225 collect while scanning a table.226 </p></dd><dt id="GUC-VACUUM-BUFFER-USAGE-LIMIT"><span class="term">227 <code class="varname">vacuum_buffer_usage_limit</code> (<code class="type">integer</code>)228 <a id="id-1.6.7.7.2.2.10.1.3" class="indexterm"></a>229 </span> <a href="#GUC-VACUUM-BUFFER-USAGE-LIMIT" class="id_link">#</a></dt><dd><p>230 Specifies the size of the231 <a class="glossterm" href="glossary.html#GLOSSARY-BUFFER-ACCESS-STRATEGY"><em class="glossterm"><a class="glossterm" href="glossary.html#GLOSSARY-BUFFER-ACCESS-STRATEGY" title="Buffer Access Strategy">Buffer Access Strategy</a></em></a>232 used by the <code class="command">VACUUM</code> and <code class="command">ANALYZE</code>233 commands. A setting of <code class="literal">0</code> will allow the operation234 to use any number of <code class="varname">shared_buffers</code>. Otherwise235 valid sizes range from <code class="literal">128 kB</code> to236 <code class="literal">16 GB</code>. If the specified size would exceed 1/8 the237 size of <code class="varname">shared_buffers</code>, the size is silently capped238 to that value. The default value is <code class="literal">256 kB</code>. If239 this value is specified without units, it is taken as kilobytes. This240 parameter can be set at any time. It can be overridden for241 <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>242 when passing the <code class="option">BUFFER_USAGE_LIMIT</code> option. Higher243 settings can allow <code class="command">VACUUM</code> and244 <code class="command">ANALYZE</code> to run more quickly, but having too large a245 setting may cause too many other useful pages to be evicted from246 shared buffers.247 </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>)248 <a id="id-1.6.7.7.2.2.11.1.3" class="indexterm"></a>249 </span> <a href="#GUC-LOGICAL-DECODING-WORK-MEM" class="id_link">#</a></dt><dd><p>250 Specifies the maximum amount of memory to be used by logical decoding,251 before some of the decoded changes are written to local disk. This252 limits the amount of memory used by logical streaming replication253 connections. It defaults to 64 megabytes (<code class="literal">64MB</code>).254 Since each replication connection only uses a single buffer of this size,255 and an installation normally doesn't have many such connections256 concurrently (as limited by <code class="varname">max_wal_senders</code>), it's257 safe to set this value significantly higher than <code class="varname">work_mem</code>,258 reducing the amount of decoded changes written to disk.259 </p></dd><dt id="GUC-MAX-STACK-DEPTH"><span class="term"><code class="varname">max_stack_depth</code> (<code class="type">integer</code>)260 <a id="id-1.6.7.7.2.2.12.1.3" class="indexterm"></a>261 </span> <a href="#GUC-MAX-STACK-DEPTH" class="id_link">#</a></dt><dd><p>262 Specifies the maximum safe depth of the server's execution stack.263 The ideal setting for this parameter is the actual stack size limit264 enforced by the kernel (as set by <code class="literal">ulimit -s</code> or local265 equivalent), less a safety margin of a megabyte or so. The safety266 margin is needed because the stack depth is not checked in every267 routine in the server, but only in key potentially-recursive routines.268 If this value is specified without units, it is taken as kilobytes.269 The default setting is two megabytes (<code class="literal">2MB</code>), which270 is conservatively small and unlikely to risk crashes. However,271 it might be too small to allow execution of complex functions.272 Only superusers and users with the appropriate <code class="literal">SET</code>273 privilege can change this setting.274 </p><p>275 Setting <code class="varname">max_stack_depth</code> higher than276 the actual kernel limit will mean that a runaway recursive function277 can crash an individual backend process. On platforms where278 <span class="productname">PostgreSQL</span> can determine the kernel limit,279 the server will not allow this variable to be set to an unsafe280 value. However, not all platforms provide the information,281 so caution is recommended in selecting a value.282 </p></dd><dt id="GUC-SHARED-MEMORY-TYPE"><span class="term"><code class="varname">shared_memory_type</code> (<code class="type">enum</code>)283 <a id="id-1.6.7.7.2.2.13.1.3" class="indexterm"></a>284 </span> <a href="#GUC-SHARED-MEMORY-TYPE" class="id_link">#</a></dt><dd><p>285 Specifies the shared memory implementation that the server286 should use for the main shared memory region that holds287 <span class="productname">PostgreSQL</span>'s shared buffers and other288 shared data. Possible values are <code class="literal">mmap</code> (for289 anonymous shared memory allocated using <code class="function">mmap</code>),290 <code class="literal">sysv</code> (for System V shared memory allocated via291 <code class="function">shmget</code>) and <code class="literal">windows</code> (for Windows292 shared memory). Not all values are supported on all platforms; the293 first supported option is the default for that platform. The use of294 the <code class="literal">sysv</code> option, which is not the default on any295 platform, is generally discouraged because it typically requires296 non-default kernel settings to allow for large allocations (see <a class="xref" href="kernel-resources.html#SYSVIPC" title="19.4.1. Shared Memory and Semaphores">Section 19.4.1</a>).297 </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>)298 <a id="id-1.6.7.7.2.2.14.1.3" class="indexterm"></a>299 </span> <a href="#GUC-DYNAMIC-SHARED-MEMORY-TYPE" class="id_link">#</a></dt><dd><p>300 Specifies the dynamic shared memory implementation that the server301 should use. Possible values are <code class="literal">posix</code> (for POSIX shared302 memory allocated using <code class="literal">shm_open</code>), <code class="literal">sysv</code>303 (for System V shared memory allocated via <code class="literal">shmget</code>),304 <code class="literal">windows</code> (for Windows shared memory),305 and <code class="literal">mmap</code> (to simulate shared memory using306 memory-mapped files stored in the data directory).307 Not all values are supported on all platforms; the first supported308 option is usually the default for that platform. The use of the309 <code class="literal">mmap</code> option, which is not the default on any platform,310 is generally discouraged because the operating system may write311 modified pages back to disk repeatedly, increasing system I/O load;312 however, it may be useful for debugging, when the313 <code class="literal">pg_dynshmem</code> directory is stored on a RAM disk, or when314 other shared memory facilities are not available.315 </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>)316 <a id="id-1.6.7.7.2.2.15.1.3" class="indexterm"></a>317 </span> <a href="#GUC-MIN-DYNAMIC-SHARED-MEMORY" class="id_link">#</a></dt><dd><p>318 Specifies the amount of memory that should be allocated at server319 startup for use by parallel queries. When this memory region is320 insufficient or exhausted by concurrent queries, new parallel queries321 try to allocate extra shared memory temporarily from the operating322 system using the method configured with323 <code class="varname">dynamic_shared_memory_type</code>, which may be slower due324 to memory management overheads. Memory that is allocated at startup325 with <code class="varname">min_dynamic_shared_memory</code> is affected by326 the <code class="varname">huge_pages</code> setting on operating systems where327 that is supported, and may be more likely to benefit from larger pages328 on operating systems where that is managed automatically.329 The default value is <code class="literal">0</code> (none). This parameter can330 only be set at server start.331 </p></dd></dl></div></div><div class="sect2" id="RUNTIME-CONFIG-RESOURCE-DISK"><div class="titlepage"><div><div><h3 class="title">20.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>)332 <a id="id-1.6.7.7.3.2.1.1.3" class="indexterm"></a>333 </span> <a href="#GUC-TEMP-FILE-LIMIT" class="id_link">#</a></dt><dd><p>334 Specifies the maximum amount of disk space that a process can use335 for temporary files, such as sort and hash temporary files, or the336 storage file for a held cursor. A transaction attempting to exceed337 this limit will be canceled.338 If this value is specified without units, it is taken as kilobytes.339 <code class="literal">-1</code> (the default) means no limit.340 Only superusers and users with the appropriate <code class="literal">SET</code>341 privilege can change this setting.342 </p><p>343 This setting constrains the total space used at any instant by all344 temporary files used by a given <span class="productname">PostgreSQL</span> process.345 It should be noted that disk space used for explicit temporary346 tables, as opposed to temporary files used behind-the-scenes in query347 execution, does <span class="emphasis"><em>not</em></span> count against this limit.348 </p></dd></dl></div></div><div class="sect2" id="RUNTIME-CONFIG-RESOURCE-KERNEL"><div class="titlepage"><div><div><h3 class="title">20.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>)349 <a id="id-1.6.7.7.4.2.1.1.3" class="indexterm"></a>350 </span> <a href="#GUC-MAX-FILES-PER-PROCESS" class="id_link">#</a></dt><dd><p>351 Sets the maximum number of simultaneously open files allowed to each352 server subprocess. The default is one thousand files. If the kernel is enforcing353 a safe per-process limit, you don't need to worry about this setting.354 But on some platforms (notably, most BSD systems), the kernel will355 allow individual processes to open many more files than the system356 can actually support if many processes all try to open357 that many files. If you find yourself seeing <span class="quote">“<span class="quote">Too many open358 files</span>”</span> failures, try reducing this setting.359 This parameter can only be set at server start.360 </p></dd></dl></div></div><div class="sect2" id="RUNTIME-CONFIG-RESOURCE-VACUUM-COST"><div class="titlepage"><div><div><h3 class="title">20.4.4. Cost-based Vacuum Delay <a href="#RUNTIME-CONFIG-RESOURCE-VACUUM-COST" class="id_link">#</a></h3></div></div></div><p>361 During the execution of <a class="xref" href="sql-vacuum.html" title="VACUUM"><span class="refentrytitle">VACUUM</span></a>362 and <a class="xref" href="sql-analyze.html" title="ANALYZE"><span class="refentrytitle">ANALYZE</span></a>363 commands, the system maintains an364 internal counter that keeps track of the estimated cost of the365 various I/O operations that are performed. When the accumulated366 cost reaches a limit (specified by367 <code class="varname">vacuum_cost_limit</code>), the process performing368 the operation will sleep for a short period of time, as specified by369 <code class="varname">vacuum_cost_delay</code>. Then it will reset the370 counter and continue execution.371 </p><p>372 The intent of this feature is to allow administrators to reduce373 the I/O impact of these commands on concurrent database374 activity. There are many situations where it is not375 important that maintenance commands like376 <code class="command">VACUUM</code> and <code class="command">ANALYZE</code> finish377 quickly; however, it is usually very important that these378 commands do not significantly interfere with the ability of the379 system to perform other database operations. Cost-based vacuum380 delay provides a way for administrators to achieve this.381 </p><p>382 This feature is disabled by default for manually issued383 <code class="command">VACUUM</code> commands. To enable it, set the384 <code class="varname">vacuum_cost_delay</code> variable to a nonzero385 value.386 </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>)387 <a id="id-1.6.7.7.5.5.1.1.3" class="indexterm"></a>388 </span> <a href="#GUC-VACUUM-COST-DELAY" class="id_link">#</a></dt><dd><p>389 The amount of time that the process will sleep390 when the cost limit has been exceeded.391 If this value is specified without units, it is taken as milliseconds.392 The default value is zero, which disables the cost-based vacuum393 delay feature. Positive values enable cost-based vacuuming.394 </p><p>395 When using cost-based vacuuming, appropriate values for396 <code class="varname">vacuum_cost_delay</code> are usually quite small, perhaps397 less than 1 millisecond. While <code class="varname">vacuum_cost_delay</code>398 can be set to fractional-millisecond values, such delays may not be399 measured accurately on older platforms. On such platforms,400 increasing <code class="command">VACUUM</code>'s throttled resource consumption401 above what you get at 1ms will require changing the other vacuum cost402 parameters. You should, nonetheless,403 keep <code class="varname">vacuum_cost_delay</code> as small as your platform404 will consistently measure; large delays are not helpful.405 </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>)406 <a id="id-1.6.7.7.5.5.2.1.3" class="indexterm"></a>407 </span> <a href="#GUC-VACUUM-COST-PAGE-HIT" class="id_link">#</a></dt><dd><p>408 The estimated cost for vacuuming a buffer found in the shared buffer409 cache. It represents the cost to lock the buffer pool, lookup410 the shared hash table and scan the content of the page. The411 default value is one.412 </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>)413 <a id="id-1.6.7.7.5.5.3.1.3" class="indexterm"></a>414 </span> <a href="#GUC-VACUUM-COST-PAGE-MISS" class="id_link">#</a></dt><dd><p>415 The estimated cost for vacuuming a buffer that has to be read from416 disk. This represents the effort to lock the buffer pool,417 lookup the shared hash table, read the desired block in from418 the disk and scan its content. The default value is 2.419 </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>)420 <a id="id-1.6.7.7.5.5.4.1.3" class="indexterm"></a>421 </span> <a href="#GUC-VACUUM-COST-PAGE-DIRTY" class="id_link">#</a></dt><dd><p>422 The estimated cost charged when vacuum modifies a block that was423 previously clean. It represents the extra I/O required to424 flush the dirty block out to disk again. The default value is425 20.426 </p></dd><dt id="GUC-VACUUM-COST-LIMIT"><span class="term"><code class="varname">vacuum_cost_limit</code> (<code class="type">integer</code>)427 <a id="id-1.6.7.7.5.5.5.1.3" class="indexterm"></a>428 </span> <a href="#GUC-VACUUM-COST-LIMIT" class="id_link">#</a></dt><dd><p>429 The accumulated cost that will cause the vacuuming process to sleep.430 The default value is 200.431 </p></dd></dl></div><div class="note"><h3 class="title">Note</h3><p>432 There are certain operations that hold critical locks and should433 therefore complete as quickly as possible. Cost-based vacuum434 delays do not occur during such operations. Therefore it is435 possible that the cost accumulates far higher than the specified436 limit. To avoid uselessly long delays in such cases, the actual437 delay is calculated as <code class="varname">vacuum_cost_delay</code> *438 <code class="varname">accumulated_balance</code> /439 <code class="varname">vacuum_cost_limit</code> with a maximum of440 <code class="varname">vacuum_cost_delay</code> * 4.441 </p></div></div><div class="sect2" id="RUNTIME-CONFIG-RESOURCE-BACKGROUND-WRITER"><div class="titlepage"><div><div><h3 class="title">20.4.5. Background Writer <a href="#RUNTIME-CONFIG-RESOURCE-BACKGROUND-WRITER" class="id_link">#</a></h3></div></div></div><p>442 There is a separate server443 process called the <em class="firstterm">background writer</em>, whose function444 is to issue writes of <span class="quote">“<span class="quote">dirty</span>”</span> (new or modified) shared445 buffers. When the number of clean shared buffers appears to be446 insufficient, the background writer writes some dirty buffers to the447 file system and marks them as clean. This reduces the likelihood448 that server processes handling user queries will be unable to find449 clean buffers and have to write dirty buffers themselves.450 However, the background writer does cause a net overall451 increase in I/O load, because while a repeatedly-dirtied page might452 otherwise be written only once per checkpoint interval, the453 background writer might write it several times as it is dirtied454 in the same interval. The parameters discussed in this subsection455 can be used to tune the behavior for local needs.456 </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>)457 <a id="id-1.6.7.7.6.3.1.1.3" class="indexterm"></a>458 </span> <a href="#GUC-BGWRITER-DELAY" class="id_link">#</a></dt><dd><p>459 Specifies the delay between activity rounds for the460 background writer. In each round the writer issues writes461 for some number of dirty buffers (controllable by the462 following parameters). It then sleeps for463 the length of <code class="varname">bgwriter_delay</code>, and repeats.464 When there are no dirty buffers in the465 buffer pool, though, it goes into a longer sleep regardless of466 <code class="varname">bgwriter_delay</code>.467 If this value is specified without units, it is taken as milliseconds.468 The default value is 200469 milliseconds (<code class="literal">200ms</code>). Note that on many systems, the470 effective resolution of sleep delays is 10 milliseconds; setting471 <code class="varname">bgwriter_delay</code> to a value that is not a multiple of 10472 might have the same results as setting it to the next higher multiple473 of 10. This parameter can only be set in the474 <code class="filename">postgresql.conf</code> file or on the server command line.475 </p></dd><dt id="GUC-BGWRITER-LRU-MAXPAGES"><span class="term"><code class="varname">bgwriter_lru_maxpages</code> (<code class="type">integer</code>)476 <a id="id-1.6.7.7.6.3.2.1.3" class="indexterm"></a>477 </span> <a href="#GUC-BGWRITER-LRU-MAXPAGES" class="id_link">#</a></dt><dd><p>478 In each round, no more than this many buffers will be written479 by the background writer. Setting this to zero disables480 background writing. (Note that checkpoints, which are managed by481 a separate, dedicated auxiliary process, are unaffected.)482 The default value is 100 buffers.483 This parameter can only be set in the <code class="filename">postgresql.conf</code>484 file or on the server command line.485 </p></dd><dt id="GUC-BGWRITER-LRU-MULTIPLIER"><span class="term"><code class="varname">bgwriter_lru_multiplier</code> (<code class="type">floating point</code>)486 <a id="id-1.6.7.7.6.3.3.1.3" class="indexterm"></a>487 </span> <a href="#GUC-BGWRITER-LRU-MULTIPLIER" class="id_link">#</a></dt><dd><p>488 The number of dirty buffers written in each round is based on the489 number of new buffers that have been needed by server processes490 during recent rounds. The average recent need is multiplied by491 <code class="varname">bgwriter_lru_multiplier</code> to arrive at an estimate of the492 number of buffers that will be needed during the next round. Dirty493 buffers are written until there are that many clean, reusable buffers494 available. (However, no more than <code class="varname">bgwriter_lru_maxpages</code>495 buffers will be written per round.)496 Thus, a setting of 1.0 represents a <span class="quote">“<span class="quote">just in time</span>”</span> policy497 of writing exactly the number of buffers predicted to be needed.498 Larger values provide some cushion against spikes in demand,499 while smaller values intentionally leave writes to be done by500 server processes.501 The default is 2.0.502 This parameter can only be set in the <code class="filename">postgresql.conf</code>503 file or on the server command line.504 </p></dd><dt id="GUC-BGWRITER-FLUSH-AFTER"><span class="term"><code class="varname">bgwriter_flush_after</code> (<code class="type">integer</code>)505 <a id="id-1.6.7.7.6.3.4.1.3" class="indexterm"></a>506 </span> <a href="#GUC-BGWRITER-FLUSH-AFTER" class="id_link">#</a></dt><dd><p>507 Whenever more than this amount of data has508 been written by the background writer, attempt to force the OS to issue these509 writes to the underlying storage. Doing so will limit the amount of510 dirty data in the kernel's page cache, reducing the likelihood of511 stalls when an <code class="function">fsync</code> is issued at the end of a checkpoint, or when512 the OS writes data back in larger batches in the background. Often513 that will result in greatly reduced transaction latency, but there514 also are some cases, especially with workloads that are bigger than515 <a class="xref" href="runtime-config-resource.html#GUC-SHARED-BUFFERS">shared_buffers</a>, but smaller than the OS's page516 cache, where performance might degrade. This setting may have no517 effect on some platforms.518 If this value is specified without units, it is taken as blocks,519 that is <code class="symbol">BLCKSZ</code> bytes, typically 8kB.520 The valid range is between521 <code class="literal">0</code>, which disables forced writeback, and522 <code class="literal">2MB</code>. The default is <code class="literal">512kB</code> on Linux,523 <code class="literal">0</code> elsewhere. (If <code class="symbol">BLCKSZ</code> is not 8kB,524 the default and maximum values scale proportionally to it.)525 This parameter can only be set in the <code class="filename">postgresql.conf</code>526 file or on the server command line.527 </p></dd></dl></div><p>528 Smaller values of <code class="varname">bgwriter_lru_maxpages</code> and529 <code class="varname">bgwriter_lru_multiplier</code> reduce the extra I/O load530 caused by the background writer, but make it more likely that server531 processes will have to issue writes for themselves, delaying interactive532 queries.533 </p></div><div class="sect2" id="RUNTIME-CONFIG-RESOURCE-ASYNC-BEHAVIOR"><div class="titlepage"><div><div><h3 class="title">20.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>)534 <a id="id-1.6.7.7.7.2.1.1.3" class="indexterm"></a>535 </span> <a href="#GUC-BACKEND-FLUSH-AFTER" class="id_link">#</a></dt><dd><p>536 Whenever more than this amount of data has537 been written by a single backend, attempt to force the OS to issue538 these writes to the underlying storage. Doing so will limit the539 amount of dirty data in the kernel's page cache, reducing the540 likelihood of stalls when an <code class="function">fsync</code> is issued at the end of a541 checkpoint, or when the OS writes data back in larger batches in the542 background. Often that will result in greatly reduced transaction543 latency, but there also are some cases, especially with workloads544 that are bigger than <a class="xref" href="runtime-config-resource.html#GUC-SHARED-BUFFERS">shared_buffers</a>, but smaller545 than the OS's page cache, where performance might degrade. This546 setting may have no effect on some platforms.547 If this value is specified without units, it is taken as blocks,548 that is <code class="symbol">BLCKSZ</code> bytes, typically 8kB.549 The valid range is550 between <code class="literal">0</code>, which disables forced writeback,551 and <code class="literal">2MB</code>. The default is <code class="literal">0</code>, i.e., no552 forced writeback. (If <code class="symbol">BLCKSZ</code> is not 8kB,553 the maximum value scales proportionally to it.)554 </p></dd><dt id="GUC-EFFECTIVE-IO-CONCURRENCY"><span class="term"><code class="varname">effective_io_concurrency</code> (<code class="type">integer</code>)555 <a id="id-1.6.7.7.7.2.2.1.3" class="indexterm"></a>556 </span> <a href="#GUC-EFFECTIVE-IO-CONCURRENCY" class="id_link">#</a></dt><dd><p>557 Sets the number of concurrent disk I/O operations that558 <span class="productname">PostgreSQL</span> expects can be executed559 simultaneously. Raising this value will increase the number of I/O560 operations that any individual <span class="productname">PostgreSQL</span> session561 attempts to initiate in parallel. The allowed range is 1 to 1000,562 or zero to disable issuance of asynchronous I/O requests. Currently,563 this setting only affects bitmap heap scans.564 </p><p>565 For magnetic drives, a good starting point for this setting is the566 number of separate567 drives comprising a RAID 0 stripe or RAID 1 mirror being used for the568 database. (For RAID 5 the parity drive should not be counted.)569 However, if the database is often busy with multiple queries issued in570 concurrent sessions, lower values may be sufficient to keep the disk571 array busy. A value higher than needed to keep the disks busy will572 only result in extra CPU overhead.573 SSDs and other memory-based storage can often process many574 concurrent requests, so the best value might be in the hundreds.575 </p><p>576 Asynchronous I/O depends on an effective <code class="function">posix_fadvise</code>577 function, which some operating systems lack. If the function is not578 present then setting this parameter to anything but zero will result579 in an error. On some operating systems (e.g., Solaris), the function580 is present but does not actually do anything.581 </p><p>582 The default is 1 on supported systems, otherwise 0. This value can583 be overridden for tables in a particular tablespace by setting the584 tablespace parameter of the same name (see585 <a class="xref" href="sql-altertablespace.html" title="ALTER TABLESPACE"><span class="refentrytitle">ALTER TABLESPACE</span></a>).586 </p></dd><dt id="GUC-MAINTENANCE-IO-CONCURRENCY"><span class="term"><code class="varname">maintenance_io_concurrency</code> (<code class="type">integer</code>)587 <a id="id-1.6.7.7.7.2.3.1.3" class="indexterm"></a>588 </span> <a href="#GUC-MAINTENANCE-IO-CONCURRENCY" class="id_link">#</a></dt><dd><p>589 Similar to <code class="varname">effective_io_concurrency</code>, but used590 for maintenance work that is done on behalf of many client sessions.591 </p><p>592 The default is 10 on supported systems, otherwise 0. This value can593 be overridden for tables in a particular tablespace by setting the594 tablespace parameter of the same name (see595 <a class="xref" href="sql-altertablespace.html" title="ALTER TABLESPACE"><span class="refentrytitle">ALTER TABLESPACE</span></a>).596 </p></dd><dt id="GUC-MAX-WORKER-PROCESSES"><span class="term"><code class="varname">max_worker_processes</code> (<code class="type">integer</code>)597 <a id="id-1.6.7.7.7.2.4.1.3" class="indexterm"></a>598 </span> <a href="#GUC-MAX-WORKER-PROCESSES" class="id_link">#</a></dt><dd><p>599 Sets the maximum number of background processes that the system600 can support. This parameter can only be set at server start. The601 default is 8.602 </p><p>603 When running a standby server, you must set this parameter to the604 same or higher value than on the primary server. Otherwise, queries605 will not be allowed in the standby server.606 </p><p>607 When changing this value, consider also adjusting608 <a class="xref" href="runtime-config-resource.html#GUC-MAX-PARALLEL-WORKERS">max_parallel_workers</a>,609 <a class="xref" href="runtime-config-resource.html#GUC-MAX-PARALLEL-MAINTENANCE-WORKERS">max_parallel_maintenance_workers</a>, and610 <a class="xref" href="runtime-config-resource.html#GUC-MAX-PARALLEL-WORKERS-PER-GATHER">max_parallel_workers_per_gather</a>.611 </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>)612 <a id="id-1.6.7.7.7.2.5.1.3" class="indexterm"></a>613 </span> <a href="#GUC-MAX-PARALLEL-WORKERS-PER-GATHER" class="id_link">#</a></dt><dd><p>614 Sets the maximum number of workers that can be started by a single615 <code class="literal">Gather</code> or <code class="literal">Gather Merge</code> node.616 Parallel workers are taken from the pool of processes established by617 <a class="xref" href="runtime-config-resource.html#GUC-MAX-WORKER-PROCESSES">max_worker_processes</a>, limited by618 <a class="xref" href="runtime-config-resource.html#GUC-MAX-PARALLEL-WORKERS">max_parallel_workers</a>. Note that the requested619 number of workers may not actually be available at run time. If this620 occurs, the plan will run with fewer workers than expected, which may621 be inefficient. The default value is 2. Setting this value to 0622 disables parallel query execution.623 </p><p>624 Note that parallel queries may consume very substantially more625 resources than non-parallel queries, because each worker process is626 a completely separate process which has roughly the same impact on the627 system as an additional user session. This should be taken into628 account when choosing a value for this setting, as well as when629 configuring other settings that control resource utilization, such630 as <a class="xref" href="runtime-config-resource.html#GUC-WORK-MEM">work_mem</a>. Resource limits such as631 <code class="varname">work_mem</code> are applied individually to each worker,632 which means the total utilization may be much higher across all633 processes than it would normally be for any single process.634 For example, a parallel query using 4 workers may use up to 5 times635 as much CPU time, memory, I/O bandwidth, and so forth as a query which636 uses no workers at all.637 </p><p>638 For more information on parallel query, see639 <a class="xref" href="parallel-query.html" title="Chapter 15. Parallel Query">Chapter 15</a>.640 </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>)641 <a id="id-1.6.7.7.7.2.6.1.3" class="indexterm"></a>642 </span> <a href="#GUC-MAX-PARALLEL-MAINTENANCE-WORKERS" class="id_link">#</a></dt><dd><p>643 Sets the maximum number of parallel workers that can be644 started by a single utility command. Currently, the parallel645 utility commands that support the use of parallel workers are646 <code class="command">CREATE INDEX</code> only when building a B-tree index,647 and <code class="command">VACUUM</code> without <code class="literal">FULL</code>648 option. Parallel workers are taken from the pool of processes649 established by <a class="xref" href="runtime-config-resource.html#GUC-MAX-WORKER-PROCESSES">max_worker_processes</a>, limited650 by <a class="xref" href="runtime-config-resource.html#GUC-MAX-PARALLEL-WORKERS">max_parallel_workers</a>. Note that the requested651 number of workers may not actually be available at run time.652 If this occurs, the utility operation will run with fewer653 workers than expected. The default value is 2. Setting this654 value to 0 disables the use of parallel workers by utility655 commands.656 </p><p>657 Note that parallel utility commands should not consume658 substantially more memory than equivalent non-parallel659 operations. This strategy differs from that of parallel660 query, where resource limits generally apply per worker661 process. Parallel utility commands treat the resource limit662 <code class="varname">maintenance_work_mem</code> as a limit to be applied to663 the entire utility command, regardless of the number of664 parallel worker processes. However, parallel utility665 commands may still consume substantially more CPU resources666 and I/O bandwidth.667 </p></dd><dt id="GUC-MAX-PARALLEL-WORKERS"><span class="term"><code class="varname">max_parallel_workers</code> (<code class="type">integer</code>)668 <a id="id-1.6.7.7.7.2.7.1.3" class="indexterm"></a>669 </span> <a href="#GUC-MAX-PARALLEL-WORKERS" class="id_link">#</a></dt><dd><p>670 Sets the maximum number of workers that the system can support for671 parallel operations. The default value is 8. When increasing or672 decreasing this value, consider also adjusting673 <a class="xref" href="runtime-config-resource.html#GUC-MAX-PARALLEL-MAINTENANCE-WORKERS">max_parallel_maintenance_workers</a> and674 <a class="xref" href="runtime-config-resource.html#GUC-MAX-PARALLEL-WORKERS-PER-GATHER">max_parallel_workers_per_gather</a>.675 Also, note that a setting for this value which is higher than676 <a class="xref" href="runtime-config-resource.html#GUC-MAX-WORKER-PROCESSES">max_worker_processes</a> will have no effect,677 since parallel workers are taken from the pool of worker processes678 established by that setting.679 </p></dd><dt id="GUC-PARALLEL-LEADER-PARTICIPATION"><span class="term">680 <code class="varname">parallel_leader_participation</code> (<code class="type">boolean</code>)681 <a id="id-1.6.7.7.7.2.8.1.3" class="indexterm"></a>682 </span> <a href="#GUC-PARALLEL-LEADER-PARTICIPATION" class="id_link">#</a></dt><dd><p>683 Allows the leader process to execute the query plan under684 <code class="literal">Gather</code> and <code class="literal">Gather Merge</code> nodes685 instead of waiting for worker processes. The default is686 <code class="literal">on</code>. Setting this value to <code class="literal">off</code>687 reduces the likelihood that workers will become blocked because the688 leader is not reading tuples fast enough, but requires the leader689 process to wait for worker processes to start up before the first690 tuples can be produced. The degree to which the leader can help or691 hinder performance depends on the plan type, number of workers and692 query duration.693 </p></dd><dt id="GUC-OLD-SNAPSHOT-THRESHOLD"><span class="term"><code class="varname">old_snapshot_threshold</code> (<code class="type">integer</code>)694 <a id="id-1.6.7.7.7.2.9.1.3" class="indexterm"></a>695 </span> <a href="#GUC-OLD-SNAPSHOT-THRESHOLD" class="id_link">#</a></dt><dd><p>696 Sets the minimum amount of time that a query snapshot can be used697 without risk of a <span class="quote">“<span class="quote">snapshot too old</span>”</span> error occurring698 when using the snapshot. Data that has been dead for longer than699 this threshold is allowed to be vacuumed away. This can help700 prevent bloat in the face of snapshots which remain in use for a701 long time. To prevent incorrect results due to cleanup of data which702 would otherwise be visible to the snapshot, an error is generated703 when the snapshot is older than this threshold and the snapshot is704 used to read a page which has been modified since the snapshot was705 built.706 </p><p>707 If this value is specified without units, it is taken as minutes.708 A value of <code class="literal">-1</code> (the default) disables this feature,709 effectively setting the snapshot age limit to infinity.710 This parameter can only be set at server start.711 </p><p>712 Useful values for production work probably range from a small number713 of hours to a few days. Small values (such as <code class="literal">0</code> or714 <code class="literal">1min</code>) are only allowed because they may sometimes be715 useful for testing. While a setting as high as <code class="literal">60d</code> is716 allowed, please note that in many workloads extreme bloat or717 transaction ID wraparound may occur in much shorter time frames.718 </p><p>719 When this feature is enabled, freed space at the end of a relation720 cannot be released to the operating system, since that could remove721 information needed to detect the <span class="quote">“<span class="quote">snapshot too old</span>”</span>722 condition. All space allocated to a relation remains associated with723 that relation for reuse only within that relation unless explicitly724 freed (for example, with <code class="command">VACUUM FULL</code>).725 </p><p>726 This setting does not attempt to guarantee that an error will be727 generated under any particular circumstances. In fact, if the728 correct results can be generated from (for example) a cursor which729 has materialized a result set, no error will be generated even if the730 underlying rows in the referenced table have been vacuumed away.731 Some tables cannot safely be vacuumed early, and so will not be732 affected by this setting, such as system catalogs. For such tables733 this setting will neither reduce bloat nor create a possibility734 of a <span class="quote">“<span class="quote">snapshot too old</span>”</span> error on scanning.735 </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="20.3. Connections and Authentication">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="runtime-config.html" title="Chapter 20. Server Configuration">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="runtime-config-wal.html" title="20.5. Write Ahead Log">Next</a></td></tr><tr><td width="40%" align="left" valign="top">20.3. Connections and Authentication </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 20.5. Write Ahead Log</td></tr></table></div></body></html>