Adding index on ACT_HI_PROCINST table

[andreea_bogdan]

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:

1. Why isn't there an index already on the column? Is there a technical limitation on this?
2. Do you see any problems adding this index? 
3. Can we expect that Activiti will add an index on this table in the next versions?

Thanx a lot.

Andreea

[gdharley]

Hi Andreaa,

Go ahead and add the index, it will cause no problems at all.

In fact, for environments that retain a large history (such as yours), it is typical that you would add this index.

As to why it isn't there by default, generally you would not want to maintain millions of rows of history data. More often than not you would move it into an archive store.

Regards,

Greg

‌

[cjose]

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.

Though this post is a bit old, it is still very relevant when building/designing business processes - http://airquill.io/2014/06/29/Storing-data-in-processes.htmlhttp://airquill.io/2014/06/29/Storing-data-in-processes.html

Regards,

Ciju