Activiti DB cleanup

Question

I've noticed that the activiti server DB is getting quite big and is using a lot of disk-space. I are working with the rest API. I don't really use the history so I don't mind deleting it but I'm having trouble figuring out how to identify all the finished jobs/tasks/processes and calling relevant delete API. If this can be done via SQL commands directly on the DB that would be fine as well.

Answer 1

Likely the tables that are growing are the history tables (ACT_HI_*) which will grow depending on the history level you have set in your Activiti engine.

That said, archiving of the history tables is very easy as there are no foreign keys.

First, retrieve all closed or terminated process instances using something like:

select proc_inst_id_ from act_hi_procinst where end_act_id_ IS NOT NULL or 
delete_reason_ IS NOT NULL

Then use this list to delete associated rows from:

ACT_HI_ACTINST
ACT_HI_ATTACHMENT
ACT_HI_COMMENT
ACT_HI_DETAIL
ACT_HI_IDENTITYLINK
ACT_HI_TASKINST
ACT_HI_VARINST

Finally, you can delete the rows from ACT_HI_PROCINST

Hope this helps, Greg

Answer 2

Not bad to use Pojo's, just have to be aware that all pojo's for both active running and closed instances are stored in the same table, so it cab get really big. And, since Pojo's are not searchable, their usefulness after a process has ended is marginal at best.

A couple of things that may help you going forward: 1. Null out any process variables after you have finished with them. 2. Don't us the process engine as a system of record, externalize all real data and retrieve it as needed. 3. This goes along with 2. but use local scoped variables where possible, this will not be saved. 4. Consider carefully your pocess history log settings (full logs a lot of data).

We have clients who's database grows exponentially because they fell for the usual rookie mistakes and used process vars for everything, never cleaned up and never considered what happens in the future. Good luck and let us know if we can help further.