How to estimate the size of projects in Jira
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
As an admin you may want to know the size of your projects, either by the number of issues on it, how many custom fields values are associated to issues of each project or the number/size of attachments for each project.
This may help while assessing migrations, planning the merging of instances and giving a better understanding of the instance size and usage.
This article aims at providing a solution to fetch this information directly from the database.
Solution
These queries were tested in PostgreSQL - if you're running into syntax issues for other database products you may need to modify the syntax accordingly.
Issue count per project
This is the most common method to estimate the size of each project. To do that, you may run this query to get issue count:
1
2
select count(*), p.pname, p.pkey from jiraissue i, project p
where i.project = p.id group by p.pname, p.pkey order by p.pname;If you already have the project keys that you'd like to check you may filter it on the query by adding the keys of your choice:
1
2
select count(*), p.pname, p.pkey from jiraissue i, project p
where i.project = p.id and p.pkey in ('PKEY1','PKEY2','PKEY3','PKEY4') group by p.pname, p.pkey order by p.pname;Number of custom field values associated to issues per project
An entry is created on customfieldvalue table whenever a custom field value is given for an issue. This query will give you how many rows on this table are related to each project:
1
2
3
select count(*), p.pname, p.pkey
from jiraissue i, project p, customfieldvalue cfv
where i.project = p.id and i.id = cfv.issue group by p.pname, p.pkey order by p.pname;For reference, different project may have a different average data consumption in database. For example, if one project has a lot more customfields configured within its scope it will have a larger footprint on the database. Another point would be attachment where the number and size of them could differ substantially.
Count and average size and total size of attachments per project
Attachment information is stored on fileattachment table and since the filesize is given in bytes we divide to get the average and total values in Mb:
1
2
3
4
5
6
select count(*),
avg(fa.filesize/1000000) as avg_filesize_Mb,
sum(fa.filesize/1000000) as total_filesize_Mb,
p.pname, p.pkey
from project p, jiraissue j, fileattachment fa
where p.id=j.project and fa.issueid=j.id group by p.pkey,p.pname;Was this helpful?