1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>24.1. Routine Vacuuming</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="maintenance.html" title="Chapter 24. Routine Database Maintenance Tasks" /><link rel="next" href="routine-reindex.html" title="24.2. Routine Reindexing" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">24.1. Routine Vacuuming</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="maintenance.html" title="Chapter 24. Routine Database Maintenance Tasks">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="maintenance.html" title="Chapter 24. Routine Database Maintenance Tasks">Up</a></td><th width="60%" align="center">Chapter 24. Routine Database Maintenance Tasks</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="routine-reindex.html" title="24.2. Routine Reindexing">Next</a></td></tr></table><hr /></div><div class="sect1" id="ROUTINE-VACUUMING"><div class="titlepage"><div><div><h2 class="title" style="clear: both">24.1. Routine Vacuuming <a href="#ROUTINE-VACUUMING" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="routine-vacuuming.html#VACUUM-BASICS">24.1.1. Vacuuming Basics</a></span></dt><dt><span class="sect2"><a href="routine-vacuuming.html#VACUUM-FOR-SPACE-RECOVERY">24.1.2. Recovering Disk Space</a></span></dt><dt><span class="sect2"><a href="routine-vacuuming.html#VACUUM-FOR-STATISTICS">24.1.3. Updating Planner Statistics</a></span></dt><dt><span class="sect2"><a href="routine-vacuuming.html#VACUUM-FOR-VISIBILITY-MAP">24.1.4. Updating the Visibility Map</a></span></dt><dt><span class="sect2"><a href="routine-vacuuming.html#VACUUM-FOR-WRAPAROUND">24.1.5. Preventing Transaction ID Wraparound Failures</a></span></dt><dt><span class="sect2"><a href="routine-vacuuming.html#AUTOVACUUM">24.1.6. The Autovacuum Daemon</a></span></dt></dl></div><a id="id-1.6.11.10.2" class="indexterm"></a><p>
3 <span class="productname">PostgreSQL</span> databases require periodic
4 maintenance known as <em class="firstterm">vacuuming</em>. For many installations, it
5 is sufficient to let vacuuming be performed by the <em class="firstterm">autovacuum
6 daemon</em>, which is described in <a class="xref" href="routine-vacuuming.html#AUTOVACUUM" title="24.1.6. The Autovacuum Daemon">Section 24.1.6</a>. You might
7 need to adjust the autovacuuming parameters described there to obtain best
8 results for your situation. Some database administrators will want to
9 supplement or replace the daemon's activities with manually-managed
10 <code class="command">VACUUM</code> commands, which typically are executed according to a
11 schedule by <span class="application">cron</span> or <span class="application">Task
12 Scheduler</span> scripts. To set up manually-managed vacuuming properly,
13 it is essential to understand the issues discussed in the next few
14 subsections. Administrators who rely on autovacuuming may still wish
15 to skim this material to help them understand and adjust autovacuuming.
16 </p><div class="sect2" id="VACUUM-BASICS"><div class="titlepage"><div><div><h3 class="title">24.1.1. Vacuuming Basics <a href="#VACUUM-BASICS" class="id_link">#</a></h3></div></div></div><p>
17 <span class="productname">PostgreSQL</span>'s
18 <a class="link" href="sql-vacuum.html" title="VACUUM"><code class="command">VACUUM</code></a> command has to
19 process each table on a regular basis for several reasons:
21 </p><div class="orderedlist"><ol class="orderedlist" type="1"><li class="listitem">To recover or reuse disk space occupied by updated or deleted
22 rows.</li><li class="listitem">To update data statistics used by the
23 <span class="productname">PostgreSQL</span> query planner.</li><li class="listitem">To update the visibility map, which speeds
24 up <a class="link" href="indexes-index-only-scans.html" title="11.9. Index-Only Scans and Covering Indexes">index-only
25 scans</a>.</li><li class="listitem">To protect against loss of very old data due to
26 <em class="firstterm">transaction ID wraparound</em> or
27 <em class="firstterm">multixact ID wraparound</em>.</li></ol></div><p>
29 Each of these reasons dictates performing <code class="command">VACUUM</code> operations
30 of varying frequency and scope, as explained in the following subsections.
32 There are two variants of <code class="command">VACUUM</code>: standard <code class="command">VACUUM</code>
33 and <code class="command">VACUUM FULL</code>. <code class="command">VACUUM FULL</code> can reclaim more
34 disk space but runs much more slowly. Also,
35 the standard form of <code class="command">VACUUM</code> can run in parallel with production
36 database operations. (Commands such as <code class="command">SELECT</code>,
37 <code class="command">INSERT</code>, <code class="command">UPDATE</code>, and
38 <code class="command">DELETE</code> will continue to function normally, though you
39 will not be able to modify the definition of a table with commands such as
40 <code class="command">ALTER TABLE</code> while it is being vacuumed.)
41 <code class="command">VACUUM FULL</code> requires an
42 <code class="literal">ACCESS EXCLUSIVE</code> lock on the table it is
43 working on, and therefore cannot be done in parallel with other use
44 of the table. Generally, therefore,
45 administrators should strive to use standard <code class="command">VACUUM</code> and
46 avoid <code class="command">VACUUM FULL</code>.
48 <code class="command">VACUUM</code> creates a substantial amount of I/O
49 traffic, which can cause poor performance for other active sessions.
50 There are configuration parameters that can be adjusted to reduce the
51 performance impact of background vacuuming — see
52 <a class="xref" href="runtime-config-vacuum.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST" title="19.10.2. Cost-based Vacuum Delay">Section 19.10.2</a>.
53 </p></div><div class="sect2" id="VACUUM-FOR-SPACE-RECOVERY"><div class="titlepage"><div><div><h3 class="title">24.1.2. Recovering Disk Space <a href="#VACUUM-FOR-SPACE-RECOVERY" class="id_link">#</a></h3></div></div></div><a id="id-1.6.11.10.5.2" class="indexterm"></a><p>
54 In <span class="productname">PostgreSQL</span>, an
55 <code class="command">UPDATE</code> or <code class="command">DELETE</code> of a row does not
56 immediately remove the old version of the row.
57 This approach is necessary to gain the benefits of multiversion
58 concurrency control (<acronym class="acronym">MVCC</acronym>, see <a class="xref" href="mvcc.html" title="Chapter 13. Concurrency Control">Chapter 13</a>): the row version
59 must not be deleted while it is still potentially visible to other
60 transactions. But eventually, an outdated or deleted row version is no
61 longer of interest to any transaction. The space it occupies must then be
62 reclaimed for reuse by new rows, to avoid unbounded growth of disk
63 space requirements. This is done by running <code class="command">VACUUM</code>.
65 The standard form of <code class="command">VACUUM</code> removes dead row
66 versions in tables and indexes and marks the space available for
67 future reuse. However, it will not return the space to the operating
68 system, except in the special case where one or more pages at the
69 end of a table become entirely free and an exclusive table lock can be
70 easily obtained. In contrast, <code class="command">VACUUM FULL</code> actively compacts
71 tables by writing a complete new version of the table file with no dead
72 space. This minimizes the size of the table, but can take a long time.
73 It also requires extra disk space for the new copy of the table, until
74 the operation completes.
76 The usual goal of routine vacuuming is to do standard <code class="command">VACUUM</code>s
77 often enough to avoid needing <code class="command">VACUUM FULL</code>. The
78 autovacuum daemon attempts to work this way, and in fact will
79 never issue <code class="command">VACUUM FULL</code>. In this approach, the idea
80 is not to keep tables at their minimum size, but to maintain steady-state
81 usage of disk space: each table occupies space equivalent to its
82 minimum size plus however much space gets used up between vacuum runs.
83 Although <code class="command">VACUUM FULL</code> can be used to shrink a table back
84 to its minimum size and return the disk space to the operating system,
85 there is not much point in this if the table will just grow again in the
86 future. Thus, moderately-frequent standard <code class="command">VACUUM</code> runs are a
87 better approach than infrequent <code class="command">VACUUM FULL</code> runs for
88 maintaining heavily-updated tables.
90 Some administrators prefer to schedule vacuuming themselves, for example
91 doing all the work at night when load is low.
92 The difficulty with doing vacuuming according to a fixed schedule
93 is that if a table has an unexpected spike in update activity, it may
94 get bloated to the point that <code class="command">VACUUM FULL</code> is really necessary
95 to reclaim space. Using the autovacuum daemon alleviates this problem,
96 since the daemon schedules vacuuming dynamically in response to update
97 activity. It is unwise to disable the daemon completely unless you
98 have an extremely predictable workload. One possible compromise is
99 to set the daemon's parameters so that it will only react to unusually
100 heavy update activity, thus keeping things from getting out of hand,
101 while scheduled <code class="command">VACUUM</code>s are expected to do the bulk of the
102 work when the load is typical.
104 For those not using autovacuum, a typical approach is to schedule a
105 database-wide <code class="command">VACUUM</code> once a day during a low-usage period,
106 supplemented by more frequent vacuuming of heavily-updated tables as
107 necessary. (Some installations with extremely high update rates vacuum
108 their busiest tables as often as once every few minutes.) If you have
109 multiple databases in a cluster, don't forget to
110 <code class="command">VACUUM</code> each one; the program <a class="xref" href="app-vacuumdb.html" title="vacuumdb"><span class="refentrytitle"><span class="application">vacuumdb</span></span></a> might be helpful.
111 </p><div class="tip"><h3 class="title">Tip</h3><p>
112 Plain <code class="command">VACUUM</code> may not be satisfactory when
113 a table contains large numbers of dead row versions as a result of
114 massive update or delete activity. If you have such a table and
115 you need to reclaim the excess disk space it occupies, you will need
116 to use <code class="command">VACUUM FULL</code>, or alternatively
117 <a class="link" href="sql-cluster.html" title="CLUSTER"><code class="command">CLUSTER</code></a>
118 or one of the table-rewriting variants of
119 <a class="link" href="sql-altertable.html" title="ALTER TABLE"><code class="command">ALTER TABLE</code></a>.
120 These commands rewrite an entire new copy of the table and build
121 new indexes for it. All these options require an
122 <code class="literal">ACCESS EXCLUSIVE</code> lock. Note that
123 they also temporarily use extra disk space approximately equal to the size
124 of the table, since the old copies of the table and indexes can't be
125 released until the new ones are complete.
126 </p></div><div class="tip"><h3 class="title">Tip</h3><p>
127 If you have a table whose entire contents are deleted on a periodic
128 basis, consider doing it with
129 <a class="link" href="sql-truncate.html" title="TRUNCATE"><code class="command">TRUNCATE</code></a> rather
130 than using <code class="command">DELETE</code> followed by
131 <code class="command">VACUUM</code>. <code class="command">TRUNCATE</code> removes the
132 entire content of the table immediately, without requiring a
133 subsequent <code class="command">VACUUM</code> or <code class="command">VACUUM
134 FULL</code> to reclaim the now-unused disk space.
135 The disadvantage is that strict MVCC semantics are violated.
136 </p></div></div><div class="sect2" id="VACUUM-FOR-STATISTICS"><div class="titlepage"><div><div><h3 class="title">24.1.3. Updating Planner Statistics <a href="#VACUUM-FOR-STATISTICS" class="id_link">#</a></h3></div></div></div><a id="id-1.6.11.10.6.2" class="indexterm"></a><a id="id-1.6.11.10.6.3" class="indexterm"></a><p>
137 The <span class="productname">PostgreSQL</span> query planner relies on
138 statistical information about the contents of tables in order to
139 generate good plans for queries. These statistics are gathered by
140 the <a class="link" href="sql-analyze.html" title="ANALYZE"><code class="command">ANALYZE</code></a> command,
141 which can be invoked by itself or
142 as an optional step in <code class="command">VACUUM</code>. It is important to have
143 reasonably accurate statistics, otherwise poor choices of plans might
144 degrade database performance.
146 The autovacuum daemon, if enabled, will automatically issue
147 <code class="command">ANALYZE</code> commands whenever the content of a table has
148 changed sufficiently. However, administrators might prefer to rely
149 on manually-scheduled <code class="command">ANALYZE</code> operations, particularly
150 if it is known that update activity on a table will not affect the
151 statistics of <span class="quote">“<span class="quote">interesting</span>”</span> columns. The daemon schedules
152 <code class="command">ANALYZE</code> strictly as a function of the number of rows
153 inserted or updated; it has no knowledge of whether that will lead
154 to meaningful statistical changes.
156 Tuples changed in partitions and inheritance children do not trigger
157 analyze on the parent table. If the parent table is empty or rarely
158 changed, it may never be processed by autovacuum, and the statistics for
159 the inheritance tree as a whole won't be collected. It is necessary to
160 run <code class="command">ANALYZE</code> on the parent table manually in order to
161 keep the statistics up to date.
163 As with vacuuming for space recovery, frequent updates of statistics
164 are more useful for heavily-updated tables than for seldom-updated
165 ones. But even for a heavily-updated table, there might be no need for
166 statistics updates if the statistical distribution of the data is
167 not changing much. A simple rule of thumb is to think about how much
168 the minimum and maximum values of the columns in the table change.
169 For example, a <code class="type">timestamp</code> column that contains the time
170 of row update will have a constantly-increasing maximum value as
171 rows are added and updated; such a column will probably need more
172 frequent statistics updates than, say, a column containing URLs for
173 pages accessed on a website. The URL column might receive changes just
174 as often, but the statistical distribution of its values probably
175 changes relatively slowly.
177 It is possible to run <code class="command">ANALYZE</code> on specific tables and even
178 just specific columns of a table, so the flexibility exists to update some
179 statistics more frequently than others if your application requires it.
180 In practice, however, it is usually best to just analyze the entire
181 database, because it is a fast operation. <code class="command">ANALYZE</code> uses a
182 statistically random sampling of the rows of a table rather than reading
184 </p><div class="tip"><h3 class="title">Tip</h3><p>
185 Although per-column tweaking of <code class="command">ANALYZE</code> frequency might not be
186 very productive, you might find it worthwhile to do per-column
187 adjustment of the level of detail of the statistics collected by
188 <code class="command">ANALYZE</code>. Columns that are heavily used in <code class="literal">WHERE</code>
189 clauses and have highly irregular data distributions might require a
190 finer-grain data histogram than other columns. See <code class="command">ALTER TABLE
191 SET STATISTICS</code>, or change the database-wide default using the <a class="xref" href="runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET">default_statistics_target</a> configuration parameter.
193 Also, by default there is limited information available about
194 the selectivity of functions. However, if you create a statistics
195 object or an expression
196 index that uses a function call, useful statistics will be
197 gathered about the function, which can greatly improve query
198 plans that use the expression index.
199 </p></div><div class="tip"><h3 class="title">Tip</h3><p>
200 The autovacuum daemon does not issue <code class="command">ANALYZE</code> commands for
201 foreign tables, since it has no means of determining how often that
202 might be useful. If your queries require statistics on foreign tables
203 for proper planning, it's a good idea to run manually-managed
204 <code class="command">ANALYZE</code> commands on those tables on a suitable schedule.
205 </p></div><div class="tip"><h3 class="title">Tip</h3><p>
206 The autovacuum daemon does not issue <code class="command">ANALYZE</code> commands
207 for partitioned tables. Inheritance parents will only be analyzed if the
208 parent itself is changed - changes to child tables do not trigger
209 autoanalyze on the parent table. If your queries require statistics on
210 parent tables for proper planning, it is necessary to periodically run
211 a manual <code class="command">ANALYZE</code> on those tables to keep the statistics
213 </p></div></div><div class="sect2" id="VACUUM-FOR-VISIBILITY-MAP"><div class="titlepage"><div><div><h3 class="title">24.1.4. Updating the Visibility Map <a href="#VACUUM-FOR-VISIBILITY-MAP" class="id_link">#</a></h3></div></div></div><p>
214 Vacuum maintains a <a class="link" href="storage-vm.html" title="66.4. Visibility Map">visibility map</a> for each
215 table to keep track of which pages contain only tuples that are known to be
216 visible to all active transactions (and all future transactions, until the
217 page is again modified). This has two purposes. First, vacuum
218 itself can skip such pages on the next run, since there is nothing to
221 Second, it allows <span class="productname">PostgreSQL</span> to answer some
222 queries using only the index, without reference to the underlying table.
223 Since <span class="productname">PostgreSQL</span> indexes don't contain tuple
224 visibility information, a normal index scan fetches the heap tuple for each
225 matching index entry, to check whether it should be seen by the current
227 An <a class="link" href="indexes-index-only-scans.html" title="11.9. Index-Only Scans and Covering Indexes"><em class="firstterm">index-only
228 scan</em></a>, on the other hand, checks the visibility map first.
229 If it's known that all tuples on the page are
230 visible, the heap fetch can be skipped. This is most useful on
231 large data sets where the visibility map can prevent disk accesses.
232 The visibility map is vastly smaller than the heap, so it can easily be
233 cached even when the heap is very large.
234 </p></div><div class="sect2" id="VACUUM-FOR-WRAPAROUND"><div class="titlepage"><div><div><h3 class="title">24.1.5. Preventing Transaction ID Wraparound Failures <a href="#VACUUM-FOR-WRAPAROUND" class="id_link">#</a></h3></div></div></div><a id="id-1.6.11.10.8.2" class="indexterm"></a><a id="id-1.6.11.10.8.3" class="indexterm"></a><p>
235 <span class="productname">PostgreSQL</span>'s
236 <a class="link" href="mvcc-intro.html" title="13.1. Introduction">MVCC</a> transaction semantics
237 depend on being able to compare transaction ID (<acronym class="acronym">XID</acronym>)
238 numbers: a row version with an insertion XID greater than the current
239 transaction's XID is <span class="quote">“<span class="quote">in the future</span>”</span> and should not be visible
240 to the current transaction. But since transaction IDs have limited size
241 (32 bits) a cluster that runs for a long time (more
242 than 4 billion transactions) would suffer <em class="firstterm">transaction ID
243 wraparound</em>: the XID counter wraps around to zero, and all of a sudden
244 transactions that were in the past appear to be in the future — which
245 means their output become invisible. In short, catastrophic data loss.
246 (Actually the data is still there, but that's cold comfort if you cannot
247 get at it.) To avoid this, it is necessary to vacuum every table
248 in every database at least once every two billion transactions.
250 The reason that periodic vacuuming solves the problem is that
251 <code class="command">VACUUM</code> will mark rows as <span class="emphasis"><em>frozen</em></span>, indicating that
252 they were inserted by a transaction that committed sufficiently far in
253 the past that the effects of the inserting transaction are certain to be
254 visible to all current and future transactions.
256 compared using modulo-2<sup>32</sup> arithmetic. This means
257 that for every normal XID, there are two billion XIDs that are
258 <span class="quote">“<span class="quote">older</span>”</span> and two billion that are <span class="quote">“<span class="quote">newer</span>”</span>; another
259 way to say it is that the normal XID space is circular with no
260 endpoint. Therefore, once a row version has been created with a particular
261 normal XID, the row version will appear to be <span class="quote">“<span class="quote">in the past</span>”</span> for
262 the next two billion transactions, no matter which normal XID we are
263 talking about. If the row version still exists after more than two billion
264 transactions, it will suddenly appear to be in the future. To
265 prevent this, <span class="productname">PostgreSQL</span> reserves a special XID,
266 <code class="literal">FrozenTransactionId</code>, which does not follow the normal XID
267 comparison rules and is always considered older
268 than every normal XID.
269 Frozen row versions are treated as if the inserting XID were
270 <code class="literal">FrozenTransactionId</code>, so that they will appear to be
271 <span class="quote">“<span class="quote">in the past</span>”</span> to all normal transactions regardless of wraparound
272 issues, and so such row versions will be valid until deleted, no matter
274 </p><div class="note"><h3 class="title">Note</h3><p>
275 In <span class="productname">PostgreSQL</span> versions before 9.4, freezing was
276 implemented by actually replacing a row's insertion XID
277 with <code class="literal">FrozenTransactionId</code>, which was visible in the
278 row's <code class="structname">xmin</code> system column. Newer versions just set a flag
279 bit, preserving the row's original <code class="structname">xmin</code> for possible
280 forensic use. However, rows with <code class="structname">xmin</code> equal
281 to <code class="literal">FrozenTransactionId</code> (2) may still be found
282 in databases <span class="application">pg_upgrade</span>'d from pre-9.4 versions.
284 Also, system catalogs may contain rows with <code class="structname">xmin</code> equal
285 to <code class="literal">BootstrapTransactionId</code> (1), indicating that they were
286 inserted during the first phase of <span class="application">initdb</span>.
287 Like <code class="literal">FrozenTransactionId</code>, this special XID is treated as
288 older than every normal XID.
290 <a class="xref" href="runtime-config-vacuum.html#GUC-VACUUM-FREEZE-MIN-AGE">vacuum_freeze_min_age</a>
291 controls how old an XID value has to be before rows bearing that XID will be
292 frozen. Increasing this setting may avoid unnecessary work if the
293 rows that would otherwise be frozen will soon be modified again,
294 but decreasing this setting increases
295 the number of transactions that can elapse before the table must be
298 <code class="command">VACUUM</code> uses the <a class="link" href="storage-vm.html" title="66.4. Visibility Map">visibility map</a>
299 to determine which pages of a table must be scanned. Normally, it
300 will skip pages that don't have any dead row versions even if those pages
301 might still have row versions with old XID values. Therefore, normal
302 <code class="command">VACUUM</code>s won't always freeze every old row version in the table.
303 When that happens, <code class="command">VACUUM</code> will eventually need to perform an
304 <em class="firstterm">aggressive vacuum</em>, which will freeze all eligible unfrozen
305 XID and MXID values, including those from all-visible but not all-frozen pages.
307 If a table is building up a backlog of all-visible but not all-frozen
308 pages, a normal vacuum may choose to scan skippable pages in an effort to
309 freeze them. Doing so decreases the number of pages the next aggressive
310 vacuum must scan. These are referred to as <em class="firstterm">eagerly
311 scanned</em> pages. Eager scanning can be tuned to attempt to freeze
312 more all-visible pages by increasing <a class="xref" href="runtime-config-vacuum.html#GUC-VACUUM-MAX-EAGER-FREEZE-FAILURE-RATE">vacuum_max_eager_freeze_failure_rate</a>. Even if eager
313 scanning has kept the number of all-visible but not all-frozen pages to a
314 minimum, most tables still require periodic aggressive vacuuming. However,
315 any pages successfully eager frozen may be skipped during an aggressive
316 vacuum, so eager freezing may minimize the overhead of aggressive vacuums.
318 <a class="xref" href="runtime-config-vacuum.html#GUC-VACUUM-FREEZE-TABLE-AGE">vacuum_freeze_table_age</a>
319 controls when a table is aggressively vacuumed. All all-visible but not all-frozen
320 pages are scanned if the number of transactions that have passed since the
321 last such scan is greater than <code class="varname">vacuum_freeze_table_age</code> minus
322 <code class="varname">vacuum_freeze_min_age</code>. Setting
323 <code class="varname">vacuum_freeze_table_age</code> to 0 forces <code class="command">VACUUM</code> to
324 always use its aggressive strategy.
326 The maximum time that a table can go unvacuumed is two billion
327 transactions minus the <code class="varname">vacuum_freeze_min_age</code> value at
328 the time of the last aggressive vacuum. If it were to go
329 unvacuumed for longer than
330 that, data loss could result. To ensure that this does not happen,
331 autovacuum is invoked on any table that might contain unfrozen rows with
332 XIDs older than the age specified by the configuration parameter <a class="xref" href="runtime-config-vacuum.html#GUC-AUTOVACUUM-FREEZE-MAX-AGE">autovacuum_freeze_max_age</a>. (This will happen even if
333 autovacuum is disabled.)
335 This implies that if a table is not otherwise vacuumed,
336 autovacuum will be invoked on it approximately once every
337 <code class="varname">autovacuum_freeze_max_age</code> minus
338 <code class="varname">vacuum_freeze_min_age</code> transactions.
339 For tables that are regularly vacuumed for space reclamation purposes,
340 this is of little importance. However, for static tables
341 (including tables that receive inserts, but no updates or deletes),
342 there is no need to vacuum for space reclamation, so it can
343 be useful to try to maximize the interval between forced autovacuums
344 on very large static tables. Obviously one can do this either by
345 increasing <code class="varname">autovacuum_freeze_max_age</code> or decreasing
346 <code class="varname">vacuum_freeze_min_age</code>.
348 The effective maximum for <code class="varname">vacuum_freeze_table_age</code> is 0.95 *
349 <code class="varname">autovacuum_freeze_max_age</code>; a setting higher than that will be
350 capped to the maximum. A value higher than
351 <code class="varname">autovacuum_freeze_max_age</code> wouldn't make sense because an
352 anti-wraparound autovacuum would be triggered at that point anyway, and
353 the 0.95 multiplier leaves some breathing room to run a manual
354 <code class="command">VACUUM</code> before that happens. As a rule of thumb,
355 <code class="command">vacuum_freeze_table_age</code> should be set to a value somewhat
356 below <code class="varname">autovacuum_freeze_max_age</code>, leaving enough gap so that
357 a regularly scheduled <code class="command">VACUUM</code> or an autovacuum triggered by
358 normal delete and update activity is run in that window. Setting it too
359 close could lead to anti-wraparound autovacuums, even though the table
360 was recently vacuumed to reclaim space, whereas lower values lead to more
361 frequent aggressive vacuuming.
363 The sole disadvantage of increasing <code class="varname">autovacuum_freeze_max_age</code>
364 (and <code class="varname">vacuum_freeze_table_age</code> along with it) is that
365 the <code class="filename">pg_xact</code> and <code class="filename">pg_commit_ts</code>
366 subdirectories of the database cluster will take more space, because it
367 must store the commit status and (if <code class="varname">track_commit_timestamp</code> is
368 enabled) timestamp of all transactions back to
369 the <code class="varname">autovacuum_freeze_max_age</code> horizon. The commit status uses
370 two bits per transaction, so if
371 <code class="varname">autovacuum_freeze_max_age</code> is set to its maximum allowed value
372 of two billion, <code class="filename">pg_xact</code> can be expected to grow to about half
373 a gigabyte and <code class="filename">pg_commit_ts</code> to about 20GB. If this
374 is trivial compared to your total database size,
375 setting <code class="varname">autovacuum_freeze_max_age</code> to its maximum allowed value
376 is recommended. Otherwise, set it depending on what you are willing to
377 allow for <code class="filename">pg_xact</code> and <code class="filename">pg_commit_ts</code> storage.
378 (The default, 200 million transactions, translates to about 50MB
379 of <code class="filename">pg_xact</code> storage and about 2GB of <code class="filename">pg_commit_ts</code>
382 One disadvantage of decreasing <code class="varname">vacuum_freeze_min_age</code> is that
383 it might cause <code class="command">VACUUM</code> to do useless work: freezing a row
384 version is a waste of time if the row is modified
385 soon thereafter (causing it to acquire a new XID). So the setting should
386 be large enough that rows are not frozen until they are unlikely to change
389 To track the age of the oldest unfrozen XIDs in a database,
390 <code class="command">VACUUM</code> stores XID
391 statistics in the system tables <code class="structname">pg_class</code> and
392 <code class="structname">pg_database</code>. In particular,
393 the <code class="structfield">relfrozenxid</code> column of a table's
394 <code class="structname">pg_class</code> row contains the oldest remaining unfrozen
395 XID at the end of the most recent <code class="command">VACUUM</code> that successfully
396 advanced <code class="structfield">relfrozenxid</code> (typically the most recent
397 aggressive VACUUM). Similarly, the
398 <code class="structfield">datfrozenxid</code> column of a database's
399 <code class="structname">pg_database</code> row is a lower bound on the unfrozen XIDs
400 appearing in that database — it is just the minimum of the
401 per-table <code class="structfield">relfrozenxid</code> values within the database.
403 examine this information is to execute queries such as:
405 </p><pre class="programlisting">
406 SELECT c.oid::regclass as table_name,
407 greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
409 LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
410 WHERE c.relkind IN ('r', 'm');
412 SELECT datname, age(datfrozenxid) FROM pg_database;
415 The <code class="literal">age</code> column measures the number of transactions from the
416 cutoff XID to the current transaction's XID.
417 </p><div class="tip"><h3 class="title">Tip</h3><p>
418 When the <code class="command">VACUUM</code> command's <code class="literal">VERBOSE</code>
419 parameter is specified, <code class="command">VACUUM</code> prints various
420 statistics about the table. This includes information about how
421 <code class="structfield">relfrozenxid</code> and
422 <code class="structfield">relminmxid</code> advanced, and the number of
423 newly frozen pages. The same details appear in the server log when
424 autovacuum logging (controlled by <a class="xref" href="runtime-config-logging.html#GUC-LOG-AUTOVACUUM-MIN-DURATION">log_autovacuum_min_duration</a>) reports on a
425 <code class="command">VACUUM</code> operation executed by autovacuum.
427 While <code class="command">VACUUM</code> scans mostly pages that have been
428 modified since the last vacuum, it may also eagerly scan some
429 all-visible but not all-frozen pages in an attempt to freeze them, but
430 the <code class="structfield">relfrozenxid</code> will only be advanced when
431 every page of the table that might contain unfrozen XIDs is scanned.
433 <code class="structfield">relfrozenxid</code> is more than
434 <code class="varname">vacuum_freeze_table_age</code> transactions old, when
435 <code class="command">VACUUM</code>'s <code class="literal">FREEZE</code> option is used, or when all
436 pages that are not already all-frozen happen to
437 require vacuuming to remove dead row versions. When <code class="command">VACUUM</code>
438 scans every page in the table that is not already all-frozen, it should
439 set <code class="literal">age(relfrozenxid)</code> to a value just a little more than the
440 <code class="varname">vacuum_freeze_min_age</code> setting
441 that was used (more by the number of transactions started since the
442 <code class="command">VACUUM</code> started). <code class="command">VACUUM</code>
443 will set <code class="structfield">relfrozenxid</code> to the oldest XID
444 that remains in the table, so it's possible that the final value
445 will be much more recent than strictly required.
446 If no <code class="structfield">relfrozenxid</code>-advancing
447 <code class="command">VACUUM</code> is issued on the table until
448 <code class="varname">autovacuum_freeze_max_age</code> is reached, an autovacuum will soon
449 be forced for the table.
451 If for some reason autovacuum fails to clear old XIDs from a table, the
452 system will begin to emit warning messages like this when the database's
453 oldest XIDs reach forty million transactions from the wraparound point:
455 </p><pre class="programlisting">
456 WARNING: database "mydb" must be vacuumed within 39985967 transactions
457 HINT: To avoid XID assignment failures, execute a database-wide VACUUM in that database.
460 (A manual <code class="command">VACUUM</code> should fix the problem, as suggested by the
461 hint; but note that the <code class="command">VACUUM</code> should be performed by a
462 superuser, else it will fail to process system catalogs, which prevent it from
463 being able to advance the database's <code class="structfield">datfrozenxid</code>.)
464 If these warnings are ignored, the system will refuse to assign new XIDs once
465 there are fewer than three million transactions left until wraparound:
467 </p><pre class="programlisting">
468 ERROR: database is not accepting commands that assign new XIDs to avoid wraparound data loss in database "mydb"
469 HINT: Execute a database-wide VACUUM in that database.
472 In this condition any transactions already in progress can continue,
473 but only read-only transactions can be started. Operations that
474 modify database records or truncate relations will fail.
475 The <code class="command">VACUUM</code> command can still be run normally.
476 Note that, contrary to what was sometimes recommended in earlier releases,
477 it is not necessary or desirable to stop the postmaster or enter single
478 user-mode in order to restore normal operation.
479 Instead, follow these steps:
481 </p><div class="orderedlist"><ol class="orderedlist" type="1"><li class="listitem">Resolve old prepared transactions. You can find these by checking
482 <a class="link" href="view-pg-prepared-xacts.html" title="53.17. pg_prepared_xacts">pg_prepared_xacts</a> for rows where
483 <code class="literal">age(transactionid)</code> is large. Such transactions should be
484 committed or rolled back.</li><li class="listitem">End long-running open transactions. You can find these by checking
485 <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW" title="27.2.3. pg_stat_activity">pg_stat_activity</a> for rows where
486 <code class="literal">age(backend_xid)</code> or <code class="literal">age(backend_xmin)</code> is
487 large. Such transactions should be committed or rolled back, or the session
488 can be terminated using <code class="literal">pg_terminate_backend</code>.</li><li class="listitem">Drop any old replication slots. Use
489 <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-VIEW" title="27.2.4. pg_stat_replication">pg_stat_replication</a> to
490 find slots where <code class="literal">age(xmin)</code> or <code class="literal">age(catalog_xmin)</code>
491 is large. In many cases, such slots were created for replication to servers that no
492 longer exist, or that have been down for a long time. If you drop a slot for a server
493 that still exists and might still try to connect to that slot, that replica may
494 need to be rebuilt.</li><li class="listitem">Execute <code class="command">VACUUM</code> in the target database. A database-wide
495 <code class="literal">VACUUM</code> is simplest; to reduce the time required, it as also possible
496 to issue manual <code class="command">VACUUM</code> commands on the tables where
497 <code class="structfield">relminxid</code> is oldest. Do not use <code class="literal">VACUUM FULL</code>
498 in this scenario, because it requires an XID and will therefore fail, except in super-user
499 mode, where it will instead consume an XID and thus increase the risk of transaction ID
500 wraparound. Do not use <code class="literal">VACUUM FREEZE</code> either, because it will do
501 more than the minimum amount of work required to restore normal operation.</li><li class="listitem">Once normal operation is restored, ensure that autovacuum is properly configured
502 in the target database in order to avoid future problems.</li></ol></div><p>
503 </p><div class="note"><h3 class="title">Note</h3><p>
504 In earlier versions, it was sometimes necessary to stop the postmaster and
505 <code class="command">VACUUM</code> the database in a single-user mode. In typical scenarios, this
506 is no longer necessary, and should be avoided whenever possible, since it involves taking
507 the system down. It is also riskier, since it disables transaction ID wraparound safeguards
508 that are designed to prevent data loss. The only reason to use single-user mode in this
509 scenario is if you wish to <code class="command">TRUNCATE</code> or <code class="command">DROP</code> unneeded
510 tables to avoid needing to <code class="command">VACUUM</code> them. The three-million-transaction
511 safety margin exists to let the administrator do this. See the
512 <a class="xref" href="app-postgres.html" title="postgres"><span class="refentrytitle"><span class="application">postgres</span></span></a> reference page for details about using single-user mode.
513 </p></div><div class="sect3" id="VACUUM-FOR-MULTIXACT-WRAPAROUND"><div class="titlepage"><div><div><h4 class="title">24.1.5.1. Multixacts and Wraparound <a href="#VACUUM-FOR-MULTIXACT-WRAPAROUND" class="id_link">#</a></h4></div></div></div><a id="id-1.6.11.10.8.21.2" class="indexterm"></a><a id="id-1.6.11.10.8.21.3" class="indexterm"></a><p>
514 <em class="firstterm">Multixact IDs</em> are used to support row locking by
515 multiple transactions. Since there is only limited space in a tuple
516 header to store lock information, that information is encoded as
517 a <span class="quote">“<span class="quote">multiple transaction ID</span>”</span>, or multixact ID for short,
518 whenever there is more than one transaction concurrently locking a
519 row. Information about which transaction IDs are included in any
520 particular multixact ID is stored separately in
521 the <code class="filename">pg_multixact</code> subdirectory, and only the multixact ID
522 appears in the <code class="structfield">xmax</code> field in the tuple header.
523 Like transaction IDs, multixact IDs are implemented as a
524 32-bit counter and corresponding storage, all of which requires
525 careful aging management, storage cleanup, and wraparound handling.
526 There is a separate storage area which holds the list of members in
527 each multixact, which also uses a 32-bit counter and which must also
528 be managed. The system function
529 <code class="function">pg_get_multixact_members()</code> described in
530 <a class="xref" href="functions-info.html#FUNCTIONS-PG-SNAPSHOT" title="Table 9.84. Transaction ID and Snapshot Information Functions">Table 9.84</a> can be used to examine the
531 transaction IDs associated with a multixact ID.
533 Whenever <code class="command">VACUUM</code> scans any part of a table, it will replace
534 any multixact ID it encounters which is older than
535 <a class="xref" href="runtime-config-vacuum.html#GUC-VACUUM-MULTIXACT-FREEZE-MIN-AGE">vacuum_multixact_freeze_min_age</a>
536 by a different value, which can be the zero value, a single
537 transaction ID, or a newer multixact ID. For each table,
538 <code class="structname">pg_class</code>.<code class="structfield">relminmxid</code> stores the oldest
539 possible multixact ID still appearing in any tuple of that table.
540 If this value is older than
541 <a class="xref" href="runtime-config-vacuum.html#GUC-VACUUM-MULTIXACT-FREEZE-TABLE-AGE">vacuum_multixact_freeze_table_age</a>, an aggressive
542 vacuum is forced. As discussed in the previous section, an aggressive
543 vacuum means that only those pages which are known to be all-frozen will
544 be skipped. <code class="function">mxid_age()</code> can be used on
545 <code class="structname">pg_class</code>.<code class="structfield">relminmxid</code> to find its age.
547 Aggressive <code class="command">VACUUM</code>s, regardless of what causes
548 them, are <span class="emphasis"><em>guaranteed</em></span> to be able to advance
549 the table's <code class="structfield">relminmxid</code>.
550 Eventually, as all tables in all databases are scanned and their
551 oldest multixact values are advanced, on-disk storage for older
552 multixacts can be removed.
554 As a safety device, an aggressive vacuum scan will
555 occur for any table whose multixact-age is greater than <a class="xref" href="runtime-config-vacuum.html#GUC-AUTOVACUUM-MULTIXACT-FREEZE-MAX-AGE">autovacuum_multixact_freeze_max_age</a>. Also, if the
556 storage occupied by multixacts members exceeds about 10GB, aggressive vacuum
557 scans will occur more often for all tables, starting with those that
558 have the oldest multixact-age. Both of these kinds of aggressive
559 scans will occur even if autovacuum is nominally disabled. The members storage
560 area can grow up to about 20GB before reaching wraparound.
562 Similar to the XID case, if autovacuum fails to clear old MXIDs from a table, the
563 system will begin to emit warning messages when the database's oldest MXIDs reach forty
564 million transactions from the wraparound point. And, just as in the XID case, if these
565 warnings are ignored, the system will refuse to generate new MXIDs once there are fewer
566 than three million left until wraparound.
568 Normal operation when MXIDs are exhausted can be restored in much the same way as
569 when XIDs are exhausted. Follow the same steps in the previous section, but with the
570 following differences:
572 </p><div class="orderedlist"><ol class="orderedlist" type="1"><li class="listitem">Running transactions and prepared transactions can be ignored if there
573 is no chance that they might appear in a multixact.</li><li class="listitem">MXID information is not directly visible in system views such as
574 <code class="literal">pg_stat_activity</code>; however, looking for old XIDs is still a good
575 way of determining which transactions are causing MXID wraparound problems.</li><li class="listitem">XID exhaustion will block all write transactions, but MXID exhaustion will
576 only block a subset of write transactions, specifically those that involve
577 row locks that require an MXID.</li></ol></div><p>
578 </p></div></div><div class="sect2" id="AUTOVACUUM"><div class="titlepage"><div><div><h3 class="title">24.1.6. The Autovacuum Daemon <a href="#AUTOVACUUM" class="id_link">#</a></h3></div></div></div><a id="id-1.6.11.10.9.2" class="indexterm"></a><p>
579 <span class="productname">PostgreSQL</span> has an optional but highly
580 recommended feature called <em class="firstterm">autovacuum</em>,
581 whose purpose is to automate the execution of
582 <code class="command">VACUUM</code> and <code class="command">ANALYZE</code> commands.
583 When enabled, autovacuum checks for
584 tables that have had a large number of inserted, updated or deleted
585 tuples. These checks use the statistics collection facility;
586 therefore, autovacuum cannot be used unless <a class="xref" href="runtime-config-statistics.html#GUC-TRACK-COUNTS">track_counts</a> is set to <code class="literal">true</code>.
587 In the default configuration, autovacuuming is enabled and the related
588 configuration parameters are appropriately set.
590 The <span class="quote">“<span class="quote">autovacuum daemon</span>”</span> actually consists of multiple processes.
591 There is a persistent daemon process, called the
592 <em class="firstterm">autovacuum launcher</em>, which is in charge of starting
593 <em class="firstterm">autovacuum worker</em> processes for all databases. The
594 launcher will distribute the work across time, attempting to start one
595 worker within each database every <a class="xref" href="runtime-config-vacuum.html#GUC-AUTOVACUUM-NAPTIME">autovacuum_naptime</a>
596 seconds. (Therefore, if the installation has <em class="replaceable"><code>N</code></em> databases,
597 a new worker will be launched every
598 <code class="varname">autovacuum_naptime</code>/<em class="replaceable"><code>N</code></em> seconds.)
599 A maximum of <a class="xref" href="runtime-config-vacuum.html#GUC-AUTOVACUUM-MAX-WORKERS">autovacuum_max_workers</a> worker processes
600 are allowed to run at the same time. If there are more than
601 <code class="varname">autovacuum_max_workers</code> databases to be processed,
602 the next database will be processed as soon as the first worker finishes.
603 Each worker process will check each table within its database and
604 execute <code class="command">VACUUM</code> and/or <code class="command">ANALYZE</code> as needed.
605 <a class="xref" href="runtime-config-logging.html#GUC-LOG-AUTOVACUUM-MIN-DURATION">log_autovacuum_min_duration</a> can be set to monitor
606 autovacuum workers' activity.
608 If several large tables all become eligible for vacuuming in a short
609 amount of time, all autovacuum workers might become occupied with
610 vacuuming those tables for a long period. This would result
611 in other tables and databases not being vacuumed until a worker becomes
612 available. There is no limit on how many workers might be in a
613 single database, but workers do try to avoid repeating work that has
614 already been done by other workers. Note that the number of running
615 workers does not count towards <a class="xref" href="runtime-config-connection.html#GUC-MAX-CONNECTIONS">max_connections</a> or
616 <a class="xref" href="runtime-config-connection.html#GUC-SUPERUSER-RESERVED-CONNECTIONS">superuser_reserved_connections</a> limits.
618 Tables whose <code class="structfield">relfrozenxid</code> value is more than
619 <a class="xref" href="runtime-config-vacuum.html#GUC-AUTOVACUUM-FREEZE-MAX-AGE">autovacuum_freeze_max_age</a> transactions old are always
620 vacuumed (this also applies to those tables whose freeze max age has
621 been modified via storage parameters; see below). Otherwise, if the
622 number of tuples obsoleted since the last
623 <code class="command">VACUUM</code> exceeds the <span class="quote">“<span class="quote">vacuum threshold</span>”</span>, the
624 table is vacuumed. The vacuum threshold is defined as:
625 </p><pre class="programlisting">
626 vacuum threshold = Minimum(vacuum max threshold, vacuum base threshold + vacuum scale factor * number of tuples)
628 where the vacuum max threshold is
629 <a class="xref" href="runtime-config-vacuum.html#GUC-AUTOVACUUM-VACUUM-MAX-THRESHOLD">autovacuum_vacuum_max_threshold</a>,
630 the vacuum base threshold is
631 <a class="xref" href="runtime-config-vacuum.html#GUC-AUTOVACUUM-VACUUM-THRESHOLD">autovacuum_vacuum_threshold</a>,
632 the vacuum scale factor is
633 <a class="xref" href="runtime-config-vacuum.html#GUC-AUTOVACUUM-VACUUM-SCALE-FACTOR">autovacuum_vacuum_scale_factor</a>,
634 and the number of tuples is
635 <code class="structname">pg_class</code>.<code class="structfield">reltuples</code>.
637 The table is also vacuumed if the number of tuples inserted since the last
638 vacuum has exceeded the defined insert threshold, which is defined as:
639 </p><pre class="programlisting">
640 vacuum insert threshold = vacuum base insert threshold + vacuum insert scale factor * number of tuples
642 where the vacuum insert base threshold is
643 <a class="xref" href="runtime-config-vacuum.html#GUC-AUTOVACUUM-VACUUM-INSERT-THRESHOLD">autovacuum_vacuum_insert_threshold</a>,
644 and vacuum insert scale factor is
645 <a class="xref" href="runtime-config-vacuum.html#GUC-AUTOVACUUM-VACUUM-INSERT-SCALE-FACTOR">autovacuum_vacuum_insert_scale_factor</a>.
646 Such vacuums may allow portions of the table to be marked as
647 <em class="firstterm">all visible</em> and also allow tuples to be frozen, which
648 can reduce the work required in subsequent vacuums.
649 For tables which receive <code class="command">INSERT</code> operations but no or
650 almost no <code class="command">UPDATE</code>/<code class="command">DELETE</code> operations,
651 it may be beneficial to lower the table's
652 <a class="xref" href="sql-createtable.html#RELOPTION-AUTOVACUUM-FREEZE-MIN-AGE">autovacuum_freeze_min_age</a> as this may allow
653 tuples to be frozen by earlier vacuums. The number of obsolete tuples and
654 the number of inserted tuples are obtained from the cumulative statistics system;
655 it is an eventually-consistent count updated by each <code class="command">UPDATE</code>,
656 <code class="command">DELETE</code> and <code class="command">INSERT</code> operation.
657 If the <code class="structfield">relfrozenxid</code> value of the table
658 is more than <code class="varname">vacuum_freeze_table_age</code> transactions old,
659 an aggressive vacuum is performed to freeze old tuples and advance
660 <code class="structfield">relfrozenxid</code>.
662 For analyze, a similar condition is used: the threshold, defined as:
663 </p><pre class="programlisting">
664 analyze threshold = analyze base threshold + analyze scale factor * number of tuples
666 is compared to the total number of tuples inserted, updated, or deleted
667 since the last <code class="command">ANALYZE</code>.
669 Partitioned tables do not directly store tuples and consequently
670 are not processed by autovacuum. (Autovacuum does process table
671 partitions just like other tables.) Unfortunately, this means that
672 autovacuum does not run <code class="command">ANALYZE</code> on partitioned
673 tables, and this can cause suboptimal plans for queries that reference
674 partitioned table statistics. You can work around this problem by
675 manually running <code class="command">ANALYZE</code> on partitioned tables
676 when they are first populated, and again whenever the distribution
677 of data in their partitions changes significantly.
679 Temporary tables cannot be accessed by autovacuum. Therefore,
680 appropriate vacuum and analyze operations should be performed via
681 session SQL commands.
683 The default thresholds and scale factors are taken from
684 <code class="filename">postgresql.conf</code>, but it is possible to override them
685 (and many other autovacuum control parameters) on a per-table basis; see
686 <a class="xref" href="sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS" title="Storage Parameters">Storage Parameters</a> for more information.
687 If a setting has been changed via a table's storage parameters, that value
688 is used when processing that table; otherwise the global settings are
689 used. See <a class="xref" href="runtime-config-vacuum.html#RUNTIME-CONFIG-AUTOVACUUM" title="19.10.1. Automatic Vacuuming">Section 19.10.1</a> for more details on
692 When multiple workers are running, the autovacuum cost delay parameters
693 (see <a class="xref" href="runtime-config-vacuum.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST" title="19.10.2. Cost-based Vacuum Delay">Section 19.10.2</a>) are
694 <span class="quote">“<span class="quote">balanced</span>”</span> among all the running workers, so that the
695 total I/O impact on the system is the same regardless of the number
696 of workers actually running. However, any workers processing tables whose
697 per-table <code class="literal">autovacuum_vacuum_cost_delay</code> or
698 <code class="literal">autovacuum_vacuum_cost_limit</code> storage parameters have been set
699 are not considered in the balancing algorithm.
701 Autovacuum workers generally don't block other commands. If a process
702 attempts to acquire a lock that conflicts with the
703 <code class="literal">SHARE UPDATE EXCLUSIVE</code> lock held by autovacuum, lock
704 acquisition will interrupt the autovacuum. For conflicting lock modes,
705 see <a class="xref" href="explicit-locking.html#TABLE-LOCK-COMPATIBILITY" title="Table 13.2. Conflicting Lock Modes">Table 13.2</a>. However, if the autovacuum
706 is running to prevent transaction ID wraparound (i.e., the autovacuum query
707 name in the <code class="structname">pg_stat_activity</code> view ends with
708 <code class="literal">(to prevent wraparound)</code>), the autovacuum is not
709 automatically interrupted.
710 </p><div class="warning"><h3 class="title">Warning</h3><p>
711 Regularly running commands that acquire locks conflicting with a
712 <code class="literal">SHARE UPDATE EXCLUSIVE</code> lock (e.g., ANALYZE) can
713 effectively prevent autovacuums from ever completing.
714 </p></div></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="maintenance.html" title="Chapter 24. Routine Database Maintenance Tasks">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="maintenance.html" title="Chapter 24. Routine Database Maintenance Tasks">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="routine-reindex.html" title="24.2. Routine Reindexing">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 24. Routine Database Maintenance Tasks </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 24.2. Routine Reindexing</td></tr></table></div></body></html>
begriffs.com / projects / ai-pg / blob