DB Size of history tables


#1

We have at the moment about 350k running process instances and they use a lot of space in the DB history tables act_hi_actinst and act_hi_job_log (about 72 GB). And it is growing fast …

I will change the history configuration from HISTORY_FULL to HISTORY_ACTIVITY, but I’m not sure if it is going to reduce the size.

I have seen both table have many db index and these indexes are using most of the space.Is it possible to delete some of the index? I’m really not sure if they are all useful.
For example:
act_idx_hi_act_inst_end: Index on end time?
act_idx_hi_act_inst_rm_time: Index on removal time?
act_idx_hi_act_inst_start : Index on start time?
act_idx_hi_act_inst_stats : Again end time …
act_idx_hi_act_inst_tenant_id: We have only one tenant…
act_idx_hi_ai_pdefid_end_time: Again end time …
act_idx_hi_job_log_rm_time: Index on removal time?
act_idx_hi_job_log_tenant_id We have only one tenant…
job_exception_stack_id_: Index on the exception?

Additionally some indexes could be dropped by improving how selections in the applications are done.

Has someone already tried to optimize the indexes?


#2

Hi @bmaehr,

All of the indexes are created intentionally (mostly but not only to speed up different queries) so removing an index could lead to bad performance for example.
I would prefer to not elaborate on each one of them, but rather suggest you to look the other way around:
Do you need all of the historic data? If the volume of the data is less the indexes won’t be problematic.
You can check the History cleanup feature to reduce the size of the historic tables. [[1]]

[[1]]: https://docs.camunda.org/manual/latest/user-guide/process-engine/history/#history-cleanup

Best regards,
Yana


#3

@Yana Thanks for your suggestion.

Cleanup is already set up, the history is only from the running instances.

Is it save to delete arbitrary rows from the history tables without crashing the engine (just missing some history information in cockpit then)?