codekingpro/portable-devtools
114k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>20.10. Automatic 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="20.9. Run-time Statistics" /><link rel="next" href="runtime-config-client.html" title="20.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">20.10. Automatic Vacuuming</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="runtime-config-statistics.html" title="20.9. Run-time Statistics">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="runtime-config.html" title="Chapter 20. Server Configuration">Up</a></td><th width="60%" align="center">Chapter 20. Server Configuration</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="runtime-config-client.html" title="20.11. Client Connection Defaults">Next</a></td></tr></table><hr /></div><div class="sect1" id="RUNTIME-CONFIG-AUTOVACUUM"><div class="titlepage"><div><div><h2 class="title" style="clear: both">20.10. Automatic Vacuuming <a href="#RUNTIME-CONFIG-AUTOVACUUM" class="id_link">#</a></h2></div></div></div><a id="id-1.6.7.13.2" class="indexterm"></a><p>3 These settings control the behavior of the <em class="firstterm">autovacuum</em>4 feature. Refer to <a class="xref" href="routine-vacuuming.html#AUTOVACUUM" title="25.1.6. The Autovacuum Daemon">Section 25.1.6</a> for more information.5 Note that many of these settings can be overridden on a per-table6 basis; see <a class="xref" href="sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS" title="Storage Parameters">Storage Parameters</a>.7 </p><div class="variablelist"><dl class="variablelist"><dt id="GUC-AUTOVACUUM"><span class="term"><code class="varname">autovacuum</code> (<code class="type">boolean</code>)8 <a id="id-1.6.7.13.4.1.1.3" class="indexterm"></a>9 </span> <a href="#GUC-AUTOVACUUM" class="id_link">#</a></dt><dd><p>10 Controls whether the server should run the11 autovacuum launcher daemon. This is on by default; however,12 <a class="xref" href="runtime-config-statistics.html#GUC-TRACK-COUNTS">track_counts</a> must also be enabled for13 autovacuum to work.14 This parameter can only be set in the <code class="filename">postgresql.conf</code>15 file or on the server command line; however, autovacuuming can be16 disabled for individual tables by changing table storage parameters.17 </p><p>18 Note that even when this parameter is disabled, the system19 will launch autovacuum processes if necessary to20 prevent transaction ID wraparound. See <a class="xref" href="routine-vacuuming.html#VACUUM-FOR-WRAPAROUND" title="25.1.5. Preventing Transaction ID Wraparound Failures">Section 25.1.5</a> for more information.21 </p></dd><dt id="GUC-AUTOVACUUM-MAX-WORKERS"><span class="term"><code class="varname">autovacuum_max_workers</code> (<code class="type">integer</code>)22 <a id="id-1.6.7.13.4.2.1.3" class="indexterm"></a>23 </span> <a href="#GUC-AUTOVACUUM-MAX-WORKERS" class="id_link">#</a></dt><dd><p>24 Specifies the maximum number of autovacuum processes (other than the25 autovacuum launcher) that may be running at any one time. The default26 is three. This parameter can only be set at server start.27 </p></dd><dt id="GUC-AUTOVACUUM-NAPTIME"><span class="term"><code class="varname">autovacuum_naptime</code> (<code class="type">integer</code>)28 <a id="id-1.6.7.13.4.3.1.3" class="indexterm"></a>29 </span> <a href="#GUC-AUTOVACUUM-NAPTIME" class="id_link">#</a></dt><dd><p>30 Specifies the minimum delay between autovacuum runs on any given31 database. In each round the daemon examines the32 database and issues <code class="command">VACUUM</code> and <code class="command">ANALYZE</code> commands33 as needed for tables in that database.34 If this value is specified without units, it is taken as seconds.35 The default is one minute (<code class="literal">1min</code>).36 This parameter can only be set in the <code class="filename">postgresql.conf</code>37 file or on the server command line.38 </p></dd><dt id="GUC-AUTOVACUUM-VACUUM-THRESHOLD"><span class="term"><code class="varname">autovacuum_vacuum_threshold</code> (<code class="type">integer</code>)39 <a id="id-1.6.7.13.4.4.1.3" class="indexterm"></a>40 </span> <a href="#GUC-AUTOVACUUM-VACUUM-THRESHOLD" class="id_link">#</a></dt><dd><p>41 Specifies the minimum number of updated or deleted tuples needed42 to trigger a <code class="command">VACUUM</code> in any one table.43 The default is 50 tuples.44 This parameter can only be set in the <code class="filename">postgresql.conf</code>45 file or on the server command line;46 but the setting can be overridden for individual tables by47 changing table storage parameters.48 </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>)49 <a id="id-1.6.7.13.4.5.1.3" class="indexterm"></a>50 </span> <a href="#GUC-AUTOVACUUM-VACUUM-INSERT-THRESHOLD" class="id_link">#</a></dt><dd><p>51 Specifies the number of inserted tuples needed to trigger a52 <code class="command">VACUUM</code> in any one table.53 The default is 1000 tuples. If -1 is specified, autovacuum will not54 trigger a <code class="command">VACUUM</code> operation on any tables based on55 the number of inserts.56 This parameter can only be set in the <code class="filename">postgresql.conf</code>57 file or on the server command line;58 but the setting can be overridden for individual tables by59 changing table storage parameters.60 </p></dd><dt id="GUC-AUTOVACUUM-ANALYZE-THRESHOLD"><span class="term"><code class="varname">autovacuum_analyze_threshold</code> (<code class="type">integer</code>)61 <a id="id-1.6.7.13.4.6.1.3" class="indexterm"></a>62 </span> <a href="#GUC-AUTOVACUUM-ANALYZE-THRESHOLD" class="id_link">#</a></dt><dd><p>63 Specifies the minimum number of inserted, updated or deleted tuples64 needed to trigger an <code class="command">ANALYZE</code> in any one table.65 The default is 50 tuples.66 This parameter can only be set in the <code class="filename">postgresql.conf</code>67 file or on the server command line;68 but the setting can be overridden for individual tables by69 changing table storage parameters.70 </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>)71 <a id="id-1.6.7.13.4.7.1.3" class="indexterm"></a>72 </span> <a href="#GUC-AUTOVACUUM-VACUUM-SCALE-FACTOR" class="id_link">#</a></dt><dd><p>73 Specifies a fraction of the table size to add to74 <code class="varname">autovacuum_vacuum_threshold</code>75 when deciding whether to trigger a <code class="command">VACUUM</code>.76 The default is 0.2 (20% of table size).77 This parameter can only be set in the <code class="filename">postgresql.conf</code>78 file or on the server command line;79 but the setting can be overridden for individual tables by80 changing table storage parameters.81 </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>)82 <a id="id-1.6.7.13.4.8.1.3" class="indexterm"></a>83 </span> <a href="#GUC-AUTOVACUUM-VACUUM-INSERT-SCALE-FACTOR" class="id_link">#</a></dt><dd><p>84 Specifies a fraction of the table size to add to85 <code class="varname">autovacuum_vacuum_insert_threshold</code>86 when deciding whether to trigger a <code class="command">VACUUM</code>.87 The default is 0.2 (20% of table size).88 This parameter can only be set in the <code class="filename">postgresql.conf</code>89 file or on the server command line;90 but the setting can be overridden for individual tables by91 changing table storage parameters.92 </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>)93 <a id="id-1.6.7.13.4.9.1.3" class="indexterm"></a>94 </span> <a href="#GUC-AUTOVACUUM-ANALYZE-SCALE-FACTOR" class="id_link">#</a></dt><dd><p>95 Specifies a fraction of the table size to add to96 <code class="varname">autovacuum_analyze_threshold</code>97 when deciding whether to trigger an <code class="command">ANALYZE</code>.98 The default is 0.1 (10% of table size).99 This parameter can only be set in the <code class="filename">postgresql.conf</code>100 file or on the server command line;101 but the setting can be overridden for individual tables by102 changing table storage parameters.103 </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>)104 <a id="id-1.6.7.13.4.10.1.3" class="indexterm"></a>105 </span> <a href="#GUC-AUTOVACUUM-FREEZE-MAX-AGE" class="id_link">#</a></dt><dd><p>106 Specifies the maximum age (in transactions) that a table's107 <code class="structname">pg_class</code>.<code class="structfield">relfrozenxid</code> field can108 attain before a <code class="command">VACUUM</code> operation is forced109 to prevent transaction ID wraparound within the table.110 Note that the system will launch autovacuum processes to111 prevent wraparound even when autovacuum is otherwise disabled.112 </p><p>113 Vacuum also allows removal of old files from the114 <code class="filename">pg_xact</code> subdirectory, which is why the default115 is a relatively low 200 million transactions.116 This parameter can only be set at server start, but the setting117 can be reduced for individual tables by118 changing table storage parameters.119 For more information see <a class="xref" href="routine-vacuuming.html#VACUUM-FOR-WRAPAROUND" title="25.1.5. Preventing Transaction ID Wraparound Failures">Section 25.1.5</a>.120 </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>)121 <a id="id-1.6.7.13.4.11.1.3" class="indexterm"></a>122 </span> <a href="#GUC-AUTOVACUUM-MULTIXACT-FREEZE-MAX-AGE" class="id_link">#</a></dt><dd><p>123 Specifies the maximum age (in multixacts) that a table's124 <code class="structname">pg_class</code>.<code class="structfield">relminmxid</code> field can125 attain before a <code class="command">VACUUM</code> operation is forced to126 prevent multixact ID wraparound within the table.127 Note that the system will launch autovacuum processes to128 prevent wraparound even when autovacuum is otherwise disabled.129 </p><p>130 Vacuuming multixacts also allows removal of old files from the131 <code class="filename">pg_multixact/members</code> and <code class="filename">pg_multixact/offsets</code>132 subdirectories, which is why the default is a relatively low133 400 million multixacts.134 This parameter can only be set at server start, but the setting can135 be reduced for individual tables by changing table storage parameters.136 For more information see <a class="xref" href="routine-vacuuming.html#VACUUM-FOR-MULTIXACT-WRAPAROUND" title="25.1.5.1. Multixacts and Wraparound">Section 25.1.5.1</a>.137 </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>)138 <a id="id-1.6.7.13.4.12.1.3" class="indexterm"></a>139 </span> <a href="#GUC-AUTOVACUUM-VACUUM-COST-DELAY" class="id_link">#</a></dt><dd><p>140 Specifies the cost delay value that will be used in automatic141 <code class="command">VACUUM</code> operations. If -1 is specified, the regular142 <a class="xref" href="runtime-config-resource.html#GUC-VACUUM-COST-DELAY">vacuum_cost_delay</a> value will be used.143 If this value is specified without units, it is taken as milliseconds.144 The default value is 2 milliseconds.145 This parameter can only be set in the <code class="filename">postgresql.conf</code>146 file or on the server command line;147 but the setting can be overridden for individual tables by148 changing table storage parameters.149 </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>)150 <a id="id-1.6.7.13.4.13.1.3" class="indexterm"></a>151 </span> <a href="#GUC-AUTOVACUUM-VACUUM-COST-LIMIT" class="id_link">#</a></dt><dd><p>152 Specifies the cost limit value that will be used in automatic153 <code class="command">VACUUM</code> operations. If -1 is specified (which is the154 default), the regular155 <a class="xref" href="runtime-config-resource.html#GUC-VACUUM-COST-LIMIT">vacuum_cost_limit</a> value will be used. Note that156 the value is distributed proportionally among the running autovacuum157 workers, if there is more than one, so that the sum of the limits for158 each worker does not exceed the value of this variable.159 This parameter can only be set in the <code class="filename">postgresql.conf</code>160 file or on the server command line;161 but the setting can be overridden for individual tables by162 changing table storage parameters.163 </p></dd></dl></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="20.9. Run-time Statistics">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="runtime-config.html" title="Chapter 20. Server Configuration">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="runtime-config-client.html" title="20.11. Client Connection Defaults">Next</a></td></tr><tr><td width="40%" align="left" valign="top">20.9. Run-time Statistics </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 20.11. Client Connection Defaults</td></tr></table></div></body></html>