implement vaccum clean

[Ludee]

We have to clean the oedb regularly and/or before we start a new run.

VACUUM PostgreSQL documentation

I will implement this in the test script that will start the eGoDP #136

[ulfmueller]

yeah Mrs. Vacuumfire! I am just performing such a vacuum by
vacuum;
on the oedb.
Did you use any further option which makes sense?
Just talked to @MGlauer . He said now there is 1 TB available again. Maybe it's due to that vacuum run I am performing...

[Ludee]

The VACUUM ANALYZE should be performed more often while the VACUUM FULL ANALYZE only from time to time.

Plain VACUUM (without FULL) simply reclaims space and makes it available for re-use. This form of the command can operate in parallel with normal reading and writing of the table, as an exclusive lock is not obtained. However, extra space is not returned to the operating system (in most cases); it's just kept available for re-use within the same table.
VACUUM FULL rewrites the entire contents of the table into a new disk file with no extra space, allowing unused space to be returned to the operating system. This form is much slower and requires an exclusive lock on each table while it is being processed.

To vacuum a table, one must ordinarily be the table's owner or a superuser. However, database owners are allowed to vacuum all tables in their databases, except shared catalogs. (The restriction for shared catalogs means that a true database-wide VACUUM can only be performed by a superuser.) VACUUM will skip over any tables that the calling user does not have permission to vacuum.

We recommend that active production databases be vacuumed frequently (at least nightly), in order to remove dead rows. After adding or deleting a large number of rows, it might be a good idea to issue a VACUUM ANALYZE command for the affected table. This will update the system catalogs with the results of all recent changes, and allow the PostgreSQL query planner to make better choices in planning queries.

The FULL option is not recommended for routine use, but might be useful in special cases. An example is when you have deleted or updated most of the rows in a table and would like the table to physically shrink to occupy less disk space and allow faster table scans. VACUUM FULL will usually shrink the table more than a plain VACUUM would.

VACUUM causes a substantial increase in I/O traffic, which might cause poor performance for other active sessions.

PostgreSQL includes an "autovacuum" facility which can automate routine vacuum maintenance.

Here is a draft for a plan:

• VACUUM ANALYZE oedb every night at 03:00 with autovacuum
• VACUUM FULL ANALYZE oedb before and after each eGoDP run

[wolfbunke]

sounds good. @MGlauer what you you think?

[Ludee]

autovacuum has to be set by a superuser.

change parameters in postgresql.conf
see: https://www.techonthenet.com/postgresql/autovacuum.php

[Ludee]

Full vacuum is included but not tested yet.
autovacuum must be included by our admins @gnn @MGlauer