We are currently experiencing a major issue regarding the ACT_HI_PROCINST historization table from Activiti.
The table has ~15 mio rows. We are querying the table in one of our activiti processes and since the table is huge, the time to query is really long.
We will be cleaning up the table to get rid of the very old data, but apart from that we would like to add an index on the PROC_DEF_ID_ column from the table. This improves the query times dramatically!
Three questions arise:
Why isn't there an index already on the column? Is there a technical limitation on this?
Do you see any problems adding this index?
Can we expect that Activiti will add an index on this table in the next versions?
Agree with what Greg mentioned above. If historical information is required for other applications/processes, it is best to be stored externally in a business data store OR internally in the same database but custom tables (See Activiti User Guide for more details on this option) which are optimized for your queries.