Insight as large Asset Management has to have retention policy

 Hello community, 

 

Today, I would like to share a small snippet sql request to cleanup Insight object histories. 

In the future, I will be happy to see the configuration in the Insight app as a retention policy. (Please, Atlassian provide retention policy feature in Insight)

 

We have 2mln+ objects in Insight, not so far, at the moment we do a cleanup and review existing potential weaknesses and improve performance of our Jira Service management DC setup. 

Once our web navigation Insight tab was slow, If we do cleanup of history it starting to be fast then do review of records. 

Every night our system syncs up with SAP, IdP and other external systems, also, event based sync up and updates objects from remote systems. 

Therefore potential weakness is duplicated history, because change log we don’t need to keep longer than 3 months, as change log keeps on master systems. 

Also, the next query helpful to find misconfigs. 

SELECT hist."ID",
       hist."AFFECTED_ATTRIBUTE",
       count(hist."ID")  updates
FROM "AO_8542F1_IFJ_OBJ_HIST" hist
GROUP BY hist."ID", hist."AFFECTED_ATTRIBUTE"
ORDER BY updates DESC;

You can find here the query in git. 

 

As a technique, I just remove via next query, of course, it’s useful. 

SELECT count("ID")
FROM "AO_8542F1_IFJ_OBJ_HIST" hist
WHERE
"CREATED" < NOW() - INTERVAL '3 months' ;

Then if scope is quite ok, you can replace select into delete to delete rows. 

Please, do before on test env, if you don’t understand the purpose and please, do backup.

Detailed info you can find here.

Please, Atlassian provide to us in Insight app the retention policy of history records. It helps to easily work with navigation in the Insight tab. 

Have a good cleanup.

Cheers,

Gonchik Tsymzhitov