(Best Practice) Using VACUUM with PostgreSQL for Talend Administration Center users

When using Talend Administration Center to retrieve, schedule, or execute Jobs, many update/delete database operations are performed, which may result in performance slowdown if you are using PostgreSQL.

Indeed, it is recommended to execute the VACUUM command with PostgreSQL, as items that are deleted or obsoleted by an update are not physically removed from their table.

The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse. However, it will not return the space to the operating system, except in the special case where one or more pages at the end of a table become entirely free and an exclusive table lock can be easily obtained. In contrast, VACUUM FULL actively compacts tables by writing a complete new version of the table file with no dead space. This minimizes the size of the table, but can take a long time. It also requires extra disk space for the new copy of the table, until the operation completes. It is recommended to run VACUUM FULL quarterly.

For more information on the VACUUM command, see the PostgreSQL documentation.

For more information on how to set up automatic vacuuming (which is a process launched at regular intervals by the PostgreSQL server to execute VACUUM only on the tables that have been updated), see the PostgreSQL documentation.