Read this before adding custom fields to large Jira instances
In the world of Jira, custom fields offer a way to gather specific information, such as URLs, short text, and numbers. However, the abundance of custom fields can potentially hinder Jira’s efficiency.
In this article, I’ll explain the main reasons behind the creation of Atlassian Jira’s Guardrails. These reasons include performance testing triggers and architectural changes. I’ll also clarify why custom field architecture was introduced in Jira. This information will be particularly interesting for Jira Data Center administrators, project managers, application owners, Jira developers, and Jira end users.
Jira conveniently provides standard custom field types like text, drop-downs, checkboxes, and dates to cater to various data tracking needs. However, the challenge arises when an excess of custom fields burdens Jira’s speed. Each added field contributes extra work, gradually slowing down vital processes.
To maintain Jira’s optimal performance, it’s a good practice to routinely clear out unused custom fields. This involves eliminating fields that have outlived their purpose. This process can effectively enhance Jira’s responsiveness and user-friendliness.
Remember, non-standard custom fields might impact performance differently, necessitating a distinct approach for cleanup.
The basics
Custom fields within Jira are typically built upon three core database tables (customfield, customfieldvalue, customfieldoption). These tables serve as repositories for managing and housing the data tied to custom fields. The utilization of these tables varies based on the Jira version and the specific custom field type.
For instance, in more recent configurations, there’s a separation of data for the user picker filter custom field. This means that user picker field data is stored in distinct tables, a design aimed at optimizing Atlassian Jira’s performance. This division of data empowers Jira to efficiently manage user picker fields, particularly in scenarios with a substantial user count.
Being mindful of these foundational tables is crucial when dealing with custom fields. They play a pivotal role in storing and retrieving associated data. Understanding the table structure assists administrators and developers in making informed choices while configuring and overseeing custom fields within Jira.
In essence, this approach refines the EAV (Entity–attribute–value model) data model, fortifying its efficiency.
Practical implementation
With the growth in the count of custom fields within Jira, the necessity for SQL joins to retrieve data also escalates. Fetching an issue accompanied by a single custom field might need a few joins. However, when dealing with many custom fields, say ten or even a hundred, the involvement of numerous joins considerably impairs query performance. Moreover, incorporating diverse value types for custom fields, such as text, number, and date, further complicates the querying process.
It’s important to maintain a careful approach to the number and structure of custom fields to strike the balance between adaptability and Jira’s performance.
Example:
SELECT jp.pkey, ji.issuenum, cf.cfname, cfv.stringvalue, cfv.numbervalue
FROM project jp, jiraissue ji, customfield cf, customfieldvalue cfv
WHERE jp.id = ji.project
AND cfv.issue = ji.id
AND cf.id = cfv.customfield
ORDER BY jp.pkey, ji.issuenum;When you run a query on a Jira instance with 700 custom fields and a total of 1,500 fields, you’ll notice a 3.2 times difference compared to a scenario without them. This test involved a limit of 10,000 with 10 repetitions, using the Data Generator for Jira tool.
Jira improves its speed in clever ways. It uses caching, upgrades the Java part, handles data better, and fine-tunes indexes. There are even special tables for some fields, like the user picker.
Why not just change the way data is managed? Currently, this isn’t possible for self-hosted solutions. This could be a reason to think about shifting to Jira Cloud. There, you get more choices and flexibility for data models and setups.
Retrospective
Around 20 years ago, things were different. For instance, PostgreSQL didn’t have JSON/JSONB support yet. Back then, the Entity-Attribute-Value (EAV) model was commonly used in content management systems (CMS). This let developers build prototypes and minimum viable products (MVPs) before going live. It was a usual way of doing things at the time.
Community opinion on EAV
Additionally, I recommend you read threads related to the EAV on Stack Overflow. One of my favorite answers from there is:
Exploring Custom Fields in Different Systems
Let’s look at another system’s approach. Take the issue-tracking system Redmine, for instance. It follows a similar concept, as outlined here: https://www.redmine.org/projects/redmine/wiki/DatabaseModel. However, this model can encounter issues, like the one explained here: https://en.wikipedia.org/wiki/Sparse_matrix, which can affect even Jira.
For more insight, check out this source: https://inviqa.com/blog/understanding-eav-data-model-and-when-use-it.
In Conclusion
The link between Custom Fields, Flexibility, and Performance is clear. You can tidy things up by identifying unused fields. For example, using a query like this:
select count(*), customfield.id, customfield.cfname, customfield.description
from customfield left join customfieldvalue on customfield.id = customfieldvalue.customfield
where customfieldvalue.stringvalue is null
and customfieldvalue.numbervalue is null
and customfieldvalue.textvalue is null
and customfieldvalue.datevalue is null
group by customfield.id, customfield.cfname, customfield.description;Further insights are available here: https://confluence.atlassian.com/enterprise/managing-custom-fields-in-jira-effectively-945523781.html. This will help you provide clear explanations, manage governance, perform cleanup, and offer recommendations to end users.
P.S. Don’t miss Mike’s presentation on Lucene!
Cheers, Gonchik Tsymzhitov
