Size growth of the asd_event_processing_data table in vRealize Orchestrator

Products

• VMware Aria Suite

Issue/Introduction

Resolve continuous growth of asd_event_processing_data table.

Symptoms:
The vPostgreSQL table asd_event_processing_data continues to grow and the data is never purged.

Environment

VMware vRealize Automation 7.x
VMware vRealize Automation 7.2.x
VMware vRealize Automation 7.6.x

Cause

• The asd_event_processing_data table is referenced by asd_workflowrunrequest.
• The asd_workflowrunrequest table is purged regularly which can be configured in the /etc/vcac/vcac.properties file by changing the default values of the following properties:​​​​​

com.vmware.vcac.designer.service.purge.run.requests.older.than.days:30
com.vmware.vcac.designer.service.purge.run.requests.cron:0 0 3 * * *

• However, the referenced table asd_event_processing_data is not purged.

Resolution

1. Clean up the orphaned records in asd_event_processing_data:

DELETE FROM asd_event_processing_data epd WHERE epd.id NOT IN
(SELECT CAST (eventprocessingdataid AS UUID) FROM asd_workflowrunrequest req where eventprocessingdataid is not null);

2. Reclaim the space used by the table by performing a vacuum:

vacuum (VERBOSE, FULL);

Additional Information

Impact/Risks:
This fix contains manual database edits.  Ensure there is a valid rollback mechanism whether that be snapshots or a managed backup solution.

It is recommended to perform this activity during a maintenance window when system access is limited as the vacuum operation can take some time depending on the space purged.