Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages

  
 
  

home | help
VACUUM(7)		 PostgreSQL 17.5 Documentation		     VACUUM(7)

NAME
       VACUUM -	garbage-collect	and optionally analyze a database

SYNOPSIS
       VACUUM [	( option [, ...] ) ] [ table_and_columns [, ...] ]

       where option can	be one of:

	   FULL	[ boolean ]
	   FREEZE [ boolean ]
	   VERBOSE [ boolean ]
	   ANALYZE [ boolean ]
	   DISABLE_PAGE_SKIPPING [ boolean ]
	   SKIP_LOCKED [ boolean ]
	   INDEX_CLEANUP { AUTO	| ON | OFF }
	   PROCESS_MAIN	[ boolean ]
	   PROCESS_TOAST [ boolean ]
	   TRUNCATE [ boolean ]
	   PARALLEL integer
	   SKIP_DATABASE_STATS [ boolean ]
	   ONLY_DATABASE_STATS [ boolean ]
	   BUFFER_USAGE_LIMIT size

       and table_and_columns is:

	   table_name [	( column_name [, ...] )	]

DESCRIPTION
       VACUUM reclaims storage occupied	by dead	tuples.	In normal PostgreSQL
       operation, tuples that are deleted or obsoleted by an update are	not
       physically removed from their table; they remain	present	until a	VACUUM
       is done.	Therefore it's necessary to do VACUUM periodically, especially
       on frequently-updated tables.

       Without a table_and_columns list, VACUUM	processes every	table and
       materialized view in the	current	database that the current user has
       permission to vacuum. With a list, VACUUM processes only	those
       table(s).

       VACUUM ANALYZE performs a VACUUM	and then an ANALYZE for	each selected
       table. This is a	handy combination form for routine maintenance
       scripts.	See ANALYZE(7) for more	details	about its processing.

       Plain VACUUM (without FULL) simply reclaims space and makes it
       available for re-use. This form of the command can operate in parallel
       with normal reading and writing of the table, as	an exclusive lock is
       not obtained. However, extra space is not returned to the operating
       system (in most cases); it's just kept available	for re-use within the
       same table. It also allows us to	leverage multiple CPUs in order	to
       process indexes.	This feature is	known as parallel vacuum. To disable
       this feature, one can use PARALLEL option and specify parallel workers
       as zero.	 VACUUM	FULL rewrites the entire contents of the table into a
       new disk	file with no extra space, allowing unused space	to be returned
       to the operating	system.	This form is much slower and requires an
       ACCESS EXCLUSIVE	lock on	each table while it is being processed.

PARAMETERS
       FULL
	   Selects "full" vacuum, which	can reclaim more space,	but takes much
	   longer and exclusively locks	the table. This	method also requires
	   extra disk space, since it writes a new copy	of the table and
	   doesn't release the old copy	until the operation is complete.
	   Usually this	should only be used when a significant amount of space
	   needs to be reclaimed from within the table.

       FREEZE
	   Selects aggressive "freezing" of tuples. Specifying FREEZE is
	   equivalent to performing VACUUM with	the vacuum_freeze_min_age and
	   vacuum_freeze_table_age parameters set to zero. Aggressive freezing
	   is always performed when the	table is rewritten, so this option is
	   redundant when FULL is specified.

       VERBOSE
	   Prints a detailed vacuum activity report for	each table.

       ANALYZE
	   Updates statistics used by the planner to determine the most
	   efficient way to execute a query.

       DISABLE_PAGE_SKIPPING
	   Normally, VACUUM will skip pages based on the visibility map. Pages
	   where all tuples are	known to be frozen can always be skipped, and
	   those where all tuples are known to be visible to all transactions
	   may be skipped except when performing an aggressive vacuum.
	   Furthermore,	except when performing an aggressive vacuum, some
	   pages may be	skipped	in order to avoid waiting for other sessions
	   to finish using them. This option disables all page-skipping
	   behavior, and is intended to	be used	only when the contents of the
	   visibility map are suspect, which should happen only	if there is a
	   hardware or software	issue causing database corruption.

       SKIP_LOCKED
	   Specifies that VACUUM should	not wait for any conflicting locks to
	   be released when beginning work on a	relation: if a relation	cannot
	   be locked immediately without waiting, the relation is skipped.
	   Note	that even with this option, VACUUM may still block when
	   opening the relation's indexes. Additionally, VACUUM	ANALYZE	may
	   still block when acquiring sample rows from partitions, table
	   inheritance children, and some types	of foreign tables. Also, while
	   VACUUM ordinarily processes all partitions of specified partitioned
	   tables, this	option will cause VACUUM to skip all partitions	if
	   there is a conflicting lock on the partitioned table.

       INDEX_CLEANUP
	   Normally, VACUUM will skip index vacuuming when there are very few
	   dead	tuples in the table. The cost of processing all	of the table's
	   indexes is expected to greatly exceed the benefit of	removing dead
	   index tuples	when this happens. This	option can be used to force
	   VACUUM to process indexes when there	are more than zero dead
	   tuples. The default is AUTO,	which allows VACUUM to skip index
	   vacuuming when appropriate. If INDEX_CLEANUP	is set to ON, VACUUM
	   will	conservatively remove all dead tuples from indexes. This may
	   be useful for backwards compatibility with earlier releases of
	   PostgreSQL where this was the standard behavior.

	   INDEX_CLEANUP can also be set to OFF	to force VACUUM	to always skip
	   index vacuuming, even when there are	many dead tuples in the	table.
	   This	may be useful when it is necessary to make VACUUM run as
	   quickly as possible to avoid	imminent transaction ID	wraparound
	   (see	Section	24.1.5). However, the wraparound failsafe mechanism
	   controlled by vacuum_failsafe_age will generally trigger
	   automatically to avoid transaction ID wraparound failure, and
	   should be preferred.	If index cleanup is not	performed regularly,
	   performance may suffer, because as the table	is modified indexes
	   will	accumulate dead	tuples and the table itself will accumulate
	   dead	line pointers that cannot be removed until index cleanup is
	   completed.

	   This	option has no effect for tables	that have no index and is
	   ignored if the FULL option is used. It also has no effect on	the
	   transaction ID wraparound failsafe mechanism. When triggered	it
	   will	skip index vacuuming, even when	INDEX_CLEANUP is set to	ON.

       PROCESS_MAIN
	   Specifies that VACUUM should	attempt	to process the main relation.
	   This	is usually the desired behavior	and is the default. Setting
	   this	option to false	may be useful when it is only necessary	to
	   vacuum a relation's corresponding TOAST table.

       PROCESS_TOAST
	   Specifies that VACUUM should	attempt	to process the corresponding
	   TOAST table for each	relation, if one exists. This is usually the
	   desired behavior and	is the default.	Setting	this option to false
	   may be useful when it is only necessary to vacuum the main
	   relation. This option is required when the FULL option is used.

       TRUNCATE
	   Specifies that VACUUM should	attempt	to truncate off	any empty
	   pages at the	end of the table and allow the disk space for the
	   truncated pages to be returned to the operating system. This	is
	   normally the	desired	behavior and is	the default unless the
	   vacuum_truncate option has been set to false	for the	table to be
	   vacuumed. Setting this option to false may be useful	to avoid
	   ACCESS EXCLUSIVE lock on the	table that the truncation requires.
	   This	option is ignored if the FULL option is	used.

       PARALLEL
	   Perform index vacuum	and index cleanup phases of VACUUM in parallel
	   using integer background workers (for the details of	each vacuum
	   phase, please refer to Table	27.46).	The number of workers used to
	   perform the operation is equal to the number	of indexes on the
	   relation that support parallel vacuum which is limited by the
	   number of workers specified with PARALLEL option if any which is
	   further limited by max_parallel_maintenance_workers.	An index can
	   participate in parallel vacuum if and only if the size of the index
	   is more than	min_parallel_index_scan_size. Please note that it is
	   not guaranteed that the number of parallel workers specified	in
	   integer will	be used	during execution. It is	possible for a vacuum
	   to run with fewer workers than specified, or	even with no workers
	   at all. Only	one worker can be used per index. So parallel workers
	   are launched	only when there	are at least 2 indexes in the table.
	   Workers for vacuum are launched before the start of each phase and
	   exit	at the end of the phase. These behaviors might change in a
	   future release. This	option can't be	used with the FULL option.

       SKIP_DATABASE_STATS
	   Specifies that VACUUM should	skip updating the database-wide
	   statistics about oldest unfrozen XIDs. Normally VACUUM will update
	   these statistics once at the	end of the command. However, this can
	   take	awhile in a database with a very large number of tables, and
	   it will accomplish nothing unless the table that had	contained the
	   oldest unfrozen XID was among those vacuumed. Moreover, if multiple
	   VACUUM commands are issued in parallel, only	one of them can	update
	   the database-wide statistics	at a time. Therefore, if an
	   application intends to issue	a series of many VACUUM	commands, it
	   can be helpful to set this option in	all but	the last such command;
	   or set it in	all the	commands and separately	issue VACUUM
	   (ONLY_DATABASE_STATS) afterwards.

       ONLY_DATABASE_STATS
	   Specifies that VACUUM should	do nothing except update the
	   database-wide statistics about oldest unfrozen XIDs.	When this
	   option is specified,	the table_and_columns list must	be empty, and
	   no other option may be enabled except VERBOSE.

       BUFFER_USAGE_LIMIT
	   Specifies the Buffer	Access Strategy	ring buffer size for VACUUM.
	   This	size is	used to	calculate the number of	shared buffers which
	   will	be reused as part of this strategy.  0 disables	use of a
	   Buffer Access Strategy. If ANALYZE is also specified, the
	   BUFFER_USAGE_LIMIT value is used for	both the vacuum	and analyze
	   stages. This	option can't be	used with the FULL option except if
	   ANALYZE is also specified. When this	option is not specified,
	   VACUUM uses the value from vacuum_buffer_usage_limit. Higher
	   settings can	allow VACUUM to	run more quickly, but having too large
	   a setting may cause too many	other useful pages to be evicted from
	   shared buffers. The minimum value is	128 kB and the maximum value
	   is 16 GB.

       boolean
	   Specifies whether the selected option should	be turned on or	off.
	   You can write TRUE, ON, or 1	to enable the option, and FALSE, OFF,
	   or 0	to disable it. The boolean value can also be omitted, in which
	   case	TRUE is	assumed.

       integer
	   Specifies a non-negative integer value passed to the	selected
	   option.

       size
	   Specifies an	amount of memory in kilobytes. Sizes may also be
	   specified as	a string containing the	numerical size followed	by any
	   one of the following	memory units: B	(bytes), kB (kilobytes), MB
	   (megabytes),	GB (gigabytes),	or TB (terabytes).

       table_name
	   The name (optionally	schema-qualified) of a specific	table or
	   materialized	view to	vacuum.	If the specified table is a
	   partitioned table, all of its leaf partitions are vacuumed.

       column_name
	   The name of a specific column to analyze. Defaults to all columns.
	   If a	column list is specified, ANALYZE must also be specified.

OUTPUTS
       When VERBOSE is specified, VACUUM emits progress	messages to indicate
       which table is currently	being processed. Various statistics about the
       tables are printed as well.

NOTES
       To vacuum a table, one must ordinarily have the MAINTAIN	privilege on
       the table. However, database owners are allowed to vacuum all tables in
       their databases,	except shared catalogs.	 VACUUM	will skip over any
       tables that the calling user does not have permission to	vacuum.

       While VACUUM is running,	the search_path	is temporarily changed to
       pg_catalog, pg_temp.

       VACUUM cannot be	executed inside	a transaction block.

       For tables with GIN indexes, VACUUM (in any form) also completes	any
       pending index insertions, by moving pending index entries to the
       appropriate places in the main GIN index	structure. See
       Section 64.4.4.1	for details.

       We recommend that all databases be vacuumed regularly in	order to
       remove dead rows.  PostgreSQL includes an "autovacuum" facility which
       can automate routine vacuum maintenance.	For more information about
       automatic and manual vacuuming, see Section 24.1.

       The FULL	option is not recommended for routine use, but might be	useful
       in special cases. An example is when you	have deleted or	updated	most
       of the rows in a	table and would	like the table to physically shrink to
       occupy less disk	space and allow	faster table scans.  VACUUM FULL will
       usually shrink the table	more than a plain VACUUM would.

       The PARALLEL option is used only	for vacuum purposes. If	this option is
       specified with the ANALYZE option, it does not affect ANALYZE.

       VACUUM causes a substantial increase in I/O traffic, which might	cause
       poor performance	for other active sessions. Therefore, it is sometimes
       advisable to use	the cost-based vacuum delay feature. For parallel
       vacuum, each worker sleeps in proportion	to the work done by that
       worker. See Section 19.4.4 for details.

       Each backend running VACUUM without the FULL option will	report its
       progress	in the pg_stat_progress_vacuum view. Backends running VACUUM
       FULL will instead report	their progress in the pg_stat_progress_cluster
       view. See Section 27.4.5	and Section 27.4.2 for details.

EXAMPLES
       To clean	a single table onek, analyze it	for the	optimizer and print a
       detailed	vacuum activity	report:

	   VACUUM (VERBOSE, ANALYZE) onek;

COMPATIBILITY
       There is	no VACUUM statement in the SQL standard.

       The following syntax was	used before PostgreSQL version 9.0 and is
       still supported:

	   VACUUM [ FULL ] [ FREEZE ] [	VERBOSE	] [ ANALYZE ] [	table_and_columns [, ...] ]

       Note that in this syntax, the options must be specified in exactly the
       order shown.

SEE ALSO
       vacuumdb(1), Section 19.4.4, Section 24.1.6, Section 27.4.5,
       Section 27.4.2

PostgreSQL 17.5			     2025			     VACUUM(7)

Want to link to this manual page? Use this URL:
<https://man.freebsd.org/cgi/man.cgi?query=VACUUM&sektion=7&manpath=FreeBSD+Ports+14.3.quarterly>

home | help