1VACUUMDB(1) PostgreSQL 15.4 Documentation VACUUMDB(1)
2
6 vacuumdb - garbage-collect and analyze a PostgreSQL database
7
9 vacuumdb [connection-option...] [option...]
10 [ -t | --table table [( column [,...] )] ]... [dbname]
11 vacuumdb [connection-option...] [option...] -a | --all
13
15 vacuumdb is a utility for cleaning a PostgreSQL database. vacuumdb
16 will also generate internal statistics used by the PostgreSQL query
17 optimizer.
18 vacuumdb is a wrapper around the SQL command VACUUM. There is no
20 effective difference between vacuuming and analyzing databases via this
21 utility and via other methods for accessing the server.
22
24 vacuumdb accepts the following command-line arguments:
25 -a
27 --all
28 Vacuum all databases.
29 [-d] dbname
31 [--dbname=]dbname
32 Specifies the name of the database to be cleaned or analyzed, when
33 -a/--all is not used. If this is not specified, the database name
34 is read from the environment variable PGDATABASE. If that is not
35 set, the user name specified for the connection is used. The dbname
36 can be a connection string. If so, connection string parameters
37 will override any conflicting command line options.
38 --disable-page-skipping
40 Disable skipping pages based on the contents of the visibility map.
41 Note
43 This option is only available for servers running PostgreSQL
44 9.6 and later.
45 -e
47 --echo
48 Echo the commands that vacuumdb generates and sends to the server.
49 -f
51 --full
52 Perform “full” vacuuming.
53 -F
55 --freeze
56 Aggressively “freeze” tuples.
57 --force-index-cleanup
59 Always remove index entries pointing to dead tuples.
60 Note
62 This option is only available for servers running PostgreSQL 12
63 and later.
64 -j njobs
66 --jobs=njobs
67 Execute the vacuum or analyze commands in parallel by running njobs
68 commands simultaneously. This option may reduce the processing time
69 but it also increases the load on the database server.
70 vacuumdb will open njobs connections to the database, so make sure
72 your max_connections setting is high enough to accommodate all
73 connections.
74 Note that using this mode together with the -f (FULL) option might
76 cause deadlock failures if certain system catalogs are processed in
77 parallel.
78 --min-mxid-age mxid_age
80 Only execute the vacuum or analyze commands on tables with a
81 multixact ID age of at least mxid_age. This setting is useful for
82 prioritizing tables to process to prevent multixact ID wraparound
83 (see Section 25.1.5.1).
84 For the purposes of this option, the multixact ID age of a relation
86 is the greatest of the ages of the main relation and its associated
87 TOAST table, if one exists. Since the commands issued by vacuumdb
88 will also process the TOAST table for the relation if necessary, it
89 does not need to be considered separately.
90 Note
92 This option is only available for servers running PostgreSQL
93 9.6 and later.
94 --min-xid-age xid_age
96 Only execute the vacuum or analyze commands on tables with a
97 transaction ID age of at least xid_age. This setting is useful for
98 prioritizing tables to process to prevent transaction ID wraparound
99 (see Section 25.1.5).
100 For the purposes of this option, the transaction ID age of a
102 relation is the greatest of the ages of the main relation and its
103 associated TOAST table, if one exists. Since the commands issued by
104 vacuumdb will also process the TOAST table for the relation if
105 necessary, it does not need to be considered separately.
106 Note
108 This option is only available for servers running PostgreSQL
109 9.6 and later.
110 --no-index-cleanup
112 Do not remove index entries pointing to dead tuples.
113 Note
115 This option is only available for servers running PostgreSQL 12
116 and later.
117 --no-process-toast
119 Skip the TOAST table associated with the table to vacuum, if any.
120 Note
122 This option is only available for servers running PostgreSQL 14
123 and later.
124 --no-truncate
126 Do not truncate empty pages at the end of the table.
127 Note
129 This option is only available for servers running PostgreSQL 12
130 and later.
131 -P parallel_workers
133 --parallel=parallel_workers
134 Specify the number of parallel workers for parallel vacuum. This
135 allows the vacuum to leverage multiple CPUs to process indexes. See
136 VACUUM(7).
137 Note
139 This option is only available for servers running PostgreSQL 13
140 and later.
141 -q
143 --quiet
144 Do not display progress messages.
145 --skip-locked
147 Skip relations that cannot be immediately locked for processing.
148 Note
150 This option is only available for servers running PostgreSQL 12
151 and later.
152 -t table [ (column [,...]) ]
154 --table=table [ (column [,...]) ]
155 Clean or analyze table only. Column names can be specified only in
156 conjunction with the --analyze or --analyze-only options. Multiple
157 tables can be vacuumed by writing multiple -t switches.
158 Tip
160 If you specify columns, you probably have to escape the
161 parentheses from the shell. (See examples below.)
162 -v
164 --verbose
165 Print detailed information during processing.
166 -V
168 --version
169 Print the vacuumdb version and exit.
170 -z
172 --analyze
173 Also calculate statistics for use by the optimizer.
174 -Z
176 --analyze-only
177 Only calculate statistics for use by the optimizer (no vacuum).
178 --analyze-in-stages
180 Only calculate statistics for use by the optimizer (no vacuum),
181 like --analyze-only. Run three stages of analyze; the first stage
182 uses the lowest possible statistics target (see
183 default_statistics_target) to produce usable statistics faster, and
184 subsequent stages build the full statistics.
185 This option is only useful to analyze a database that currently has
187 no statistics or has wholly incorrect ones, such as if it is newly
188 populated from a restored dump or by pg_upgrade. Be aware that
189 running with this option in a database with existing statistics may
190 cause the query optimizer choices to become transiently worse due
191 to the low statistics targets of the early stages.
192 -?
194 --help
195 Show help about vacuumdb command line arguments, and exit.
196 vacuumdb also accepts the following command-line arguments for
198 connection parameters:
199 -h host
201 --host=host
202 Specifies the host name of the machine on which the server is
203 running. If the value begins with a slash, it is used as the
204 directory for the Unix domain socket.
205 -p port
207 --port=port
208 Specifies the TCP port or local Unix domain socket file extension
209 on which the server is listening for connections.
210 -U username
212 --username=username
213 User name to connect as.
214 -w
216 --no-password
217 Never issue a password prompt. If the server requires password
218 authentication and a password is not available by other means such
219 as a .pgpass file, the connection attempt will fail. This option
220 can be useful in batch jobs and scripts where no user is present to
221 enter a password.
222 -W
224 --password
225 Force vacuumdb to prompt for a password before connecting to a
226 database.
227 This option is never essential, since vacuumdb will automatically
229 prompt for a password if the server demands password
230 authentication. However, vacuumdb will waste a connection attempt
231 finding out that the server wants a password. In some cases it is
232 worth typing -W to avoid the extra connection attempt.
233 --maintenance-db=dbname
235 Specifies the name of the database to connect to to discover which
236 databases should be vacuumed, when -a/--all is used. If not
237 specified, the postgres database will be used, or if that does not
238 exist, template1 will be used. This can be a connection string. If
239 so, connection string parameters will override any conflicting
240 command line options. Also, connection string parameters other than
241 the database name itself will be re-used when connecting to other
242 databases.
243
245 PGDATABASE
246 PGHOST
247 PGPORT
248 PGUSER
249 Default connection parameters
250 PG_COLOR
252 Specifies whether to use color in diagnostic messages. Possible
253 values are always, auto and never.
254 This utility, like most other PostgreSQL utilities, also uses the
256 environment variables supported by libpq (see Section 34.15).
257
259 In case of difficulty, see VACUUM(7) and psql(1) for discussions of
260 potential problems and error messages. The database server must be
261 running at the targeted host. Also, any default connection settings and
262 environment variables used by the libpq front-end library will apply.
263
265 vacuumdb might need to connect several times to the PostgreSQL server,
266 asking for a password each time. It is convenient to have a ~/.pgpass
267 file in such cases. See Section 34.16 for more information.
268
270 To clean the database test:
271 $ vacuumdb test
273 To clean and analyze for the optimizer a database named bigdb:
275 $ vacuumdb --analyze bigdb
277 To clean a single table foo in a database named xyzzy, and analyze a
279 single column bar of the table for the optimizer:
280 $ vacuumdb --analyze --verbose --table='foo(bar)' xyzzy
282
284 VACUUM(7)
285PostgreSQL 15.4 2023 VACUUMDB(1)