Moving the vacuum GUCs' docs out of the Client Connection Defaults section
Started by Melanie Plagemanabout 1 year ago17 messages
I was reviewing all the vacuum related GUCs, and I noticed that they
fall into three main subsections of Chapter 19 (Server Configuration)
in the docs [1]https://www.postgresql.org/docs/17/runtime-config.html: Automatic Vacuuming [2]https://www.postgresql.org/docs/17/runtime-config-autovacuum.html, Resource Consumption [3]https://www.postgresql.org/docs/17/runtime-config-resource.html,
and Client Connection Defaults [4]https://www.postgresql.org/docs/17/runtime-config-client.html. The last one I find pretty
confusing.
vacuum_freeze_min_age, vacuum_freeze_table_age, vacuum_failsafe_age
and their multixact equivalents are all in the Statement Behavior
subsection of the Client Connection Defaults subsection. I could maybe
see a justification for this if these GUCs only affected VACUUM
statements -- but that's not the case. All of these GUCs affect the
behavior of both manually invoked vacuums and autovacuums (with some
caveats about precedence when equivalent table storage parameters are
specified).
But maybe there is some other reason they are documented there that I
am missing.
If not, maybe we should fix it?
I'm not totally sure what the solution should be. Perhaps we rename
the "Automatic Vacuuming" subsection "Vacuuming" and then make
"Automatic Vacuuming" a sub-subsection? And move the vacuum-related
GUCs from client connection defaults to "Vacuuming"?
- Melanie
[1]: https://www.postgresql.org/docs/17/runtime-config.html
[2]: https://www.postgresql.org/docs/17/runtime-config-autovacuum.html
[3]: https://www.postgresql.org/docs/17/runtime-config-resource.html
[4]: https://www.postgresql.org/docs/17/runtime-config-client.html
Re: Moving the vacuum GUCs' docs out of the Client Connection Defaults section
Melanie Plageman <melanieplageman@gmail.com> writes:
I was reviewing all the vacuum related GUCs, and I noticed that they
fall into three main subsections of Chapter 19 (Server Configuration)
in the docs [1]: Automatic Vacuuming [2], Resource Consumption [3],
and Client Connection Defaults [4]. The last one I find pretty
confusing.
Yeah, it's a mess. It sounds good to consolidate all of those under
a top-level Vacuuming section.
regards, tom lane
Re: Moving the vacuum GUCs' docs out of the Client Connection Defaults section
On Mon, Jan 6, 2025 at 8:26 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Melanie Plageman <melanieplageman@gmail.com> writes:
I was reviewing all the vacuum related GUCs, and I noticed that they
fall into three main subsections of Chapter 19 (Server Configuration)
in the docs [1]: Automatic Vacuuming [2], Resource Consumption [3],
and Client Connection Defaults [4]. The last one I find pretty
confusing.Yeah, it's a mess. It sounds good to consolidate all of those under
a top-level Vacuuming section.
Cool, I've attached a patch to do this. I left a few of the GUCs under
Resource Consumption (like autovacuum_work_mem and
vacuum_buffer_usage_limit) where they are because it seemed
appropriate.
This is my first docs patch that introduces new sections and such, so
I'm not sure I got the indentation 100% correct (I, of course, tried
to follow conventions).
- Melanie
Attachments:
v1-0001-Consolidate-docs-for-vacuum-related-GUCs-in-new-s.patchtext/x-patch; charset=US-ASCII; name=v1-0001-Consolidate-docs-for-vacuum-related-GUCs-in-new-s.patchDownload
From 26ecb39b4b065ae466b9edd074a99d9d28fca476 Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplageman@gmail.com>
Date: Tue, 7 Jan 2025 11:50:28 -0500
Subject: [PATCH v1] Consolidate docs for vacuum-related GUCs in new subsection
GUCs related to vacuum's freezing behavior were documented in a
subsection of the Client Connection Defaults documentation. These GUCs
don't belong there, as they affect the freezing behavior of all vacuums
-- including autovacuums.
There wasn't a clear alternative location, so this commit makes a new
Server Configuration docs subsection, "Vacuuming", with a subsection for
"Freezing". It also moves the "Automatic Vacuuming" subsection and the
docs on GUCs controlling cost-based vacuum delay under the new
"Vacuuming" subsection.
The other vacuum-related GUCs under the "Resource Consumption"
subsection have been left in their current location, as they seem to fit
there.
Discussion: https://postgr.es/m/flat/1373018.1736213217%40sss.pgh.pa.us#105c713a7966f87e4ac4301246e3cabe
---
doc/src/sgml/config.sgml | 1237 +++++++++++++++++++-------------------
1 file changed, 628 insertions(+), 609 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 740ff5d5044..3b2430eff55 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -2367,149 +2367,6 @@ include_dir 'conf.d'
</variablelist>
</sect2>
- <sect2 id="runtime-config-resource-vacuum-cost">
- <title>Cost-based Vacuum Delay</title>
-
- <para>
- During the execution of <xref linkend="sql-vacuum"/>
- and <xref linkend="sql-analyze"/>
- commands, the system maintains an
- internal counter that keeps track of the estimated cost of the
- various I/O operations that are performed. When the accumulated
- cost reaches a limit (specified by
- <varname>vacuum_cost_limit</varname>), the process performing
- the operation will sleep for a short period of time, as specified by
- <varname>vacuum_cost_delay</varname>. Then it will reset the
- counter and continue execution.
- </para>
-
- <para>
- The intent of this feature is to allow administrators to reduce
- the I/O impact of these commands on concurrent database
- activity. There are many situations where it is not
- important that maintenance commands like
- <command>VACUUM</command> and <command>ANALYZE</command> finish
- quickly; however, it is usually very important that these
- commands do not significantly interfere with the ability of the
- system to perform other database operations. Cost-based vacuum
- delay provides a way for administrators to achieve this.
- </para>
-
- <para>
- This feature is disabled by default for manually issued
- <command>VACUUM</command> commands. To enable it, set the
- <varname>vacuum_cost_delay</varname> variable to a nonzero
- value.
- </para>
-
- <variablelist>
- <varlistentry id="guc-vacuum-cost-delay" xreflabel="vacuum_cost_delay">
- <term><varname>vacuum_cost_delay</varname> (<type>floating point</type>)
- <indexterm>
- <primary><varname>vacuum_cost_delay</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- The amount of time that the process will sleep
- when the cost limit has been exceeded.
- If this value is specified without units, it is taken as milliseconds.
- The default value is zero, which disables the cost-based vacuum
- delay feature. Positive values enable cost-based vacuuming.
- </para>
-
- <para>
- When using cost-based vacuuming, appropriate values for
- <varname>vacuum_cost_delay</varname> are usually quite small, perhaps
- less than 1 millisecond. While <varname>vacuum_cost_delay</varname>
- can be set to fractional-millisecond values, such delays may not be
- measured accurately on older platforms. On such platforms,
- increasing <command>VACUUM</command>'s throttled resource consumption
- above what you get at 1ms will require changing the other vacuum cost
- parameters. You should, nonetheless,
- keep <varname>vacuum_cost_delay</varname> as small as your platform
- will consistently measure; large delays are not helpful.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="guc-vacuum-cost-page-hit" xreflabel="vacuum_cost_page_hit">
- <term><varname>vacuum_cost_page_hit</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>vacuum_cost_page_hit</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- The estimated cost for vacuuming a buffer found in the shared buffer
- cache. It represents the cost to lock the buffer pool, lookup
- the shared hash table and scan the content of the page. The
- default value is one.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="guc-vacuum-cost-page-miss" xreflabel="vacuum_cost_page_miss">
- <term><varname>vacuum_cost_page_miss</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>vacuum_cost_page_miss</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- The estimated cost for vacuuming a buffer that has to be read from
- disk. This represents the effort to lock the buffer pool,
- lookup the shared hash table, read the desired block in from
- the disk and scan its content. The default value is 2.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="guc-vacuum-cost-page-dirty" xreflabel="vacuum_cost_page_dirty">
- <term><varname>vacuum_cost_page_dirty</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>vacuum_cost_page_dirty</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- The estimated cost charged when vacuum modifies a block that was
- previously clean. It represents the extra I/O required to
- flush the dirty block out to disk again. The default value is
- 20.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="guc-vacuum-cost-limit" xreflabel="vacuum_cost_limit">
- <term><varname>vacuum_cost_limit</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>vacuum_cost_limit</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- This is the accumulated cost that will cause the vacuuming process to sleep
- for <varname>vacuum_cost_delay</varname>. The default is 200.
- </para>
- </listitem>
- </varlistentry>
- </variablelist>
-
- <note>
- <para>
- There are certain operations that hold critical locks and should
- therefore complete as quickly as possible. Cost-based vacuum
- delays do not occur during such operations. Therefore it is
- possible that the cost accumulates far higher than the specified
- limit. To avoid uselessly long delays in such cases, the actual
- delay is calculated as <varname>vacuum_cost_delay</varname> *
- <varname>accumulated_balance</varname> /
- <varname>vacuum_cost_limit</varname> with a maximum of
- <varname>vacuum_cost_delay</varname> * 4.
- </para>
- </note>
- </sect2>
<sect2 id="runtime-config-resource-background-writer">
<title>Background Writer</title>
@@ -8588,14 +8445,17 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
</sect2>
</sect1>
- <sect1 id="runtime-config-autovacuum">
- <title>Automatic Vacuuming</title>
+ <sect1 id="runtime-config-vacuum">
+ <title>Vacuuming</title>
<indexterm>
- <primary>autovacuum</primary>
+ <primary>vacuum</primary>
<secondary>configuration parameters</secondary>
</indexterm>
+ <sect2 id="runtime-config-autovacuum">
+ <title>Automatic Vacuuming</title>
+
<para>
These settings control the behavior of the <firstterm>autovacuum</firstterm>
feature. Refer to <xref linkend="autovacuum"/> for more information.
@@ -8603,324 +8463,645 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
basis; see <xref linkend="sql-createtable-storage-parameters"/>.
</para>
- <variablelist>
+ <variablelist>
- <varlistentry id="guc-autovacuum" xreflabel="autovacuum">
- <term><varname>autovacuum</varname> (<type>boolean</type>)
- <indexterm>
- <primary><varname>autovacuum</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Controls whether the server should run the
- autovacuum launcher daemon. This is on by default; however,
- <xref linkend="guc-track-counts"/> must also be enabled for
- autovacuum to work.
- This parameter can only be set in the <filename>postgresql.conf</filename>
- file or on the server command line; however, autovacuuming can be
- disabled for individual tables by changing table storage parameters.
- </para>
- <para>
- Note that even when this parameter is disabled, the system
- will launch autovacuum processes if necessary to
- prevent transaction ID wraparound. See <xref
- linkend="vacuum-for-wraparound"/> for more information.
- </para>
- </listitem>
- </varlistentry>
+ <varlistentry id="guc-autovacuum" xreflabel="autovacuum">
+ <term><varname>autovacuum</varname> (<type>boolean</type>)
+ <indexterm>
+ <primary><varname>autovacuum</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Controls whether the server should run the
+ autovacuum launcher daemon. This is on by default; however,
+ <xref linkend="guc-track-counts"/> must also be enabled for
+ autovacuum to work.
+ This parameter can only be set in the <filename>postgresql.conf</filename>
+ file or on the server command line; however, autovacuuming can be
+ disabled for individual tables by changing table storage parameters.
+ </para>
+ <para>
+ Note that even when this parameter is disabled, the system
+ will launch autovacuum processes if necessary to
+ prevent transaction ID wraparound. See <xref
+ linkend="vacuum-for-wraparound"/> for more information.
+ </para>
+ </listitem>
+ </varlistentry>
- <varlistentry id="guc-autovacuum-worker-slots" xreflabel="autovacuum_worker_slots">
- <term><varname>autovacuum_worker_slots</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>autovacuum_worker_slots</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the number of backend slots to reserve for autovacuum worker
- processes. The default is 16. This parameter can only be set at server
- start.
- </para>
- <para>
- When changing this value, consider also adjusting
- <xref linkend="guc-autovacuum-max-workers"/>.
- </para>
- </listitem>
- </varlistentry>
+ <varlistentry id="guc-autovacuum-worker-slots" xreflabel="autovacuum_worker_slots">
+ <term><varname>autovacuum_worker_slots</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>autovacuum_worker_slots</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the number of backend slots to reserve for autovacuum worker
+ processes. The default is 16. This parameter can only be set at server
+ start.
+ </para>
+ <para>
+ When changing this value, consider also adjusting
+ <xref linkend="guc-autovacuum-max-workers"/>.
+ </para>
+ </listitem>
+ </varlistentry>
- <varlistentry id="guc-autovacuum-max-workers" xreflabel="autovacuum_max_workers">
- <term><varname>autovacuum_max_workers</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>autovacuum_max_workers</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the maximum number of autovacuum processes (other than the
- autovacuum launcher) that may be running at any one time. The default
- is three. This parameter can only be set in the
- <filename>postgresql.conf</filename> file or on the server command line.
- </para>
- <para>
- Note that a setting for this value which is higher than
- <xref linkend="guc-autovacuum-worker-slots"/> will have no effect,
- since autovacuum workers are taken from the pool of slots established
- by that setting.
- </para>
- </listitem>
- </varlistentry>
+ <varlistentry id="guc-autovacuum-max-workers" xreflabel="autovacuum_max_workers">
+ <term><varname>autovacuum_max_workers</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>autovacuum_max_workers</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the maximum number of autovacuum processes (other than the
+ autovacuum launcher) that may be running at any one time. The default
+ is three. This parameter can only be set in the
+ <filename>postgresql.conf</filename> file or on the server command line.
+ </para>
+ <para>
+ Note that a setting for this value which is higher than
+ <xref linkend="guc-autovacuum-worker-slots"/> will have no effect,
+ since autovacuum workers are taken from the pool of slots established
+ by that setting.
+ </para>
+ </listitem>
+ </varlistentry>
- <varlistentry id="guc-autovacuum-naptime" xreflabel="autovacuum_naptime">
- <term><varname>autovacuum_naptime</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>autovacuum_naptime</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the minimum delay between autovacuum runs on any given
- database. In each round the daemon examines the
- database and issues <command>VACUUM</command> and <command>ANALYZE</command> commands
- as needed for tables in that database.
- If this value is specified without units, it is taken as seconds.
- The default is one minute (<literal>1min</literal>).
- This parameter can only be set in the <filename>postgresql.conf</filename>
- file or on the server command line.
- </para>
- </listitem>
- </varlistentry>
+ <varlistentry id="guc-autovacuum-naptime" xreflabel="autovacuum_naptime">
+ <term><varname>autovacuum_naptime</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>autovacuum_naptime</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the minimum delay between autovacuum runs on any given
+ database. In each round the daemon examines the
+ database and issues <command>VACUUM</command> and <command>ANALYZE</command> commands
+ as needed for tables in that database.
+ If this value is specified without units, it is taken as seconds.
+ The default is one minute (<literal>1min</literal>).
+ This parameter can only be set in the <filename>postgresql.conf</filename>
+ file or on the server command line.
+ </para>
+ </listitem>
+ </varlistentry>
- <varlistentry id="guc-autovacuum-vacuum-threshold" xreflabel="autovacuum_vacuum_threshold">
- <term><varname>autovacuum_vacuum_threshold</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>autovacuum_vacuum_threshold</varname></primary>
- <secondary>configuration parameter</secondary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the minimum number of updated or deleted tuples needed
- to trigger a <command>VACUUM</command> in any one table.
- The default is 50 tuples.
- This parameter can only be set in the <filename>postgresql.conf</filename>
- file or on the server command line;
- but the setting can be overridden for individual tables by
- changing table storage parameters.
- </para>
- </listitem>
- </varlistentry>
+ <varlistentry id="guc-autovacuum-vacuum-threshold" xreflabel="autovacuum_vacuum_threshold">
+ <term><varname>autovacuum_vacuum_threshold</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>autovacuum_vacuum_threshold</varname></primary>
+ <secondary>configuration parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the minimum number of updated or deleted tuples needed
+ to trigger a <command>VACUUM</command> in any one table.
+ The default is 50 tuples.
+ This parameter can only be set in the <filename>postgresql.conf</filename>
+ file or on the server command line;
+ but the setting can be overridden for individual tables by
+ changing table storage parameters.
+ </para>
+ </listitem>
+ </varlistentry>
- <varlistentry id="guc-autovacuum-vacuum-insert-threshold" xreflabel="autovacuum_vacuum_insert_threshold">
- <term><varname>autovacuum_vacuum_insert_threshold</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>autovacuum_vacuum_insert_threshold</varname></primary>
- <secondary>configuration parameter</secondary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the number of inserted tuples needed to trigger a
- <command>VACUUM</command> in any one table.
- The default is 1000 tuples. If -1 is specified, autovacuum will not
- trigger a <command>VACUUM</command> operation on any tables based on
- the number of inserts.
- This parameter can only be set in the <filename>postgresql.conf</filename>
- file or on the server command line;
- but the setting can be overridden for individual tables by
- changing table storage parameters.
- </para>
- </listitem>
- </varlistentry>
+ <varlistentry id="guc-autovacuum-vacuum-insert-threshold" xreflabel="autovacuum_vacuum_insert_threshold">
+ <term><varname>autovacuum_vacuum_insert_threshold</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>autovacuum_vacuum_insert_threshold</varname></primary>
+ <secondary>configuration parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the number of inserted tuples needed to trigger a
+ <command>VACUUM</command> in any one table.
+ The default is 1000 tuples. If -1 is specified, autovacuum will not
+ trigger a <command>VACUUM</command> operation on any tables based on
+ the number of inserts.
+ This parameter can only be set in the <filename>postgresql.conf</filename>
+ file or on the server command line;
+ but the setting can be overridden for individual tables by
+ changing table storage parameters.
+ </para>
+ </listitem>
+ </varlistentry>
- <varlistentry id="guc-autovacuum-analyze-threshold" xreflabel="autovacuum_analyze_threshold">
- <term><varname>autovacuum_analyze_threshold</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>autovacuum_analyze_threshold</varname></primary>
- <secondary>configuration parameter</secondary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the minimum number of inserted, updated or deleted tuples
- needed to trigger an <command>ANALYZE</command> in any one table.
- The default is 50 tuples.
- This parameter can only be set in the <filename>postgresql.conf</filename>
- file or on the server command line;
- but the setting can be overridden for individual tables by
- changing table storage parameters.
- </para>
- </listitem>
- </varlistentry>
+ <varlistentry id="guc-autovacuum-analyze-threshold" xreflabel="autovacuum_analyze_threshold">
+ <term><varname>autovacuum_analyze_threshold</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>autovacuum_analyze_threshold</varname></primary>
+ <secondary>configuration parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the minimum number of inserted, updated or deleted tuples
+ needed to trigger an <command>ANALYZE</command> in any one table.
+ The default is 50 tuples.
+ This parameter can only be set in the <filename>postgresql.conf</filename>
+ file or on the server command line;
+ but the setting can be overridden for individual tables by
+ changing table storage parameters.
+ </para>
+ </listitem>
+ </varlistentry>
- <varlistentry id="guc-autovacuum-vacuum-scale-factor" xreflabel="autovacuum_vacuum_scale_factor">
- <term><varname>autovacuum_vacuum_scale_factor</varname> (<type>floating point</type>)
- <indexterm>
- <primary><varname>autovacuum_vacuum_scale_factor</varname></primary>
- <secondary>configuration parameter</secondary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies a fraction of the table size to add to
- <varname>autovacuum_vacuum_threshold</varname>
- when deciding whether to trigger a <command>VACUUM</command>.
- The default is 0.2 (20% of table size).
- This parameter can only be set in the <filename>postgresql.conf</filename>
- file or on the server command line;
- but the setting can be overridden for individual tables by
- changing table storage parameters.
- </para>
- </listitem>
- </varlistentry>
+ <varlistentry id="guc-autovacuum-vacuum-scale-factor" xreflabel="autovacuum_vacuum_scale_factor">
+ <term><varname>autovacuum_vacuum_scale_factor</varname> (<type>floating point</type>)
+ <indexterm>
+ <primary><varname>autovacuum_vacuum_scale_factor</varname></primary>
+ <secondary>configuration parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies a fraction of the table size to add to
+ <varname>autovacuum_vacuum_threshold</varname>
+ when deciding whether to trigger a <command>VACUUM</command>.
+ The default is 0.2 (20% of table size).
+ This parameter can only be set in the <filename>postgresql.conf</filename>
+ file or on the server command line;
+ but the setting can be overridden for individual tables by
+ changing table storage parameters.
+ </para>
+ </listitem>
+ </varlistentry>
- <varlistentry id="guc-autovacuum-vacuum-insert-scale-factor" xreflabel="autovacuum_vacuum_insert_scale_factor">
- <term><varname>autovacuum_vacuum_insert_scale_factor</varname> (<type>floating point</type>)
- <indexterm>
- <primary><varname>autovacuum_vacuum_insert_scale_factor</varname></primary>
- <secondary>configuration parameter</secondary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies a fraction of the table size to add to
- <varname>autovacuum_vacuum_insert_threshold</varname>
- when deciding whether to trigger a <command>VACUUM</command>.
- The default is 0.2 (20% of table size).
- This parameter can only be set in the <filename>postgresql.conf</filename>
- file or on the server command line;
- but the setting can be overridden for individual tables by
- changing table storage parameters.
- </para>
- </listitem>
- </varlistentry>
+ <varlistentry id="guc-autovacuum-vacuum-insert-scale-factor" xreflabel="autovacuum_vacuum_insert_scale_factor">
+ <term><varname>autovacuum_vacuum_insert_scale_factor</varname> (<type>floating point</type>)
+ <indexterm>
+ <primary><varname>autovacuum_vacuum_insert_scale_factor</varname></primary>
+ <secondary>configuration parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies a fraction of the table size to add to
+ <varname>autovacuum_vacuum_insert_threshold</varname>
+ when deciding whether to trigger a <command>VACUUM</command>.
+ The default is 0.2 (20% of table size).
+ This parameter can only be set in the <filename>postgresql.conf</filename>
+ file or on the server command line;
+ but the setting can be overridden for individual tables by
+ changing table storage parameters.
+ </para>
+ </listitem>
+ </varlistentry>
- <varlistentry id="guc-autovacuum-analyze-scale-factor" xreflabel="autovacuum_analyze_scale_factor">
- <term><varname>autovacuum_analyze_scale_factor</varname> (<type>floating point</type>)
- <indexterm>
- <primary><varname>autovacuum_analyze_scale_factor</varname></primary>
- <secondary>configuration parameter</secondary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies a fraction of the table size to add to
- <varname>autovacuum_analyze_threshold</varname>
- when deciding whether to trigger an <command>ANALYZE</command>.
- The default is 0.1 (10% of table size).
- This parameter can only be set in the <filename>postgresql.conf</filename>
- file or on the server command line;
- but the setting can be overridden for individual tables by
- changing table storage parameters.
- </para>
- </listitem>
- </varlistentry>
+ <varlistentry id="guc-autovacuum-analyze-scale-factor" xreflabel="autovacuum_analyze_scale_factor">
+ <term><varname>autovacuum_analyze_scale_factor</varname> (<type>floating point</type>)
+ <indexterm>
+ <primary><varname>autovacuum_analyze_scale_factor</varname></primary>
+ <secondary>configuration parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies a fraction of the table size to add to
+ <varname>autovacuum_analyze_threshold</varname>
+ when deciding whether to trigger an <command>ANALYZE</command>.
+ The default is 0.1 (10% of table size).
+ This parameter can only be set in the <filename>postgresql.conf</filename>
+ file or on the server command line;
+ but the setting can be overridden for individual tables by
+ changing table storage parameters.
+ </para>
+ </listitem>
+ </varlistentry>
- <varlistentry id="guc-autovacuum-freeze-max-age" xreflabel="autovacuum_freeze_max_age">
- <term><varname>autovacuum_freeze_max_age</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>autovacuum_freeze_max_age</varname></primary>
- <secondary>configuration parameter</secondary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the maximum age (in transactions) that a table's
- <structname>pg_class</structname>.<structfield>relfrozenxid</structfield> field can
- attain before a <command>VACUUM</command> operation is forced
- to prevent transaction ID wraparound within the table.
- Note that the system will launch autovacuum processes to
- prevent wraparound even when autovacuum is otherwise disabled.
- </para>
+ <varlistentry id="guc-autovacuum-freeze-max-age" xreflabel="autovacuum_freeze_max_age">
+ <term><varname>autovacuum_freeze_max_age</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>autovacuum_freeze_max_age</varname></primary>
+ <secondary>configuration parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the maximum age (in transactions) that a table's
+ <structname>pg_class</structname>.<structfield>relfrozenxid</structfield> field can
+ attain before a <command>VACUUM</command> operation is forced
+ to prevent transaction ID wraparound within the table.
+ Note that the system will launch autovacuum processes to
+ prevent wraparound even when autovacuum is otherwise disabled.
+ </para>
- <para>
- Vacuum also allows removal of old files from the
- <filename>pg_xact</filename> subdirectory, which is why the default
- is a relatively low 200 million transactions.
- This parameter can only be set at server start, but the setting
- can be reduced for individual tables by
- changing table storage parameters.
- For more information see <xref linkend="vacuum-for-wraparound"/>.
- </para>
- </listitem>
- </varlistentry>
+ <para>
+ Vacuum also allows removal of old files from the
+ <filename>pg_xact</filename> subdirectory, which is why the default
+ is a relatively low 200 million transactions.
+ This parameter can only be set at server start, but the setting
+ can be reduced for individual tables by
+ changing table storage parameters.
+ For more information see <xref linkend="vacuum-for-wraparound"/>.
+ </para>
+ </listitem>
+ </varlistentry>
- <varlistentry id="guc-autovacuum-multixact-freeze-max-age" xreflabel="autovacuum_multixact_freeze_max_age">
- <term><varname>autovacuum_multixact_freeze_max_age</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>autovacuum_multixact_freeze_max_age</varname></primary>
- <secondary>configuration parameter</secondary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the maximum age (in multixacts) that a table's
- <structname>pg_class</structname>.<structfield>relminmxid</structfield> field can
- attain before a <command>VACUUM</command> operation is forced to
- prevent multixact ID wraparound within the table.
- Note that the system will launch autovacuum processes to
- prevent wraparound even when autovacuum is otherwise disabled.
- </para>
+ <varlistentry id="guc-autovacuum-multixact-freeze-max-age" xreflabel="autovacuum_multixact_freeze_max_age">
+ <term><varname>autovacuum_multixact_freeze_max_age</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>autovacuum_multixact_freeze_max_age</varname></primary>
+ <secondary>configuration parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the maximum age (in multixacts) that a table's
+ <structname>pg_class</structname>.<structfield>relminmxid</structfield> field can
+ attain before a <command>VACUUM</command> operation is forced to
+ prevent multixact ID wraparound within the table.
+ Note that the system will launch autovacuum processes to
+ prevent wraparound even when autovacuum is otherwise disabled.
+ </para>
- <para>
- Vacuuming multixacts also allows removal of old files from the
- <filename>pg_multixact/members</filename> and <filename>pg_multixact/offsets</filename>
- subdirectories, which is why the default is a relatively low
- 400 million multixacts.
- This parameter can only be set at server start, but the setting can
- be reduced for individual tables by changing table storage parameters.
- For more information see <xref linkend="vacuum-for-multixact-wraparound"/>.
- </para>
- </listitem>
- </varlistentry>
+ <para>
+ Vacuuming multixacts also allows removal of old files from the
+ <filename>pg_multixact/members</filename> and <filename>pg_multixact/offsets</filename>
+ subdirectories, which is why the default is a relatively low
+ 400 million multixacts.
+ This parameter can only be set at server start, but the setting can
+ be reduced for individual tables by changing table storage parameters.
+ For more information see <xref linkend="vacuum-for-multixact-wraparound"/>.
+ </para>
+ </listitem>
+ </varlistentry>
- <varlistentry id="guc-autovacuum-vacuum-cost-delay" xreflabel="autovacuum_vacuum_cost_delay">
- <term><varname>autovacuum_vacuum_cost_delay</varname> (<type>floating point</type>)
- <indexterm>
- <primary><varname>autovacuum_vacuum_cost_delay</varname></primary>
- <secondary>configuration parameter</secondary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the cost delay value that will be used in automatic
- <command>VACUUM</command> operations. If -1 is specified, the regular
- <xref linkend="guc-vacuum-cost-delay"/> value will be used.
- If this value is specified without units, it is taken as milliseconds.
- The default value is 2 milliseconds.
- This parameter can only be set in the <filename>postgresql.conf</filename>
- file or on the server command line;
- but the setting can be overridden for individual tables by
- changing table storage parameters.
- </para>
- </listitem>
- </varlistentry>
+ <varlistentry id="guc-autovacuum-vacuum-cost-delay" xreflabel="autovacuum_vacuum_cost_delay">
+ <term><varname>autovacuum_vacuum_cost_delay</varname> (<type>floating point</type>)
+ <indexterm>
+ <primary><varname>autovacuum_vacuum_cost_delay</varname></primary>
+ <secondary>configuration parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the cost delay value that will be used in automatic
+ <command>VACUUM</command> operations. If -1 is specified, the regular
+ <xref linkend="guc-vacuum-cost-delay"/> value will be used.
+ If this value is specified without units, it is taken as milliseconds.
+ The default value is 2 milliseconds.
+ This parameter can only be set in the <filename>postgresql.conf</filename>
+ file or on the server command line;
+ but the setting can be overridden for individual tables by
+ changing table storage parameters.
+ </para>
+ </listitem>
+ </varlistentry>
- <varlistentry id="guc-autovacuum-vacuum-cost-limit" xreflabel="autovacuum_vacuum_cost_limit">
- <term><varname>autovacuum_vacuum_cost_limit</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>autovacuum_vacuum_cost_limit</varname></primary>
- <secondary>configuration parameter</secondary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the cost limit value that will be used in automatic
- <command>VACUUM</command> operations. If -1 is specified (which is the
- default), the regular
- <xref linkend="guc-vacuum-cost-limit"/> value will be used. Note that
- the value is distributed proportionally among the running autovacuum
- workers, if there is more than one, so that the sum of the limits for
- each worker does not exceed the value of this variable.
- This parameter can only be set in the <filename>postgresql.conf</filename>
- file or on the server command line;
- but the setting can be overridden for individual tables by
- changing table storage parameters.
- </para>
- </listitem>
- </varlistentry>
+ <varlistentry id="guc-autovacuum-vacuum-cost-limit" xreflabel="autovacuum_vacuum_cost_limit">
+ <term><varname>autovacuum_vacuum_cost_limit</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>autovacuum_vacuum_cost_limit</varname></primary>
+ <secondary>configuration parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the cost limit value that will be used in automatic
+ <command>VACUUM</command> operations. If -1 is specified (which is the
+ default), the regular
+ <xref linkend="guc-vacuum-cost-limit"/> value will be used. Note that
+ the value is distributed proportionally among the running autovacuum
+ workers, if there is more than one, so that the sum of the limits for
+ each worker does not exceed the value of this variable.
+ This parameter can only be set in the <filename>postgresql.conf</filename>
+ file or on the server command line;
+ but the setting can be overridden for individual tables by
+ changing table storage parameters.
+ </para>
+ </listitem>
+ </varlistentry>
- </variablelist>
+ </variablelist>
+ </sect2>
+
+ <sect2 id="runtime-config-resource-vacuum-cost">
+ <title>Cost-based Vacuum Delay</title>
+
+ <para>
+ During the execution of <xref linkend="sql-vacuum"/>
+ and <xref linkend="sql-analyze"/>
+ commands, the system maintains an
+ internal counter that keeps track of the estimated cost of the
+ various I/O operations that are performed. When the accumulated
+ cost reaches a limit (specified by
+ <varname>vacuum_cost_limit</varname>), the process performing
+ the operation will sleep for a short period of time, as specified by
+ <varname>vacuum_cost_delay</varname>. Then it will reset the
+ counter and continue execution.
+ </para>
+
+ <para>
+ The intent of this feature is to allow administrators to reduce
+ the I/O impact of these commands on concurrent database
+ activity. There are many situations where it is not
+ important that maintenance commands like
+ <command>VACUUM</command> and <command>ANALYZE</command> finish
+ quickly; however, it is usually very important that these
+ commands do not significantly interfere with the ability of the
+ system to perform other database operations. Cost-based vacuum
+ delay provides a way for administrators to achieve this.
+ </para>
+
+ <para>
+ This feature is disabled by default for manually issued
+ <command>VACUUM</command> commands. To enable it, set the
+ <varname>vacuum_cost_delay</varname> variable to a nonzero
+ value.
+ </para>
+
+ <variablelist>
+ <varlistentry id="guc-vacuum-cost-delay" xreflabel="vacuum_cost_delay">
+ <term><varname>vacuum_cost_delay</varname> (<type>floating point</type>)
+ <indexterm>
+ <primary><varname>vacuum_cost_delay</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ The amount of time that the process will sleep
+ when the cost limit has been exceeded.
+ If this value is specified without units, it is taken as milliseconds.
+ The default value is zero, which disables the cost-based vacuum
+ delay feature. Positive values enable cost-based vacuuming.
+ </para>
+
+ <para>
+ When using cost-based vacuuming, appropriate values for
+ <varname>vacuum_cost_delay</varname> are usually quite small, perhaps
+ less than 1 millisecond. While <varname>vacuum_cost_delay</varname>
+ can be set to fractional-millisecond values, such delays may not be
+ measured accurately on older platforms. On such platforms,
+ increasing <command>VACUUM</command>'s throttled resource consumption
+ above what you get at 1ms will require changing the other vacuum cost
+ parameters. You should, nonetheless,
+ keep <varname>vacuum_cost_delay</varname> as small as your platform
+ will consistently measure; large delays are not helpful.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-vacuum-cost-page-hit" xreflabel="vacuum_cost_page_hit">
+ <term><varname>vacuum_cost_page_hit</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>vacuum_cost_page_hit</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ The estimated cost for vacuuming a buffer found in the shared buffer
+ cache. It represents the cost to lock the buffer pool, lookup
+ the shared hash table and scan the content of the page. The
+ default value is one.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-vacuum-cost-page-miss" xreflabel="vacuum_cost_page_miss">
+ <term><varname>vacuum_cost_page_miss</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>vacuum_cost_page_miss</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ The estimated cost for vacuuming a buffer that has to be read from
+ disk. This represents the effort to lock the buffer pool,
+ lookup the shared hash table, read the desired block in from
+ the disk and scan its content. The default value is 2.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-vacuum-cost-page-dirty" xreflabel="vacuum_cost_page_dirty">
+ <term><varname>vacuum_cost_page_dirty</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>vacuum_cost_page_dirty</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ The estimated cost charged when vacuum modifies a block that was
+ previously clean. It represents the extra I/O required to
+ flush the dirty block out to disk again. The default value is
+ 20.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-vacuum-cost-limit" xreflabel="vacuum_cost_limit">
+ <term><varname>vacuum_cost_limit</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>vacuum_cost_limit</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ This is the accumulated cost that will cause the vacuuming process to sleep
+ for <varname>vacuum_cost_delay</varname>. The default is 200.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+
+ <note>
+ <para>
+ There are certain operations that hold critical locks and should
+ therefore complete as quickly as possible. Cost-based vacuum
+ delays do not occur during such operations. Therefore it is
+ possible that the cost accumulates far higher than the specified
+ limit. To avoid uselessly long delays in such cases, the actual
+ delay is calculated as <varname>vacuum_cost_delay</varname> *
+ <varname>accumulated_balance</varname> /
+ <varname>vacuum_cost_limit</varname> with a maximum of
+ <varname>vacuum_cost_delay</varname> * 4.
+ </para>
+ </note>
+ </sect2>
+
+ <sect2 id="runtime-config-vacuum-freezing">
+ <title>Freezing</title>
+
+ <para>
+ Vacuum operations are also responsible for freezing rows to avoid
+ transaction ID wraparound. These settings control vacuum's freezing
+ behavior. See <xref linkend="vacuum-for-wraparound"/> for more
+ information on transaction ID wraparound and tuning these parameters.
+ </para>
+
+ <variablelist>
+ <varlistentry id="guc-vacuum-freeze-table-age" xreflabel="vacuum_freeze_table_age">
+ <term><varname>vacuum_freeze_table_age</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>vacuum_freeze_table_age</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ <command>VACUUM</command> performs an aggressive scan if the table's
+ <structname>pg_class</structname>.<structfield>relfrozenxid</structfield> field has reached
+ the age specified by this setting. An aggressive scan differs from
+ a regular <command>VACUUM</command> in that it visits every page that might
+ contain unfrozen XIDs or MXIDs, not just those that might contain dead
+ tuples. The default is 150 million transactions. Although users can
+ set this value anywhere from zero to two billion, <command>VACUUM</command>
+ will silently limit the effective value to 95% of
+ <xref linkend="guc-autovacuum-freeze-max-age"/>, so that a
+ periodic manual <command>VACUUM</command> has a chance to run before an
+ anti-wraparound autovacuum is launched for the table. For more
+ information see
+ <xref linkend="vacuum-for-wraparound"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-vacuum-freeze-min-age" xreflabel="vacuum_freeze_min_age">
+ <term><varname>vacuum_freeze_min_age</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>vacuum_freeze_min_age</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the cutoff age (in transactions) that
+ <command>VACUUM</command> should use to decide whether to
+ trigger freezing of pages that have an older XID.
+ The default is 50 million transactions. Although
+ users can set this value anywhere from zero to one billion,
+ <command>VACUUM</command> will silently limit the effective value to half
+ the value of <xref linkend="guc-autovacuum-freeze-max-age"/>, so
+ that there is not an unreasonably short time between forced
+ autovacuums. For more information see <xref
+ linkend="vacuum-for-wraparound"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-vacuum-failsafe-age" xreflabel="vacuum_failsafe_age">
+ <term><varname>vacuum_failsafe_age</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>vacuum_failsafe_age</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the maximum age (in transactions) that a table's
+ <structname>pg_class</structname>.<structfield>relfrozenxid</structfield>
+ field can attain before <command>VACUUM</command> takes
+ extraordinary measures to avoid system-wide transaction ID
+ wraparound failure. This is <command>VACUUM</command>'s
+ strategy of last resort. The failsafe typically triggers
+ when an autovacuum to prevent transaction ID wraparound has
+ already been running for some time, though it's possible for
+ the failsafe to trigger during any <command>VACUUM</command>.
+ </para>
+ <para>
+ When the failsafe is triggered, any cost-based delay that is
+ in effect will no longer be applied, further non-essential
+ maintenance tasks (such as index vacuuming) are bypassed, and any
+ <glossterm linkend="glossary-buffer-access-strategy">Buffer Access Strategy</glossterm>
+ in use will be disabled resulting in <command>VACUUM</command> being
+ free to make use of all of
+ <glossterm linkend="glossary-shared-memory">shared buffers</glossterm>.
+ </para>
+ <para>
+ The default is 1.6 billion transactions. Although users can
+ set this value anywhere from zero to 2.1 billion,
+ <command>VACUUM</command> will silently adjust the effective
+ value to no less than 105% of <xref
+ linkend="guc-autovacuum-freeze-max-age"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-vacuum-multixact-freeze-table-age" xreflabel="vacuum_multixact_freeze_table_age">
+ <term><varname>vacuum_multixact_freeze_table_age</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>vacuum_multixact_freeze_table_age</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ <command>VACUUM</command> performs an aggressive scan if the table's
+ <structname>pg_class</structname>.<structfield>relminmxid</structfield> field has reached
+ the age specified by this setting. An aggressive scan differs from
+ a regular <command>VACUUM</command> in that it visits every page that might
+ contain unfrozen XIDs or MXIDs, not just those that might contain dead
+ tuples. The default is 150 million multixacts.
+ Although users can set this value anywhere from zero to two billion,
+ <command>VACUUM</command> will silently limit the effective value to 95% of
+ <xref linkend="guc-autovacuum-multixact-freeze-max-age"/>, so that a
+ periodic manual <command>VACUUM</command> has a chance to run before an
+ anti-wraparound is launched for the table.
+ For more information see <xref linkend="vacuum-for-multixact-wraparound"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-vacuum-multixact-freeze-min-age" xreflabel="vacuum_multixact_freeze_min_age">
+ <term><varname>vacuum_multixact_freeze_min_age</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>vacuum_multixact_freeze_min_age</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the cutoff age (in multixacts) that <command>VACUUM</command>
+ should use to decide whether to trigger freezing of pages with
+ an older multixact ID. The default is 5 million multixacts.
+ Although users can set this value anywhere from zero to one billion,
+ <command>VACUUM</command> will silently limit the effective value to half
+ the value of <xref linkend="guc-autovacuum-multixact-freeze-max-age"/>,
+ so that there is not an unreasonably short time between forced
+ autovacuums.
+ For more information see <xref linkend="vacuum-for-multixact-wraparound"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-vacuum-multixact-failsafe-age" xreflabel="vacuum_multixact_failsafe_age">
+ <term><varname>vacuum_multixact_failsafe_age</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>vacuum_multixact_failsafe_age</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the maximum age (in multixacts) that a table's
+ <structname>pg_class</structname>.<structfield>relminmxid</structfield>
+ field can attain before <command>VACUUM</command> takes
+ extraordinary measures to avoid system-wide multixact ID
+ wraparound failure. This is <command>VACUUM</command>'s
+ strategy of last resort. The failsafe typically triggers when
+ an autovacuum to prevent transaction ID wraparound has already
+ been running for some time, though it's possible for the
+ failsafe to trigger during any <command>VACUUM</command>.
+ </para>
+ <para>
+ When the failsafe is triggered, any cost-based delay that is
+ in effect will no longer be applied, and further non-essential
+ maintenance tasks (such as index vacuuming) are bypassed.
+ </para>
+ <para>
+ The default is 1.6 billion multixacts. Although users can set
+ this value anywhere from zero to 2.1 billion,
+ <command>VACUUM</command> will silently adjust the effective
+ value to no less than 105% of <xref
+ linkend="guc-autovacuum-multixact-freeze-max-age"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </sect2>
</sect1>
<sect1 id="runtime-config-client">
@@ -9592,168 +9773,6 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
</listitem>
</varlistentry>
- <varlistentry id="guc-vacuum-freeze-table-age" xreflabel="vacuum_freeze_table_age">
- <term><varname>vacuum_freeze_table_age</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>vacuum_freeze_table_age</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- <command>VACUUM</command> performs an aggressive scan if the table's
- <structname>pg_class</structname>.<structfield>relfrozenxid</structfield> field has reached
- the age specified by this setting. An aggressive scan differs from
- a regular <command>VACUUM</command> in that it visits every page that might
- contain unfrozen XIDs or MXIDs, not just those that might contain dead
- tuples. The default is 150 million transactions. Although users can
- set this value anywhere from zero to two billion, <command>VACUUM</command>
- will silently limit the effective value to 95% of
- <xref linkend="guc-autovacuum-freeze-max-age"/>, so that a
- periodic manual <command>VACUUM</command> has a chance to run before an
- anti-wraparound autovacuum is launched for the table. For more
- information see
- <xref linkend="vacuum-for-wraparound"/>.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="guc-vacuum-freeze-min-age" xreflabel="vacuum_freeze_min_age">
- <term><varname>vacuum_freeze_min_age</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>vacuum_freeze_min_age</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the cutoff age (in transactions) that
- <command>VACUUM</command> should use to decide whether to
- trigger freezing of pages that have an older XID.
- The default is 50 million transactions. Although
- users can set this value anywhere from zero to one billion,
- <command>VACUUM</command> will silently limit the effective value to half
- the value of <xref linkend="guc-autovacuum-freeze-max-age"/>, so
- that there is not an unreasonably short time between forced
- autovacuums. For more information see <xref
- linkend="vacuum-for-wraparound"/>.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="guc-vacuum-failsafe-age" xreflabel="vacuum_failsafe_age">
- <term><varname>vacuum_failsafe_age</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>vacuum_failsafe_age</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the maximum age (in transactions) that a table's
- <structname>pg_class</structname>.<structfield>relfrozenxid</structfield>
- field can attain before <command>VACUUM</command> takes
- extraordinary measures to avoid system-wide transaction ID
- wraparound failure. This is <command>VACUUM</command>'s
- strategy of last resort. The failsafe typically triggers
- when an autovacuum to prevent transaction ID wraparound has
- already been running for some time, though it's possible for
- the failsafe to trigger during any <command>VACUUM</command>.
- </para>
- <para>
- When the failsafe is triggered, any cost-based delay that is
- in effect will no longer be applied, further non-essential
- maintenance tasks (such as index vacuuming) are bypassed, and any
- <glossterm linkend="glossary-buffer-access-strategy">Buffer Access Strategy</glossterm>
- in use will be disabled resulting in <command>VACUUM</command> being
- free to make use of all of
- <glossterm linkend="glossary-shared-memory">shared buffers</glossterm>.
- </para>
- <para>
- The default is 1.6 billion transactions. Although users can
- set this value anywhere from zero to 2.1 billion,
- <command>VACUUM</command> will silently adjust the effective
- value to no less than 105% of <xref
- linkend="guc-autovacuum-freeze-max-age"/>.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="guc-vacuum-multixact-freeze-table-age" xreflabel="vacuum_multixact_freeze_table_age">
- <term><varname>vacuum_multixact_freeze_table_age</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>vacuum_multixact_freeze_table_age</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- <command>VACUUM</command> performs an aggressive scan if the table's
- <structname>pg_class</structname>.<structfield>relminmxid</structfield> field has reached
- the age specified by this setting. An aggressive scan differs from
- a regular <command>VACUUM</command> in that it visits every page that might
- contain unfrozen XIDs or MXIDs, not just those that might contain dead
- tuples. The default is 150 million multixacts.
- Although users can set this value anywhere from zero to two billion,
- <command>VACUUM</command> will silently limit the effective value to 95% of
- <xref linkend="guc-autovacuum-multixact-freeze-max-age"/>, so that a
- periodic manual <command>VACUUM</command> has a chance to run before an
- anti-wraparound is launched for the table.
- For more information see <xref linkend="vacuum-for-multixact-wraparound"/>.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="guc-vacuum-multixact-freeze-min-age" xreflabel="vacuum_multixact_freeze_min_age">
- <term><varname>vacuum_multixact_freeze_min_age</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>vacuum_multixact_freeze_min_age</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the cutoff age (in multixacts) that <command>VACUUM</command>
- should use to decide whether to trigger freezing of pages with
- an older multixact ID. The default is 5 million multixacts.
- Although users can set this value anywhere from zero to one billion,
- <command>VACUUM</command> will silently limit the effective value to half
- the value of <xref linkend="guc-autovacuum-multixact-freeze-max-age"/>,
- so that there is not an unreasonably short time between forced
- autovacuums.
- For more information see <xref linkend="vacuum-for-multixact-wraparound"/>.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="guc-vacuum-multixact-failsafe-age" xreflabel="vacuum_multixact_failsafe_age">
- <term><varname>vacuum_multixact_failsafe_age</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>vacuum_multixact_failsafe_age</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the maximum age (in multixacts) that a table's
- <structname>pg_class</structname>.<structfield>relminmxid</structfield>
- field can attain before <command>VACUUM</command> takes
- extraordinary measures to avoid system-wide multixact ID
- wraparound failure. This is <command>VACUUM</command>'s
- strategy of last resort. The failsafe typically triggers when
- an autovacuum to prevent transaction ID wraparound has already
- been running for some time, though it's possible for the
- failsafe to trigger during any <command>VACUUM</command>.
- </para>
- <para>
- When the failsafe is triggered, any cost-based delay that is
- in effect will no longer be applied, and further non-essential
- maintenance tasks (such as index vacuuming) are bypassed.
- </para>
- <para>
- The default is 1.6 billion multixacts. Although users can set
- this value anywhere from zero to 2.1 billion,
- <command>VACUUM</command> will silently adjust the effective
- value to no less than 105% of <xref
- linkend="guc-autovacuum-multixact-freeze-max-age"/>.
- </para>
- </listitem>
- </varlistentry>
-
<varlistentry id="guc-bytea-output" xreflabel="bytea_output">
<term><varname>bytea_output</varname> (<type>enum</type>)
<indexterm>
--
2.34.1
Re: Moving the vacuum GUCs' docs out of the Client Connection Defaults section
On Tue, Jan 7, 2025 at 12:15 PM Melanie Plageman
<melanieplageman@gmail.com> wrote:
Cool, I've attached a patch to do this. I left a few of the GUCs under
Resource Consumption (like autovacuum_work_mem and
vacuum_buffer_usage_limit) where they are because it seemed
appropriate.This is my first docs patch that introduces new sections and such, so
I'm not sure I got the indentation 100% correct (I, of course, tried
to follow conventions).
Oh, one thing I forgot to say. Though I increased the indentation of
some of the subsections that I moved, I didn't rewrap the lines
because they were already not wrapped to 78. I can do this, but I
can't tell from the existing docs what text width the paragraphs of
text are supposed to be wrapped to.
- Melanie
Re: Moving the vacuum GUCs' docs out of the Client Connection Defaults section
On Tue, Jan 07, 2025 at 12:15:17PM -0500, Melanie Plageman wrote:
This is my first docs patch that introduces new sections and such, so
I'm not sure I got the indentation 100% correct (I, of course, tried
to follow conventions).
I haven't reviewed the patch in depth, but I think it's worth considering
whether this change will break any links that work in one version but break
if you change the version number. I believe appendix-obsolete.sgml is
designed to help with that a bit, but I've had mixed results when I've
tried to use it. Of course, it's also possible that we don't care too
much...
But overall, consolidating the docs for the vacuum GUCs seems like a good
idea.
--
nathan
Re: Moving the vacuum GUCs' docs out of the Client Connection Defaults section
Melanie Plageman <melanieplageman@gmail.com> writes:
This is my first docs patch that introduces new sections and such, so
I'm not sure I got the indentation 100% correct (I, of course, tried
to follow conventions).
There really isn't much convention there :-(. The amount of
indentation used varies wildly across different chunks of our docs.
I'd try to make it look like nearby sections, but those might
themselves be inconsistent.
Oh, one thing I forgot to say. Though I increased the indentation of
some of the subsections that I moved, I didn't rewrap the lines
because they were already not wrapped to 78. I can do this, but I
can't tell from the existing docs what text width the paragraphs of
text are supposed to be wrapped to.
If you're moving the text anyway, I'd rewrap it to something less than
80 columns. Again, there's not a lot of uniformity about exactly how
much less. I frequently use 74-column width for new docs text, to
leave some room for minor adjustments without having to rewrap;
but I don't think other people follow that rule.
A lot of the existing violations of 80-column right margin come from
when we switched to XML rules and had to fill out abbreviated closing
tags ("</>") to full tags ("</foo>"). That was done with some more or
less automated conversion that didn't do anything about rewrapping
lines that it made too long. I think that choice was fine, because
it reduced the size of the diff. But if you're rewriting or moving a
para, that's a good time to clean things up by rewrapping it; it'll
all be new according to "git blame" anyway.
regards, tom lane
Re: Moving the vacuum GUCs' docs out of the Client Connection Defaults section
Nathan Bossart <nathandbossart@gmail.com> writes:
I haven't reviewed the patch in depth, but I think it's worth considering
whether this change will break any links that work in one version but break
if you change the version number. I believe appendix-obsolete.sgml is
designed to help with that a bit, but I've had mixed results when I've
tried to use it. Of course, it's also possible that we don't care too
much...
Yeah, this change:
- <sect1 id="runtime-config-autovacuum">
+ <sect1 id="runtime-config-vacuum">will change the doc page's URL and thus break any external links or
bookmarks for the whole page or anything on it. That's not the
end of the world, but it's slightly annoying. appendix-obsolete.sgml
seems to be designed for slightly more heavyweight changes, where
it's worth having an intermediate page that explains what changed.
Here I think we'd just like a redirect.
I might be wrong, but I had the idea that our docs website has a
capability to provide such redirects. You'd probably need to ask
about that on the pgsql-www list, unless somebody who knows the
answer notices this thread.
regards, tom lane
Re: Moving the vacuum GUCs' docs out of the Client Connection Defaults section
On 7 Jan 2025, at 21:14, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I might be wrong, but I had the idea that our docs website has a
capability to provide such redirects. You'd probably need to ask
about that on the pgsql-www list, unless somebody who knows the
answer notices this thread.
There is functionality in pgweb to provide a page alias for whenever a page is
renamed to keep the links to other versions working. One example is:
https://www.postgresql.org/docs/9.4/catalog-pg-replication-slots.html
https://www.postgresql.org/docs/17/view-pg-replication-slots.html
There is also a redirect functionality, which isn't used anywhere right now,
but ideally could be used to redirect bookmarks for /current/<oldname> to
/current/<newname>.
--
Daniel Gustafsson
Re: Moving the vacuum GUCs' docs out of the Client Connection Defaults section
On 07.01.25 18:31, Melanie Plageman wrote:
On Tue, Jan 7, 2025 at 12:15 PM Melanie Plageman
<melanieplageman@gmail.com> wrote:Cool, I've attached a patch to do this. I left a few of the GUCs under
Resource Consumption (like autovacuum_work_mem and
vacuum_buffer_usage_limit) where they are because it seemed
appropriate.This is my first docs patch that introduces new sections and such, so
I'm not sure I got the indentation 100% correct (I, of course, tried
to follow conventions).Oh, one thing I forgot to say. Though I increased the indentation of
some of the subsections that I moved, I didn't rewrap the lines
because they were already not wrapped to 78. I can do this, but I
can't tell from the existing docs what text width the paragraphs of
text are supposed to be wrapped to.
For a patch that moves things around like this, I would leave everything
as is and not rewrap. That makes it easier to follow what's going on
with "git diff -w", "git show -w" etc.
In .dir-locals.el, there is this configuration for Emacs:
(nxml-mode . ((fill-column . 78)
(indent-tabs-mode . nil)))
So that's one data point about what the line length should be.
Re: Moving the vacuum GUCs' docs out of the Client Connection Defaults section
On Wed, Jan 8, 2025 at 6:35 AM Daniel Gustafsson <daniel@yesql.se> wrote:
On 7 Jan 2025, at 21:14, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I might be wrong, but I had the idea that our docs website has a
capability to provide such redirects. You'd probably need to ask
about that on the pgsql-www list, unless somebody who knows the
answer notices this thread.There is functionality in pgweb to provide a page alias for whenever a page is
renamed to keep the links to other versions working. One example is:https://www.postgresql.org/docs/9.4/catalog-pg-replication-slots.html
https://www.postgresql.org/docs/17/view-pg-replication-slots.htmlThere is also a redirect functionality, which isn't used anywhere right now,
but ideally could be used to redirect bookmarks for /current/<oldname> to
/current/<newname>.
Thanks to Nathan and Tom for noticing and Daniel for replying. So, if
I understand correctly, pgweb will do this for me without me needing
to do anything in my patch?
- Melanie
Re: Moving the vacuum GUCs' docs out of the Client Connection Defaults section
On Wed, Jan 8, 2025 at 8:39 AM Peter Eisentraut <peter@eisentraut.org> wrote:
On 07.01.25 18:31, Melanie Plageman wrote:
Oh, one thing I forgot to say. Though I increased the indentation of
some of the subsections that I moved, I didn't rewrap the lines
because they were already not wrapped to 78. I can do this, but I
can't tell from the existing docs what text width the paragraphs of
text are supposed to be wrapped to.For a patch that moves things around like this, I would leave everything
as is and not rewrap. That makes it easier to follow what's going on
with "git diff -w", "git show -w" etc.In .dir-locals.el, there is this configuration for Emacs:
(nxml-mode . ((fill-column . 78)
(indent-tabs-mode . nil)))So that's one data point about what the line length should be.
Well, in this case, the diff won't look different with git show/diff
-w because moving them to another part of the file is more than a
whitespace change. For clarity, I added a note to the commit message
that the actual GUCs' docs are just lifted and shifted.
I decided in the end not to wrap it anyway, though. I tried it and
didn't like the result. If I wrap it to 78, that actually makes a good
number of the GUCs' docs wider (i.e. they were wrapped to less than
78). Which means future additions are more likely to need to wrap (as
Tom mentioned upthread).
Attached is v2 (required a rebase).
- Melanie
Attachments:
v2-0001-Consolidate-docs-for-vacuum-related-GUCs-in-new-s.patchapplication/octet-stream; name=v2-0001-Consolidate-docs-for-vacuum-related-GUCs-in-new-s.patchDownload
From 924fdf568eac442a780456d07bcd53756a36ecc0 Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplageman@gmail.com>
Date: Tue, 7 Jan 2025 12:31:37 -0500
Subject: [PATCH v2] Consolidate docs for vacuum-related GUCs in new subsection
GUCs related to vacuum's freezing behavior were documented in a
subsection of the Client Connection Defaults documentation. These GUCs
don't belong there, as they affect the freezing behavior of all vacuums
-- including autovacuums.
There wasn't a clear alternative location, so this commit makes a new
Server Configuration docs subsection, "Vacuuming", with a subsection for
"Freezing". It also moves the "Automatic Vacuuming" subsection and the
docs on GUCs controlling cost-based vacuum delay under the new
"Vacuuming" subsection.
The other vacuum-related GUCs under the "Resource Consumption"
subsection have been left in their current location, as they seem to fit
there.
All of the documentation for the GUCs themselves are just lifted and
shifted.
Discussion: https://postgr.es/m/flat/1373018.1736213217%40sss.pgh.pa.us#105c713a7966f87e4ac4301246e3cabe
---
doc/src/sgml/config.sgml | 1240 +++++++++++++++++++-------------------
1 file changed, 629 insertions(+), 611 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 8683f0bdf53..a43619c0d9f 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -2367,149 +2367,6 @@ include_dir 'conf.d'
</variablelist>
</sect2>
- <sect2 id="runtime-config-resource-vacuum-cost">
- <title>Cost-based Vacuum Delay</title>
-
- <para>
- During the execution of <xref linkend="sql-vacuum"/>
- and <xref linkend="sql-analyze"/>
- commands, the system maintains an
- internal counter that keeps track of the estimated cost of the
- various I/O operations that are performed. When the accumulated
- cost reaches a limit (specified by
- <varname>vacuum_cost_limit</varname>), the process performing
- the operation will sleep for a short period of time, as specified by
- <varname>vacuum_cost_delay</varname>. Then it will reset the
- counter and continue execution.
- </para>
-
- <para>
- The intent of this feature is to allow administrators to reduce
- the I/O impact of these commands on concurrent database
- activity. There are many situations where it is not
- important that maintenance commands like
- <command>VACUUM</command> and <command>ANALYZE</command> finish
- quickly; however, it is usually very important that these
- commands do not significantly interfere with the ability of the
- system to perform other database operations. Cost-based vacuum
- delay provides a way for administrators to achieve this.
- </para>
-
- <para>
- This feature is disabled by default for manually issued
- <command>VACUUM</command> commands. To enable it, set the
- <varname>vacuum_cost_delay</varname> variable to a nonzero
- value.
- </para>
-
- <variablelist>
- <varlistentry id="guc-vacuum-cost-delay" xreflabel="vacuum_cost_delay">
- <term><varname>vacuum_cost_delay</varname> (<type>floating point</type>)
- <indexterm>
- <primary><varname>vacuum_cost_delay</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- The amount of time that the process will sleep
- when the cost limit has been exceeded.
- If this value is specified without units, it is taken as milliseconds.
- The default value is zero, which disables the cost-based vacuum
- delay feature. Positive values enable cost-based vacuuming.
- </para>
-
- <para>
- When using cost-based vacuuming, appropriate values for
- <varname>vacuum_cost_delay</varname> are usually quite small, perhaps
- less than 1 millisecond. While <varname>vacuum_cost_delay</varname>
- can be set to fractional-millisecond values, such delays may not be
- measured accurately on older platforms. On such platforms,
- increasing <command>VACUUM</command>'s throttled resource consumption
- above what you get at 1ms will require changing the other vacuum cost
- parameters. You should, nonetheless,
- keep <varname>vacuum_cost_delay</varname> as small as your platform
- will consistently measure; large delays are not helpful.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="guc-vacuum-cost-page-hit" xreflabel="vacuum_cost_page_hit">
- <term><varname>vacuum_cost_page_hit</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>vacuum_cost_page_hit</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- The estimated cost for vacuuming a buffer found in the shared buffer
- cache. It represents the cost to lock the buffer pool, lookup
- the shared hash table and scan the content of the page. The
- default value is one.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="guc-vacuum-cost-page-miss" xreflabel="vacuum_cost_page_miss">
- <term><varname>vacuum_cost_page_miss</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>vacuum_cost_page_miss</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- The estimated cost for vacuuming a buffer that has to be read from
- disk. This represents the effort to lock the buffer pool,
- lookup the shared hash table, read the desired block in from
- the disk and scan its content. The default value is 2.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="guc-vacuum-cost-page-dirty" xreflabel="vacuum_cost_page_dirty">
- <term><varname>vacuum_cost_page_dirty</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>vacuum_cost_page_dirty</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- The estimated cost charged when vacuum modifies a block that was
- previously clean. It represents the extra I/O required to
- flush the dirty block out to disk again. The default value is
- 20.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="guc-vacuum-cost-limit" xreflabel="vacuum_cost_limit">
- <term><varname>vacuum_cost_limit</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>vacuum_cost_limit</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- This is the accumulated cost that will cause the vacuuming process to sleep
- for <varname>vacuum_cost_delay</varname>. The default is 200.
- </para>
- </listitem>
- </varlistentry>
- </variablelist>
-
- <note>
- <para>
- There are certain operations that hold critical locks and should
- therefore complete as quickly as possible. Cost-based vacuum
- delays do not occur during such operations. Therefore it is
- possible that the cost accumulates far higher than the specified
- limit. To avoid uselessly long delays in such cases, the actual
- delay is calculated as <varname>vacuum_cost_delay</varname> *
- <varname>accumulated_balance</varname> /
- <varname>vacuum_cost_limit</varname> with a maximum of
- <varname>vacuum_cost_delay</varname> * 4.
- </para>
- </note>
- </sect2>
<sect2 id="runtime-config-resource-background-writer">
<title>Background Writer</title>
@@ -8588,14 +8445,17 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
</sect2>
</sect1>
- <sect1 id="runtime-config-autovacuum">
- <title>Automatic Vacuuming</title>
+ <sect1 id="runtime-config-vacuum">
+ <title>Vacuuming</title>
<indexterm>
- <primary>autovacuum</primary>
+ <primary>vacuum</primary>
<secondary>configuration parameters</secondary>
</indexterm>
+ <sect2 id="runtime-config-autovacuum">
+ <title>Automatic Vacuuming</title>
+
<para>
These settings control the behavior of the <firstterm>autovacuum</firstterm>
feature. Refer to <xref linkend="autovacuum"/> for more information.
@@ -8603,325 +8463,645 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
basis; see <xref linkend="sql-createtable-storage-parameters"/>.
</para>
- <variablelist>
-
- <varlistentry id="guc-autovacuum" xreflabel="autovacuum">
- <term><varname>autovacuum</varname> (<type>boolean</type>)
- <indexterm>
- <primary><varname>autovacuum</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Controls whether the server should run the
- autovacuum launcher daemon. This is on by default; however,
- <xref linkend="guc-track-counts"/> must also be enabled for
- autovacuum to work.
- This parameter can only be set in the <filename>postgresql.conf</filename>
- file or on the server command line; however, autovacuuming can be
- disabled for individual tables by changing table storage parameters.
- </para>
- <para>
- Note that even when this parameter is disabled, the system
- will launch autovacuum processes if necessary to
- prevent transaction ID wraparound. See <xref
- linkend="vacuum-for-wraparound"/> for more information.
- </para>
- </listitem>
- </varlistentry>
+ <variablelist>
- <varlistentry id="guc-autovacuum-worker-slots" xreflabel="autovacuum_worker_slots">
- <term><varname>autovacuum_worker_slots</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>autovacuum_worker_slots</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the number of backend slots to reserve for autovacuum worker
- processes. The default is typically 16 slots, but might be less if
- your kernel settings will not support it (as determined during initdb).
- This parameter can only be set at server start.
- </para>
- <para>
- When changing this value, consider also adjusting
- <xref linkend="guc-autovacuum-max-workers"/>.
- </para>
- </listitem>
- </varlistentry>
+ <varlistentry id="guc-autovacuum" xreflabel="autovacuum">
+ <term><varname>autovacuum</varname> (<type>boolean</type>)
+ <indexterm>
+ <primary><varname>autovacuum</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Controls whether the server should run the
+ autovacuum launcher daemon. This is on by default; however,
+ <xref linkend="guc-track-counts"/> must also be enabled for
+ autovacuum to work.
+ This parameter can only be set in the <filename>postgresql.conf</filename>
+ file or on the server command line; however, autovacuuming can be
+ disabled for individual tables by changing table storage parameters.
+ </para>
+ <para>
+ Note that even when this parameter is disabled, the system
+ will launch autovacuum processes if necessary to
+ prevent transaction ID wraparound. See <xref
+ linkend="vacuum-for-wraparound"/> for more information.
+ </para>
+ </listitem>
+ </varlistentry>
- <varlistentry id="guc-autovacuum-max-workers" xreflabel="autovacuum_max_workers">
- <term><varname>autovacuum_max_workers</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>autovacuum_max_workers</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the maximum number of autovacuum processes (other than the
- autovacuum launcher) that may be running at any one time. The default
- is three. This parameter can only be set in the
- <filename>postgresql.conf</filename> file or on the server command line.
- </para>
- <para>
- Note that a setting for this value which is higher than
- <xref linkend="guc-autovacuum-worker-slots"/> will have no effect,
- since autovacuum workers are taken from the pool of slots established
- by that setting.
- </para>
- </listitem>
- </varlistentry>
+ <varlistentry id="guc-autovacuum-worker-slots" xreflabel="autovacuum_worker_slots">
+ <term><varname>autovacuum_worker_slots</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>autovacuum_worker_slots</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the number of backend slots to reserve for autovacuum worker
+ processes. The default is typically 16 slots, but might be less if
+ your kernel settings will not support it (as determined during initdb).
+ This parameter can only be set at server start.
+ </para>
+ <para>
+ When changing this value, consider also adjusting
+ <xref linkend="guc-autovacuum-max-workers"/>.
+ </para>
+ </listitem>
+ </varlistentry>
- <varlistentry id="guc-autovacuum-naptime" xreflabel="autovacuum_naptime">
- <term><varname>autovacuum_naptime</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>autovacuum_naptime</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the minimum delay between autovacuum runs on any given
- database. In each round the daemon examines the
- database and issues <command>VACUUM</command> and <command>ANALYZE</command> commands
- as needed for tables in that database.
- If this value is specified without units, it is taken as seconds.
- The default is one minute (<literal>1min</literal>).
- This parameter can only be set in the <filename>postgresql.conf</filename>
- file or on the server command line.
- </para>
- </listitem>
- </varlistentry>
+ <varlistentry id="guc-autovacuum-max-workers" xreflabel="autovacuum_max_workers">
+ <term><varname>autovacuum_max_workers</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>autovacuum_max_workers</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the maximum number of autovacuum processes (other than the
+ autovacuum launcher) that may be running at any one time. The default
+ is three. This parameter can only be set in the
+ <filename>postgresql.conf</filename> file or on the server command line.
+ </para>
+ <para>
+ Note that a setting for this value which is higher than
+ <xref linkend="guc-autovacuum-worker-slots"/> will have no effect,
+ since autovacuum workers are taken from the pool of slots established
+ by that setting.
+ </para>
+ </listitem>
+ </varlistentry>
- <varlistentry id="guc-autovacuum-vacuum-threshold" xreflabel="autovacuum_vacuum_threshold">
- <term><varname>autovacuum_vacuum_threshold</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>autovacuum_vacuum_threshold</varname></primary>
- <secondary>configuration parameter</secondary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the minimum number of updated or deleted tuples needed
- to trigger a <command>VACUUM</command> in any one table.
- The default is 50 tuples.
- This parameter can only be set in the <filename>postgresql.conf</filename>
- file or on the server command line;
- but the setting can be overridden for individual tables by
- changing table storage parameters.
- </para>
- </listitem>
- </varlistentry>
+ <varlistentry id="guc-autovacuum-naptime" xreflabel="autovacuum_naptime">
+ <term><varname>autovacuum_naptime</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>autovacuum_naptime</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the minimum delay between autovacuum runs on any given
+ database. In each round the daemon examines the
+ database and issues <command>VACUUM</command> and <command>ANALYZE</command> commands
+ as needed for tables in that database.
+ If this value is specified without units, it is taken as seconds.
+ The default is one minute (<literal>1min</literal>).
+ This parameter can only be set in the <filename>postgresql.conf</filename>
+ file or on the server command line.
+ </para>
+ </listitem>
+ </varlistentry>
- <varlistentry id="guc-autovacuum-vacuum-insert-threshold" xreflabel="autovacuum_vacuum_insert_threshold">
- <term><varname>autovacuum_vacuum_insert_threshold</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>autovacuum_vacuum_insert_threshold</varname></primary>
- <secondary>configuration parameter</secondary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the number of inserted tuples needed to trigger a
- <command>VACUUM</command> in any one table.
- The default is 1000 tuples. If -1 is specified, autovacuum will not
- trigger a <command>VACUUM</command> operation on any tables based on
- the number of inserts.
- This parameter can only be set in the <filename>postgresql.conf</filename>
- file or on the server command line;
- but the setting can be overridden for individual tables by
- changing table storage parameters.
- </para>
- </listitem>
- </varlistentry>
+ <varlistentry id="guc-autovacuum-vacuum-threshold" xreflabel="autovacuum_vacuum_threshold">
+ <term><varname>autovacuum_vacuum_threshold</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>autovacuum_vacuum_threshold</varname></primary>
+ <secondary>configuration parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the minimum number of updated or deleted tuples needed
+ to trigger a <command>VACUUM</command> in any one table.
+ The default is 50 tuples.
+ This parameter can only be set in the <filename>postgresql.conf</filename>
+ file or on the server command line;
+ but the setting can be overridden for individual tables by
+ changing table storage parameters.
+ </para>
+ </listitem>
+ </varlistentry>
- <varlistentry id="guc-autovacuum-analyze-threshold" xreflabel="autovacuum_analyze_threshold">
- <term><varname>autovacuum_analyze_threshold</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>autovacuum_analyze_threshold</varname></primary>
- <secondary>configuration parameter</secondary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the minimum number of inserted, updated or deleted tuples
- needed to trigger an <command>ANALYZE</command> in any one table.
- The default is 50 tuples.
- This parameter can only be set in the <filename>postgresql.conf</filename>
- file or on the server command line;
- but the setting can be overridden for individual tables by
- changing table storage parameters.
- </para>
- </listitem>
- </varlistentry>
+ <varlistentry id="guc-autovacuum-vacuum-insert-threshold" xreflabel="autovacuum_vacuum_insert_threshold">
+ <term><varname>autovacuum_vacuum_insert_threshold</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>autovacuum_vacuum_insert_threshold</varname></primary>
+ <secondary>configuration parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the number of inserted tuples needed to trigger a
+ <command>VACUUM</command> in any one table.
+ The default is 1000 tuples. If -1 is specified, autovacuum will not
+ trigger a <command>VACUUM</command> operation on any tables based on
+ the number of inserts.
+ This parameter can only be set in the <filename>postgresql.conf</filename>
+ file or on the server command line;
+ but the setting can be overridden for individual tables by
+ changing table storage parameters.
+ </para>
+ </listitem>
+ </varlistentry>
- <varlistentry id="guc-autovacuum-vacuum-scale-factor" xreflabel="autovacuum_vacuum_scale_factor">
- <term><varname>autovacuum_vacuum_scale_factor</varname> (<type>floating point</type>)
- <indexterm>
- <primary><varname>autovacuum_vacuum_scale_factor</varname></primary>
- <secondary>configuration parameter</secondary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies a fraction of the table size to add to
- <varname>autovacuum_vacuum_threshold</varname>
- when deciding whether to trigger a <command>VACUUM</command>.
- The default is 0.2 (20% of table size).
- This parameter can only be set in the <filename>postgresql.conf</filename>
- file or on the server command line;
- but the setting can be overridden for individual tables by
- changing table storage parameters.
- </para>
- </listitem>
- </varlistentry>
+ <varlistentry id="guc-autovacuum-analyze-threshold" xreflabel="autovacuum_analyze_threshold">
+ <term><varname>autovacuum_analyze_threshold</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>autovacuum_analyze_threshold</varname></primary>
+ <secondary>configuration parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the minimum number of inserted, updated or deleted tuples
+ needed to trigger an <command>ANALYZE</command> in any one table.
+ The default is 50 tuples.
+ This parameter can only be set in the <filename>postgresql.conf</filename>
+ file or on the server command line;
+ but the setting can be overridden for individual tables by
+ changing table storage parameters.
+ </para>
+ </listitem>
+ </varlistentry>
- <varlistentry id="guc-autovacuum-vacuum-insert-scale-factor" xreflabel="autovacuum_vacuum_insert_scale_factor">
- <term><varname>autovacuum_vacuum_insert_scale_factor</varname> (<type>floating point</type>)
- <indexterm>
- <primary><varname>autovacuum_vacuum_insert_scale_factor</varname></primary>
- <secondary>configuration parameter</secondary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies a fraction of the table size to add to
- <varname>autovacuum_vacuum_insert_threshold</varname>
- when deciding whether to trigger a <command>VACUUM</command>.
- The default is 0.2 (20% of table size).
- This parameter can only be set in the <filename>postgresql.conf</filename>
- file or on the server command line;
- but the setting can be overridden for individual tables by
- changing table storage parameters.
- </para>
- </listitem>
- </varlistentry>
+ <varlistentry id="guc-autovacuum-vacuum-scale-factor" xreflabel="autovacuum_vacuum_scale_factor">
+ <term><varname>autovacuum_vacuum_scale_factor</varname> (<type>floating point</type>)
+ <indexterm>
+ <primary><varname>autovacuum_vacuum_scale_factor</varname></primary>
+ <secondary>configuration parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies a fraction of the table size to add to
+ <varname>autovacuum_vacuum_threshold</varname>
+ when deciding whether to trigger a <command>VACUUM</command>.
+ The default is 0.2 (20% of table size).
+ This parameter can only be set in the <filename>postgresql.conf</filename>
+ file or on the server command line;
+ but the setting can be overridden for individual tables by
+ changing table storage parameters.
+ </para>
+ </listitem>
+ </varlistentry>
- <varlistentry id="guc-autovacuum-analyze-scale-factor" xreflabel="autovacuum_analyze_scale_factor">
- <term><varname>autovacuum_analyze_scale_factor</varname> (<type>floating point</type>)
- <indexterm>
- <primary><varname>autovacuum_analyze_scale_factor</varname></primary>
- <secondary>configuration parameter</secondary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies a fraction of the table size to add to
- <varname>autovacuum_analyze_threshold</varname>
- when deciding whether to trigger an <command>ANALYZE</command>.
- The default is 0.1 (10% of table size).
- This parameter can only be set in the <filename>postgresql.conf</filename>
- file or on the server command line;
- but the setting can be overridden for individual tables by
- changing table storage parameters.
- </para>
- </listitem>
- </varlistentry>
+ <varlistentry id="guc-autovacuum-vacuum-insert-scale-factor" xreflabel="autovacuum_vacuum_insert_scale_factor">
+ <term><varname>autovacuum_vacuum_insert_scale_factor</varname> (<type>floating point</type>)
+ <indexterm>
+ <primary><varname>autovacuum_vacuum_insert_scale_factor</varname></primary>
+ <secondary>configuration parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies a fraction of the table size to add to
+ <varname>autovacuum_vacuum_insert_threshold</varname>
+ when deciding whether to trigger a <command>VACUUM</command>.
+ The default is 0.2 (20% of table size).
+ This parameter can only be set in the <filename>postgresql.conf</filename>
+ file or on the server command line;
+ but the setting can be overridden for individual tables by
+ changing table storage parameters.
+ </para>
+ </listitem>
+ </varlistentry>
- <varlistentry id="guc-autovacuum-freeze-max-age" xreflabel="autovacuum_freeze_max_age">
- <term><varname>autovacuum_freeze_max_age</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>autovacuum_freeze_max_age</varname></primary>
- <secondary>configuration parameter</secondary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the maximum age (in transactions) that a table's
- <structname>pg_class</structname>.<structfield>relfrozenxid</structfield> field can
- attain before a <command>VACUUM</command> operation is forced
- to prevent transaction ID wraparound within the table.
- Note that the system will launch autovacuum processes to
- prevent wraparound even when autovacuum is otherwise disabled.
- </para>
+ <varlistentry id="guc-autovacuum-analyze-scale-factor" xreflabel="autovacuum_analyze_scale_factor">
+ <term><varname>autovacuum_analyze_scale_factor</varname> (<type>floating point</type>)
+ <indexterm>
+ <primary><varname>autovacuum_analyze_scale_factor</varname></primary>
+ <secondary>configuration parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies a fraction of the table size to add to
+ <varname>autovacuum_analyze_threshold</varname>
+ when deciding whether to trigger an <command>ANALYZE</command>.
+ The default is 0.1 (10% of table size).
+ This parameter can only be set in the <filename>postgresql.conf</filename>
+ file or on the server command line;
+ but the setting can be overridden for individual tables by
+ changing table storage parameters.
+ </para>
+ </listitem>
+ </varlistentry>
- <para>
- Vacuum also allows removal of old files from the
- <filename>pg_xact</filename> subdirectory, which is why the default
- is a relatively low 200 million transactions.
- This parameter can only be set at server start, but the setting
- can be reduced for individual tables by
- changing table storage parameters.
- For more information see <xref linkend="vacuum-for-wraparound"/>.
- </para>
- </listitem>
- </varlistentry>
+ <varlistentry id="guc-autovacuum-freeze-max-age" xreflabel="autovacuum_freeze_max_age">
+ <term><varname>autovacuum_freeze_max_age</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>autovacuum_freeze_max_age</varname></primary>
+ <secondary>configuration parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the maximum age (in transactions) that a table's
+ <structname>pg_class</structname>.<structfield>relfrozenxid</structfield> field can
+ attain before a <command>VACUUM</command> operation is forced
+ to prevent transaction ID wraparound within the table.
+ Note that the system will launch autovacuum processes to
+ prevent wraparound even when autovacuum is otherwise disabled.
+ </para>
- <varlistentry id="guc-autovacuum-multixact-freeze-max-age" xreflabel="autovacuum_multixact_freeze_max_age">
- <term><varname>autovacuum_multixact_freeze_max_age</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>autovacuum_multixact_freeze_max_age</varname></primary>
- <secondary>configuration parameter</secondary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the maximum age (in multixacts) that a table's
- <structname>pg_class</structname>.<structfield>relminmxid</structfield> field can
- attain before a <command>VACUUM</command> operation is forced to
- prevent multixact ID wraparound within the table.
- Note that the system will launch autovacuum processes to
- prevent wraparound even when autovacuum is otherwise disabled.
- </para>
+ <para>
+ Vacuum also allows removal of old files from the
+ <filename>pg_xact</filename> subdirectory, which is why the default
+ is a relatively low 200 million transactions.
+ This parameter can only be set at server start, but the setting
+ can be reduced for individual tables by
+ changing table storage parameters.
+ For more information see <xref linkend="vacuum-for-wraparound"/>.
+ </para>
+ </listitem>
+ </varlistentry>
- <para>
- Vacuuming multixacts also allows removal of old files from the
- <filename>pg_multixact/members</filename> and <filename>pg_multixact/offsets</filename>
- subdirectories, which is why the default is a relatively low
- 400 million multixacts.
- This parameter can only be set at server start, but the setting can
- be reduced for individual tables by changing table storage parameters.
- For more information see <xref linkend="vacuum-for-multixact-wraparound"/>.
- </para>
- </listitem>
- </varlistentry>
+ <varlistentry id="guc-autovacuum-multixact-freeze-max-age" xreflabel="autovacuum_multixact_freeze_max_age">
+ <term><varname>autovacuum_multixact_freeze_max_age</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>autovacuum_multixact_freeze_max_age</varname></primary>
+ <secondary>configuration parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the maximum age (in multixacts) that a table's
+ <structname>pg_class</structname>.<structfield>relminmxid</structfield> field can
+ attain before a <command>VACUUM</command> operation is forced to
+ prevent multixact ID wraparound within the table.
+ Note that the system will launch autovacuum processes to
+ prevent wraparound even when autovacuum is otherwise disabled.
+ </para>
- <varlistentry id="guc-autovacuum-vacuum-cost-delay" xreflabel="autovacuum_vacuum_cost_delay">
- <term><varname>autovacuum_vacuum_cost_delay</varname> (<type>floating point</type>)
- <indexterm>
- <primary><varname>autovacuum_vacuum_cost_delay</varname></primary>
- <secondary>configuration parameter</secondary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the cost delay value that will be used in automatic
- <command>VACUUM</command> operations. If -1 is specified, the regular
- <xref linkend="guc-vacuum-cost-delay"/> value will be used.
- If this value is specified without units, it is taken as milliseconds.
- The default value is 2 milliseconds.
- This parameter can only be set in the <filename>postgresql.conf</filename>
- file or on the server command line;
- but the setting can be overridden for individual tables by
- changing table storage parameters.
- </para>
- </listitem>
- </varlistentry>
+ <para>
+ Vacuuming multixacts also allows removal of old files from the
+ <filename>pg_multixact/members</filename> and <filename>pg_multixact/offsets</filename>
+ subdirectories, which is why the default is a relatively low
+ 400 million multixacts.
+ This parameter can only be set at server start, but the setting can
+ be reduced for individual tables by changing table storage parameters.
+ For more information see <xref linkend="vacuum-for-multixact-wraparound"/>.
+ </para>
+ </listitem>
+ </varlistentry>
- <varlistentry id="guc-autovacuum-vacuum-cost-limit" xreflabel="autovacuum_vacuum_cost_limit">
- <term><varname>autovacuum_vacuum_cost_limit</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>autovacuum_vacuum_cost_limit</varname></primary>
- <secondary>configuration parameter</secondary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the cost limit value that will be used in automatic
- <command>VACUUM</command> operations. If -1 is specified (which is the
- default), the regular
- <xref linkend="guc-vacuum-cost-limit"/> value will be used. Note that
- the value is distributed proportionally among the running autovacuum
- workers, if there is more than one, so that the sum of the limits for
- each worker does not exceed the value of this variable.
- This parameter can only be set in the <filename>postgresql.conf</filename>
- file or on the server command line;
- but the setting can be overridden for individual tables by
- changing table storage parameters.
- </para>
- </listitem>
- </varlistentry>
+ <varlistentry id="guc-autovacuum-vacuum-cost-delay" xreflabel="autovacuum_vacuum_cost_delay">
+ <term><varname>autovacuum_vacuum_cost_delay</varname> (<type>floating point</type>)
+ <indexterm>
+ <primary><varname>autovacuum_vacuum_cost_delay</varname></primary>
+ <secondary>configuration parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the cost delay value that will be used in automatic
+ <command>VACUUM</command> operations. If -1 is specified, the regular
+ <xref linkend="guc-vacuum-cost-delay"/> value will be used.
+ If this value is specified without units, it is taken as milliseconds.
+ The default value is 2 milliseconds.
+ This parameter can only be set in the <filename>postgresql.conf</filename>
+ file or on the server command line;
+ but the setting can be overridden for individual tables by
+ changing table storage parameters.
+ </para>
+ </listitem>
+ </varlistentry>
- </variablelist>
+ <varlistentry id="guc-autovacuum-vacuum-cost-limit" xreflabel="autovacuum_vacuum_cost_limit">
+ <term><varname>autovacuum_vacuum_cost_limit</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>autovacuum_vacuum_cost_limit</varname></primary>
+ <secondary>configuration parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the cost limit value that will be used in automatic
+ <command>VACUUM</command> operations. If -1 is specified (which is the
+ default), the regular
+ <xref linkend="guc-vacuum-cost-limit"/> value will be used. Note that
+ the value is distributed proportionally among the running autovacuum
+ workers, if there is more than one, so that the sum of the limits for
+ each worker does not exceed the value of this variable.
+ This parameter can only be set in the <filename>postgresql.conf</filename>
+ file or on the server command line;
+ but the setting can be overridden for individual tables by
+ changing table storage parameters.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </sect2>
+
+ <sect2 id="runtime-config-resource-vacuum-cost">
+ <title>Cost-based Vacuum Delay</title>
+
+ <para>
+ During the execution of <xref linkend="sql-vacuum"/>
+ and <xref linkend="sql-analyze"/>
+ commands, the system maintains an internal counter that keeps track of
+ the estimated cost of the various I/O operations that are performed.
+ When the accumulated cost reaches a limit (specified by
+ <varname>vacuum_cost_limit</varname>), the process performing the
+ operation will sleep for a short period of time, as specified by
+ <varname>vacuum_cost_delay</varname>. Then it will reset the counter and
+ continue execution.
+ </para>
+
+ <para>
+ The intent of this feature is to allow administrators to reduce
+ the I/O impact of these commands on concurrent database
+ activity. There are many situations where it is not
+ important that maintenance commands like
+ <command>VACUUM</command> and <command>ANALYZE</command> finish
+ quickly; however, it is usually very important that these
+ commands do not significantly interfere with the ability of the
+ system to perform other database operations. Cost-based vacuum
+ delay provides a way for administrators to achieve this.
+ </para>
+
+ <para>
+ This feature is disabled by default for manually issued
+ <command>VACUUM</command> commands. To enable it, set the
+ <varname>vacuum_cost_delay</varname> variable to a nonzero
+ value.
+ </para>
+
+ <variablelist>
+ <varlistentry id="guc-vacuum-cost-delay" xreflabel="vacuum_cost_delay">
+ <term><varname>vacuum_cost_delay</varname> (<type>floating point</type>)
+ <indexterm>
+ <primary><varname>vacuum_cost_delay</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ The amount of time that the process will sleep
+ when the cost limit has been exceeded.
+ If this value is specified without units, it is taken as milliseconds.
+ The default value is zero, which disables the cost-based vacuum
+ delay feature. Positive values enable cost-based vacuuming.
+ </para>
+
+ <para>
+ When using cost-based vacuuming, appropriate values for
+ <varname>vacuum_cost_delay</varname> are usually quite small, perhaps
+ less than 1 millisecond. While <varname>vacuum_cost_delay</varname>
+ can be set to fractional-millisecond values, such delays may not be
+ measured accurately on older platforms. On such platforms,
+ increasing <command>VACUUM</command>'s throttled resource consumption
+ above what you get at 1ms will require changing the other vacuum cost
+ parameters. You should, nonetheless,
+ keep <varname>vacuum_cost_delay</varname> as small as your platform
+ will consistently measure; large delays are not helpful.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-vacuum-cost-page-hit" xreflabel="vacuum_cost_page_hit">
+ <term><varname>vacuum_cost_page_hit</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>vacuum_cost_page_hit</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ The estimated cost for vacuuming a buffer found in the shared buffer
+ cache. It represents the cost to lock the buffer pool, lookup
+ the shared hash table and scan the content of the page. The
+ default value is one.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-vacuum-cost-page-miss" xreflabel="vacuum_cost_page_miss">
+ <term><varname>vacuum_cost_page_miss</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>vacuum_cost_page_miss</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ The estimated cost for vacuuming a buffer that has to be read from
+ disk. This represents the effort to lock the buffer pool,
+ lookup the shared hash table, read the desired block in from
+ the disk and scan its content. The default value is 2.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-vacuum-cost-page-dirty" xreflabel="vacuum_cost_page_dirty">
+ <term><varname>vacuum_cost_page_dirty</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>vacuum_cost_page_dirty</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ The estimated cost charged when vacuum modifies a block that was
+ previously clean. It represents the extra I/O required to
+ flush the dirty block out to disk again. The default value is
+ 20.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-vacuum-cost-limit" xreflabel="vacuum_cost_limit">
+ <term><varname>vacuum_cost_limit</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>vacuum_cost_limit</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ This is the accumulated cost that will cause the vacuuming process to sleep
+ for <varname>vacuum_cost_delay</varname>. The default is 200.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+
+ <note>
+ <para>
+ There are certain operations that hold critical locks and should
+ therefore complete as quickly as possible. Cost-based vacuum
+ delays do not occur during such operations. Therefore it is
+ possible that the cost accumulates far higher than the specified
+ limit. To avoid uselessly long delays in such cases, the actual
+ delay is calculated as <varname>vacuum_cost_delay</varname> *
+ <varname>accumulated_balance</varname> /
+ <varname>vacuum_cost_limit</varname> with a maximum of
+ <varname>vacuum_cost_delay</varname> * 4.
+ </para>
+ </note>
+ </sect2>
+
+ <sect2 id="runtime-config-vacuum-freezing">
+ <title>Freezing</title>
+
+ <para>
+ Vacuum operations are also responsible for freezing rows to avoid
+ transaction ID wraparound. These settings control vacuum's freezing
+ behavior. See <xref linkend="vacuum-for-wraparound"/> for more
+ information on transaction ID wraparound and tuning these parameters.
+ </para>
+
+ <variablelist>
+ <varlistentry id="guc-vacuum-freeze-table-age" xreflabel="vacuum_freeze_table_age">
+ <term><varname>vacuum_freeze_table_age</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>vacuum_freeze_table_age</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ <command>VACUUM</command> performs an aggressive scan if the table's
+ <structname>pg_class</structname>.<structfield>relfrozenxid</structfield> field has reached
+ the age specified by this setting. An aggressive scan differs from
+ a regular <command>VACUUM</command> in that it visits every page that might
+ contain unfrozen XIDs or MXIDs, not just those that might contain dead
+ tuples. The default is 150 million transactions. Although users can
+ set this value anywhere from zero to two billion, <command>VACUUM</command>
+ will silently limit the effective value to 95% of
+ <xref linkend="guc-autovacuum-freeze-max-age"/>, so that a
+ periodic manual <command>VACUUM</command> has a chance to run before an
+ anti-wraparound autovacuum is launched for the table. For more
+ information see
+ <xref linkend="vacuum-for-wraparound"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-vacuum-freeze-min-age" xreflabel="vacuum_freeze_min_age">
+ <term><varname>vacuum_freeze_min_age</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>vacuum_freeze_min_age</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the cutoff age (in transactions) that
+ <command>VACUUM</command> should use to decide whether to
+ trigger freezing of pages that have an older XID.
+ The default is 50 million transactions. Although
+ users can set this value anywhere from zero to one billion,
+ <command>VACUUM</command> will silently limit the effective value to half
+ the value of <xref linkend="guc-autovacuum-freeze-max-age"/>, so
+ that there is not an unreasonably short time between forced
+ autovacuums. For more information see <xref
+ linkend="vacuum-for-wraparound"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-vacuum-failsafe-age" xreflabel="vacuum_failsafe_age">
+ <term><varname>vacuum_failsafe_age</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>vacuum_failsafe_age</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the maximum age (in transactions) that a table's
+ <structname>pg_class</structname>.<structfield>relfrozenxid</structfield>
+ field can attain before <command>VACUUM</command> takes
+ extraordinary measures to avoid system-wide transaction ID
+ wraparound failure. This is <command>VACUUM</command>'s
+ strategy of last resort. The failsafe typically triggers
+ when an autovacuum to prevent transaction ID wraparound has
+ already been running for some time, though it's possible for
+ the failsafe to trigger during any <command>VACUUM</command>.
+ </para>
+ <para>
+ When the failsafe is triggered, any cost-based delay that is
+ in effect will no longer be applied, further non-essential
+ maintenance tasks (such as index vacuuming) are bypassed, and any
+ <glossterm linkend="glossary-buffer-access-strategy">Buffer Access Strategy</glossterm>
+ in use will be disabled resulting in <command>VACUUM</command> being
+ free to make use of all of
+ <glossterm linkend="glossary-shared-memory">shared buffers</glossterm>.
+ </para>
+ <para>
+ The default is 1.6 billion transactions. Although users can
+ set this value anywhere from zero to 2.1 billion,
+ <command>VACUUM</command> will silently adjust the effective
+ value to no less than 105% of <xref
+ linkend="guc-autovacuum-freeze-max-age"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-vacuum-multixact-freeze-table-age" xreflabel="vacuum_multixact_freeze_table_age">
+ <term><varname>vacuum_multixact_freeze_table_age</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>vacuum_multixact_freeze_table_age</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ <command>VACUUM</command> performs an aggressive scan if the table's
+ <structname>pg_class</structname>.<structfield>relminmxid</structfield> field has reached
+ the age specified by this setting. An aggressive scan differs from
+ a regular <command>VACUUM</command> in that it visits every page that might
+ contain unfrozen XIDs or MXIDs, not just those that might contain dead
+ tuples. The default is 150 million multixacts.
+ Although users can set this value anywhere from zero to two billion,
+ <command>VACUUM</command> will silently limit the effective value to 95% of
+ <xref linkend="guc-autovacuum-multixact-freeze-max-age"/>, so that a
+ periodic manual <command>VACUUM</command> has a chance to run before an
+ anti-wraparound is launched for the table.
+ For more information see <xref linkend="vacuum-for-multixact-wraparound"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-vacuum-multixact-freeze-min-age" xreflabel="vacuum_multixact_freeze_min_age">
+ <term><varname>vacuum_multixact_freeze_min_age</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>vacuum_multixact_freeze_min_age</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the cutoff age (in multixacts) that <command>VACUUM</command>
+ should use to decide whether to trigger freezing of pages with
+ an older multixact ID. The default is 5 million multixacts.
+ Although users can set this value anywhere from zero to one billion,
+ <command>VACUUM</command> will silently limit the effective value to half
+ the value of <xref linkend="guc-autovacuum-multixact-freeze-max-age"/>,
+ so that there is not an unreasonably short time between forced
+ autovacuums.
+ For more information see <xref linkend="vacuum-for-multixact-wraparound"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-vacuum-multixact-failsafe-age" xreflabel="vacuum_multixact_failsafe_age">
+ <term><varname>vacuum_multixact_failsafe_age</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>vacuum_multixact_failsafe_age</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the maximum age (in multixacts) that a table's
+ <structname>pg_class</structname>.<structfield>relminmxid</structfield>
+ field can attain before <command>VACUUM</command> takes
+ extraordinary measures to avoid system-wide multixact ID
+ wraparound failure. This is <command>VACUUM</command>'s
+ strategy of last resort. The failsafe typically triggers when
+ an autovacuum to prevent transaction ID wraparound has already
+ been running for some time, though it's possible for the
+ failsafe to trigger during any <command>VACUUM</command>.
+ </para>
+ <para>
+ When the failsafe is triggered, any cost-based delay that is
+ in effect will no longer be applied, and further non-essential
+ maintenance tasks (such as index vacuuming) are bypassed.
+ </para>
+ <para>
+ The default is 1.6 billion multixacts. Although users can set
+ this value anywhere from zero to 2.1 billion,
+ <command>VACUUM</command> will silently adjust the effective
+ value to no less than 105% of <xref
+ linkend="guc-autovacuum-multixact-freeze-max-age"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </sect2>
</sect1>
<sect1 id="runtime-config-client">
@@ -9593,168 +9773,6 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
</listitem>
</varlistentry>
- <varlistentry id="guc-vacuum-freeze-table-age" xreflabel="vacuum_freeze_table_age">
- <term><varname>vacuum_freeze_table_age</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>vacuum_freeze_table_age</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- <command>VACUUM</command> performs an aggressive scan if the table's
- <structname>pg_class</structname>.<structfield>relfrozenxid</structfield> field has reached
- the age specified by this setting. An aggressive scan differs from
- a regular <command>VACUUM</command> in that it visits every page that might
- contain unfrozen XIDs or MXIDs, not just those that might contain dead
- tuples. The default is 150 million transactions. Although users can
- set this value anywhere from zero to two billion, <command>VACUUM</command>
- will silently limit the effective value to 95% of
- <xref linkend="guc-autovacuum-freeze-max-age"/>, so that a
- periodic manual <command>VACUUM</command> has a chance to run before an
- anti-wraparound autovacuum is launched for the table. For more
- information see
- <xref linkend="vacuum-for-wraparound"/>.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="guc-vacuum-freeze-min-age" xreflabel="vacuum_freeze_min_age">
- <term><varname>vacuum_freeze_min_age</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>vacuum_freeze_min_age</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the cutoff age (in transactions) that
- <command>VACUUM</command> should use to decide whether to
- trigger freezing of pages that have an older XID.
- The default is 50 million transactions. Although
- users can set this value anywhere from zero to one billion,
- <command>VACUUM</command> will silently limit the effective value to half
- the value of <xref linkend="guc-autovacuum-freeze-max-age"/>, so
- that there is not an unreasonably short time between forced
- autovacuums. For more information see <xref
- linkend="vacuum-for-wraparound"/>.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="guc-vacuum-failsafe-age" xreflabel="vacuum_failsafe_age">
- <term><varname>vacuum_failsafe_age</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>vacuum_failsafe_age</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the maximum age (in transactions) that a table's
- <structname>pg_class</structname>.<structfield>relfrozenxid</structfield>
- field can attain before <command>VACUUM</command> takes
- extraordinary measures to avoid system-wide transaction ID
- wraparound failure. This is <command>VACUUM</command>'s
- strategy of last resort. The failsafe typically triggers
- when an autovacuum to prevent transaction ID wraparound has
- already been running for some time, though it's possible for
- the failsafe to trigger during any <command>VACUUM</command>.
- </para>
- <para>
- When the failsafe is triggered, any cost-based delay that is
- in effect will no longer be applied, further non-essential
- maintenance tasks (such as index vacuuming) are bypassed, and any
- <glossterm linkend="glossary-buffer-access-strategy">Buffer Access Strategy</glossterm>
- in use will be disabled resulting in <command>VACUUM</command> being
- free to make use of all of
- <glossterm linkend="glossary-shared-memory">shared buffers</glossterm>.
- </para>
- <para>
- The default is 1.6 billion transactions. Although users can
- set this value anywhere from zero to 2.1 billion,
- <command>VACUUM</command> will silently adjust the effective
- value to no less than 105% of <xref
- linkend="guc-autovacuum-freeze-max-age"/>.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="guc-vacuum-multixact-freeze-table-age" xreflabel="vacuum_multixact_freeze_table_age">
- <term><varname>vacuum_multixact_freeze_table_age</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>vacuum_multixact_freeze_table_age</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- <command>VACUUM</command> performs an aggressive scan if the table's
- <structname>pg_class</structname>.<structfield>relminmxid</structfield> field has reached
- the age specified by this setting. An aggressive scan differs from
- a regular <command>VACUUM</command> in that it visits every page that might
- contain unfrozen XIDs or MXIDs, not just those that might contain dead
- tuples. The default is 150 million multixacts.
- Although users can set this value anywhere from zero to two billion,
- <command>VACUUM</command> will silently limit the effective value to 95% of
- <xref linkend="guc-autovacuum-multixact-freeze-max-age"/>, so that a
- periodic manual <command>VACUUM</command> has a chance to run before an
- anti-wraparound is launched for the table.
- For more information see <xref linkend="vacuum-for-multixact-wraparound"/>.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="guc-vacuum-multixact-freeze-min-age" xreflabel="vacuum_multixact_freeze_min_age">
- <term><varname>vacuum_multixact_freeze_min_age</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>vacuum_multixact_freeze_min_age</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the cutoff age (in multixacts) that <command>VACUUM</command>
- should use to decide whether to trigger freezing of pages with
- an older multixact ID. The default is 5 million multixacts.
- Although users can set this value anywhere from zero to one billion,
- <command>VACUUM</command> will silently limit the effective value to half
- the value of <xref linkend="guc-autovacuum-multixact-freeze-max-age"/>,
- so that there is not an unreasonably short time between forced
- autovacuums.
- For more information see <xref linkend="vacuum-for-multixact-wraparound"/>.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="guc-vacuum-multixact-failsafe-age" xreflabel="vacuum_multixact_failsafe_age">
- <term><varname>vacuum_multixact_failsafe_age</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>vacuum_multixact_failsafe_age</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the maximum age (in multixacts) that a table's
- <structname>pg_class</structname>.<structfield>relminmxid</structfield>
- field can attain before <command>VACUUM</command> takes
- extraordinary measures to avoid system-wide multixact ID
- wraparound failure. This is <command>VACUUM</command>'s
- strategy of last resort. The failsafe typically triggers when
- an autovacuum to prevent transaction ID wraparound has already
- been running for some time, though it's possible for the
- failsafe to trigger during any <command>VACUUM</command>.
- </para>
- <para>
- When the failsafe is triggered, any cost-based delay that is
- in effect will no longer be applied, and further non-essential
- maintenance tasks (such as index vacuuming) are bypassed.
- </para>
- <para>
- The default is 1.6 billion multixacts. Although users can set
- this value anywhere from zero to 2.1 billion,
- <command>VACUUM</command> will silently adjust the effective
- value to no less than 105% of <xref
- linkend="guc-autovacuum-multixact-freeze-max-age"/>.
- </para>
- </listitem>
- </varlistentry>
-
<varlistentry id="guc-bytea-output" xreflabel="bytea_output">
<term><varname>bytea_output</varname> (<type>enum</type>)
<indexterm>
--
2.45.2
Re: Moving the vacuum GUCs' docs out of the Client Connection Defaults section
On 9 Jan 2025, at 02:30, Melanie Plageman <melanieplageman@gmail.com> wrote:
On Wed, Jan 8, 2025 at 6:35 AM Daniel Gustafsson <daniel@yesql.se> wrote:
On 7 Jan 2025, at 21:14, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I might be wrong, but I had the idea that our docs website has a
capability to provide such redirects. You'd probably need to ask
about that on the pgsql-www list, unless somebody who knows the
answer notices this thread.There is functionality in pgweb to provide a page alias for whenever a page is
renamed to keep the links to other versions working. One example is:https://www.postgresql.org/docs/9.4/catalog-pg-replication-slots.html
https://www.postgresql.org/docs/17/view-pg-replication-slots.htmlThere is also a redirect functionality, which isn't used anywhere right now,
but ideally could be used to redirect bookmarks for /current/<oldname> to
/current/<newname>.Thanks to Nathan and Tom for noticing and Daniel for replying. So, if
I understand correctly, pgweb will do this for me without me needing
to do anything in my patch?
Correct, it's a setting in the database on the postgresql.org website.
--
Daniel Gustafsson
Re: Moving the vacuum GUCs' docs out of the Client Connection Defaults section
On 09.01.25 02:45, Melanie Plageman wrote:
On Wed, Jan 8, 2025 at 8:39 AM Peter Eisentraut <peter@eisentraut.org> wrote:
On 07.01.25 18:31, Melanie Plageman wrote:
Oh, one thing I forgot to say. Though I increased the indentation of
some of the subsections that I moved, I didn't rewrap the lines
because they were already not wrapped to 78. I can do this, but I
can't tell from the existing docs what text width the paragraphs of
text are supposed to be wrapped to.For a patch that moves things around like this, I would leave everything
as is and not rewrap. That makes it easier to follow what's going on
with "git diff -w", "git show -w" etc.In .dir-locals.el, there is this configuration for Emacs:
(nxml-mode . ((fill-column . 78)
(indent-tabs-mode . nil)))So that's one data point about what the line length should be.
Well, in this case, the diff won't look different with git show/diff
-w because moving them to another part of the file is more than a
whitespace change.
Correct. The right option is
git diff --color-moved
This can also be put into .gitconfig.
Re: Moving the vacuum GUCs' docs out of the Client Connection Defaults section
On 9 Jan 2025, at 02:45, Melanie Plageman <melanieplageman@gmail.com> wrote:
Attached is v2 (required a rebase).
I think this is a really good restructuring which will make life easier for our
users. Some of the comments below are on wording which wasn't introduced in
this patch, but which I hadn't thought about before, so feel free to ignore
those comments.
+ <sect2 id="runtime-config-vacuum-freezing">
+ <title>Freezing</title>
+
+ <para>
Trying to read this as a new user, I think it would be good to start this
subsection with a sentence describing what freezing actually is. Vacuum is
hard enough for users as it is =)+ default value is one.
Grepping around indicates that we typically use the numeric value rather than
writing it in text, and the next settting down has "default value is 2". For
consistency I would change that to "1" instead of "one".
+ <varname>vacuum_cost_delay</varname>. Then it will reset the counter and
+ continue execution.
I know starting a sentence with "Then" is grammatically correct when it's the
last sentence in a paragraph, but being a non-native speaker I always find
myself re-reading such sentences to parse them as they stand out as odd.+ can be set to fractional-millisecond values, such delays may not be
+ measured accurately on older platforms. On such platforms,
This sentence seems quite vague and hard to act on for users, what qualifies as
an "older platform" by the time v18 rolls around (this was added in v12). I'm
sure there are such platforms in existence that postgres 18 will run on, but
are we helping users with ambiguity?--
Daniel Gustafsson
Re: Moving the vacuum GUCs' docs out of the Client Connection Defaults section
On Thu, Jan 9, 2025 at 5:05 PM Daniel Gustafsson <daniel@yesql.se> wrote:
I think this is a really good restructuring which will make life easier for our
users. Some of the comments below are on wording which wasn't introduced in
this patch, but which I hadn't thought about before, so feel free to ignore
those comments.+ <sect2 id="runtime-config-vacuum-freezing"> + <title>Freezing</title> + + <para> Trying to read this as a new user, I think it would be good to start this subsection with a sentence describing what freezing actually is. Vacuum is hard enough for users as it is =)
I've taken a stab at improving this. Let me know if you think it works.
+ default value is one.
Grepping around indicates that we typically use the numeric value rather than
writing it in text, and the next settting down has "default value is 2". For
consistency I would change that to "1" instead of "one".
I think this is a reasonable cleanup to lump in with the rest of this
commit. I have taken the liberty of also adding a <literal> tag and
then updating the other places in the proposed "Vacuuming" subsection
where a literal default value is specified without the <literal> tag.
+ <varname>vacuum_cost_delay</varname>. Then it will reset the counter and + continue execution. I know starting a sentence with "Then" is grammatically correct when it's the last sentence in a paragraph, but being a non-native speaker I always find myself re-reading such sentences to parse them as they stand out as odd.
I hear you. I couldn't think of something much clearer, so I left it as is.
+ can be set to fractional-millisecond values, such delays may not be + measured accurately on older platforms. On such platforms, This sentence seems quite vague and hard to act on for users, what qualifies as an "older platform" by the time v18 rolls around (this was added in v12). I'm sure there are such platforms in existence that postgres 18 will run on, but are we helping users with ambiguity?
While I agree that what counted as older hardware in 2019 may no
longer be around at all, I am more hesitant to update this in the same
commit as a bunch of other cut-and-pastes. Someone at some point
decided this was important to point out, and I don't have sufficient
evidence that it no longer makes sense. And if I did, I'd probably
want to update this part of the docs in a dedicated commit.
- Melanie
Attachments:
v3-0001-Consolidate-docs-for-vacuum-related-GUCs-in-new-s.patchapplication/octet-stream; name=v3-0001-Consolidate-docs-for-vacuum-related-GUCs-in-new-s.patchDownload
From f0a9c874ceb3e02b407e70ee4d56d9bd6569a200 Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplageman@gmail.com>
Date: Tue, 7 Jan 2025 12:31:37 -0500
Subject: [PATCH v3] Consolidate docs for vacuum-related GUCs in new subsection
GUCs related to vacuum's freezing behavior were documented in a
subsection of the Client Connection Defaults documentation. These GUCs
don't belong there, as they affect the freezing behavior of all vacuums
-- including autovacuums.
There wasn't a clear alternative location, so this commit makes a new
"Server Configuration" docs subsection, "Vacuuming", with a subsection
for "Freezing". It also moves the "Automatic Vacuuming" subsection and
the docs on GUCs controlling cost-based vacuum delay under the new
"Vacuuming" subsection.
The other vacuum-related GUCs under the "Resource Consumption"
subsection have been left in their current location, as they seem to fit
there.
The GUCs' documentation was largely lifted and shifted. The only
modification made was the addition of a few missing <literal> tags.
Reviewed-by: Daniel Gustafsson <daniel@yesql.se>
Discussion: https://postgr.es/m/flat/CAAKRu_aQUOaMYrcjNuXeSkJtaX9oRUzKP57bsYbC0gVVWS%2BcbA%40mail.gmail.com
---
doc/src/sgml/config.sgml | 1254 +++++++++++++++++++-------------------
1 file changed, 643 insertions(+), 611 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index f1ab614575a..3f41a17b1fe 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -2367,149 +2367,6 @@ include_dir 'conf.d'
</variablelist>
</sect2>
- <sect2 id="runtime-config-resource-vacuum-cost">
- <title>Cost-based Vacuum Delay</title>
-
- <para>
- During the execution of <xref linkend="sql-vacuum"/>
- and <xref linkend="sql-analyze"/>
- commands, the system maintains an
- internal counter that keeps track of the estimated cost of the
- various I/O operations that are performed. When the accumulated
- cost reaches a limit (specified by
- <varname>vacuum_cost_limit</varname>), the process performing
- the operation will sleep for a short period of time, as specified by
- <varname>vacuum_cost_delay</varname>. Then it will reset the
- counter and continue execution.
- </para>
-
- <para>
- The intent of this feature is to allow administrators to reduce
- the I/O impact of these commands on concurrent database
- activity. There are many situations where it is not
- important that maintenance commands like
- <command>VACUUM</command> and <command>ANALYZE</command> finish
- quickly; however, it is usually very important that these
- commands do not significantly interfere with the ability of the
- system to perform other database operations. Cost-based vacuum
- delay provides a way for administrators to achieve this.
- </para>
-
- <para>
- This feature is disabled by default for manually issued
- <command>VACUUM</command> commands. To enable it, set the
- <varname>vacuum_cost_delay</varname> variable to a nonzero
- value.
- </para>
-
- <variablelist>
- <varlistentry id="guc-vacuum-cost-delay" xreflabel="vacuum_cost_delay">
- <term><varname>vacuum_cost_delay</varname> (<type>floating point</type>)
- <indexterm>
- <primary><varname>vacuum_cost_delay</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- The amount of time that the process will sleep
- when the cost limit has been exceeded.
- If this value is specified without units, it is taken as milliseconds.
- The default value is zero, which disables the cost-based vacuum
- delay feature. Positive values enable cost-based vacuuming.
- </para>
-
- <para>
- When using cost-based vacuuming, appropriate values for
- <varname>vacuum_cost_delay</varname> are usually quite small, perhaps
- less than 1 millisecond. While <varname>vacuum_cost_delay</varname>
- can be set to fractional-millisecond values, such delays may not be
- measured accurately on older platforms. On such platforms,
- increasing <command>VACUUM</command>'s throttled resource consumption
- above what you get at 1ms will require changing the other vacuum cost
- parameters. You should, nonetheless,
- keep <varname>vacuum_cost_delay</varname> as small as your platform
- will consistently measure; large delays are not helpful.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="guc-vacuum-cost-page-hit" xreflabel="vacuum_cost_page_hit">
- <term><varname>vacuum_cost_page_hit</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>vacuum_cost_page_hit</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- The estimated cost for vacuuming a buffer found in the shared buffer
- cache. It represents the cost to lock the buffer pool, lookup
- the shared hash table and scan the content of the page. The
- default value is one.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="guc-vacuum-cost-page-miss" xreflabel="vacuum_cost_page_miss">
- <term><varname>vacuum_cost_page_miss</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>vacuum_cost_page_miss</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- The estimated cost for vacuuming a buffer that has to be read from
- disk. This represents the effort to lock the buffer pool,
- lookup the shared hash table, read the desired block in from
- the disk and scan its content. The default value is 2.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="guc-vacuum-cost-page-dirty" xreflabel="vacuum_cost_page_dirty">
- <term><varname>vacuum_cost_page_dirty</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>vacuum_cost_page_dirty</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- The estimated cost charged when vacuum modifies a block that was
- previously clean. It represents the extra I/O required to
- flush the dirty block out to disk again. The default value is
- 20.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="guc-vacuum-cost-limit" xreflabel="vacuum_cost_limit">
- <term><varname>vacuum_cost_limit</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>vacuum_cost_limit</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- This is the accumulated cost that will cause the vacuuming process to sleep
- for <varname>vacuum_cost_delay</varname>. The default is 200.
- </para>
- </listitem>
- </varlistentry>
- </variablelist>
-
- <note>
- <para>
- There are certain operations that hold critical locks and should
- therefore complete as quickly as possible. Cost-based vacuum
- delays do not occur during such operations. Therefore it is
- possible that the cost accumulates far higher than the specified
- limit. To avoid uselessly long delays in such cases, the actual
- delay is calculated as <varname>vacuum_cost_delay</varname> *
- <varname>accumulated_balance</varname> /
- <varname>vacuum_cost_limit</varname> with a maximum of
- <varname>vacuum_cost_delay</varname> * 4.
- </para>
- </note>
- </sect2>
<sect2 id="runtime-config-resource-background-writer">
<title>Background Writer</title>
@@ -8590,14 +8447,22 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
</sect2>
</sect1>
- <sect1 id="runtime-config-autovacuum">
- <title>Automatic Vacuuming</title>
+ <sect1 id="runtime-config-vacuum">
+ <title>Vacuuming</title>
<indexterm>
- <primary>autovacuum</primary>
+ <primary>vacuum</primary>
<secondary>configuration parameters</secondary>
</indexterm>
+ <para>
+ These parameters control vacuuming behavior. For more information on the
+ purpose and responsibilities of vacuum, see <xref linkend="routine-vacuuming"/>.
+ </para>
+
+ <sect2 id="runtime-config-autovacuum">
+ <title>Automatic Vacuuming</title>
+
<para>
These settings control the behavior of the <firstterm>autovacuum</firstterm>
feature. Refer to <xref linkend="autovacuum"/> for more information.
@@ -8605,325 +8470,654 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
basis; see <xref linkend="sql-createtable-storage-parameters"/>.
</para>
- <variablelist>
-
- <varlistentry id="guc-autovacuum" xreflabel="autovacuum">
- <term><varname>autovacuum</varname> (<type>boolean</type>)
- <indexterm>
- <primary><varname>autovacuum</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Controls whether the server should run the
- autovacuum launcher daemon. This is on by default; however,
- <xref linkend="guc-track-counts"/> must also be enabled for
- autovacuum to work.
- This parameter can only be set in the <filename>postgresql.conf</filename>
- file or on the server command line; however, autovacuuming can be
- disabled for individual tables by changing table storage parameters.
- </para>
- <para>
- Note that even when this parameter is disabled, the system
- will launch autovacuum processes if necessary to
- prevent transaction ID wraparound. See <xref
- linkend="vacuum-for-wraparound"/> for more information.
- </para>
- </listitem>
- </varlistentry>
+ <variablelist>
- <varlistentry id="guc-autovacuum-worker-slots" xreflabel="autovacuum_worker_slots">
- <term><varname>autovacuum_worker_slots</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>autovacuum_worker_slots</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the number of backend slots to reserve for autovacuum worker
- processes. The default is typically 16 slots, but might be less if
- your kernel settings will not support it (as determined during initdb).
- This parameter can only be set at server start.
- </para>
- <para>
- When changing this value, consider also adjusting
- <xref linkend="guc-autovacuum-max-workers"/>.
- </para>
- </listitem>
- </varlistentry>
+ <varlistentry id="guc-autovacuum" xreflabel="autovacuum">
+ <term><varname>autovacuum</varname> (<type>boolean</type>)
+ <indexterm>
+ <primary><varname>autovacuum</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Controls whether the server should run the
+ autovacuum launcher daemon. This is on by default; however,
+ <xref linkend="guc-track-counts"/> must also be enabled for
+ autovacuum to work.
+ This parameter can only be set in the <filename>postgresql.conf</filename>
+ file or on the server command line; however, autovacuuming can be
+ disabled for individual tables by changing table storage parameters.
+ </para>
+ <para>
+ Note that even when this parameter is disabled, the system
+ will launch autovacuum processes if necessary to
+ prevent transaction ID wraparound. See <xref
+ linkend="vacuum-for-wraparound"/> for more information.
+ </para>
+ </listitem>
+ </varlistentry>
- <varlistentry id="guc-autovacuum-max-workers" xreflabel="autovacuum_max_workers">
- <term><varname>autovacuum_max_workers</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>autovacuum_max_workers</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the maximum number of autovacuum processes (other than the
- autovacuum launcher) that may be running at any one time. The default
- is three. This parameter can only be set in the
- <filename>postgresql.conf</filename> file or on the server command line.
- </para>
- <para>
- Note that a setting for this value which is higher than
- <xref linkend="guc-autovacuum-worker-slots"/> will have no effect,
- since autovacuum workers are taken from the pool of slots established
- by that setting.
- </para>
- </listitem>
- </varlistentry>
+ <varlistentry id="guc-autovacuum-worker-slots" xreflabel="autovacuum_worker_slots">
+ <term><varname>autovacuum_worker_slots</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>autovacuum_worker_slots</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the number of backend slots to reserve for autovacuum worker
+ processes. The default is typically 16 slots, but might be less if
+ your kernel settings will not support it (as determined during initdb).
+ This parameter can only be set at server start.
+ </para>
+ <para>
+ When changing this value, consider also adjusting
+ <xref linkend="guc-autovacuum-max-workers"/>.
+ </para>
+ </listitem>
+ </varlistentry>
- <varlistentry id="guc-autovacuum-naptime" xreflabel="autovacuum_naptime">
- <term><varname>autovacuum_naptime</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>autovacuum_naptime</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the minimum delay between autovacuum runs on any given
- database. In each round the daemon examines the
- database and issues <command>VACUUM</command> and <command>ANALYZE</command> commands
- as needed for tables in that database.
- If this value is specified without units, it is taken as seconds.
- The default is one minute (<literal>1min</literal>).
- This parameter can only be set in the <filename>postgresql.conf</filename>
- file or on the server command line.
- </para>
- </listitem>
- </varlistentry>
+ <varlistentry id="guc-autovacuum-max-workers" xreflabel="autovacuum_max_workers">
+ <term><varname>autovacuum_max_workers</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>autovacuum_max_workers</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the maximum number of autovacuum processes (other than the
+ autovacuum launcher) that may be running at any one time. The default
+ is <literal>3</literal>. This parameter can only be set in the
+ <filename>postgresql.conf</filename> file or on the server command line.
+ </para>
+ <para>
+ Note that a setting for this value which is higher than
+ <xref linkend="guc-autovacuum-worker-slots"/> will have no effect,
+ since autovacuum workers are taken from the pool of slots established
+ by that setting.
+ </para>
+ </listitem>
+ </varlistentry>
- <varlistentry id="guc-autovacuum-vacuum-threshold" xreflabel="autovacuum_vacuum_threshold">
- <term><varname>autovacuum_vacuum_threshold</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>autovacuum_vacuum_threshold</varname></primary>
- <secondary>configuration parameter</secondary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the minimum number of updated or deleted tuples needed
- to trigger a <command>VACUUM</command> in any one table.
- The default is 50 tuples.
- This parameter can only be set in the <filename>postgresql.conf</filename>
- file or on the server command line;
- but the setting can be overridden for individual tables by
- changing table storage parameters.
- </para>
- </listitem>
- </varlistentry>
+ <varlistentry id="guc-autovacuum-naptime" xreflabel="autovacuum_naptime">
+ <term><varname>autovacuum_naptime</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>autovacuum_naptime</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the minimum delay between autovacuum runs on any given
+ database. In each round the daemon examines the
+ database and issues <command>VACUUM</command> and <command>ANALYZE</command> commands
+ as needed for tables in that database.
+ If this value is specified without units, it is taken as seconds.
+ The default is one minute (<literal>1min</literal>).
+ This parameter can only be set in the <filename>postgresql.conf</filename>
+ file or on the server command line.
+ </para>
+ </listitem>
+ </varlistentry>
- <varlistentry id="guc-autovacuum-vacuum-insert-threshold" xreflabel="autovacuum_vacuum_insert_threshold">
- <term><varname>autovacuum_vacuum_insert_threshold</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>autovacuum_vacuum_insert_threshold</varname></primary>
- <secondary>configuration parameter</secondary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the number of inserted tuples needed to trigger a
- <command>VACUUM</command> in any one table.
- The default is 1000 tuples. If -1 is specified, autovacuum will not
- trigger a <command>VACUUM</command> operation on any tables based on
- the number of inserts.
- This parameter can only be set in the <filename>postgresql.conf</filename>
- file or on the server command line;
- but the setting can be overridden for individual tables by
- changing table storage parameters.
- </para>
- </listitem>
- </varlistentry>
+ <varlistentry id="guc-autovacuum-vacuum-threshold" xreflabel="autovacuum_vacuum_threshold">
+ <term><varname>autovacuum_vacuum_threshold</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>autovacuum_vacuum_threshold</varname></primary>
+ <secondary>configuration parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the minimum number of updated or deleted tuples needed
+ to trigger a <command>VACUUM</command> in any one table.
+ The default is 50 tuples.
+ This parameter can only be set in the <filename>postgresql.conf</filename>
+ file or on the server command line;
+ but the setting can be overridden for individual tables by
+ changing table storage parameters.
+ </para>
+ </listitem>
+ </varlistentry>
- <varlistentry id="guc-autovacuum-analyze-threshold" xreflabel="autovacuum_analyze_threshold">
- <term><varname>autovacuum_analyze_threshold</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>autovacuum_analyze_threshold</varname></primary>
- <secondary>configuration parameter</secondary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the minimum number of inserted, updated or deleted tuples
- needed to trigger an <command>ANALYZE</command> in any one table.
- The default is 50 tuples.
- This parameter can only be set in the <filename>postgresql.conf</filename>
- file or on the server command line;
- but the setting can be overridden for individual tables by
- changing table storage parameters.
- </para>
- </listitem>
- </varlistentry>
+ <varlistentry id="guc-autovacuum-vacuum-insert-threshold" xreflabel="autovacuum_vacuum_insert_threshold">
+ <term><varname>autovacuum_vacuum_insert_threshold</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>autovacuum_vacuum_insert_threshold</varname></primary>
+ <secondary>configuration parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the number of inserted tuples needed to trigger a
+ <command>VACUUM</command> in any one table.
+ The default is 1000 tuples. If -1 is specified, autovacuum will not
+ trigger a <command>VACUUM</command> operation on any tables based on
+ the number of inserts.
+ This parameter can only be set in the <filename>postgresql.conf</filename>
+ file or on the server command line;
+ but the setting can be overridden for individual tables by
+ changing table storage parameters.
+ </para>
+ </listitem>
+ </varlistentry>
- <varlistentry id="guc-autovacuum-vacuum-scale-factor" xreflabel="autovacuum_vacuum_scale_factor">
- <term><varname>autovacuum_vacuum_scale_factor</varname> (<type>floating point</type>)
- <indexterm>
- <primary><varname>autovacuum_vacuum_scale_factor</varname></primary>
- <secondary>configuration parameter</secondary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies a fraction of the table size to add to
- <varname>autovacuum_vacuum_threshold</varname>
- when deciding whether to trigger a <command>VACUUM</command>.
- The default is 0.2 (20% of table size).
- This parameter can only be set in the <filename>postgresql.conf</filename>
- file or on the server command line;
- but the setting can be overridden for individual tables by
- changing table storage parameters.
- </para>
- </listitem>
- </varlistentry>
+ <varlistentry id="guc-autovacuum-analyze-threshold" xreflabel="autovacuum_analyze_threshold">
+ <term><varname>autovacuum_analyze_threshold</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>autovacuum_analyze_threshold</varname></primary>
+ <secondary>configuration parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the minimum number of inserted, updated or deleted tuples
+ needed to trigger an <command>ANALYZE</command> in any one table.
+ The default is 50 tuples.
+ This parameter can only be set in the <filename>postgresql.conf</filename>
+ file or on the server command line;
+ but the setting can be overridden for individual tables by
+ changing table storage parameters.
+ </para>
+ </listitem>
+ </varlistentry>
- <varlistentry id="guc-autovacuum-vacuum-insert-scale-factor" xreflabel="autovacuum_vacuum_insert_scale_factor">
- <term><varname>autovacuum_vacuum_insert_scale_factor</varname> (<type>floating point</type>)
- <indexterm>
- <primary><varname>autovacuum_vacuum_insert_scale_factor</varname></primary>
- <secondary>configuration parameter</secondary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies a fraction of the table size to add to
- <varname>autovacuum_vacuum_insert_threshold</varname>
- when deciding whether to trigger a <command>VACUUM</command>.
- The default is 0.2 (20% of table size).
- This parameter can only be set in the <filename>postgresql.conf</filename>
- file or on the server command line;
- but the setting can be overridden for individual tables by
- changing table storage parameters.
- </para>
- </listitem>
- </varlistentry>
+ <varlistentry id="guc-autovacuum-vacuum-scale-factor" xreflabel="autovacuum_vacuum_scale_factor">
+ <term><varname>autovacuum_vacuum_scale_factor</varname> (<type>floating point</type>)
+ <indexterm>
+ <primary><varname>autovacuum_vacuum_scale_factor</varname></primary>
+ <secondary>configuration parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies a fraction of the table size to add to
+ <varname>autovacuum_vacuum_threshold</varname>
+ when deciding whether to trigger a <command>VACUUM</command>.
+ The default is <literal>0.2</literal> (20% of table size).
+ This parameter can only be set in the <filename>postgresql.conf</filename>
+ file or on the server command line;
+ but the setting can be overridden for individual tables by
+ changing table storage parameters.
+ </para>
+ </listitem>
+ </varlistentry>
- <varlistentry id="guc-autovacuum-analyze-scale-factor" xreflabel="autovacuum_analyze_scale_factor">
- <term><varname>autovacuum_analyze_scale_factor</varname> (<type>floating point</type>)
- <indexterm>
- <primary><varname>autovacuum_analyze_scale_factor</varname></primary>
- <secondary>configuration parameter</secondary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies a fraction of the table size to add to
- <varname>autovacuum_analyze_threshold</varname>
- when deciding whether to trigger an <command>ANALYZE</command>.
- The default is 0.1 (10% of table size).
- This parameter can only be set in the <filename>postgresql.conf</filename>
- file or on the server command line;
- but the setting can be overridden for individual tables by
- changing table storage parameters.
- </para>
- </listitem>
- </varlistentry>
+ <varlistentry id="guc-autovacuum-vacuum-insert-scale-factor" xreflabel="autovacuum_vacuum_insert_scale_factor">
+ <term><varname>autovacuum_vacuum_insert_scale_factor</varname> (<type>floating point</type>)
+ <indexterm>
+ <primary><varname>autovacuum_vacuum_insert_scale_factor</varname></primary>
+ <secondary>configuration parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies a fraction of the table size to add to
+ <varname>autovacuum_vacuum_insert_threshold</varname>
+ when deciding whether to trigger a <command>VACUUM</command>.
+ The default is <literal>0.2</literal> (20% of table size).
+ This parameter can only be set in the <filename>postgresql.conf</filename>
+ file or on the server command line;
+ but the setting can be overridden for individual tables by
+ changing table storage parameters.
+ </para>
+ </listitem>
+ </varlistentry>
- <varlistentry id="guc-autovacuum-freeze-max-age" xreflabel="autovacuum_freeze_max_age">
- <term><varname>autovacuum_freeze_max_age</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>autovacuum_freeze_max_age</varname></primary>
- <secondary>configuration parameter</secondary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the maximum age (in transactions) that a table's
- <structname>pg_class</structname>.<structfield>relfrozenxid</structfield> field can
- attain before a <command>VACUUM</command> operation is forced
- to prevent transaction ID wraparound within the table.
- Note that the system will launch autovacuum processes to
- prevent wraparound even when autovacuum is otherwise disabled.
- </para>
+ <varlistentry id="guc-autovacuum-analyze-scale-factor" xreflabel="autovacuum_analyze_scale_factor">
+ <term><varname>autovacuum_analyze_scale_factor</varname> (<type>floating point</type>)
+ <indexterm>
+ <primary><varname>autovacuum_analyze_scale_factor</varname></primary>
+ <secondary>configuration parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies a fraction of the table size to add to
+ <varname>autovacuum_analyze_threshold</varname>
+ when deciding whether to trigger an <command>ANALYZE</command>.
+ The default is <literal>0.1</literal> (10% of table size).
+ This parameter can only be set in the <filename>postgresql.conf</filename>
+ file or on the server command line;
+ but the setting can be overridden for individual tables by
+ changing table storage parameters.
+ </para>
+ </listitem>
+ </varlistentry>
- <para>
- Vacuum also allows removal of old files from the
- <filename>pg_xact</filename> subdirectory, which is why the default
- is a relatively low 200 million transactions.
- This parameter can only be set at server start, but the setting
- can be reduced for individual tables by
- changing table storage parameters.
- For more information see <xref linkend="vacuum-for-wraparound"/>.
- </para>
- </listitem>
- </varlistentry>
+ <varlistentry id="guc-autovacuum-freeze-max-age" xreflabel="autovacuum_freeze_max_age">
+ <term><varname>autovacuum_freeze_max_age</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>autovacuum_freeze_max_age</varname></primary>
+ <secondary>configuration parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the maximum age (in transactions) that a table's
+ <structname>pg_class</structname>.<structfield>relfrozenxid</structfield> field can
+ attain before a <command>VACUUM</command> operation is forced
+ to prevent transaction ID wraparound within the table.
+ Note that the system will launch autovacuum processes to
+ prevent wraparound even when autovacuum is otherwise disabled.
+ </para>
- <varlistentry id="guc-autovacuum-multixact-freeze-max-age" xreflabel="autovacuum_multixact_freeze_max_age">
- <term><varname>autovacuum_multixact_freeze_max_age</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>autovacuum_multixact_freeze_max_age</varname></primary>
- <secondary>configuration parameter</secondary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the maximum age (in multixacts) that a table's
- <structname>pg_class</structname>.<structfield>relminmxid</structfield> field can
- attain before a <command>VACUUM</command> operation is forced to
- prevent multixact ID wraparound within the table.
- Note that the system will launch autovacuum processes to
- prevent wraparound even when autovacuum is otherwise disabled.
- </para>
+ <para>
+ Vacuum also allows removal of old files from the
+ <filename>pg_xact</filename> subdirectory, which is why the default
+ is a relatively low 200 million transactions.
+ This parameter can only be set at server start, but the setting
+ can be reduced for individual tables by
+ changing table storage parameters.
+ For more information see <xref linkend="vacuum-for-wraparound"/>.
+ </para>
+ </listitem>
+ </varlistentry>
- <para>
- Vacuuming multixacts also allows removal of old files from the
- <filename>pg_multixact/members</filename> and <filename>pg_multixact/offsets</filename>
- subdirectories, which is why the default is a relatively low
- 400 million multixacts.
- This parameter can only be set at server start, but the setting can
- be reduced for individual tables by changing table storage parameters.
- For more information see <xref linkend="vacuum-for-multixact-wraparound"/>.
- </para>
- </listitem>
- </varlistentry>
+ <varlistentry id="guc-autovacuum-multixact-freeze-max-age" xreflabel="autovacuum_multixact_freeze_max_age">
+ <term><varname>autovacuum_multixact_freeze_max_age</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>autovacuum_multixact_freeze_max_age</varname></primary>
+ <secondary>configuration parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the maximum age (in multixacts) that a table's
+ <structname>pg_class</structname>.<structfield>relminmxid</structfield> field can
+ attain before a <command>VACUUM</command> operation is forced to
+ prevent multixact ID wraparound within the table.
+ Note that the system will launch autovacuum processes to
+ prevent wraparound even when autovacuum is otherwise disabled.
+ </para>
- <varlistentry id="guc-autovacuum-vacuum-cost-delay" xreflabel="autovacuum_vacuum_cost_delay">
- <term><varname>autovacuum_vacuum_cost_delay</varname> (<type>floating point</type>)
- <indexterm>
- <primary><varname>autovacuum_vacuum_cost_delay</varname></primary>
- <secondary>configuration parameter</secondary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the cost delay value that will be used in automatic
- <command>VACUUM</command> operations. If -1 is specified, the regular
- <xref linkend="guc-vacuum-cost-delay"/> value will be used.
- If this value is specified without units, it is taken as milliseconds.
- The default value is 2 milliseconds.
- This parameter can only be set in the <filename>postgresql.conf</filename>
- file or on the server command line;
- but the setting can be overridden for individual tables by
- changing table storage parameters.
- </para>
- </listitem>
- </varlistentry>
+ <para>
+ Vacuuming multixacts also allows removal of old files from the
+ <filename>pg_multixact/members</filename> and <filename>pg_multixact/offsets</filename>
+ subdirectories, which is why the default is a relatively low
+ 400 million multixacts.
+ This parameter can only be set at server start, but the setting can
+ be reduced for individual tables by changing table storage parameters.
+ For more information see <xref linkend="vacuum-for-multixact-wraparound"/>.
+ </para>
+ </listitem>
+ </varlistentry>
- <varlistentry id="guc-autovacuum-vacuum-cost-limit" xreflabel="autovacuum_vacuum_cost_limit">
- <term><varname>autovacuum_vacuum_cost_limit</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>autovacuum_vacuum_cost_limit</varname></primary>
- <secondary>configuration parameter</secondary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the cost limit value that will be used in automatic
- <command>VACUUM</command> operations. If -1 is specified (which is the
- default), the regular
- <xref linkend="guc-vacuum-cost-limit"/> value will be used. Note that
- the value is distributed proportionally among the running autovacuum
- workers, if there is more than one, so that the sum of the limits for
- each worker does not exceed the value of this variable.
- This parameter can only be set in the <filename>postgresql.conf</filename>
- file or on the server command line;
- but the setting can be overridden for individual tables by
- changing table storage parameters.
- </para>
- </listitem>
- </varlistentry>
+ <varlistentry id="guc-autovacuum-vacuum-cost-delay" xreflabel="autovacuum_vacuum_cost_delay">
+ <term><varname>autovacuum_vacuum_cost_delay</varname> (<type>floating point</type>)
+ <indexterm>
+ <primary><varname>autovacuum_vacuum_cost_delay</varname></primary>
+ <secondary>configuration parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the cost delay value that will be used in automatic
+ <command>VACUUM</command> operations. If -1 is specified, the regular
+ <xref linkend="guc-vacuum-cost-delay"/> value will be used.
+ If this value is specified without units, it is taken as milliseconds.
+ The default value is 2 milliseconds.
+ This parameter can only be set in the <filename>postgresql.conf</filename>
+ file or on the server command line;
+ but the setting can be overridden for individual tables by
+ changing table storage parameters.
+ </para>
+ </listitem>
+ </varlistentry>
- </variablelist>
+ <varlistentry id="guc-autovacuum-vacuum-cost-limit" xreflabel="autovacuum_vacuum_cost_limit">
+ <term><varname>autovacuum_vacuum_cost_limit</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>autovacuum_vacuum_cost_limit</varname></primary>
+ <secondary>configuration parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the cost limit value that will be used in automatic
+ <command>VACUUM</command> operations. If <literal>-1</literal>
+ is specified (which is the default), the regular
+ <xref linkend="guc-vacuum-cost-limit"/> value will be used. Note that
+ the value is distributed proportionally among the running autovacuum
+ workers, if there is more than one, so that the sum of the limits for
+ each worker does not exceed the value of this variable.
+ This parameter can only be set in the <filename>postgresql.conf</filename>
+ file or on the server command line;
+ but the setting can be overridden for individual tables by
+ changing table storage parameters.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </sect2>
+
+ <sect2 id="runtime-config-resource-vacuum-cost">
+ <title>Cost-based Vacuum Delay</title>
+
+ <para>
+ During the execution of <xref linkend="sql-vacuum"/>
+ and <xref linkend="sql-analyze"/>
+ commands, the system maintains an
+ internal counter that keeps track of the estimated cost of the
+ various I/O operations that are performed. When the accumulated
+ cost reaches a limit (specified by
+ <varname>vacuum_cost_limit</varname>), the process performing
+ the operation will sleep for a short period of time, as specified by
+ <varname>vacuum_cost_delay</varname>. Then it will reset the
+ counter and continue execution.
+ </para>
+
+ <para>
+ The intent of this feature is to allow administrators to reduce
+ the I/O impact of these commands on concurrent database
+ activity. There are many situations where it is not
+ important that maintenance commands like
+ <command>VACUUM</command> and <command>ANALYZE</command> finish
+ quickly; however, it is usually very important that these
+ commands do not significantly interfere with the ability of the
+ system to perform other database operations. Cost-based vacuum
+ delay provides a way for administrators to achieve this.
+ </para>
+
+ <para>
+ This feature is disabled by default for manually issued
+ <command>VACUUM</command> commands. To enable it, set the
+ <varname>vacuum_cost_delay</varname> variable to a nonzero
+ value.
+ </para>
+
+ <variablelist>
+ <varlistentry id="guc-vacuum-cost-delay" xreflabel="vacuum_cost_delay">
+ <term><varname>vacuum_cost_delay</varname> (<type>floating point</type>)
+ <indexterm>
+ <primary><varname>vacuum_cost_delay</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ The amount of time that the process will sleep when the cost
+ limit has been exceeded. If this value is specified without
+ units, it is taken as milliseconds. The default value is
+ <literal>0</literal>, which disables the cost-based vacuum delay
+ feature. Positive values enable cost-based vacuuming.
+ </para>
+
+ <para>
+ When using cost-based vacuuming, appropriate values for
+ <varname>vacuum_cost_delay</varname> are usually quite small, perhaps
+ less than 1 millisecond. While <varname>vacuum_cost_delay</varname>
+ can be set to fractional-millisecond values, such delays may not be
+ measured accurately on older platforms. On such platforms,
+ increasing <command>VACUUM</command>'s throttled resource consumption
+ above what you get at 1ms will require changing the other vacuum cost
+ parameters. You should, nonetheless,
+ keep <varname>vacuum_cost_delay</varname> as small as your platform
+ will consistently measure; large delays are not helpful.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-vacuum-cost-page-hit" xreflabel="vacuum_cost_page_hit">
+ <term><varname>vacuum_cost_page_hit</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>vacuum_cost_page_hit</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ The estimated cost for vacuuming a buffer found in the shared
+ buffer cache. It represents the cost to lock the buffer pool,
+ lookup the shared hash table and scan the content of the page.
+ The default value is <literal>1</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-vacuum-cost-page-miss" xreflabel="vacuum_cost_page_miss">
+ <term><varname>vacuum_cost_page_miss</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>vacuum_cost_page_miss</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ The estimated cost for vacuuming a buffer that has to be read from
+ disk. This represents the effort to lock the buffer pool,
+ lookup the shared hash table, read the desired block in from
+ the disk and scan its content. The default value is
+ <literal>2</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-vacuum-cost-page-dirty" xreflabel="vacuum_cost_page_dirty">
+ <term><varname>vacuum_cost_page_dirty</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>vacuum_cost_page_dirty</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ The estimated cost charged when vacuum modifies a block that was
+ previously clean. It represents the extra I/O required to
+ flush the dirty block out to disk again. The default value is
+ <literal>20</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-vacuum-cost-limit" xreflabel="vacuum_cost_limit">
+ <term><varname>vacuum_cost_limit</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>vacuum_cost_limit</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ This is the accumulated cost that will cause the vacuuming
+ process to sleep for <varname>vacuum_cost_delay</varname>. The
+ default is <literal>200</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+
+ <note>
+ <para>
+ There are certain operations that hold critical locks and should
+ therefore complete as quickly as possible. Cost-based vacuum
+ delays do not occur during such operations. Therefore it is
+ possible that the cost accumulates far higher than the specified
+ limit. To avoid uselessly long delays in such cases, the actual
+ delay is calculated as <varname>vacuum_cost_delay</varname> *
+ <varname>accumulated_balance</varname> /
+ <varname>vacuum_cost_limit</varname> with a maximum of
+ <varname>vacuum_cost_delay</varname> * 4.
+ </para>
+ </note>
+ </sect2>
+
+ <sect2 id="runtime-config-vacuum-freezing">
+ <title>Freezing</title>
+
+ <para>
+ To maintain correctness even after transaction IDs wrap around,
+ <productname>PostgreSQL</productname> marks rows that are sufficiently
+ old as <emphasis>frozen</emphasis>. These rows are visible to everyone;
+ other transactions do not need to examine their inserting XID to
+ determine visibility. <command>VACUUM</command> is responsible for
+ marking rows as frozen. The following settings control
+ <command>VACUUM</command>'s freezing behavior and should be tuned based
+ on the XID consumption rate of the system and data access patterns of the
+ dominant workloads. See <xref linkend="vacuum-for-wraparound"/> for more
+ information on transaction ID wraparound and tuning these parameters.
+ </para>
+
+ <variablelist>
+ <varlistentry id="guc-vacuum-freeze-table-age" xreflabel="vacuum_freeze_table_age">
+ <term><varname>vacuum_freeze_table_age</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>vacuum_freeze_table_age</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ <command>VACUUM</command> performs an aggressive scan if the table's
+ <structname>pg_class</structname>.<structfield>relfrozenxid</structfield> field has reached
+ the age specified by this setting. An aggressive scan differs from
+ a regular <command>VACUUM</command> in that it visits every page that might
+ contain unfrozen XIDs or MXIDs, not just those that might contain dead
+ tuples. The default is 150 million transactions. Although users can
+ set this value anywhere from zero to two billion, <command>VACUUM</command>
+ will silently limit the effective value to 95% of
+ <xref linkend="guc-autovacuum-freeze-max-age"/>, so that a
+ periodic manual <command>VACUUM</command> has a chance to run before an
+ anti-wraparound autovacuum is launched for the table. For more
+ information see
+ <xref linkend="vacuum-for-wraparound"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-vacuum-freeze-min-age" xreflabel="vacuum_freeze_min_age">
+ <term><varname>vacuum_freeze_min_age</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>vacuum_freeze_min_age</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the cutoff age (in transactions) that
+ <command>VACUUM</command> should use to decide whether to
+ trigger freezing of pages that have an older XID.
+ The default is 50 million transactions. Although
+ users can set this value anywhere from zero to one billion,
+ <command>VACUUM</command> will silently limit the effective value to half
+ the value of <xref linkend="guc-autovacuum-freeze-max-age"/>, so
+ that there is not an unreasonably short time between forced
+ autovacuums. For more information see <xref
+ linkend="vacuum-for-wraparound"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-vacuum-failsafe-age" xreflabel="vacuum_failsafe_age">
+ <term><varname>vacuum_failsafe_age</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>vacuum_failsafe_age</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the maximum age (in transactions) that a table's
+ <structname>pg_class</structname>.<structfield>relfrozenxid</structfield>
+ field can attain before <command>VACUUM</command> takes
+ extraordinary measures to avoid system-wide transaction ID
+ wraparound failure. This is <command>VACUUM</command>'s
+ strategy of last resort. The failsafe typically triggers
+ when an autovacuum to prevent transaction ID wraparound has
+ already been running for some time, though it's possible for
+ the failsafe to trigger during any <command>VACUUM</command>.
+ </para>
+ <para>
+ When the failsafe is triggered, any cost-based delay that is
+ in effect will no longer be applied, further non-essential
+ maintenance tasks (such as index vacuuming) are bypassed, and any
+ <glossterm linkend="glossary-buffer-access-strategy">Buffer Access Strategy</glossterm>
+ in use will be disabled resulting in <command>VACUUM</command> being
+ free to make use of all of
+ <glossterm linkend="glossary-shared-memory">shared buffers</glossterm>.
+ </para>
+ <para>
+ The default is 1.6 billion transactions. Although users can
+ set this value anywhere from zero to 2.1 billion,
+ <command>VACUUM</command> will silently adjust the effective
+ value to no less than 105% of <xref
+ linkend="guc-autovacuum-freeze-max-age"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-vacuum-multixact-freeze-table-age" xreflabel="vacuum_multixact_freeze_table_age">
+ <term><varname>vacuum_multixact_freeze_table_age</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>vacuum_multixact_freeze_table_age</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ <command>VACUUM</command> performs an aggressive scan if the table's
+ <structname>pg_class</structname>.<structfield>relminmxid</structfield> field has reached
+ the age specified by this setting. An aggressive scan differs from
+ a regular <command>VACUUM</command> in that it visits every page that might
+ contain unfrozen XIDs or MXIDs, not just those that might contain dead
+ tuples. The default is 150 million multixacts.
+ Although users can set this value anywhere from zero to two billion,
+ <command>VACUUM</command> will silently limit the effective value to 95% of
+ <xref linkend="guc-autovacuum-multixact-freeze-max-age"/>, so that a
+ periodic manual <command>VACUUM</command> has a chance to run before an
+ anti-wraparound is launched for the table.
+ For more information see <xref linkend="vacuum-for-multixact-wraparound"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-vacuum-multixact-freeze-min-age" xreflabel="vacuum_multixact_freeze_min_age">
+ <term><varname>vacuum_multixact_freeze_min_age</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>vacuum_multixact_freeze_min_age</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the cutoff age (in multixacts) that <command>VACUUM</command>
+ should use to decide whether to trigger freezing of pages with
+ an older multixact ID. The default is 5 million multixacts.
+ Although users can set this value anywhere from zero to one billion,
+ <command>VACUUM</command> will silently limit the effective value to half
+ the value of <xref linkend="guc-autovacuum-multixact-freeze-max-age"/>,
+ so that there is not an unreasonably short time between forced
+ autovacuums.
+ For more information see <xref linkend="vacuum-for-multixact-wraparound"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-vacuum-multixact-failsafe-age" xreflabel="vacuum_multixact_failsafe_age">
+ <term><varname>vacuum_multixact_failsafe_age</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>vacuum_multixact_failsafe_age</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the maximum age (in multixacts) that a table's
+ <structname>pg_class</structname>.<structfield>relminmxid</structfield>
+ field can attain before <command>VACUUM</command> takes
+ extraordinary measures to avoid system-wide multixact ID
+ wraparound failure. This is <command>VACUUM</command>'s
+ strategy of last resort. The failsafe typically triggers when
+ an autovacuum to prevent transaction ID wraparound has already
+ been running for some time, though it's possible for the
+ failsafe to trigger during any <command>VACUUM</command>.
+ </para>
+ <para>
+ When the failsafe is triggered, any cost-based delay that is
+ in effect will no longer be applied, and further non-essential
+ maintenance tasks (such as index vacuuming) are bypassed.
+ </para>
+ <para>
+ The default is 1.6 billion multixacts. Although users can set
+ this value anywhere from zero to 2.1 billion,
+ <command>VACUUM</command> will silently adjust the effective
+ value to no less than 105% of <xref
+ linkend="guc-autovacuum-multixact-freeze-max-age"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </sect2>
</sect1>
<sect1 id="runtime-config-client">
@@ -9595,168 +9789,6 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
</listitem>
</varlistentry>
- <varlistentry id="guc-vacuum-freeze-table-age" xreflabel="vacuum_freeze_table_age">
- <term><varname>vacuum_freeze_table_age</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>vacuum_freeze_table_age</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- <command>VACUUM</command> performs an aggressive scan if the table's
- <structname>pg_class</structname>.<structfield>relfrozenxid</structfield> field has reached
- the age specified by this setting. An aggressive scan differs from
- a regular <command>VACUUM</command> in that it visits every page that might
- contain unfrozen XIDs or MXIDs, not just those that might contain dead
- tuples. The default is 150 million transactions. Although users can
- set this value anywhere from zero to two billion, <command>VACUUM</command>
- will silently limit the effective value to 95% of
- <xref linkend="guc-autovacuum-freeze-max-age"/>, so that a
- periodic manual <command>VACUUM</command> has a chance to run before an
- anti-wraparound autovacuum is launched for the table. For more
- information see
- <xref linkend="vacuum-for-wraparound"/>.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="guc-vacuum-freeze-min-age" xreflabel="vacuum_freeze_min_age">
- <term><varname>vacuum_freeze_min_age</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>vacuum_freeze_min_age</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the cutoff age (in transactions) that
- <command>VACUUM</command> should use to decide whether to
- trigger freezing of pages that have an older XID.
- The default is 50 million transactions. Although
- users can set this value anywhere from zero to one billion,
- <command>VACUUM</command> will silently limit the effective value to half
- the value of <xref linkend="guc-autovacuum-freeze-max-age"/>, so
- that there is not an unreasonably short time between forced
- autovacuums. For more information see <xref
- linkend="vacuum-for-wraparound"/>.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="guc-vacuum-failsafe-age" xreflabel="vacuum_failsafe_age">
- <term><varname>vacuum_failsafe_age</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>vacuum_failsafe_age</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the maximum age (in transactions) that a table's
- <structname>pg_class</structname>.<structfield>relfrozenxid</structfield>
- field can attain before <command>VACUUM</command> takes
- extraordinary measures to avoid system-wide transaction ID
- wraparound failure. This is <command>VACUUM</command>'s
- strategy of last resort. The failsafe typically triggers
- when an autovacuum to prevent transaction ID wraparound has
- already been running for some time, though it's possible for
- the failsafe to trigger during any <command>VACUUM</command>.
- </para>
- <para>
- When the failsafe is triggered, any cost-based delay that is
- in effect will no longer be applied, further non-essential
- maintenance tasks (such as index vacuuming) are bypassed, and any
- <glossterm linkend="glossary-buffer-access-strategy">Buffer Access Strategy</glossterm>
- in use will be disabled resulting in <command>VACUUM</command> being
- free to make use of all of
- <glossterm linkend="glossary-shared-memory">shared buffers</glossterm>.
- </para>
- <para>
- The default is 1.6 billion transactions. Although users can
- set this value anywhere from zero to 2.1 billion,
- <command>VACUUM</command> will silently adjust the effective
- value to no less than 105% of <xref
- linkend="guc-autovacuum-freeze-max-age"/>.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="guc-vacuum-multixact-freeze-table-age" xreflabel="vacuum_multixact_freeze_table_age">
- <term><varname>vacuum_multixact_freeze_table_age</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>vacuum_multixact_freeze_table_age</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- <command>VACUUM</command> performs an aggressive scan if the table's
- <structname>pg_class</structname>.<structfield>relminmxid</structfield> field has reached
- the age specified by this setting. An aggressive scan differs from
- a regular <command>VACUUM</command> in that it visits every page that might
- contain unfrozen XIDs or MXIDs, not just those that might contain dead
- tuples. The default is 150 million multixacts.
- Although users can set this value anywhere from zero to two billion,
- <command>VACUUM</command> will silently limit the effective value to 95% of
- <xref linkend="guc-autovacuum-multixact-freeze-max-age"/>, so that a
- periodic manual <command>VACUUM</command> has a chance to run before an
- anti-wraparound is launched for the table.
- For more information see <xref linkend="vacuum-for-multixact-wraparound"/>.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="guc-vacuum-multixact-freeze-min-age" xreflabel="vacuum_multixact_freeze_min_age">
- <term><varname>vacuum_multixact_freeze_min_age</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>vacuum_multixact_freeze_min_age</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the cutoff age (in multixacts) that <command>VACUUM</command>
- should use to decide whether to trigger freezing of pages with
- an older multixact ID. The default is 5 million multixacts.
- Although users can set this value anywhere from zero to one billion,
- <command>VACUUM</command> will silently limit the effective value to half
- the value of <xref linkend="guc-autovacuum-multixact-freeze-max-age"/>,
- so that there is not an unreasonably short time between forced
- autovacuums.
- For more information see <xref linkend="vacuum-for-multixact-wraparound"/>.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="guc-vacuum-multixact-failsafe-age" xreflabel="vacuum_multixact_failsafe_age">
- <term><varname>vacuum_multixact_failsafe_age</varname> (<type>integer</type>)
- <indexterm>
- <primary><varname>vacuum_multixact_failsafe_age</varname> configuration parameter</primary>
- </indexterm>
- </term>
- <listitem>
- <para>
- Specifies the maximum age (in multixacts) that a table's
- <structname>pg_class</structname>.<structfield>relminmxid</structfield>
- field can attain before <command>VACUUM</command> takes
- extraordinary measures to avoid system-wide multixact ID
- wraparound failure. This is <command>VACUUM</command>'s
- strategy of last resort. The failsafe typically triggers when
- an autovacuum to prevent transaction ID wraparound has already
- been running for some time, though it's possible for the
- failsafe to trigger during any <command>VACUUM</command>.
- </para>
- <para>
- When the failsafe is triggered, any cost-based delay that is
- in effect will no longer be applied, and further non-essential
- maintenance tasks (such as index vacuuming) are bypassed.
- </para>
- <para>
- The default is 1.6 billion multixacts. Although users can set
- this value anywhere from zero to 2.1 billion,
- <command>VACUUM</command> will silently adjust the effective
- value to no less than 105% of <xref
- linkend="guc-autovacuum-multixact-freeze-max-age"/>.
- </para>
- </listitem>
- </varlistentry>
-
<varlistentry id="guc-bytea-output" xreflabel="bytea_output">
<term><varname>bytea_output</varname> (<type>enum</type>)
<indexterm>
--
2.45.2
Re: Moving the vacuum GUCs' docs out of the Client Connection Defaults section
On 10 Jan 2025, at 23:09, Melanie Plageman <melanieplageman@gmail.com> wrote:
On Thu, Jan 9, 2025 at 5:05 PM Daniel Gustafsson <daniel@yesql.se> wrote:
I think this is a really good restructuring which will make life easier for our
users. Some of the comments below are on wording which wasn't introduced in
this patch, but which I hadn't thought about before, so feel free to ignore
those comments.+ <sect2 id="runtime-config-vacuum-freezing"> + <title>Freezing</title> + + <para> Trying to read this as a new user, I think it would be good to start this subsection with a sentence describing what freezing actually is. Vacuum is hard enough for users as it is =)I've taken a stab at improving this. Let me know if you think it works.
I like what you added, it's IMO the right level of detail here.
+ default value is one.
Grepping around indicates that we typically use the numeric value rather than
writing it in text, and the next settting down has "default value is 2". For
consistency I would change that to "1" instead of "one".I think this is a reasonable cleanup to lump in with the rest of this
commit. I have taken the liberty of also adding a <literal> tag and
then updating the other places in the proposed "Vacuuming" subsection
where a literal default value is specified without the <literal> tag.
LGTM. We're not entirely consistent with how we mark up the default value, but
I think moving towards using <literal>value</literal> whenever we are changing
things there anyways is the right level of cleanup.
+ can be set to fractional-millisecond values, such delays may not be + measured accurately on older platforms. On such platforms, This sentence seems quite vague and hard to act on for users, what qualifies as an "older platform" by the time v18 rolls around (this was added in v12). I'm sure there are such platforms in existence that postgres 18 will run on, but are we helping users with ambiguity?While I agree that what counted as older hardware in 2019 may no
longer be around at all, I am more hesitant to update this in the same
commit as a bunch of other cut-and-pastes. Someone at some point
decided this was important to point out, and I don't have sufficient
evidence that it no longer makes sense. And if I did, I'd probably
want to update this part of the docs in a dedicated commit.
To be fair I didn't really expect this to be changed as part of this (and I
should written that in my email), but it stood out when reading so wanted to
point it out. No objections to leaving it as is as part of this work.
+ Specifies the cutoff age (in multixacts) that <command>VACUUM</command>
I wish we had a glossary entry for multixact we could link to here. But,
that's not really the responsibility of this patch to fix, just something that
came to mind when reading the resulting page.
+1 on going ahead with this version, there are still improvements we can make
to the vacuum config docs but that shouldn't stand in the way of a reorg patch
which improves overall presentation structure.
--
Daniel Gustafsson
Re: Moving the vacuum GUCs' docs out of the Client Connection Defaults section
On Fri, Jan 10, 2025 at 6:00 PM Daniel Gustafsson <daniel@yesql.se> wrote:
On 10 Jan 2025, at 23:09, Melanie Plageman <melanieplageman@gmail.com> wrote:
On Thu, Jan 9, 2025 at 5:05 PM Daniel Gustafsson <daniel@yesql.se> wrote:
I think this is a really good restructuring which will make life easier for our
users. Some of the comments below are on wording which wasn't introduced in
this patch, but which I hadn't thought about before, so feel free to ignore
those comments.+ <sect2 id="runtime-config-vacuum-freezing"> + <title>Freezing</title> + + <para> Trying to read this as a new user, I think it would be good to start this subsection with a sentence describing what freezing actually is. Vacuum is hard enough for users as it is =)I've taken a stab at improving this. Let me know if you think it works.
I like what you added, it's IMO the right level of detail here.
Cool, thanks! I pushed.
+ Specifies the cutoff age (in multixacts) that <command>VACUUM</command>
I wish we had a glossary entry for multixact we could link to here. But,
that's not really the responsibility of this patch to fix, just something that
came to mind when reading the resulting page.
Personally, I really wish we had a docs page explaining more about
what mutlixacts are and maybe even a bit about the architecture. FWIW,
the index [1]https://www.postgresql.org/docs/devel/bookindex.html has an entry for MultiXacts but it goes straight to
MultiXacts and Wraparound [2]https://www.postgresql.org/docs/devel/routine-vacuuming.html#VACUUM-FOR-MULTIXACT-WRAPAROUND.
- Melanie
[1]: https://www.postgresql.org/docs/devel/bookindex.html
[2]: https://www.postgresql.org/docs/devel/routine-vacuuming.html#VACUUM-FOR-MULTIXACT-WRAPAROUND