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.10. Vacuuming</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-statistics.html" title="19.9. Run-time Statistics" /><link rel="next" href="runtime-config-client.html" title="19.11. Client Connection Defaults" /></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.10. Vacuuming</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="runtime-config-statistics.html" title="19.9. Run-time Statistics">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-client.html" title="19.11. Client Connection Defaults">Next</a></td></tr></table><hr /></div><div class="sect1" id="RUNTIME-CONFIG-VACUUM"><div class="titlepage"><div><div><h2 class="title" style="clear: both">19.10. Vacuuming <a href="#RUNTIME-CONFIG-VACUUM" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="runtime-config-vacuum.html#RUNTIME-CONFIG-AUTOVACUUM">19.10.1. Automatic Vacuuming</a></span></dt><dt><span class="sect2"><a href="runtime-config-vacuum.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST">19.10.2. Cost-based Vacuum Delay</a></span></dt><dt><span class="sect2"><a href="runtime-config-vacuum.html#RUNTIME-CONFIG-VACUUM-DEFAULT">19.10.3. Default Behavior</a></span></dt><dt><span class="sect2"><a href="runtime-config-vacuum.html#RUNTIME-CONFIG-VACUUM-FREEZING">19.10.4. Freezing</a></span></dt></dl></div><a id="id-1.6.6.13.2" class="indexterm"></a><p>
3 These parameters control vacuuming behavior. For more information on the
4 purpose and responsibilities of vacuum, see <a class="xref" href="routine-vacuuming.html" title="24.1. Routine Vacuuming">Section 24.1</a>.
5 </p><div class="sect2" id="RUNTIME-CONFIG-AUTOVACUUM"><div class="titlepage"><div><div><h3 class="title">19.10.1. Automatic Vacuuming <a href="#RUNTIME-CONFIG-AUTOVACUUM" class="id_link">#</a></h3></div></div></div><p>
6 These settings control the behavior of the <em class="firstterm">autovacuum</em>
7 feature. Refer to <a class="xref" href="routine-vacuuming.html#AUTOVACUUM" title="24.1.6. The Autovacuum Daemon">Section 24.1.6</a> for more information.
8 Note that many of these settings can be overridden on a per-table
9 basis; see <a class="xref" href="sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS" title="Storage Parameters">Storage Parameters</a>.
10 </p><div class="variablelist"><dl class="variablelist"><dt id="GUC-AUTOVACUUM"><span class="term"><code class="varname">autovacuum</code> (<code class="type">boolean</code>)
11 <a id="id-1.6.6.13.4.3.1.1.3" class="indexterm"></a>
12 </span> <a href="#GUC-AUTOVACUUM" class="id_link">#</a></dt><dd><p>
13 Controls whether the server should run the
14 autovacuum launcher daemon. This is on by default; however,
15 <a class="xref" href="runtime-config-statistics.html#GUC-TRACK-COUNTS">track_counts</a> must also be enabled for
17 This parameter can only be set in the <code class="filename">postgresql.conf</code>
18 file or on the server command line; however, autovacuuming can be
19 disabled for individual tables by changing table storage parameters.
21 Note that even when this parameter is disabled, the system
22 will launch autovacuum processes if necessary to
23 prevent transaction ID wraparound. See <a class="xref" href="routine-vacuuming.html#VACUUM-FOR-WRAPAROUND" title="24.1.5. Preventing Transaction ID Wraparound Failures">Section 24.1.5</a> for more information.
24 </p></dd><dt id="GUC-AUTOVACUUM-WORKER-SLOTS"><span class="term"><code class="varname">autovacuum_worker_slots</code> (<code class="type">integer</code>)
25 <a id="id-1.6.6.13.4.3.2.1.3" class="indexterm"></a>
26 </span> <a href="#GUC-AUTOVACUUM-WORKER-SLOTS" class="id_link">#</a></dt><dd><p>
27 Specifies the number of backend slots to reserve for autovacuum worker
28 processes. The default is typically 16 slots, but might be less if
29 your kernel settings will not support it (as determined during initdb).
30 This parameter can only be set at server start.
32 When changing this value, consider also adjusting
33 <a class="xref" href="runtime-config-vacuum.html#GUC-AUTOVACUUM-MAX-WORKERS">autovacuum_max_workers</a>.
34 </p></dd><dt id="GUC-AUTOVACUUM-MAX-WORKERS"><span class="term"><code class="varname">autovacuum_max_workers</code> (<code class="type">integer</code>)
35 <a id="id-1.6.6.13.4.3.3.1.3" class="indexterm"></a>
36 </span> <a href="#GUC-AUTOVACUUM-MAX-WORKERS" class="id_link">#</a></dt><dd><p>
37 Specifies the maximum number of autovacuum processes (other than the
38 autovacuum launcher) that may be running at any one time. The default
39 is <code class="literal">3</code>. This parameter can only be set in the
40 <code class="filename">postgresql.conf</code> file or on the server command line.
42 Note that a setting for this value which is higher than
43 <a class="xref" href="runtime-config-vacuum.html#GUC-AUTOVACUUM-WORKER-SLOTS">autovacuum_worker_slots</a> will have no effect,
44 since autovacuum workers are taken from the pool of slots established
46 </p></dd><dt id="GUC-AUTOVACUUM-NAPTIME"><span class="term"><code class="varname">autovacuum_naptime</code> (<code class="type">integer</code>)
47 <a id="id-1.6.6.13.4.3.4.1.3" class="indexterm"></a>
48 </span> <a href="#GUC-AUTOVACUUM-NAPTIME" class="id_link">#</a></dt><dd><p>
49 Specifies the minimum delay between autovacuum runs on any given
50 database. In each round the daemon examines the
51 database and issues <code class="command">VACUUM</code> and <code class="command">ANALYZE</code> commands
52 as needed for tables in that database.
53 If this value is specified without units, it is taken as seconds.
54 The default is one minute (<code class="literal">1min</code>).
55 This parameter can only be set in the <code class="filename">postgresql.conf</code>
56 file or on the server command line.
57 </p></dd><dt id="GUC-AUTOVACUUM-VACUUM-THRESHOLD"><span class="term"><code class="varname">autovacuum_vacuum_threshold</code> (<code class="type">integer</code>)
58 <a id="id-1.6.6.13.4.3.5.1.3" class="indexterm"></a>
59 </span> <a href="#GUC-AUTOVACUUM-VACUUM-THRESHOLD" class="id_link">#</a></dt><dd><p>
60 Specifies the minimum number of updated or deleted tuples needed
61 to trigger a <code class="command">VACUUM</code> in any one table.
62 The default is 50 tuples.
63 This parameter can only be set in the <code class="filename">postgresql.conf</code>
64 file or on the server command line;
65 but the setting can be overridden for individual tables by
66 changing table storage parameters.
67 </p></dd><dt id="GUC-AUTOVACUUM-VACUUM-INSERT-THRESHOLD"><span class="term"><code class="varname">autovacuum_vacuum_insert_threshold</code> (<code class="type">integer</code>)
68 <a id="id-1.6.6.13.4.3.6.1.3" class="indexterm"></a>
69 </span> <a href="#GUC-AUTOVACUUM-VACUUM-INSERT-THRESHOLD" class="id_link">#</a></dt><dd><p>
70 Specifies the number of inserted tuples needed to trigger a
71 <code class="command">VACUUM</code> in any one table.
72 The default is 1000 tuples. If -1 is specified, autovacuum will not
73 trigger a <code class="command">VACUUM</code> operation on any tables based on
74 the number of inserts.
75 This parameter can only be set in the <code class="filename">postgresql.conf</code>
76 file or on the server command line;
77 but the setting can be overridden for individual tables by
78 changing table storage parameters.
79 </p></dd><dt id="GUC-AUTOVACUUM-ANALYZE-THRESHOLD"><span class="term"><code class="varname">autovacuum_analyze_threshold</code> (<code class="type">integer</code>)
80 <a id="id-1.6.6.13.4.3.7.1.3" class="indexterm"></a>
81 </span> <a href="#GUC-AUTOVACUUM-ANALYZE-THRESHOLD" class="id_link">#</a></dt><dd><p>
82 Specifies the minimum number of inserted, updated or deleted tuples
83 needed to trigger an <code class="command">ANALYZE</code> in any one table.
84 The default is 50 tuples.
85 This parameter can only be set in the <code class="filename">postgresql.conf</code>
86 file or on the server command line;
87 but the setting can be overridden for individual tables by
88 changing table storage parameters.
89 </p></dd><dt id="GUC-AUTOVACUUM-VACUUM-SCALE-FACTOR"><span class="term"><code class="varname">autovacuum_vacuum_scale_factor</code> (<code class="type">floating point</code>)
90 <a id="id-1.6.6.13.4.3.8.1.3" class="indexterm"></a>
91 </span> <a href="#GUC-AUTOVACUUM-VACUUM-SCALE-FACTOR" class="id_link">#</a></dt><dd><p>
92 Specifies a fraction of the table size to add to
93 <code class="varname">autovacuum_vacuum_threshold</code>
94 when deciding whether to trigger a <code class="command">VACUUM</code>.
95 The default is <code class="literal">0.2</code> (20% of table size).
96 This parameter can only be set in the <code class="filename">postgresql.conf</code>
97 file or on the server command line;
98 but the setting can be overridden for individual tables by
99 changing table storage parameters.
100 </p></dd><dt id="GUC-AUTOVACUUM-VACUUM-INSERT-SCALE-FACTOR"><span class="term"><code class="varname">autovacuum_vacuum_insert_scale_factor</code> (<code class="type">floating point</code>)
101 <a id="id-1.6.6.13.4.3.9.1.3" class="indexterm"></a>
102 </span> <a href="#GUC-AUTOVACUUM-VACUUM-INSERT-SCALE-FACTOR" class="id_link">#</a></dt><dd><p>
103 Specifies a fraction of the unfrozen pages in the table to add to
104 <code class="varname">autovacuum_vacuum_insert_threshold</code> when deciding
105 whether to trigger a <code class="command">VACUUM</code>. The default is
106 <code class="literal">0.2</code> (20% of unfrozen pages in table). This
107 parameter can only be set in the <code class="filename">postgresql.conf</code>
108 file or on the server command line; but the setting can be overridden
109 for individual tables by changing table storage parameters.
110 </p></dd><dt id="GUC-AUTOVACUUM-ANALYZE-SCALE-FACTOR"><span class="term"><code class="varname">autovacuum_analyze_scale_factor</code> (<code class="type">floating point</code>)
111 <a id="id-1.6.6.13.4.3.10.1.3" class="indexterm"></a>
112 </span> <a href="#GUC-AUTOVACUUM-ANALYZE-SCALE-FACTOR" class="id_link">#</a></dt><dd><p>
113 Specifies a fraction of the table size to add to
114 <code class="varname">autovacuum_analyze_threshold</code>
115 when deciding whether to trigger an <code class="command">ANALYZE</code>.
116 The default is <code class="literal">0.1</code> (10% of table size).
117 This parameter can only be set in the <code class="filename">postgresql.conf</code>
118 file or on the server command line;
119 but the setting can be overridden for individual tables by
120 changing table storage parameters.
121 </p></dd><dt id="GUC-AUTOVACUUM-VACUUM-MAX-THRESHOLD"><span class="term"><code class="varname">autovacuum_vacuum_max_threshold</code> (<code class="type">integer</code>)
122 <a id="id-1.6.6.13.4.3.11.1.3" class="indexterm"></a>
123 </span> <a href="#GUC-AUTOVACUUM-VACUUM-MAX-THRESHOLD" class="id_link">#</a></dt><dd><p>
124 Specifies the maximum number of updated or deleted tuples needed to
125 trigger a <code class="command">VACUUM</code> in any one table, i.e., a limit on
126 the value calculated with
127 <code class="varname">autovacuum_vacuum_threshold</code> and
128 <code class="varname">autovacuum_vacuum_scale_factor</code>. The default is
129 100,000,000 tuples. If -1 is specified, autovacuum will not enforce a
130 maximum number of updated or deleted tuples that will trigger a
131 <code class="command">VACUUM</code> operation. This parameter can only be set
132 in the <code class="filename">postgresql.conf</code> file or on the server
133 command line; but the setting can be overridden for individual tables
134 by changing storage parameters.
135 </p></dd><dt id="GUC-AUTOVACUUM-FREEZE-MAX-AGE"><span class="term"><code class="varname">autovacuum_freeze_max_age</code> (<code class="type">integer</code>)
136 <a id="id-1.6.6.13.4.3.12.1.3" class="indexterm"></a>
137 </span> <a href="#GUC-AUTOVACUUM-FREEZE-MAX-AGE" class="id_link">#</a></dt><dd><p>
138 Specifies the maximum age (in transactions) that a table's
139 <code class="structname">pg_class</code>.<code class="structfield">relfrozenxid</code> field can
140 attain before a <code class="command">VACUUM</code> operation is forced
141 to prevent transaction ID wraparound within the table.
142 Note that the system will launch autovacuum processes to
143 prevent wraparound even when autovacuum is otherwise disabled.
145 Vacuum also allows removal of old files from the
146 <code class="filename">pg_xact</code> subdirectory, which is why the default
147 is a relatively low 200 million transactions.
148 This parameter can only be set at server start, but the setting
149 can be reduced for individual tables by
150 changing table storage parameters.
151 For more information see <a class="xref" href="routine-vacuuming.html#VACUUM-FOR-WRAPAROUND" title="24.1.5. Preventing Transaction ID Wraparound Failures">Section 24.1.5</a>.
152 </p></dd><dt id="GUC-AUTOVACUUM-MULTIXACT-FREEZE-MAX-AGE"><span class="term"><code class="varname">autovacuum_multixact_freeze_max_age</code> (<code class="type">integer</code>)
153 <a id="id-1.6.6.13.4.3.13.1.3" class="indexterm"></a>
154 </span> <a href="#GUC-AUTOVACUUM-MULTIXACT-FREEZE-MAX-AGE" class="id_link">#</a></dt><dd><p>
155 Specifies the maximum age (in multixacts) that a table's
156 <code class="structname">pg_class</code>.<code class="structfield">relminmxid</code> field can
157 attain before a <code class="command">VACUUM</code> operation is forced to
158 prevent multixact ID wraparound within the table.
159 Note that the system will launch autovacuum processes to
160 prevent wraparound even when autovacuum is otherwise disabled.
162 Vacuuming multixacts also allows removal of old files from the
163 <code class="filename">pg_multixact/members</code> and <code class="filename">pg_multixact/offsets</code>
164 subdirectories, which is why the default is a relatively low
165 400 million multixacts.
166 This parameter can only be set at server start, but the setting can
167 be reduced for individual tables by changing table storage parameters.
168 For more information see <a class="xref" href="routine-vacuuming.html#VACUUM-FOR-MULTIXACT-WRAPAROUND" title="24.1.5.1. Multixacts and Wraparound">Section 24.1.5.1</a>.
169 </p></dd><dt id="GUC-AUTOVACUUM-VACUUM-COST-DELAY"><span class="term"><code class="varname">autovacuum_vacuum_cost_delay</code> (<code class="type">floating point</code>)
170 <a id="id-1.6.6.13.4.3.14.1.3" class="indexterm"></a>
171 </span> <a href="#GUC-AUTOVACUUM-VACUUM-COST-DELAY" class="id_link">#</a></dt><dd><p>
172 Specifies the cost delay value that will be used in automatic
173 <code class="command">VACUUM</code> operations. If -1 is specified, the regular
174 <a class="xref" href="runtime-config-vacuum.html#GUC-VACUUM-COST-DELAY">vacuum_cost_delay</a> value will be used.
175 If this value is specified without units, it is taken as milliseconds.
176 The default value is 2 milliseconds.
177 This parameter can only be set in the <code class="filename">postgresql.conf</code>
178 file or on the server command line;
179 but the setting can be overridden for individual tables by
180 changing table storage parameters.
181 </p></dd><dt id="GUC-AUTOVACUUM-VACUUM-COST-LIMIT"><span class="term"><code class="varname">autovacuum_vacuum_cost_limit</code> (<code class="type">integer</code>)
182 <a id="id-1.6.6.13.4.3.15.1.3" class="indexterm"></a>
183 </span> <a href="#GUC-AUTOVACUUM-VACUUM-COST-LIMIT" class="id_link">#</a></dt><dd><p>
184 Specifies the cost limit value that will be used in automatic
185 <code class="command">VACUUM</code> operations. If <code class="literal">-1</code>
186 is specified (which is the default), the regular
187 <a class="xref" href="runtime-config-vacuum.html#GUC-VACUUM-COST-LIMIT">vacuum_cost_limit</a> value will be used. Note that
188 the value is distributed proportionally among the running autovacuum
189 workers, if there is more than one, so that the sum of the limits for
190 each worker does not exceed the value of this variable.
191 This parameter can only be set in the <code class="filename">postgresql.conf</code>
192 file or on the server command line;
193 but the setting can be overridden for individual tables by
194 changing table storage parameters.
195 </p></dd></dl></div></div><div class="sect2" id="RUNTIME-CONFIG-RESOURCE-VACUUM-COST"><div class="titlepage"><div><div><h3 class="title">19.10.2. Cost-based Vacuum Delay <a href="#RUNTIME-CONFIG-RESOURCE-VACUUM-COST" class="id_link">#</a></h3></div></div></div><p>
196 During the execution of <a class="xref" href="sql-vacuum.html" title="VACUUM"><span class="refentrytitle">VACUUM</span></a>
197 and <a class="xref" href="sql-analyze.html" title="ANALYZE"><span class="refentrytitle">ANALYZE</span></a>
198 commands, the system maintains an
199 internal counter that keeps track of the estimated cost of the
200 various I/O operations that are performed. When the accumulated
201 cost reaches a limit (specified by
202 <code class="varname">vacuum_cost_limit</code>), the process performing
203 the operation will sleep for a short period of time, as specified by
204 <code class="varname">vacuum_cost_delay</code>. Then it will reset the
205 counter and continue execution.
207 The intent of this feature is to allow administrators to reduce
208 the I/O impact of these commands on concurrent database
209 activity. There are many situations where it is not
210 important that maintenance commands like
211 <code class="command">VACUUM</code> and <code class="command">ANALYZE</code> finish
212 quickly; however, it is usually very important that these
213 commands do not significantly interfere with the ability of the
214 system to perform other database operations. Cost-based vacuum
215 delay provides a way for administrators to achieve this.
217 This feature is disabled by default for manually issued
218 <code class="command">VACUUM</code> commands. To enable it, set the
219 <code class="varname">vacuum_cost_delay</code> variable to a nonzero
221 </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>)
222 <a id="id-1.6.6.13.5.5.1.1.3" class="indexterm"></a>
223 </span> <a href="#GUC-VACUUM-COST-DELAY" class="id_link">#</a></dt><dd><p>
224 The amount of time that the process will sleep when the cost
225 limit has been exceeded. If this value is specified without
226 units, it is taken as milliseconds. The default value is
227 <code class="literal">0</code>, which disables the cost-based vacuum delay
228 feature. Positive values enable cost-based vacuuming.
230 When using cost-based vacuuming, appropriate values for
231 <code class="varname">vacuum_cost_delay</code> are usually quite small, perhaps
232 less than 1 millisecond. While <code class="varname">vacuum_cost_delay</code>
233 can be set to fractional-millisecond values, such delays may not be
234 measured accurately on older platforms. On such platforms,
235 increasing <code class="command">VACUUM</code>'s throttled resource consumption
236 above what you get at 1ms will require changing the other vacuum cost
237 parameters. You should, nonetheless,
238 keep <code class="varname">vacuum_cost_delay</code> as small as your platform
239 will consistently measure; large delays are not helpful.
240 </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>)
241 <a id="id-1.6.6.13.5.5.2.1.3" class="indexterm"></a>
242 </span> <a href="#GUC-VACUUM-COST-PAGE-HIT" class="id_link">#</a></dt><dd><p>
243 The estimated cost for vacuuming a buffer found in the shared
244 buffer cache. It represents the cost to lock the buffer pool,
245 lookup the shared hash table and scan the content of the page.
246 The default value is <code class="literal">1</code>.
247 </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>)
248 <a id="id-1.6.6.13.5.5.3.1.3" class="indexterm"></a>
249 </span> <a href="#GUC-VACUUM-COST-PAGE-MISS" class="id_link">#</a></dt><dd><p>
250 The estimated cost for vacuuming a buffer that has to be read from
251 disk. This represents the effort to lock the buffer pool,
252 lookup the shared hash table, read the desired block in from
253 the disk and scan its content. The default value is
254 <code class="literal">2</code>.
255 </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>)
256 <a id="id-1.6.6.13.5.5.4.1.3" class="indexterm"></a>
257 </span> <a href="#GUC-VACUUM-COST-PAGE-DIRTY" class="id_link">#</a></dt><dd><p>
258 The estimated cost charged when vacuum modifies a block that was
259 previously clean. It represents the extra I/O required to
260 flush the dirty block out to disk again. The default value is
261 <code class="literal">20</code>.
262 </p></dd><dt id="GUC-VACUUM-COST-LIMIT"><span class="term"><code class="varname">vacuum_cost_limit</code> (<code class="type">integer</code>)
263 <a id="id-1.6.6.13.5.5.5.1.3" class="indexterm"></a>
264 </span> <a href="#GUC-VACUUM-COST-LIMIT" class="id_link">#</a></dt><dd><p>
265 This is the accumulated cost that will cause the vacuuming
266 process to sleep for <code class="varname">vacuum_cost_delay</code>. The
267 default is <code class="literal">200</code>.
268 </p></dd></dl></div><div class="note"><h3 class="title">Note</h3><p>
269 There are certain operations that hold critical locks and should
270 therefore complete as quickly as possible. Cost-based vacuum
271 delays do not occur during such operations. Therefore it is
272 possible that the cost accumulates far higher than the specified
273 limit. To avoid uselessly long delays in such cases, the actual
274 delay is calculated as <code class="varname">vacuum_cost_delay</code> *
275 <code class="varname">accumulated_balance</code> /
276 <code class="varname">vacuum_cost_limit</code> with a maximum of
277 <code class="varname">vacuum_cost_delay</code> * 4.
278 </p></div></div><div class="sect2" id="RUNTIME-CONFIG-VACUUM-DEFAULT"><div class="titlepage"><div><div><h3 class="title">19.10.3. Default Behavior <a href="#RUNTIME-CONFIG-VACUUM-DEFAULT" class="id_link">#</a></h3></div></div></div><div class="variablelist"><dl class="variablelist"><dt id="GUC-VACUUM-TRUNCATE"><span class="term"><code class="varname">vacuum_truncate</code> (<code class="type">boolean</code>)
279 <a id="id-1.6.6.13.6.2.1.1.3" class="indexterm"></a>
280 </span> <a href="#GUC-VACUUM-TRUNCATE" class="id_link">#</a></dt><dd><p>
281 Enables or disables vacuum to try to truncate off any empty pages at
282 the end of the table. The default value is <code class="literal">true</code>.
283 If <code class="literal">true</code>, <code class="command">VACUUM</code> and autovacuum
284 do the truncation and the disk space for the truncated pages is
285 returned to the operating system. Note that the truncation requires
286 an <code class="literal">ACCESS EXCLUSIVE</code> lock on the table. The
287 <code class="literal">TRUNCATE</code> parameter of
288 <a class="link" href="sql-vacuum.html" title="VACUUM"><code class="command">VACUUM</code></a>, if
289 specified, overrides the value of this parameter. The setting can
290 also be overridden for individual tables by changing table storage
292 </p></dd></dl></div></div><div class="sect2" id="RUNTIME-CONFIG-VACUUM-FREEZING"><div class="titlepage"><div><div><h3 class="title">19.10.4. Freezing <a href="#RUNTIME-CONFIG-VACUUM-FREEZING" class="id_link">#</a></h3></div></div></div><p>
293 To maintain correctness even after transaction IDs wrap around,
294 <span class="productname">PostgreSQL</span> marks rows that are sufficiently
295 old as <span class="emphasis"><em>frozen</em></span>. These rows are visible to everyone;
296 other transactions do not need to examine their inserting XID to
297 determine visibility. <code class="command">VACUUM</code> is responsible for
298 marking rows as frozen. The following settings control
299 <code class="command">VACUUM</code>'s freezing behavior and should be tuned based
300 on the XID consumption rate of the system and data access patterns of the
301 dominant workloads. See <a class="xref" href="routine-vacuuming.html#VACUUM-FOR-WRAPAROUND" title="24.1.5. Preventing Transaction ID Wraparound Failures">Section 24.1.5</a> for more
302 information on transaction ID wraparound and tuning these parameters.
303 </p><div class="variablelist"><dl class="variablelist"><dt id="GUC-VACUUM-FREEZE-TABLE-AGE"><span class="term"><code class="varname">vacuum_freeze_table_age</code> (<code class="type">integer</code>)
304 <a id="id-1.6.6.13.7.3.1.1.3" class="indexterm"></a>
305 </span> <a href="#GUC-VACUUM-FREEZE-TABLE-AGE" class="id_link">#</a></dt><dd><p>
306 <code class="command">VACUUM</code> performs an aggressive scan if the table's
307 <code class="structname">pg_class</code>.<code class="structfield">relfrozenxid</code> field has reached
308 the age specified by this setting. An aggressive scan differs from
309 a regular <code class="command">VACUUM</code> in that it visits every page that might
310 contain unfrozen XIDs or MXIDs, not just those that might contain dead
311 tuples. The default is 150 million transactions. Although users can
312 set this value anywhere from zero to two billion, <code class="command">VACUUM</code>
313 will silently limit the effective value to 95% of
314 <a class="xref" href="runtime-config-vacuum.html#GUC-AUTOVACUUM-FREEZE-MAX-AGE">autovacuum_freeze_max_age</a>, so that a
315 periodic manual <code class="command">VACUUM</code> has a chance to run before an
316 anti-wraparound autovacuum is launched for the table. For more
318 <a class="xref" href="routine-vacuuming.html#VACUUM-FOR-WRAPAROUND" title="24.1.5. Preventing Transaction ID Wraparound Failures">Section 24.1.5</a>.
319 </p></dd><dt id="GUC-VACUUM-FREEZE-MIN-AGE"><span class="term"><code class="varname">vacuum_freeze_min_age</code> (<code class="type">integer</code>)
320 <a id="id-1.6.6.13.7.3.2.1.3" class="indexterm"></a>
321 </span> <a href="#GUC-VACUUM-FREEZE-MIN-AGE" class="id_link">#</a></dt><dd><p>
322 Specifies the cutoff age (in transactions) that
323 <code class="command">VACUUM</code> should use to decide whether to
324 trigger freezing of pages that have an older XID.
325 The default is 50 million transactions. Although
326 users can set this value anywhere from zero to one billion,
327 <code class="command">VACUUM</code> will silently limit the effective value to half
328 the value of <a class="xref" href="runtime-config-vacuum.html#GUC-AUTOVACUUM-FREEZE-MAX-AGE">autovacuum_freeze_max_age</a>, so
329 that there is not an unreasonably short time between forced
330 autovacuums. For more information see <a class="xref" href="routine-vacuuming.html#VACUUM-FOR-WRAPAROUND" title="24.1.5. Preventing Transaction ID Wraparound Failures">Section 24.1.5</a>.
331 </p></dd><dt id="GUC-VACUUM-FAILSAFE-AGE"><span class="term"><code class="varname">vacuum_failsafe_age</code> (<code class="type">integer</code>)
332 <a id="id-1.6.6.13.7.3.3.1.3" class="indexterm"></a>
333 </span> <a href="#GUC-VACUUM-FAILSAFE-AGE" class="id_link">#</a></dt><dd><p>
334 Specifies the maximum age (in transactions) that a table's
335 <code class="structname">pg_class</code>.<code class="structfield">relfrozenxid</code>
336 field can attain before <code class="command">VACUUM</code> takes
337 extraordinary measures to avoid system-wide transaction ID
338 wraparound failure. This is <code class="command">VACUUM</code>'s
339 strategy of last resort. The failsafe typically triggers
340 when an autovacuum to prevent transaction ID wraparound has
341 already been running for some time, though it's possible for
342 the failsafe to trigger during any <code class="command">VACUUM</code>.
344 When the failsafe is triggered, any cost-based delay that is
345 in effect will no longer be applied, further non-essential
346 maintenance tasks (such as index vacuuming) are bypassed, and any
347 <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>
348 in use will be disabled resulting in <code class="command">VACUUM</code> being
349 free to make use of all of
350 <a class="glossterm" href="glossary.html#GLOSSARY-SHARED-MEMORY"><em class="glossterm"><a class="glossterm" href="glossary.html#GLOSSARY-SHARED-MEMORY" title="Shared memory">shared buffers</a></em></a>.
352 The default is 1.6 billion transactions. Although users can
353 set this value anywhere from zero to 2.1 billion,
354 <code class="command">VACUUM</code> will silently adjust the effective
355 value to no less than 105% of <a class="xref" href="runtime-config-vacuum.html#GUC-AUTOVACUUM-FREEZE-MAX-AGE">autovacuum_freeze_max_age</a>.
356 </p></dd><dt id="GUC-VACUUM-MULTIXACT-FREEZE-TABLE-AGE"><span class="term"><code class="varname">vacuum_multixact_freeze_table_age</code> (<code class="type">integer</code>)
357 <a id="id-1.6.6.13.7.3.4.1.3" class="indexterm"></a>
358 </span> <a href="#GUC-VACUUM-MULTIXACT-FREEZE-TABLE-AGE" class="id_link">#</a></dt><dd><p>
359 <code class="command">VACUUM</code> performs an aggressive scan if the table's
360 <code class="structname">pg_class</code>.<code class="structfield">relminmxid</code> field has reached
361 the age specified by this setting. An aggressive scan differs from
362 a regular <code class="command">VACUUM</code> in that it visits every page that might
363 contain unfrozen XIDs or MXIDs, not just those that might contain dead
364 tuples. The default is 150 million multixacts.
365 Although users can set this value anywhere from zero to two billion,
366 <code class="command">VACUUM</code> will silently limit the effective value to 95% of
367 <a class="xref" href="runtime-config-vacuum.html#GUC-AUTOVACUUM-MULTIXACT-FREEZE-MAX-AGE">autovacuum_multixact_freeze_max_age</a>, so that a
368 periodic manual <code class="command">VACUUM</code> has a chance to run before an
369 anti-wraparound is launched for the table.
370 For more information see <a class="xref" href="routine-vacuuming.html#VACUUM-FOR-MULTIXACT-WRAPAROUND" title="24.1.5.1. Multixacts and Wraparound">Section 24.1.5.1</a>.
371 </p></dd><dt id="GUC-VACUUM-MULTIXACT-FREEZE-MIN-AGE"><span class="term"><code class="varname">vacuum_multixact_freeze_min_age</code> (<code class="type">integer</code>)
372 <a id="id-1.6.6.13.7.3.5.1.3" class="indexterm"></a>
373 </span> <a href="#GUC-VACUUM-MULTIXACT-FREEZE-MIN-AGE" class="id_link">#</a></dt><dd><p>
374 Specifies the cutoff age (in multixacts) that <code class="command">VACUUM</code>
375 should use to decide whether to trigger freezing of pages with
376 an older multixact ID. The default is 5 million multixacts.
377 Although users can set this value anywhere from zero to one billion,
378 <code class="command">VACUUM</code> will silently limit the effective value to half
379 the value of <a class="xref" href="runtime-config-vacuum.html#GUC-AUTOVACUUM-MULTIXACT-FREEZE-MAX-AGE">autovacuum_multixact_freeze_max_age</a>,
380 so that there is not an unreasonably short time between forced
382 For more information see <a class="xref" href="routine-vacuuming.html#VACUUM-FOR-MULTIXACT-WRAPAROUND" title="24.1.5.1. Multixacts and Wraparound">Section 24.1.5.1</a>.
383 </p></dd><dt id="GUC-VACUUM-MULTIXACT-FAILSAFE-AGE"><span class="term"><code class="varname">vacuum_multixact_failsafe_age</code> (<code class="type">integer</code>)
384 <a id="id-1.6.6.13.7.3.6.1.3" class="indexterm"></a>
385 </span> <a href="#GUC-VACUUM-MULTIXACT-FAILSAFE-AGE" class="id_link">#</a></dt><dd><p>
386 Specifies the maximum age (in multixacts) that a table's
387 <code class="structname">pg_class</code>.<code class="structfield">relminmxid</code>
388 field can attain before <code class="command">VACUUM</code> takes
389 extraordinary measures to avoid system-wide multixact ID
390 wraparound failure. This is <code class="command">VACUUM</code>'s
391 strategy of last resort. The failsafe typically triggers when
392 an autovacuum to prevent transaction ID wraparound has already
393 been running for some time, though it's possible for the
394 failsafe to trigger during any <code class="command">VACUUM</code>.
396 When the failsafe is triggered, any cost-based delay that is
397 in effect will no longer be applied, and further non-essential
398 maintenance tasks (such as index vacuuming) are bypassed.
400 The default is 1.6 billion multixacts. Although users can set
401 this value anywhere from zero to 2.1 billion,
402 <code class="command">VACUUM</code> will silently adjust the effective
403 value to no less than 105% of <a class="xref" href="runtime-config-vacuum.html#GUC-AUTOVACUUM-MULTIXACT-FREEZE-MAX-AGE">autovacuum_multixact_freeze_max_age</a>.
404 </p></dd><dt id="GUC-VACUUM-MAX-EAGER-FREEZE-FAILURE-RATE"><span class="term"><code class="varname">vacuum_max_eager_freeze_failure_rate</code> (<code class="type">floating point</code>)
405 <a id="id-1.6.6.13.7.3.7.1.3" class="indexterm"></a>
406 </span> <a href="#GUC-VACUUM-MAX-EAGER-FREEZE-FAILURE-RATE" class="id_link">#</a></dt><dd><p>
407 Specifies the maximum number of pages (as a fraction of total pages in
408 the relation) that <code class="command">VACUUM</code> may scan and
409 <span class="emphasis"><em>fail</em></span> to set all-frozen in the visibility map
410 before disabling eager scanning. A value of <code class="literal">0</code>
411 disables eager scanning altogether. The default is
412 <code class="literal">0.03</code> (3%).
414 Note that when eager scanning is enabled, only freeze failures
415 count against the cap, not successful freezing. Successful page
416 freezes are capped internally at 20% of the all-visible but not
417 all-frozen pages in the relation. Capping successful page freezes helps
418 amortize the overhead across multiple normal vacuums and limits the
419 potential downside of wasted eager freezes of pages that are modified
420 again before the next aggressive vacuum.
422 This parameter can only be set in the
423 <code class="filename">postgresql.conf</code> file or on the server command
424 line; but the setting can be overridden for individual tables by
426 <a class="link" href="sql-createtable.html#RELOPTION-VACUUM-MAX-EAGER-FREEZE-FAILURE-RATE">
427 corresponding table storage parameter</a>.
428 For more information on tuning vacuum's freezing behavior,
429 see <a class="xref" href="routine-vacuuming.html#VACUUM-FOR-WRAPAROUND" title="24.1.5. Preventing Transaction ID Wraparound Failures">Section 24.1.5</a>.
430 </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-statistics.html" title="19.9. Run-time Statistics">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-client.html" title="19.11. Client Connection Defaults">Next</a></td></tr><tr><td width="40%" align="left" valign="top">19.9. Run-time Statistics </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.11. Client Connection Defaults</td></tr></table></div></body></html>
begriffs.com / projects / ai-pg / blob