How can i remove items from the changeitem table.

[BroadcomS]

My changeitem table now consumes over 50 percent of my database and it is growing, currently it is 19g on a 30g database with 500 issues. Most of the entries are due builds and interaction with SCM systems. What can i do

1) remove items periodically from change table.

2) limit the number of changeitems kept per issue.

[Nic Brough -Adaptavist- · Accepted solution]

Removing the data destroys the information about changes, so you need to think very carefully before you do this.

The only way to do it without coding is to use SQL directly to kill the lines you decide you don't want. As you specify builds and SCM interactions, I'd attack them first. Identify the patterns, work out a decent rule for them, and remove the lines in SQL. However, you will also need to remove lines from changeGROUP for consistency - removing any changegroup lines that no longer contain any changeitems.

When you run the SQL for the two tables, you absolutely MUST backup the database, have Jira offline, and reindex as soon as you restart Jira.

As for point 2 - you can't. There's no limitations in Jira, it just keeps tracking changes.

[BroadcomS]

Thanks, you answered my question.

[Serhii Riabovil]

you will also need to remove lines from changeGROUP for consistency - removing any changegroup lines that no longer contain any changeitems

those who interested, PostgreSQL snippet to delete change groups that don't have change items

-- WARNING! DO NOT APPLY FOR PROD INSTANCE!

DELETEFROM changegroup
WHERE ID = any (
  ARRAY(
    SELECT G.id
    FROM changegroup G
    LEFT JOIN changeitem I ON I.groupid = G.id
    WHERE I.groupid IS NULL
  )
);

[Nic Brough -Adaptavist-]

DO NOT DO THAT.

There are items in changegroup that have no items for very good reasons.  You are damaging your data.

Please, restore your data from the backup you took before doing this and don't ever do it again.

[Serhii Riabovil]

There are items in changegroup that have no items for very good reasons. 

Nic Brough, any example of such entries? Checked on a large enough prod JIRA instance and didn't find any Change groups without Change items.

[Nic Brough -Adaptavist-]

Yes.

Why are you removing them?

[Serhii Riabovil]

Why are you removing them?

In my case it was for partly anonymized staging (copy of prod) instance.

But generally I added the snippet for those who trying to do this:

you will also need to remove lines from changeGROUP for consistency - removing any changegroup lines that no longer contain any changeitems

[Nic Brough -Adaptavist-]

Ok, you're going about "anonymising" it in a broken way.  You should never delete this stuff, just change it so it's not identifiable.

Please, step away from the database, you could be doing all sorts of damage by doing things you don't understand.

For anonymisation, do it the correct way - see https://confluence.atlassian.com/jira/anonymising-jira-data-139008.html