Issue Deleting Large Number of Process Instances in Camunda BPM with PostgreSQL

Camunda 7 Topics
1

Hello, I’m experiencing an issue when trying to delete a large number of process instances in Camunda BPM, backed by a PostgreSQL database. My goal is to delete all process instances corresponding to a specific version identified by the processDefinitionKey. My controller method works fine for a small number of instances (e.g., hundreds), but fails when the count exceeds a large number, such as 100,000+. When attempting to delete such large volumes, I encounter an exception related to the database connection being broken.

Here’s the error message I’m receiving:

HikariPool-1 - Connection org.postgresql.jdbc.PgConnection@... marked as broken because of SQLSTATE(08006), ErrorCode(0)
An I/O error occurred while sending to the backend.
ENGINE-16004 Exception while closing command context: An exception occurred in the persistence layer.

Below is the code snippet of my delete operation:

@DeleteMapping("/rest-engine/process-instances/delete/{processDefinitionId}")
    public ResponseEntity<Void> deleteProcessInstancesByDefinitionId(@PathVariable String processDefinitionId) {
        log.info("starting process instances deletion of definitionId:{}", processDefinitionId);
        List<String> processInstances = runtimeService.createProcessInstanceQuery()
                .processDefinitionId(processDefinitionId)
                .list()
                .parallelStream()
                .map(Execution::getProcessInstanceId)
                .toList();

        if (processInstances.isEmpty()) {
            log.error("no process instances found by definitionId:{}", processDefinitionId);
            return ResponseEntity.notFound().build();
        }
        runtimeService.deleteProcessInstancesAsync(processInstances, "Deleted by process definition id");

        log.info("successfully deleted process instances of  definitionId:{}   count:{}", processDefinitionId,processInstances.size());
        return ResponseEntity.noContent().build();
    }

Has anyone faced a similar issue or can provide insight into what might be causing this problem and how to resolve it? Any suggestions on how to better handle the deletion of a large number of instances to avoid such errors would be greatly appreciated.