I was checking the flowable java API for a possibilty to do mass deletion of process instances. Our goal is to clean up all running and historic instances of a specific process definition. There are API on the RuntimeService and HistoryService for deleting single instances. However, this does not seem to scale properly if you have thousands of instances in the system as it would create single sql statements only.
Deleting process instances involves, deleting sub processes, tasks, scoped variables, identity links and more. Definitely the hierarchical structures that could be there for a process instances make it impossible to delete process instances with a simple sql query. So for running instances you would need to do it one by one.
For historic process instances that are completed it’s easier to delete all historical data, because we don’t have foreign keys etc in these historic tables. But still there’s the hierarchical structures of sub processes and scoped variables etc, that make it necessary to delete data process by process there as well.