The vacuum and vacuum full contrast of PostgreSQL

Vacuum commands exist in two forms, vacuum and vacuum full, and the difference between them is shown in the following table:

	No vacuum	VACUUM	VACUUM full
After deleting large amounts of data	Simply set the deleted data status to deleted and the space cannot be recorded for reuse.	If the deleted record is at the end of the table, the space it occupies will be physically released and returned to the operating system. If it is not the end data, the command resets the space occupied by the deleted data in the specified table or index back to the available state, so that when new data is inserted in the future, the space will be used preferentially until all reused space is exhausted, and then consider using the new disk page.	Regardless of whether the deleted data is at the end of the data table, the space occupied by the data will be physically released and returned to the operating system. When new data is inserted, a new disk page is allocated for use.
Execution efficiency		Because only the state is set to operate, it is highly efficient.	In the current version of PostgreSQL (v9.1), the command regenerates a data file for the specified table or index, imports the data that is available in the original file into a new file, and then deletes the original data file. Therefore, during the import process, the current disk is required to have more space available for this operation. This shows that the execution efficiency of this command is relatively low.
Whether the physical space occupied by the deleted data is being re-planned to the operating system.	No	No	Yes
Whether other operations on the table can be executed concurrently when the vacuum command is executed.		Because the operation is a shared lock, it can be done in parallel with other operations.	Because this operation requires an exclusive lock on the specified table, any operations based on that table will be suspended during the operation, knowing that the operation is complete.
Recommended ways to use	In the case of data emptying, you can use the truncate operation because the operation will physically empty the data table and return the space it occupies directly to the operating system.	To ensure that the number of disk pages in a datasheet is maintained at a relatively stable value, you can periodically perform this operation, such as a period of relatively small data operations per day or week.	Given the overhead of this operation and the exclusion of other errors, it is recommended to periodically monitor a table with a large variation in the volume of data, and only consider performing the operation once if it confirms that its disk page occupancy is close to the threshold. Even so, it is important to take care to do this by selecting a period of time when the data is operating less.
Efficiency of other operations after execution	For queries, efficiency is progressively reduced due to the large number of disk page fragments.	Compared to not performing any vacuum operation, it is more efficient, but the efficiency of insertion is reduced.	After performing this operation, all operations based on the table will be significantly more efficient.

by Baltic

The vacuum and vacuum full contrast of PostgreSQL