What is the difference between VACUUM, VACUUM FULL, and autovacuum?

In PostgreSQL:

• VACUUM — is a command to clean up "dead" rows left after updates and deletes. It frees space for reuse but does not reduce the physical size of the table. VACUUM helps maintain performance and prevents transaction ID wraparound.

• VACUUM FULL — is a more intensive operation that not only cleans dead rows but also compresses the table, reducing its physical size on disk. It locks the table for writes, which can affect availability.

• autovacuum — is a background process that automatically runs VACUUM and ANALYZE as needed to keep the database healthy without administrator intervention.

Example:

-- Regular vacuum
VACUUM my_table;

-- Full vacuum with compression
VACUUM FULL my_table;

In most cases, autovacuum is sufficient, but in cases of large changes or performance issues, manual VACUUM or VACUUM FULL may be required.