1VACUUMDB(1) PostgreSQL 14.3 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 several (currently three) stages of
182 analyze with different configuration settings, to produce usable
183 statistics faster.
184 This option is useful to analyze a database that was newly
186 populated from a restored dump or by pg_upgrade. This option will
187 try to create some statistics as fast as possible, to make the
188 database usable, and then produce full statistics in the subsequent
189 stages.
190 -?
192 --help
193 Show help about vacuumdb command line arguments, and exit.
194 vacuumdb also accepts the following command-line arguments for
196 connection parameters:
197 -h host
199 --host=host
200 Specifies the host name of the machine on which the server is
201 running. If the value begins with a slash, it is used as the
202 directory for the Unix domain socket.
203 -p port
205 --port=port
206 Specifies the TCP port or local Unix domain socket file extension
207 on which the server is listening for connections.
208 -U username
210 --username=username
211 User name to connect as.
212 -w
214 --no-password
215 Never issue a password prompt. If the server requires password
216 authentication and a password is not available by other means such
217 as a .pgpass file, the connection attempt will fail. This option
218 can be useful in batch jobs and scripts where no user is present to
219 enter a password.
220 -W
222 --password
223 Force vacuumdb to prompt for a password before connecting to a
224 database.
225 This option is never essential, since vacuumdb will automatically
227 prompt for a password if the server demands password
228 authentication. However, vacuumdb will waste a connection attempt
229 finding out that the server wants a password. In some cases it is
230 worth typing -W to avoid the extra connection attempt.
231 --maintenance-db=dbname
233 Specifies the name of the database to connect to to discover which
234 databases should be vacuumed, when -a/--all is used. If not
235 specified, the postgres database will be used, or if that does not
236 exist, template1 will be used. This can be a connection string. If
237 so, connection string parameters will override any conflicting
238 command line options. Also, connection string parameters other than
239 the database name itself will be re-used when connecting to other
240 databases.
241
243 PGDATABASE
244 PGHOST
245 PGPORT
246 PGUSER
247 Default connection parameters
248 PG_COLOR
250 Specifies whether to use color in diagnostic messages. Possible
251 values are always, auto and never.
252 This utility, like most other PostgreSQL utilities, also uses the
254 environment variables supported by libpq (see Section 34.15).
255
257 In case of difficulty, see VACUUM(7) and psql(1) for discussions of
258 potential problems and error messages. The database server must be
259 running at the targeted host. Also, any default connection settings and
260 environment variables used by the libpq front-end library will apply.
261
263 vacuumdb might need to connect several times to the PostgreSQL server,
264 asking for a password each time. It is convenient to have a ~/.pgpass
265 file in such cases. See Section 34.16 for more information.
266
268 To clean the database test:
269 $ vacuumdb test
271 To clean and analyze for the optimizer a database named bigdb:
273 $ vacuumdb --analyze bigdb
275 To clean a single table foo in a database named xyzzy, and analyze a
277 single column bar of the table for the optimizer:
278 $ vacuumdb --analyze --verbose --table='foo(bar)' xyzzy
280
282 VACUUM(7)
283PostgreSQL 14.3 2022 VACUUMDB(1)