what is the difference between vacuum and full vacuum in postgresql

Question

In the real time environment, what is the difference between vacuum and full vacuum in Azure Database for PostgreSQL flexible server? Also, is it possible to turn on Auto vacuum in the night? so that it can take care of itself by morning, that would be good way to handle the vacuum instead of manually vacuuming it?

Answer 1

Hi Naveen Kumar Kakarla (Quadrant Resource),

Welcome to Microsoft Q&A forum.

As I understand, you want to know the difference between vacuum and full vacuum in postgresql.

In addition to the answer by Mohammed Hantour, please refer to the official postgresql document statement which is as follows:

VACUUM FULLcan reclaim more disk space but runs much more slowly. Also, the standard form ofVACUUMcan run in parallel with production database operations. (Commands such asSELECT, INSERT, UPDATE, and DELETEwill continue to function normally, though you will not be able to modify the definition of a table with commands such asALTER TABLEwhile it is being vacuumed.)VACUUM FULLrequires anACCESS EXCLUSIVElock on the table it is working on, and therefore cannot be done in parallel with other use of the table. Generally, therefore, administrators should strive to use standardVACUUMand avoidVACUUM FULL.

VACUUM creates a substantial amount of I/O traffic, which can cause poor performance for other active sessions. There are configuration parameters that can be adjusted to reduce the performance impact of background vacuuming.

Reference link: https://www.postgresql.org/docs/current/routine-vacuuming.html#:~:text=There%20are%20two%20variants%20of,parallel%20with%20production%20database%20operations.

Let us know if this helps or you have further queries. Thanks

Answer 2

"Vacuum" is a routine process that reclaims storage occupied by dead rows and ensures that the space can be reused and it doesn't apply locks on tables which means it's a lightweight operation if you compare it to "Full Vacuum".

On the other hand, "Full Vacuum" is a more intensive operation that reclaims all storage occupied by dead rows, including the space at the end of the table. It will lock the tables of course and that is why it's expensive on the resource-level. As for "Auto Vacuum" you need to know that it is enabled by default and you will not be able to schedule it manually. However, you can control the parameters that triggers like "autovacuum_vacuum_scale_factor" and "autovacuum_analyze_scale_factor" parameters.

Also you can manually trigger an ANALYZE operation on a table to update statistics because updating statistics can influence the auto-vacuum process.