Database Clean Up

To optimize the performance of the Polyspace^® Access database, perform regular database clean up operations such as vacuuming and the deletion of old or obsolete projects. It is recommended that you back up your database before you perform a clean up operation. See Create Database Backup.

Perform Database Vacuuming

When a row is updated or deleted in a database table, it is not physically removed from the table because other database transactions might still use the old version of the row. To reclaim the disk space of old rows that are no longer used by any database transaction, use the PostgreSQL vacuumdb command. Vacuuming the database regularly prevents your database disk space from growing too large or fragmented.

Before you perform a vacuum operation, ensure that no users are connected to Polyspace Access then stop the Polyspace Access Web Server and Polyspace Access ETL services. To stop the services, from a terminal on the server hosting these services, use this command and entering:

docker stop polyspace-access-etl-main polyspace-access-web-server-main

To vacuum your Polyspace Access database, open a terminal on the server hosting your database and enter:

docker exec polyspace-access-db-main vacuumdb -U postgres prs_data

You can also run the vacuumdb command and use the --analyze option to update the PostgreSQL server statistics. Open a terminal on the server hosting your database and enter:

docker exec polyspace-access-db-main vacuumdb -U postgres --analyze prs_data

Accurate server statistics help prevent degradations in the performance of the database.

To minimize the size of your database tables and return unused space to the operating system, run vacuumdb by using the --full option. Open a terminal on the server hosting your database and enter:

docker exec polyspace-access-db-main vacuumdb -U postgres --full prs_data

This operation can take a long time and writes a new version of the table that does not have any empty spaces. When you perform a full vacuum, no other database process can run in parallel. The database is not accessible during a full vacuum.

Establish a policy for how often you want to perform a regular and a full vacuum. For instance perform a regular vacuum weekly.

After you complete the vacuum operation, restart the Polyspace Access Web Server and Polyspace Access ETL services. Use this command:

docker start polyspace-access-etl-main polyspace-access-web-server-main

After you restart the Polyspace Access Web Server service, it might take a few moments before you can open Polyspace Access in your web browser.

Delete Outdated Projects

When users delete projects from the Project Explorer of the Polyspace Access web interface, the projects move to the ProjectsWaitingForDeletion folder. The projects, including all the runs that you uploaded to the projects, remain in the database until you explicitly delete them.

The ProjectsWaitingForDeletion folder is visible only to Polyspace Access users who have the role of Administrator. Even users who have the Administrator role cannot delete projects from the Polyspace Access interface.

Define a policy for how often you delete older projects or project runs from the database. Automate this operation by using a script. You can delete older results even if these are not in the ProjectsWaitingForDeletion folder.

To remove old project runs or entire projects from your database, write a command in a text file that you save as a .pscauto file. Run the command by copying the .pscauto file to the Storage directory of the Polyspace Access ETL service. Only a user who has write privileges on the Storage directory can perform this operation.

To delete project runs from a project but not the project itself, use the clean_project command. Specify the project path with one of these command parameters.
- clean_project projectPath DATE YYYY-MM-DD
  The command deletes project runs that were uploaded before YYYY-MM-DD.
- clean_project projectPath MAXRUNS NNN
  NNN is an integer. The command keeps the NNN most recent runs. To delete all the project runs, use MAXRUNS 0.
- clean_project projectPath AGE DDD
  DDD is the number of days. To remove recently uploaded results, use this option. The command deletes project runs that are older than DDD days.
To completely delete a project from the Polyspace Access database, use the delete_project command and specify the project path:
```
delete_project projectPath
```

projectPath is the full project path in the Polyspace Access Project Explorer. To get a project path, use the context menu in the Project Explorer or, at the command line, use the polyspace-access binary with the -list-project flag. For more information, see polyspace-access -h -list-project.

If the path contains whitespace characters, enclose the project path in double quotes. If you use echo to write the commands to a file, you must also use a "\" character to escape whitespace characters in the project path.

For example, to perform a one-time cleanup of project public/Bug_Finder_Example (Bug Finder) and remove all results uploaded before a specific date:

Open a text editor, paste this command, then save the file as a .pscauto file, for instance cleanup.pscauto.
```
clean_project "public/Bug_Finder_Example (Bug Finder)" DATE 2019-09-01
```
Copy the file to the Storage directory of the Polyspace Access ETL service:
```
cp cleanup.pscauto /local/Access_install_dir/polyspace/storage
```

All analysis runs uploaded to project public/Bug_Finder_Example (Bug Finder) prior to September 1, 2019 are deleted from the database.

You can also perform an automatic cleanup on a specific project every time you upload a run to that project. To keep only the 10 most recent runs every time you upload a result to public/Bug_Finder_Example (Bug Finder), save these commands to your .pscauto file.

assign_to_project "public/Bug_Finder_Example (Bug Finder)" AFTER_STATISTICS myScript
clean_project "public/Bug_Finder_Example (Bug Finder)" MAXRUNS 10

The commands that you enter after the assign_to_project line are stored internally in a script myScript that is assigned to the project public/Bug_Finder_Example (Bug Finder). Use distinct names for the internal script that you assign to different projects. You specify the internal script name with the last parameter of the assign_to_project command. After you copy the file to the Storage directory of the Polyspace Access ETL service, the automatic cleanup starts.

To turn off the automatic cleanup, save this command to a .pscauto file and copy that file to the Storage directory:

unassign_to_project "public/Bug_Finder_Example (Bug Finder)" myScript

You must provide the name of the internal script that you assigned to the project by using the assign_to_project command. Make sure that you keep a record of the internal script names and the projects to which they are assigned.

Caution

You cannot recover the data that you delete by using the .pscauto script unless you have a backup copy of the data.

Documentation