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>19.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="19.3. Connections and Authentication" /><link rel="next" href="runtime-config-wal.html" title="19.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">19.4. Resource Consumption</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="runtime-config-connection.html" title="19.3. Connections and Authentication">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="runtime-config.html" title="Chapter 19. Server Configuration">Up</a></td><th width="60%" align="center">Chapter 19. Server Configuration</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="runtime-config-wal.html" title="19.5. Write Ahead Log">Next</a></td></tr></table><hr /></div><div class="sect1" id="RUNTIME-CONFIG-RESOURCE"><div class="titlepage"><div><div><h2 class="title" style="clear: both">19.4. Resource Consumption <a href="#RUNTIME-CONFIG-RESOURCE" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-MEMORY">19.4.1. Memory</a></span></dt><dt><span class="sect2"><a href="runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-DISK">19.4.2. Disk</a></span></dt><dt><span class="sect2"><a href="runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-KERNEL">19.4.3. Kernel Resource Usage</a></span></dt><dt><span class="sect2"><a href="runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-BACKGROUND-WRITER">19.4.4. Background Writer</a></span></dt><dt><span class="sect2"><a href="runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-IO">19.4.5. I/O</a></span></dt><dt><span class="sect2"><a href="runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-WORKER-PROCESSES">19.4.6. Worker Processes</a></span></dt></dl></div><div class="sect2" id="RUNTIME-CONFIG-RESOURCE-MEMORY"><div class="titlepage"><div><div><h3 class="title">19.4.1. Memory <a href="#RUNTIME-CONFIG-RESOURCE-MEMORY" class="id_link">#</a></h3></div></div></div><div class="variablelist"><dl class="variablelist"><dt id="GUC-SHARED-BUFFERS"><span class="term"><code class="varname">shared_buffers</code> (<code class="type">integer</code>)
3 <a id="id-1.6.6.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 shared
6 memory buffers. The default is typically 128 megabytes
7 (<code class="literal">128MB</code>), but might be less if your kernel settings will
8 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 needed
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 minimum
16 This parameter can only be set at server start.
18 If you have a dedicated database server with 1GB or more of RAM, a
19 reasonable starting value for <code class="varname">shared_buffers</code> is 25%
20 of the memory in your system. There are some workloads where even
21 larger settings for <code class="varname">shared_buffers</code> are effective, but
22 because <span class="productname">PostgreSQL</span> also relies on the
23 operating system cache, it is unlikely that an allocation of more than
24 40% of RAM to <code class="varname">shared_buffers</code> will work better than a
25 smaller amount. Larger settings for <code class="varname">shared_buffers</code>
26 usually require a corresponding increase in
27 <code class="varname">max_wal_size</code>, in order to spread out the
28 process of writing large quantities of new or changed data over a
29 longer period of time.
31 On systems with less than 1GB of RAM, a smaller percentage of RAM is
32 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.6.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 memory
37 area. Valid values are <code class="literal">try</code> (the default),
38 <code class="literal">on</code>, and <code class="literal">off</code>. With
39 <code class="varname">huge_pages</code> set to <code class="literal">try</code>, the
40 server will try to request huge pages, but fall back to the default if
41 that fails. With <code class="literal">on</code>, failure to request huge pages
42 will prevent the server from starting up. With <code class="literal">off</code>,
43 huge pages will not be requested. The actual state of huge pages is
44 indicated by the server variable
45 <a class="xref" href="runtime-config-preset.html#GUC-HUGE-PAGES-STATUS">huge_pages_status</a>.
47 At present, this setting is supported only on Linux and Windows. The
48 setting is ignored on other systems when set to
49 <code class="literal">try</code>. On Linux, it is only supported when
50 <code class="varname">shared_memory_type</code> is set to <code class="literal">mmap</code>
53 The use of huge pages results in smaller page tables and less CPU time
54 spent on memory management, increasing performance. For more details about
55 using huge pages on Linux, see <a class="xref" href="kernel-resources.html#LINUX-HUGE-PAGES" title="18.4.5. Linux Huge Pages">Section 18.4.5</a>.
57 Huge pages are known as large pages on Windows. To use them, you need to
58 assign the user right <span class="quote">“<span class="quote">Lock pages in memory</span>”</span> to the Windows user account
59 that runs <span class="productname">PostgreSQL</span>.
60 You can use Windows Group Policy tool (gpedit.msc) to assign the user right
61 <span class="quote">“<span class="quote">Lock pages in memory</span>”</span>.
62 To start the database server on the command prompt as a standalone process,
63 not as a Windows service, the command prompt must be run as an administrator or
64 User Access Control (UAC) must be disabled. When the UAC is enabled, the normal
65 command prompt revokes the user right <span class="quote">“<span class="quote">Lock pages in memory</span>”</span> when started.
67 Note that this setting only affects the main shared memory area.
68 Operating systems such as Linux, FreeBSD, and Illumos can also use
69 huge pages (also known as <span class="quote">“<span class="quote">super</span>”</span> pages or
70 <span class="quote">“<span class="quote">large</span>”</span> pages) automatically for normal memory
71 allocation, without an explicit request from
72 <span class="productname">PostgreSQL</span>. On Linux, this is called
73 <span class="quote">“<span class="quote">transparent huge pages</span>”</span><a id="id-1.6.6.7.2.2.2.2.5.5" class="indexterm"></a> (THP). That feature has been known to
74 cause performance degradation with
75 <span class="productname">PostgreSQL</span> for some users on some Linux
76 versions, so its use is currently discouraged (unlike explicit use of
77 <code class="varname">huge_pages</code>).
78 </p></dd><dt id="GUC-HUGE-PAGE-SIZE"><span class="term"><code class="varname">huge_page_size</code> (<code class="type">integer</code>)
79 <a id="id-1.6.6.7.2.2.3.1.3" class="indexterm"></a>
80 </span> <a href="#GUC-HUGE-PAGE-SIZE" class="id_link">#</a></dt><dd><p>
81 Controls the size of huge pages, when they are enabled with
82 <a class="xref" href="runtime-config-resource.html#GUC-HUGE-PAGES">huge_pages</a>.
83 The default is zero (<code class="literal">0</code>).
84 When set to <code class="literal">0</code>, the default huge page size on the
85 system will be used. This parameter can only be set at server start.
87 Some commonly available page sizes on modern 64 bit server architectures include:
88 <code class="literal">2MB</code> and <code class="literal">1GB</code> (Intel and AMD), <code class="literal">16MB</code> and
89 <code class="literal">16GB</code> (IBM POWER), and <code class="literal">64kB</code>, <code class="literal">2MB</code>,
90 <code class="literal">32MB</code> and <code class="literal">1GB</code> (ARM). For more information
91 about usage and support, see <a class="xref" href="kernel-resources.html#LINUX-HUGE-PAGES" title="18.4.5. Linux Huge Pages">Section 18.4.5</a>.
93 Non-default settings are currently supported only on Linux.
94 </p></dd><dt id="GUC-TEMP-BUFFERS"><span class="term"><code class="varname">temp_buffers</code> (<code class="type">integer</code>)
95 <a id="id-1.6.6.7.2.2.4.1.3" class="indexterm"></a>
96 </span> <a href="#GUC-TEMP-BUFFERS" class="id_link">#</a></dt><dd><p>
97 Sets the maximum amount of memory used for temporary buffers within
98 each database session. These are session-local buffers used only
99 for access to temporary tables.
100 If this value is specified without units, it is taken as blocks,
101 that is <code class="symbol">BLCKSZ</code> bytes, typically 8kB.
102 The default is eight megabytes (<code class="literal">8MB</code>).
103 (If <code class="symbol">BLCKSZ</code> is not 8kB, the default value scales
104 proportionally to it.)
105 This setting can be changed within individual
106 sessions, but only before the first use of temporary tables
107 within the session; subsequent attempts to change the value will
108 have no effect on that session.
110 A session will allocate temporary buffers as needed up to the limit
111 given by <code class="varname">temp_buffers</code>. The cost of setting a large
112 value in sessions that do not actually need many temporary
113 buffers is only a buffer descriptor, or about 64 bytes, per
114 increment in <code class="varname">temp_buffers</code>. However if a buffer is
115 actually used an additional 8192 bytes will be consumed for it
116 (or in general, <code class="symbol">BLCKSZ</code> bytes).
117 </p></dd><dt id="GUC-MAX-PREPARED-TRANSACTIONS"><span class="term"><code class="varname">max_prepared_transactions</code> (<code class="type">integer</code>)
118 <a id="id-1.6.6.7.2.2.5.1.3" class="indexterm"></a>
119 </span> <a href="#GUC-MAX-PREPARED-TRANSACTIONS" class="id_link">#</a></dt><dd><p>
120 Sets the maximum number of transactions that can be in the
121 <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>).
122 Setting this parameter to zero (which is the default)
123 disables the prepared-transaction feature.
124 This parameter can only be set at server start.
126 If you are not planning to use prepared transactions, this parameter
127 should be set to zero to prevent accidental creation of prepared
128 transactions. If you are using prepared transactions, you will
129 probably want <code class="varname">max_prepared_transactions</code> to be at
130 least as large as <a class="xref" href="runtime-config-connection.html#GUC-MAX-CONNECTIONS">max_connections</a>, so that every
131 session can have a prepared transaction pending.
133 When running a standby server, you must set this parameter to the
134 same or higher value than on the primary server. Otherwise, queries
135 will not be allowed in the standby server.
136 </p></dd><dt id="GUC-WORK-MEM"><span class="term"><code class="varname">work_mem</code> (<code class="type">integer</code>)
137 <a id="id-1.6.6.7.2.2.6.1.3" class="indexterm"></a>
138 </span> <a href="#GUC-WORK-MEM" class="id_link">#</a></dt><dd><p>
139 Sets the base maximum amount of memory to be used by a query operation
140 (such as a sort or hash table) before writing to temporary disk files.
141 If this value is specified without units, it is taken as kilobytes.
142 The default value is four megabytes (<code class="literal">4MB</code>).
143 Note that a complex query might perform several sort and hash
144 operations at the same time, with each operation generally being
145 allowed to use as much memory as this value specifies before
147 to write data into temporary files. Also, several running
148 sessions could be doing such operations concurrently.
149 Therefore, the total memory used could be many times the value
150 of <code class="varname">work_mem</code>; it is necessary to keep this
151 fact in mind when choosing the value. Sort operations are used
152 for <code class="literal">ORDER BY</code>, <code class="literal">DISTINCT</code>,
154 Hash tables are used in hash joins, hash-based aggregation, memoize
155 nodes and hash-based processing of <code class="literal">IN</code> subqueries.
157 Hash-based operations are generally more sensitive to memory
158 availability than equivalent sort-based operations. The
159 memory limit for a hash table is computed by multiplying
160 <code class="varname">work_mem</code> by
161 <code class="varname">hash_mem_multiplier</code>. This makes it
162 possible for hash-based operations to use an amount of memory
163 that exceeds the usual <code class="varname">work_mem</code> base
165 </p></dd><dt id="GUC-HASH-MEM-MULTIPLIER"><span class="term"><code class="varname">hash_mem_multiplier</code> (<code class="type">floating point</code>)
166 <a id="id-1.6.6.7.2.2.7.1.3" class="indexterm"></a>
167 </span> <a href="#GUC-HASH-MEM-MULTIPLIER" class="id_link">#</a></dt><dd><p>
168 Used to compute the maximum amount of memory that hash-based
169 operations can use. The final limit is determined by
170 multiplying <code class="varname">work_mem</code> by
171 <code class="varname">hash_mem_multiplier</code>. The default value is
172 2.0, which makes hash-based operations use twice the usual
173 <code class="varname">work_mem</code> base amount.
175 Consider increasing <code class="varname">hash_mem_multiplier</code> in
176 environments where spilling by query operations is a regular
177 occurrence, especially when simply increasing
178 <code class="varname">work_mem</code> results in memory pressure (memory
179 pressure typically takes the form of intermittent out of
180 memory errors). The default setting of 2.0 is often effective with
181 mixed workloads. Higher settings in the range of 2.0 - 8.0 or
182 more may be effective in environments where
183 <code class="varname">work_mem</code> has already been increased to 40MB
185 </p></dd><dt id="GUC-MAINTENANCE-WORK-MEM"><span class="term"><code class="varname">maintenance_work_mem</code> (<code class="type">integer</code>)
186 <a id="id-1.6.6.7.2.2.8.1.3" class="indexterm"></a>
187 </span> <a href="#GUC-MAINTENANCE-WORK-MEM" class="id_link">#</a></dt><dd><p>
188 Specifies the maximum amount of memory to be used by maintenance
189 operations, such as <code class="command">VACUUM</code>, <code class="command">CREATE
190 INDEX</code>, and <code class="command">ALTER TABLE ADD FOREIGN KEY</code>.
191 If this value is specified without units, it is taken as kilobytes.
193 to 64 megabytes (<code class="literal">64MB</code>). Since only one of these
194 operations can be executed at a time by a database session, and
195 an installation normally doesn't have many of them running
196 concurrently, it's safe to set this value significantly larger
197 than <code class="varname">work_mem</code>. Larger settings might improve
198 performance for vacuuming and for restoring database dumps.
200 Note that when autovacuum runs, up to
201 <a class="xref" href="runtime-config-vacuum.html#GUC-AUTOVACUUM-MAX-WORKERS">autovacuum_max_workers</a> times this memory
202 may be allocated, so be careful not to set the default value
203 too high. It may be useful to control for this by separately
204 setting <a class="xref" href="runtime-config-resource.html#GUC-AUTOVACUUM-WORK-MEM">autovacuum_work_mem</a>.
205 </p></dd><dt id="GUC-AUTOVACUUM-WORK-MEM"><span class="term"><code class="varname">autovacuum_work_mem</code> (<code class="type">integer</code>)
206 <a id="id-1.6.6.7.2.2.9.1.3" class="indexterm"></a>
207 </span> <a href="#GUC-AUTOVACUUM-WORK-MEM" class="id_link">#</a></dt><dd><p>
208 Specifies the maximum amount of memory to be used by each
209 autovacuum worker process.
210 If this value is specified without units, it is taken as kilobytes.
211 It defaults to -1, indicating that
212 the value of <a class="xref" href="runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM">maintenance_work_mem</a> should
213 be used instead. The setting has no effect on the behavior of
214 <code class="command">VACUUM</code> when run in other contexts.
215 This parameter can only be set in the
216 <code class="filename">postgresql.conf</code> file or on the server command
218 </p></dd><dt id="GUC-VACUUM-BUFFER-USAGE-LIMIT"><span class="term">
219 <code class="varname">vacuum_buffer_usage_limit</code> (<code class="type">integer</code>)
220 <a id="id-1.6.6.7.2.2.10.1.3" class="indexterm"></a>
221 </span> <a href="#GUC-VACUUM-BUFFER-USAGE-LIMIT" class="id_link">#</a></dt><dd><p>
222 Specifies the size of the
223 <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>
224 used by the <code class="command">VACUUM</code> and <code class="command">ANALYZE</code>
225 commands. A setting of <code class="literal">0</code> will allow the operation
226 to use any number of <code class="varname">shared_buffers</code>. Otherwise
227 valid sizes range from <code class="literal">128 kB</code> to
228 <code class="literal">16 GB</code>. If the specified size would exceed 1/8 the
229 size of <code class="varname">shared_buffers</code>, the size is silently capped
230 to that value. The default value is <code class="literal">2MB</code>. If
231 this value is specified without units, it is taken as kilobytes. This
232 parameter can be set at any time. It can be overridden for
233 <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>
234 when passing the <code class="option">BUFFER_USAGE_LIMIT</code> option. Higher
235 settings can allow <code class="command">VACUUM</code> and
236 <code class="command">ANALYZE</code> to run more quickly, but having too large a
237 setting may cause too many other useful pages to be evicted from
239 </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>)
240 <a id="id-1.6.6.7.2.2.11.1.3" class="indexterm"></a>
241 </span> <a href="#GUC-LOGICAL-DECODING-WORK-MEM" class="id_link">#</a></dt><dd><p>
242 Specifies the maximum amount of memory to be used by logical decoding,
243 before some of the decoded changes are written to local disk. This
244 limits the amount of memory used by logical streaming replication
245 connections. It defaults to 64 megabytes (<code class="literal">64MB</code>).
246 Since each replication connection only uses a single buffer of this size,
247 and an installation normally doesn't have many such connections
248 concurrently (as limited by <code class="varname">max_wal_senders</code>), it's
249 safe to set this value significantly higher than <code class="varname">work_mem</code>,
250 reducing the amount of decoded changes written to disk.
251 </p></dd><dt id="GUC-COMMIT-TIMESTAMP-BUFFERS"><span class="term"><code class="varname">commit_timestamp_buffers</code> (<code class="type">integer</code>)
252 <a id="id-1.6.6.7.2.2.12.1.3" class="indexterm"></a>
253 </span> <a href="#GUC-COMMIT-TIMESTAMP-BUFFERS" class="id_link">#</a></dt><dd><p>
254 Specifies the amount of memory to use to cache the contents of
255 <code class="literal">pg_commit_ts</code> (see
256 <a class="xref" href="storage-file-layout.html#PGDATA-CONTENTS-TABLE" title="Table 66.1. Contents of PGDATA">Table 66.1</a>).
257 If this value is specified without units, it is taken as blocks,
258 that is <code class="symbol">BLCKSZ</code> bytes, typically 8kB.
259 The default value is <code class="literal">0</code>, which requests
260 <code class="varname">shared_buffers</code>/512 up to 1024 blocks,
261 but not fewer than 16 blocks.
262 This parameter can only be set at server start.
263 </p></dd><dt id="GUC-MULTIXACT-MEMBER-BUFFERS"><span class="term"><code class="varname">multixact_member_buffers</code> (<code class="type">integer</code>)
264 <a id="id-1.6.6.7.2.2.13.1.3" class="indexterm"></a>
265 </span> <a href="#GUC-MULTIXACT-MEMBER-BUFFERS" class="id_link">#</a></dt><dd><p>
266 Specifies the amount of shared memory to use to cache the contents
267 of <code class="literal">pg_multixact/members</code> (see
268 <a class="xref" href="storage-file-layout.html#PGDATA-CONTENTS-TABLE" title="Table 66.1. Contents of PGDATA">Table 66.1</a>).
269 If this value is specified without units, it is taken as blocks,
270 that is <code class="symbol">BLCKSZ</code> bytes, typically 8kB.
271 The default value is <code class="literal">32</code>.
272 This parameter can only be set at server start.
273 </p></dd><dt id="GUC-MULTIXACT-OFFSET-BUFFERS"><span class="term"><code class="varname">multixact_offset_buffers</code> (<code class="type">integer</code>)
274 <a id="id-1.6.6.7.2.2.14.1.3" class="indexterm"></a>
275 </span> <a href="#GUC-MULTIXACT-OFFSET-BUFFERS" class="id_link">#</a></dt><dd><p>
276 Specifies the amount of shared memory to use to cache the contents
277 of <code class="literal">pg_multixact/offsets</code> (see
278 <a class="xref" href="storage-file-layout.html#PGDATA-CONTENTS-TABLE" title="Table 66.1. Contents of PGDATA">Table 66.1</a>).
279 If this value is specified without units, it is taken as blocks,
280 that is <code class="symbol">BLCKSZ</code> bytes, typically 8kB.
281 The default value is <code class="literal">16</code>.
282 This parameter can only be set at server start.
283 </p></dd><dt id="GUC-NOTIFY-BUFFERS"><span class="term"><code class="varname">notify_buffers</code> (<code class="type">integer</code>)
284 <a id="id-1.6.6.7.2.2.15.1.3" class="indexterm"></a>
285 </span> <a href="#GUC-NOTIFY-BUFFERS" class="id_link">#</a></dt><dd><p>
286 Specifies the amount of shared memory to use to cache the contents
287 of <code class="literal">pg_notify</code> (see
288 <a class="xref" href="storage-file-layout.html#PGDATA-CONTENTS-TABLE" title="Table 66.1. Contents of PGDATA">Table 66.1</a>).
289 If this value is specified without units, it is taken as blocks,
290 that is <code class="symbol">BLCKSZ</code> bytes, typically 8kB.
291 The default value is <code class="literal">16</code>.
292 This parameter can only be set at server start.
293 </p></dd><dt id="GUC-SERIALIZABLE-BUFFERS"><span class="term"><code class="varname">serializable_buffers</code> (<code class="type">integer</code>)
294 <a id="id-1.6.6.7.2.2.16.1.3" class="indexterm"></a>
295 </span> <a href="#GUC-SERIALIZABLE-BUFFERS" class="id_link">#</a></dt><dd><p>
296 Specifies the amount of shared memory to use to cache the contents
297 of <code class="literal">pg_serial</code> (see
298 <a class="xref" href="storage-file-layout.html#PGDATA-CONTENTS-TABLE" title="Table 66.1. Contents of PGDATA">Table 66.1</a>).
299 If this value is specified without units, it is taken as blocks,
300 that is <code class="symbol">BLCKSZ</code> bytes, typically 8kB.
301 The default value is <code class="literal">32</code>.
302 This parameter can only be set at server start.
303 </p></dd><dt id="GUC-SUBTRANSACTION-BUFFERS"><span class="term"><code class="varname">subtransaction_buffers</code> (<code class="type">integer</code>)
304 <a id="id-1.6.6.7.2.2.17.1.3" class="indexterm"></a>
305 </span> <a href="#GUC-SUBTRANSACTION-BUFFERS" class="id_link">#</a></dt><dd><p>
306 Specifies the amount of shared memory to use to cache the contents
307 of <code class="literal">pg_subtrans</code> (see
308 <a class="xref" href="storage-file-layout.html#PGDATA-CONTENTS-TABLE" title="Table 66.1. Contents of PGDATA">Table 66.1</a>).
309 If this value is specified without units, it is taken as blocks,
310 that is <code class="symbol">BLCKSZ</code> bytes, typically 8kB.
311 The default value is <code class="literal">0</code>, which requests
312 <code class="varname">shared_buffers</code>/512 up to 1024 blocks,
313 but not fewer than 16 blocks.
314 This parameter can only be set at server start.
315 </p></dd><dt id="GUC-TRANSACTION-BUFFERS"><span class="term"><code class="varname">transaction_buffers</code> (<code class="type">integer</code>)
316 <a id="id-1.6.6.7.2.2.18.1.3" class="indexterm"></a>
317 </span> <a href="#GUC-TRANSACTION-BUFFERS" class="id_link">#</a></dt><dd><p>
318 Specifies the amount of shared memory to use to cache the contents
319 of <code class="literal">pg_xact</code> (see
320 <a class="xref" href="storage-file-layout.html#PGDATA-CONTENTS-TABLE" title="Table 66.1. Contents of PGDATA">Table 66.1</a>).
321 If this value is specified without units, it is taken as blocks,
322 that is <code class="symbol">BLCKSZ</code> bytes, typically 8kB.
323 The default value is <code class="literal">0</code>, which requests
324 <code class="varname">shared_buffers</code>/512 up to 1024 blocks,
325 but not fewer than 16 blocks.
326 This parameter can only be set at server start.
327 </p></dd><dt id="GUC-MAX-STACK-DEPTH"><span class="term"><code class="varname">max_stack_depth</code> (<code class="type">integer</code>)
328 <a id="id-1.6.6.7.2.2.19.1.3" class="indexterm"></a>
329 </span> <a href="#GUC-MAX-STACK-DEPTH" class="id_link">#</a></dt><dd><p>
330 Specifies the maximum safe depth of the server's execution stack.
331 The ideal setting for this parameter is the actual stack size limit
332 enforced by the kernel (as set by <code class="literal">ulimit -s</code> or local
333 equivalent), less a safety margin of a megabyte or so. The safety
334 margin is needed because the stack depth is not checked in every
335 routine in the server, but only in key potentially-recursive routines.
336 If this value is specified without units, it is taken as kilobytes.
337 The default setting is two megabytes (<code class="literal">2MB</code>), which
338 is conservatively small and unlikely to risk crashes. However,
339 it might be too small to allow execution of complex functions.
340 Only superusers and users with the appropriate <code class="literal">SET</code>
341 privilege can change this setting.
343 Setting <code class="varname">max_stack_depth</code> higher than
344 the actual kernel limit will mean that a runaway recursive function
345 can crash an individual backend process. On platforms where
346 <span class="productname">PostgreSQL</span> can determine the kernel limit,
347 the server will not allow this variable to be set to an unsafe
348 value. However, not all platforms provide the information,
349 so caution is recommended in selecting a value.
350 </p></dd><dt id="GUC-SHARED-MEMORY-TYPE"><span class="term"><code class="varname">shared_memory_type</code> (<code class="type">enum</code>)
351 <a id="id-1.6.6.7.2.2.20.1.3" class="indexterm"></a>
352 </span> <a href="#GUC-SHARED-MEMORY-TYPE" class="id_link">#</a></dt><dd><p>
353 Specifies the shared memory implementation that the server
354 should use for the main shared memory region that holds
355 <span class="productname">PostgreSQL</span>'s shared buffers and other
356 shared data. Possible values are <code class="literal">mmap</code> (for
357 anonymous shared memory allocated using <code class="function">mmap</code>),
358 <code class="literal">sysv</code> (for System V shared memory allocated via
359 <code class="function">shmget</code>) and <code class="literal">windows</code> (for Windows
360 shared memory). Not all values are supported on all platforms; the
361 first supported option is the default for that platform. The use of
362 the <code class="literal">sysv</code> option, which is not the default on any
363 platform, is generally discouraged because it typically requires
364 non-default kernel settings to allow for large allocations (see <a class="xref" href="kernel-resources.html#SYSVIPC" title="18.4.1. Shared Memory and Semaphores">Section 18.4.1</a>).
365 </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>)
366 <a id="id-1.6.6.7.2.2.21.1.3" class="indexterm"></a>
367 </span> <a href="#GUC-DYNAMIC-SHARED-MEMORY-TYPE" class="id_link">#</a></dt><dd><p>
368 Specifies the dynamic shared memory implementation that the server
369 should use. Possible values are <code class="literal">posix</code> (for POSIX shared
370 memory allocated using <code class="literal">shm_open</code>), <code class="literal">sysv</code>
371 (for System V shared memory allocated via <code class="literal">shmget</code>),
372 <code class="literal">windows</code> (for Windows shared memory),
373 and <code class="literal">mmap</code> (to simulate shared memory using
374 memory-mapped files stored in the data directory).
375 Not all values are supported on all platforms; the first supported
376 option is usually the default for that platform. The use of the
377 <code class="literal">mmap</code> option, which is not the default on any platform,
378 is generally discouraged because the operating system may write
379 modified pages back to disk repeatedly, increasing system I/O load;
380 however, it may be useful for debugging, when the
381 <code class="literal">pg_dynshmem</code> directory is stored on a RAM disk, or when
382 other shared memory facilities are not available.
383 </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>)
384 <a id="id-1.6.6.7.2.2.22.1.3" class="indexterm"></a>
385 </span> <a href="#GUC-MIN-DYNAMIC-SHARED-MEMORY" class="id_link">#</a></dt><dd><p>
386 Specifies the amount of memory that should be allocated at server
387 startup for use by parallel queries. When this memory region is
388 insufficient or exhausted by concurrent queries, new parallel queries
389 try to allocate extra shared memory temporarily from the operating
390 system using the method configured with
391 <code class="varname">dynamic_shared_memory_type</code>, which may be slower due
392 to memory management overheads. Memory that is allocated at startup
393 with <code class="varname">min_dynamic_shared_memory</code> is affected by
394 the <code class="varname">huge_pages</code> setting on operating systems where
395 that is supported, and may be more likely to benefit from larger pages
396 on operating systems where that is managed automatically.
397 The default value is <code class="literal">0</code> (none). This parameter can
398 only be set at server start.
399 </p></dd></dl></div></div><div class="sect2" id="RUNTIME-CONFIG-RESOURCE-DISK"><div class="titlepage"><div><div><h3 class="title">19.4.2. Disk <a href="#RUNTIME-CONFIG-RESOURCE-DISK" class="id_link">#</a></h3></div></div></div><div class="variablelist"><dl class="variablelist"><dt id="GUC-TEMP-FILE-LIMIT"><span class="term"><code class="varname">temp_file_limit</code> (<code class="type">integer</code>)
400 <a id="id-1.6.6.7.3.2.1.1.3" class="indexterm"></a>
401 </span> <a href="#GUC-TEMP-FILE-LIMIT" class="id_link">#</a></dt><dd><p>
402 Specifies the maximum amount of disk space that a process can use
403 for temporary files, such as sort and hash temporary files, or the
404 storage file for a held cursor. A transaction attempting to exceed
405 this limit will be canceled.
406 If this value is specified without units, it is taken as kilobytes.
407 <code class="literal">-1</code> (the default) means no limit.
408 Only superusers and users with the appropriate <code class="literal">SET</code>
409 privilege can change this setting.
411 This setting constrains the total space used at any instant by all
412 temporary files used by a given <span class="productname">PostgreSQL</span> process.
413 It should be noted that disk space used for explicit temporary
414 tables, as opposed to temporary files used behind-the-scenes in query
415 execution, does <span class="emphasis"><em>not</em></span> count against this limit.
416 </p></dd><dt id="GUC-FILE-COPY-METHOD"><span class="term"><code class="varname">file_copy_method</code> (<code class="type">enum</code>)
417 <a id="id-1.6.6.7.3.2.2.1.3" class="indexterm"></a>
418 </span> <a href="#GUC-FILE-COPY-METHOD" class="id_link">#</a></dt><dd><p>
419 Specifies the method used to copy files.
420 Possible values are <code class="literal">COPY</code> (default) and
421 <code class="literal">CLONE</code> (if operating support is available).
423 This parameter affects:
424 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
425 <code class="literal"><code class="command">CREATE DATABASE ... STRATEGY=FILE_COPY</code></code>
426 </p></li><li class="listitem"><p>
427 <code class="command">ALTER DATABASE ... SET TABLESPACE ...</code>
428 </p></li></ul></div><p>
429 <code class="literal">CLONE</code> uses the <code class="function">copy_file_range()</code>
430 (Linux, FreeBSD) or <code class="function">copyfile</code>
431 (macOS) system calls, giving the kernel the opportunity to share disk
432 blocks or push work down to lower layers on some file systems.
433 </p></dd><dt id="GUC-MAX-NOTIFY-QUEUE-PAGES"><span class="term"><code class="varname">max_notify_queue_pages</code> (<code class="type">integer</code>)
434 <a id="id-1.6.6.7.3.2.3.1.3" class="indexterm"></a>
435 </span> <a href="#GUC-MAX-NOTIFY-QUEUE-PAGES" class="id_link">#</a></dt><dd><p>
436 Specifies the maximum amount of allocated pages for
437 <a class="xref" href="sql-notify.html" title="NOTIFY"><span class="refentrytitle">NOTIFY</span></a> / <a class="xref" href="sql-listen.html" title="LISTEN"><span class="refentrytitle">LISTEN</span></a> queue.
438 The default value is 1048576. For 8 KB pages it allows to consume
439 up to 8 GB of disk space.
440 </p></dd></dl></div></div><div class="sect2" id="RUNTIME-CONFIG-RESOURCE-KERNEL"><div class="titlepage"><div><div><h3 class="title">19.4.3. Kernel Resource Usage <a href="#RUNTIME-CONFIG-RESOURCE-KERNEL" class="id_link">#</a></h3></div></div></div><div class="variablelist"><dl class="variablelist"><dt id="GUC-MAX-FILES-PER-PROCESS"><span class="term"><code class="varname">max_files_per_process</code> (<code class="type">integer</code>)
441 <a id="id-1.6.6.7.4.2.1.1.3" class="indexterm"></a>
442 </span> <a href="#GUC-MAX-FILES-PER-PROCESS" class="id_link">#</a></dt><dd><p>
443 Sets the maximum number of open files each server subprocess is
444 allowed to open simultaneously; files already opened in the
445 postmaster are not counted toward this limit. The default is one
448 If the kernel is enforcing
449 a safe per-process limit, you don't need to worry about this setting.
450 But on some platforms (notably, most BSD systems), the kernel will
451 allow individual processes to open many more files than the system
452 can actually support if many processes all try to open
453 that many files. If you find yourself seeing <span class="quote">“<span class="quote">Too many open
454 files</span>”</span> failures, try reducing this setting.
455 This parameter can only be set at server start.
456 </p></dd></dl></div></div><div class="sect2" id="RUNTIME-CONFIG-RESOURCE-BACKGROUND-WRITER"><div class="titlepage"><div><div><h3 class="title">19.4.4. Background Writer <a href="#RUNTIME-CONFIG-RESOURCE-BACKGROUND-WRITER" class="id_link">#</a></h3></div></div></div><p>
457 There is a separate server
458 process called the <em class="firstterm">background writer</em>, whose function
459 is to issue writes of <span class="quote">“<span class="quote">dirty</span>”</span> (new or modified) shared
460 buffers. When the number of clean shared buffers appears to be
461 insufficient, the background writer writes some dirty buffers to the
462 file system and marks them as clean. This reduces the likelihood
463 that server processes handling user queries will be unable to find
464 clean buffers and have to write dirty buffers themselves.
465 However, the background writer does cause a net overall
466 increase in I/O load, because while a repeatedly-dirtied page might
467 otherwise be written only once per checkpoint interval, the
468 background writer might write it several times as it is dirtied
469 in the same interval. The parameters discussed in this subsection
470 can be used to tune the behavior for local needs.
471 </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>)
472 <a id="id-1.6.6.7.5.3.1.1.3" class="indexterm"></a>
473 </span> <a href="#GUC-BGWRITER-DELAY" class="id_link">#</a></dt><dd><p>
474 Specifies the delay between activity rounds for the
475 background writer. In each round the writer issues writes
476 for some number of dirty buffers (controllable by the
477 following parameters). It then sleeps for
478 the length of <code class="varname">bgwriter_delay</code>, and repeats.
479 When there are no dirty buffers in the
480 buffer pool, though, it goes into a longer sleep regardless of
481 <code class="varname">bgwriter_delay</code>.
482 If this value is specified without units, it is taken as milliseconds.
483 The default value is 200
484 milliseconds (<code class="literal">200ms</code>). Note that on some systems, the
485 effective resolution of sleep delays is 10 milliseconds; setting
486 <code class="varname">bgwriter_delay</code> to a value that is not a multiple of 10
487 might have the same results as setting it to the next higher multiple
488 of 10. This parameter can only be set in the
489 <code class="filename">postgresql.conf</code> file or on the server command line.
490 </p></dd><dt id="GUC-BGWRITER-LRU-MAXPAGES"><span class="term"><code class="varname">bgwriter_lru_maxpages</code> (<code class="type">integer</code>)
491 <a id="id-1.6.6.7.5.3.2.1.3" class="indexterm"></a>
492 </span> <a href="#GUC-BGWRITER-LRU-MAXPAGES" class="id_link">#</a></dt><dd><p>
493 In each round, no more than this many buffers will be written
494 by the background writer. Setting this to zero disables
495 background writing. (Note that checkpoints, which are managed by
496 a separate, dedicated auxiliary process, are unaffected.)
497 The default value is 100 buffers.
498 This parameter can only be set in the <code class="filename">postgresql.conf</code>
499 file or on the server command line.
500 </p></dd><dt id="GUC-BGWRITER-LRU-MULTIPLIER"><span class="term"><code class="varname">bgwriter_lru_multiplier</code> (<code class="type">floating point</code>)
501 <a id="id-1.6.6.7.5.3.3.1.3" class="indexterm"></a>
502 </span> <a href="#GUC-BGWRITER-LRU-MULTIPLIER" class="id_link">#</a></dt><dd><p>
503 The number of dirty buffers written in each round is based on the
504 number of new buffers that have been needed by server processes
505 during recent rounds. The average recent need is multiplied by
506 <code class="varname">bgwriter_lru_multiplier</code> to arrive at an estimate of the
507 number of buffers that will be needed during the next round. Dirty
508 buffers are written until there are that many clean, reusable buffers
509 available. (However, no more than <code class="varname">bgwriter_lru_maxpages</code>
510 buffers will be written per round.)
511 Thus, a setting of 1.0 represents a <span class="quote">“<span class="quote">just in time</span>”</span> policy
512 of writing exactly the number of buffers predicted to be needed.
513 Larger values provide some cushion against spikes in demand,
514 while smaller values intentionally leave writes to be done by
517 This parameter can only be set in the <code class="filename">postgresql.conf</code>
518 file or on the server command line.
519 </p></dd><dt id="GUC-BGWRITER-FLUSH-AFTER"><span class="term"><code class="varname">bgwriter_flush_after</code> (<code class="type">integer</code>)
520 <a id="id-1.6.6.7.5.3.4.1.3" class="indexterm"></a>
521 </span> <a href="#GUC-BGWRITER-FLUSH-AFTER" class="id_link">#</a></dt><dd><p>
522 Whenever more than this amount of data has
523 been written by the background writer, attempt to force the OS to issue these
524 writes to the underlying storage. Doing so will limit the amount of
525 dirty data in the kernel's page cache, reducing the likelihood of
526 stalls when an <code class="function">fsync</code> is issued at the end of a checkpoint, or when
527 the OS writes data back in larger batches in the background. Often
528 that will result in greatly reduced transaction latency, but there
529 also are some cases, especially with workloads that are bigger than
530 <a class="xref" href="runtime-config-resource.html#GUC-SHARED-BUFFERS">shared_buffers</a>, but smaller than the OS's page
531 cache, where performance might degrade. This setting may have no
532 effect on some platforms.
533 If this value is specified without units, it is taken as blocks,
534 that is <code class="symbol">BLCKSZ</code> bytes, typically 8kB.
535 The valid range is between
536 <code class="literal">0</code>, which disables forced writeback, and
537 <code class="literal">2MB</code>. The default is <code class="literal">512kB</code> on Linux,
538 <code class="literal">0</code> elsewhere. (If <code class="symbol">BLCKSZ</code> is not 8kB,
539 the default and maximum values scale proportionally to it.)
540 This parameter can only be set in the <code class="filename">postgresql.conf</code>
541 file or on the server command line.
542 </p></dd></dl></div><p>
543 Smaller values of <code class="varname">bgwriter_lru_maxpages</code> and
544 <code class="varname">bgwriter_lru_multiplier</code> reduce the extra I/O load
545 caused by the background writer, but make it more likely that server
546 processes will have to issue writes for themselves, delaying interactive
548 </p></div><div class="sect2" id="RUNTIME-CONFIG-RESOURCE-IO"><div class="titlepage"><div><div><h3 class="title">19.4.5. I/O <a href="#RUNTIME-CONFIG-RESOURCE-IO" 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>)
549 <a id="id-1.6.6.7.6.2.1.1.3" class="indexterm"></a>
550 </span> <a href="#GUC-BACKEND-FLUSH-AFTER" class="id_link">#</a></dt><dd><p>
551 Whenever more than this amount of data has
552 been written by a single backend, attempt to force the OS to issue
553 these writes to the underlying storage. Doing so will limit the
554 amount of dirty data in the kernel's page cache, reducing the
555 likelihood of stalls when an <code class="function">fsync</code> is issued at the end of a
556 checkpoint, or when the OS writes data back in larger batches in the
557 background. Often that will result in greatly reduced transaction
558 latency, but there also are some cases, especially with workloads
559 that are bigger than <a class="xref" href="runtime-config-resource.html#GUC-SHARED-BUFFERS">shared_buffers</a>, but smaller
560 than the OS's page cache, where performance might degrade. This
561 setting may have no effect on some platforms.
562 If this value is specified without units, it is taken as blocks,
563 that is <code class="symbol">BLCKSZ</code> bytes, typically 8kB.
565 between <code class="literal">0</code>, which disables forced writeback,
566 and <code class="literal">2MB</code>. The default is <code class="literal">0</code>, i.e., no
567 forced writeback. (If <code class="symbol">BLCKSZ</code> is not 8kB,
568 the maximum value scales proportionally to it.)
569 </p></dd><dt id="GUC-EFFECTIVE-IO-CONCURRENCY"><span class="term"><code class="varname">effective_io_concurrency</code> (<code class="type">integer</code>)
570 <a id="id-1.6.6.7.6.2.2.1.3" class="indexterm"></a>
571 </span> <a href="#GUC-EFFECTIVE-IO-CONCURRENCY" class="id_link">#</a></dt><dd><p>
572 Sets the number of concurrent storage I/O operations that
573 <span class="productname">PostgreSQL</span> expects can be executed
574 simultaneously. Raising this value will increase the number of I/O
575 operations that any individual <span class="productname">PostgreSQL</span>
576 session attempts to initiate in parallel. The allowed range is
577 <code class="literal">1</code> to <code class="literal">1000</code>, or
578 <code class="literal">0</code> to disable issuance of asynchronous I/O requests.
579 The default is <code class="literal">16</code>.
581 Higher values will have the most impact on higher latency storage
582 where queries otherwise experience noticeable I/O stalls and on
583 devices with high IOPs. Unnecessarily high values may increase I/O
584 latency for all queries on the system
586 On systems with prefetch advice support,
587 <code class="varname">effective_io_concurrency</code> also controls the
590 This value can be overridden for tables in a particular tablespace by
591 setting the tablespace parameter of the same name (see <a class="xref" href="sql-altertablespace.html" title="ALTER TABLESPACE"><span class="refentrytitle">ALTER TABLESPACE</span></a>).
592 </p></dd><dt id="GUC-MAINTENANCE-IO-CONCURRENCY"><span class="term"><code class="varname">maintenance_io_concurrency</code> (<code class="type">integer</code>)
593 <a id="id-1.6.6.7.6.2.3.1.3" class="indexterm"></a>
594 </span> <a href="#GUC-MAINTENANCE-IO-CONCURRENCY" class="id_link">#</a></dt><dd><p>
595 Similar to <code class="varname">effective_io_concurrency</code>, but used
596 for maintenance work that is done on behalf of many client sessions.
598 The default is <code class="literal">16</code>. This value can be overridden
599 for tables in a particular tablespace by setting the tablespace
600 parameter of the same name (see <a class="xref" href="sql-altertablespace.html" title="ALTER TABLESPACE"><span class="refentrytitle">ALTER TABLESPACE</span></a>).
601 </p></dd><dt id="GUC-IO-MAX-COMBINE-LIMIT"><span class="term"><code class="varname">io_max_combine_limit</code> (<code class="type">integer</code>)
602 <a id="id-1.6.6.7.6.2.4.1.3" class="indexterm"></a>
603 </span> <a href="#GUC-IO-MAX-COMBINE-LIMIT" class="id_link">#</a></dt><dd><p>
604 Controls the largest I/O size in operations that combine I/O, and silently
605 limits the user-settable parameter <code class="varname">io_combine_limit</code>.
606 This parameter can only be set in
607 the <code class="filename">postgresql.conf</code> file or on the server
609 The maximum possible size depends on the operating system and block
610 size, but is typically 1MB on Unix and 128kB on Windows.
611 The default is 128kB.
612 </p></dd><dt id="GUC-IO-COMBINE-LIMIT"><span class="term"><code class="varname">io_combine_limit</code> (<code class="type">integer</code>)
613 <a id="id-1.6.6.7.6.2.5.1.3" class="indexterm"></a>
614 </span> <a href="#GUC-IO-COMBINE-LIMIT" class="id_link">#</a></dt><dd><p>
615 Controls the largest I/O size in operations that combine I/O. If set
616 higher than the <code class="varname">io_max_combine_limit</code> parameter, the
617 lower value will silently be used instead, so both may need to be raised
618 to increase the I/O size.
619 The maximum possible size depends on the operating system and block
620 size, but is typically 1MB on Unix and 128kB on Windows.
621 The default is 128kB.
622 </p></dd><dt id="GUC-IO-MAX-CONCURRENCY"><span class="term"><code class="varname">io_max_concurrency</code> (<code class="type">integer</code>)
623 <a id="id-1.6.6.7.6.2.6.1.3" class="indexterm"></a>
624 </span> <a href="#GUC-IO-MAX-CONCURRENCY" class="id_link">#</a></dt><dd><p>
625 Controls the maximum number of I/O operations that one process can
626 execute simultaneously.
628 The default setting of <code class="literal">-1</code> selects a number based
629 on <a class="xref" href="runtime-config-resource.html#GUC-SHARED-BUFFERS">shared_buffers</a> and the maximum number of
630 processes (<a class="xref" href="runtime-config-connection.html#GUC-MAX-CONNECTIONS">max_connections</a>, <a class="xref" href="runtime-config-vacuum.html#GUC-AUTOVACUUM-WORKER-SLOTS">autovacuum_worker_slots</a>, <a class="xref" href="runtime-config-resource.html#GUC-MAX-WORKER-PROCESSES">max_worker_processes</a> and <a class="xref" href="runtime-config-replication.html#GUC-MAX-WAL-SENDERS">max_wal_senders</a>), but not more than
631 <code class="literal">64</code>.
633 This parameter can only be set at server start.
634 </p></dd><dt id="GUC-IO-METHOD"><span class="term"><code class="varname">io_method</code> (<code class="type">enum</code>)
635 <a id="id-1.6.6.7.6.2.7.1.3" class="indexterm"></a>
636 </span> <a href="#GUC-IO-METHOD" class="id_link">#</a></dt><dd><p>
637 Selects the method for executing asynchronous I/O.
639 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
640 <code class="literal">worker</code> (execute asynchronous I/O using worker processes)
641 </p></li><li class="listitem"><p>
642 <code class="literal">io_uring</code> (execute asynchronous I/O using
643 io_uring, requires a build with
644 <a class="link" href="install-make.html#CONFIGURE-OPTION-WITH-LIBURING"><code class="option">--with-liburing</code></a> /
645 <a class="link" href="install-meson.html#CONFIGURE-WITH-LIBURING-MESON"><code class="option">-Dliburing</code></a>)
646 </p></li><li class="listitem"><p>
647 <code class="literal">sync</code> (execute asynchronous-eligible I/O synchronously)
648 </p></li></ul></div><p>
649 The default is <code class="literal">worker</code>.
651 This parameter can only be set at server start.
652 </p></dd><dt id="GUC-IO-WORKERS"><span class="term"><code class="varname">io_workers</code> (<code class="type">integer</code>)
653 <a id="id-1.6.6.7.6.2.8.1.3" class="indexterm"></a>
654 </span> <a href="#GUC-IO-WORKERS" class="id_link">#</a></dt><dd><p>
655 Selects the number of I/O worker processes to use. The default is
656 3. This parameter can only be set in the
657 <code class="filename">postgresql.conf</code> file or on the server command
660 Only has an effect if <a class="xref" href="runtime-config-resource.html#GUC-IO-METHOD">io_method</a> is set to
661 <code class="literal">worker</code>.
662 </p></dd></dl></div></div><div class="sect2" id="RUNTIME-CONFIG-RESOURCE-WORKER-PROCESSES"><div class="titlepage"><div><div><h3 class="title">19.4.6. Worker Processes <a href="#RUNTIME-CONFIG-RESOURCE-WORKER-PROCESSES" class="id_link">#</a></h3></div></div></div><div class="variablelist"><dl class="variablelist"><dt id="GUC-MAX-WORKER-PROCESSES"><span class="term"><code class="varname">max_worker_processes</code> (<code class="type">integer</code>)
663 <a id="id-1.6.6.7.7.2.1.1.3" class="indexterm"></a>
664 </span> <a href="#GUC-MAX-WORKER-PROCESSES" class="id_link">#</a></dt><dd><p>
665 Sets the maximum number of background processes that the cluster
666 can support. This parameter can only be set at server start. The
669 When running a standby server, you must set this parameter to the
670 same or higher value than on the primary server. Otherwise, queries
671 will not be allowed in the standby server.
673 When changing this value, consider also adjusting
674 <a class="xref" href="runtime-config-resource.html#GUC-MAX-PARALLEL-WORKERS">max_parallel_workers</a>,
675 <a class="xref" href="runtime-config-resource.html#GUC-MAX-PARALLEL-MAINTENANCE-WORKERS">max_parallel_maintenance_workers</a>, and
676 <a class="xref" href="runtime-config-resource.html#GUC-MAX-PARALLEL-WORKERS-PER-GATHER">max_parallel_workers_per_gather</a>.
677 </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>)
678 <a id="id-1.6.6.7.7.2.2.1.3" class="indexterm"></a>
679 </span> <a href="#GUC-MAX-PARALLEL-WORKERS-PER-GATHER" class="id_link">#</a></dt><dd><p>
680 Sets the maximum number of workers that can be started by a single
681 <code class="literal">Gather</code> or <code class="literal">Gather Merge</code> node.
682 Parallel workers are taken from the pool of processes established by
683 <a class="xref" href="runtime-config-resource.html#GUC-MAX-WORKER-PROCESSES">max_worker_processes</a>, limited by
684 <a class="xref" href="runtime-config-resource.html#GUC-MAX-PARALLEL-WORKERS">max_parallel_workers</a>. Note that the requested
685 number of workers may not actually be available at run time. If this
686 occurs, the plan will run with fewer workers than expected, which may
687 be inefficient. The default value is 2. Setting this value to 0
688 disables parallel query execution.
690 Note that parallel queries may consume very substantially more
691 resources than non-parallel queries, because each worker process is
692 a completely separate process which has roughly the same impact on the
693 system as an additional user session. This should be taken into
694 account when choosing a value for this setting, as well as when
695 configuring other settings that control resource utilization, such
696 as <a class="xref" href="runtime-config-resource.html#GUC-WORK-MEM">work_mem</a>. Resource limits such as
697 <code class="varname">work_mem</code> are applied individually to each worker,
698 which means the total utilization may be much higher across all
699 processes than it would normally be for any single process.
700 For example, a parallel query using 4 workers may use up to 5 times
701 as much CPU time, memory, I/O bandwidth, and so forth as a query which
702 uses no workers at all.
704 For more information on parallel query, see
705 <a class="xref" href="parallel-query.html" title="Chapter 15. Parallel Query">Chapter 15</a>.
706 </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>)
707 <a id="id-1.6.6.7.7.2.3.1.3" class="indexterm"></a>
708 </span> <a href="#GUC-MAX-PARALLEL-MAINTENANCE-WORKERS" class="id_link">#</a></dt><dd><p>
709 Sets the maximum number of parallel workers that can be
710 started by a single utility command. Currently, the parallel
711 utility commands that support the use of parallel workers are
712 <code class="command">CREATE INDEX</code> when building a B-tree,
714 and <code class="command">VACUUM</code> without <code class="literal">FULL</code>
715 option. Parallel workers are taken from the pool of processes
716 established by <a class="xref" href="runtime-config-resource.html#GUC-MAX-WORKER-PROCESSES">max_worker_processes</a>, limited
717 by <a class="xref" href="runtime-config-resource.html#GUC-MAX-PARALLEL-WORKERS">max_parallel_workers</a>. Note that the requested
718 number of workers may not actually be available at run time.
719 If this occurs, the utility operation will run with fewer
720 workers than expected. The default value is 2. Setting this
721 value to 0 disables the use of parallel workers by utility
724 Note that parallel utility commands should not consume
725 substantially more memory than equivalent non-parallel
726 operations. This strategy differs from that of parallel
727 query, where resource limits generally apply per worker
728 process. Parallel utility commands treat the resource limit
729 <code class="varname">maintenance_work_mem</code> as a limit to be applied to
730 the entire utility command, regardless of the number of
731 parallel worker processes. However, parallel utility
732 commands may still consume substantially more CPU resources
734 </p></dd><dt id="GUC-MAX-PARALLEL-WORKERS"><span class="term"><code class="varname">max_parallel_workers</code> (<code class="type">integer</code>)
735 <a id="id-1.6.6.7.7.2.4.1.3" class="indexterm"></a>
736 </span> <a href="#GUC-MAX-PARALLEL-WORKERS" class="id_link">#</a></dt><dd><p>
737 Sets the maximum number of workers that the cluster can support for
738 parallel operations. The default value is 8. When increasing or
739 decreasing this value, consider also adjusting
740 <a class="xref" href="runtime-config-resource.html#GUC-MAX-PARALLEL-MAINTENANCE-WORKERS">max_parallel_maintenance_workers</a> and
741 <a class="xref" href="runtime-config-resource.html#GUC-MAX-PARALLEL-WORKERS-PER-GATHER">max_parallel_workers_per_gather</a>.
742 Also, note that a setting for this value which is higher than
743 <a class="xref" href="runtime-config-resource.html#GUC-MAX-WORKER-PROCESSES">max_worker_processes</a> will have no effect,
744 since parallel workers are taken from the pool of worker processes
745 established by that setting.
746 </p></dd><dt id="GUC-PARALLEL-LEADER-PARTICIPATION"><span class="term">
747 <code class="varname">parallel_leader_participation</code> (<code class="type">boolean</code>)
748 <a id="id-1.6.6.7.7.2.5.1.3" class="indexterm"></a>
749 </span> <a href="#GUC-PARALLEL-LEADER-PARTICIPATION" class="id_link">#</a></dt><dd><p>
750 Allows the leader process to execute the query plan under
751 <code class="literal">Gather</code> and <code class="literal">Gather Merge</code> nodes
752 instead of waiting for worker processes. The default is
753 <code class="literal">on</code>. Setting this value to <code class="literal">off</code>
754 reduces the likelihood that workers will become blocked because the
755 leader is not reading tuples fast enough, but requires the leader
756 process to wait for worker processes to start up before the first
757 tuples can be produced. The degree to which the leader can help or
758 hinder performance depends on the plan type, number of workers and
760 </p></dd></dl></div></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="runtime-config-connection.html" title="19.3. Connections and Authentication">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="runtime-config.html" title="Chapter 19. Server Configuration">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="runtime-config-wal.html" title="19.5. Write Ahead Log">Next</a></td></tr><tr><td width="40%" align="left" valign="top">19.3. Connections and Authentication </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 19.5. Write Ahead Log</td></tr></table></div></body></html>
begriffs.com / projects / ai-pg / blob