How to resolve issues with the growing size of public.artstatemap Arsenale app table in the Jira database

This article explains how to resolve issues with the growing size of the table public.artstatemap in the Jira database.

The size of the Arsenale Dataplane app table “artstatemap” is largely a function of:

• The number of non-archived Jira issues/projects

• The number of custom fields

• The longevity of the Jira instance

 Instructions

1. Archive as many older Jira issues/projects as possible and reduce the number of custom fields in the Jira instance. As more Jira issues/projects are archived, you need to rebuild the Dataplane index in order to remove those archived issues/projects from the index.

2. Use this database query to identify Jira issues with bad change log dates (where the timestamp on field value changes is earlier than the issue’s creation date). This query is written for PostgreSQL.

   select concat(p.pkey, concat('-', i.issuenum)) as "Issue", i.created as "Created", author.lower_user_name as "Author", cg.created as "Change date", ci.field, ci.oldvalue, ci.oldstring, ci.newvalue, ci.newstring from changeitem ci join changegroup cg on cg.id = ci.groupid join jiraissue i on i.id = cg.issueid join project p on p.id = i.project left join app_user a_author on a_author.user_key = cg.author left join cwd_user author on author.lower_user_name = a_author.lower_user_name where cg.created < i.created order by cg.created desc;

3. Use this JQL search in Jira to identify issues with bad work log dates (where the timestamp of logged work is earlier than the issue’s creation date). Replace “2015-01-01” with the earliest date you expect issues to be created in Jira.

   worklogDate < "2015-01-01"