[Postgres] A Tool Called Vacuum

The Vacuum is a database maintenance utility. Vacuum exists because of the unique transaction control that postgreSQL has: the MVCC. The two main operations performed by this tool are:

• Recover disk space due to updated or deleted records;
• Update the statistics used by the optimizer to determine the most efficient way to run a query in PostgreSQL.

When we perform an UPDATE the record itself does not change; A new clone record is added. Thus, PostgreSQL marks the “original” (old) record as expired and makes the change to the clone. If it is a DELETE, PostgreSQL only marks the deleted record as expired. That’s why it’s normal to see the database grow so huge in PostgreSQL and surveys start to get slower and slower. This slowness is most visible especially if the query is not indexed and sequential access is used, which causes PostgreSQL to check ALL records (including expired ones) to find the data. In the case of the Index, it is responsible for locating the data.

When we run the Vacuum, it physically removes the expired die, takes the last two records from the table and plays instead of the “hole.”

VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table]
VACUUM [ FULL ] [ FREEZE ]
[ VERBOSE ] ANALYZE [ table[ (column[, …] )] ]

Example: VACUUM VERBOSE ANALYZE table_test;

It is very important to use the Vacuum daily, so do not forget this utility because it saves us a headache
One important fact: Vaccum FULL uses exclusive table access, ie it can lock the table if it needs to be executed with connected operators. Run the vacuum without the FULL parameter.