1. 'Getting stuck' can mean different things. In case of an automatically executed task (eg. something needs to be done after 2 hours or a service-call that needs to run asynchronously), a job is retried 3 times by default in case of an exception when executing. After that, the process will be "stuck" there, but is in a consistent state in the DB. You can manually retry the job using the API or set the retry-count to any number in order to have the job-executor run the job again, the next time a slot is available.
If you're referring to a user task getting stuck, there is no way of restarting the process or moving to a previous task, by default. You can always build in such loopback in your BPMN 2.0 diagram. You can, for example, add a boundary signal/error/message event on the user-task and when signalled/messaged, move the process flow back to the start using a sequence-flow. This is the way you handle such things in BPMN 2.0 I'm afraid.
2. Completed workflows and related entities are stored in separate tables (starting with ACT_HI_*), if history is turned on. Depending on the history level setting, more entities will be stored in history (eg. variable updates). Once a workflow is completed, there is no trace left in the runtime tables - not affecting runtime performance. No foreign keys are present between runtime and history tables nor are there foreign keys between different history tables. It's perfectly possible to delete certain or all entities from the history, after archiving them anywhere you want.
3. We don't use many DB-specific SQL-statements, most of the SQL is shared among all DB vendors. No special requirements are needed from the DB, so it comes down to what system is best for your environment. We always use MySQL or Postgres, but that's just us
If you're referring to a user task getting stuck, there is no way of restarting the process or moving to a previous task, by default. You can always build in such loopback in your BPMN 2.0 diagram. You can, for example, add a boundary signal/error/message event on the user-task and when signalled/messaged, move the process flow back to the start using a sequence-flow. This is the way you handle such things in BPMN 2.0 I'm afraid.
2. Completed workflows and related entities are stored in separate tables (starting with ACT_HI_*), if history is turned on. Depending on the history level setting, more entities will be stored in history (eg. variable updates). Once a workflow is completed, there is no trace left in the runtime tables - not affecting runtime performance. No foreign keys are present between runtime and history tables nor are there foreign keys between different history tables. It's perfectly possible to delete certain or all entities from the history, after archiving them anywhere you want.
3. We don't use many DB-specific SQL-statements, most of the SQL is shared among all DB vendors. No special requirements are needed from the DB, so it comes down to what system is best for your environment. We always use MySQL or Postgres, but that's just us
