Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
runtime-config-resource.html735 linesDownload Raw Back to html
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>
codekingpro/portable-devtools · Team Ai