1VACUUM(7) PostgreSQL 13.4 Documentation VACUUM(7)
2
6 VACUUM - garbage-collect and optionally analyze a database
7
9 VACUUM [ ( option [, ...] ) ] [ table_and_columns [, ...] ]
10 VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ table_and_columns [, ...] ]
11 where option can be one of:
13 FULL [ boolean ]
15 FREEZE [ boolean ]
16 VERBOSE [ boolean ]
17 ANALYZE [ boolean ]
18 DISABLE_PAGE_SKIPPING [ boolean ]
19 SKIP_LOCKED [ boolean ]
20 INDEX_CLEANUP [ boolean ]
21 TRUNCATE [ boolean ]
22 PARALLEL integer
23 and table_and_columns is:
25 table_name [ ( column_name [, ...] ) ]
27
29 VACUUM reclaims storage occupied by dead tuples. In normal PostgreSQL
30 operation, tuples that are deleted or obsoleted by an update are not
31 physically removed from their table; they remain present until a VACUUM
32 is done. Therefore it's necessary to do VACUUM periodically, especially
33 on frequently-updated tables.
34 Without a table_and_columns list, VACUUM processes every table and
36 materialized view in the current database that the current user has
37 permission to vacuum. With a list, VACUUM processes only those
38 table(s).
39 VACUUM ANALYZE performs a VACUUM and then an ANALYZE for each selected
41 table. This is a handy combination form for routine maintenance
42 scripts. See ANALYZE(7) for more details about its processing.
43 Plain VACUUM (without FULL) simply reclaims space and makes it
45 available for re-use. This form of the command can operate in parallel
46 with normal reading and writing of the table, as an exclusive lock is
47 not obtained. However, extra space is not returned to the operating
48 system (in most cases); it's just kept available for re-use within the
49 same table. It also allows us to leverage multiple CPUs in order to
50 process indexes. This feature is known as parallel vacuum. To disable
51 this feature, one can use PARALLEL option and specify parallel workers
52 as zero. VACUUM FULL rewrites the entire contents of the table into a
53 new disk file with no extra space, allowing unused space to be returned
54 to the operating system. This form is much slower and requires an
55 ACCESS EXCLUSIVE lock on each table while it is being processed.
56 When the option list is surrounded by parentheses, the options can be
58 written in any order. Without parentheses, options must be specified in
59 exactly the order shown above. The parenthesized syntax was added in
60 PostgreSQL 9.0; the unparenthesized syntax is deprecated.
61
63 FULL
64 Selects “full” vacuum, which can reclaim more space, but takes much
65 longer and exclusively locks the table. This method also requires
66 extra disk space, since it writes a new copy of the table and
67 doesn't release the old copy until the operation is complete.
68 Usually this should only be used when a significant amount of space
69 needs to be reclaimed from within the table.
70 FREEZE
72 Selects aggressive “freezing” of tuples. Specifying FREEZE is
73 equivalent to performing VACUUM with the vacuum_freeze_min_age and
74 vacuum_freeze_table_age parameters set to zero. Aggressive freezing
75 is always performed when the table is rewritten, so this option is
76 redundant when FULL is specified.
77 VERBOSE
79 Prints a detailed vacuum activity report for each table.
80 ANALYZE
82 Updates statistics used by the planner to determine the most
83 efficient way to execute a query.
84 DISABLE_PAGE_SKIPPING
86 Normally, VACUUM will skip pages based on the visibility map. Pages
87 where all tuples are known to be frozen can always be skipped, and
88 those where all tuples are known to be visible to all transactions
89 may be skipped except when performing an aggressive vacuum.
90 Furthermore, except when performing an aggressive vacuum, some
91 pages may be skipped in order to avoid waiting for other sessions
92 to finish using them. This option disables all page-skipping
93 behavior, and is intended to be used only when the contents of the
94 visibility map are suspect, which should happen only if there is a
95 hardware or software issue causing database corruption.
96 SKIP_LOCKED
98 Specifies that VACUUM should not wait for any conflicting locks to
99 be released when beginning work on a relation: if a relation cannot
100 be locked immediately without waiting, the relation is skipped.
101 Note that even with this option, VACUUM may still block when
102 opening the relation's indexes. Additionally, VACUUM ANALYZE may
103 still block when acquiring sample rows from partitions, table
104 inheritance children, and some types of foreign tables. Also, while
105 VACUUM ordinarily processes all partitions of specified partitioned
106 tables, this option will cause VACUUM to skip all partitions if
107 there is a conflicting lock on the partitioned table.
108 INDEX_CLEANUP
110 Specifies that VACUUM should attempt to remove index entries
111 pointing to dead tuples. This is normally the desired behavior and
112 is the default unless the vacuum_index_cleanup option has been set
113 to false for the table to be vacuumed. Setting this option to false
114 may be useful when it is necessary to make vacuum run as quickly as
115 possible, for example to avoid imminent transaction ID wraparound
116 (see Section 24.1.5). However, if index cleanup is not performed
117 regularly, performance may suffer, because as the table is
118 modified, indexes will accumulate dead tuples and the table itself
119 will accumulate dead line pointers that cannot be removed until
120 index cleanup is completed. This option has no effect for tables
121 that do not have an index and is ignored if the FULL option is
122 used.
123 TRUNCATE
125 Specifies that VACUUM should attempt to truncate off any empty
126 pages at the end of the table and allow the disk space for the
127 truncated pages to be returned to the operating system. This is
128 normally the desired behavior and is the default unless the
129 vacuum_truncate option has been set to false for the table to be
130 vacuumed. Setting this option to false may be useful to avoid
131 ACCESS EXCLUSIVE lock on the table that the truncation requires.
132 This option is ignored if the FULL option is used.
133 PARALLEL
135 Perform index vacuum and index cleanup phases of VACUUM in parallel
136 using integer background workers (for the details of each vacuum
137 phase, please refer to Table 27.37). The number of workers used to
138 perform the operation is equal to the number of indexes on the
139 relation that support parallel vacuum which is limited by the
140 number of workers specified with PARALLEL option if any which is
141 further limited by max_parallel_maintenance_workers. An index can
142 participate in parallel vacuum if and only if the size of the index
143 is more than min_parallel_index_scan_size. Please note that it is
144 not guaranteed that the number of parallel workers specified in
145 integer will be used during execution. It is possible for a vacuum
146 to run with fewer workers than specified, or even with no workers
147 at all. Only one worker can be used per index. So parallel workers
148 are launched only when there are at least 2 indexes in the table.
149 Workers for vacuum are launched before the start of each phase and
150 exit at the end of the phase. These behaviors might change in a
151 future release. This option can't be used with the FULL option.
152 boolean
154 Specifies whether the selected option should be turned on or off.
155 You can write TRUE, ON, or 1 to enable the option, and FALSE, OFF,
156 or 0 to disable it. The boolean value can also be omitted, in which
157 case TRUE is assumed.
158 integer
160 Specifies a non-negative integer value passed to the selected
161 option.
162 table_name
164 The name (optionally schema-qualified) of a specific table or
165 materialized view to vacuum. If the specified table is a
166 partitioned table, all of its leaf partitions are vacuumed.
167 column_name
169 The name of a specific column to analyze. Defaults to all columns.
170 If a column list is specified, ANALYZE must also be specified.
171
173 When VERBOSE is specified, VACUUM emits progress messages to indicate
174 which table is currently being processed. Various statistics about the
175 tables are printed as well.
176
178 To vacuum a table, one must ordinarily be the table's owner or a
179 superuser. However, database owners are allowed to vacuum all tables in
180 their databases, except shared catalogs. (The restriction for shared
181 catalogs means that a true database-wide VACUUM can only be performed
182 by a superuser.) VACUUM will skip over any tables that the calling
183 user does not have permission to vacuum.
184 VACUUM cannot be executed inside a transaction block.
186 For tables with GIN indexes, VACUUM (in any form) also completes any
188 pending index insertions, by moving pending index entries to the
189 appropriate places in the main GIN index structure. See Section 66.4.1
190 for details.
191 We recommend that active production databases be vacuumed frequently
193 (at least nightly), in order to remove dead rows. After adding or
194 deleting a large number of rows, it might be a good idea to issue a
195 VACUUM ANALYZE command for the affected table. This will update the
196 system catalogs with the results of all recent changes, and allow the
197 PostgreSQL query planner to make better choices in planning queries.
198 The FULL option is not recommended for routine use, but might be useful
200 in special cases. An example is when you have deleted or updated most
201 of the rows in a table and would like the table to physically shrink to
202 occupy less disk space and allow faster table scans. VACUUM FULL will
203 usually shrink the table more than a plain VACUUM would.
204 The PARALLEL option is used only for vacuum purposes. If this option is
206 specified with the ANALYZE option, it does not affect ANALYZE.
207 VACUUM causes a substantial increase in I/O traffic, which might cause
209 poor performance for other active sessions. Therefore, it is sometimes
210 advisable to use the cost-based vacuum delay feature. For parallel
211 vacuum, each worker sleeps in proportion to the work done by that
212 worker. See Section 19.4.4 for details.
213 PostgreSQL includes an “autovacuum” facility which can automate routine
215 vacuum maintenance. For more information about automatic and manual
216 vacuuming, see Section 24.1.
217
219 To clean a single table onek, analyze it for the optimizer and print a
220 detailed vacuum activity report:
221 VACUUM (VERBOSE, ANALYZE) onek;
223
225 There is no VACUUM statement in the SQL standard.
226
228 vacuumdb(1), Section 19.4.4, Section 24.1.6
229PostgreSQL 13.4 2021 VACUUM(7)