“Why do I have to update tens of thousands of rows every time, when all I want is to reorder tasks a little?” — Are you tired of this kind of database design?
Many developers initially choose the simple method of assigning sequence numbers (1,2,3…) to each item. However, as the system grows, this approach runs into serious problems. In this article, we’ll look at those limitations and introduce LexoRank, the solution used in Atlassian’s Jira.
💡 Who benefits from this?
- Developers managing ordering in a database
- People building their own task management or backlog systems
- Anyone who has suffered through the “renumbering hell” of order management
😵 Limitations of the sequence number approach
1. The renumbering nightmare
Every time you insert a new element in the middle of the list, you have to update all subsequent elements.
Example:
[1: Requirements] [2: Design] [3: Implementation] [4: Testing]
Insert “Review” between Design and Implementation…
[1: Requirements] [2: Design] [3: Review] [4: Implementation] [5: Testing]
All items after Implementation must be updated. With tens of thousands of rows, this becomes extremely costly.
2. Performance degradation
In lists with thousands or tens of thousands of items, running hundreds or thousands of UPDATEs each time quickly overwhelms database performance. For systems like task management or backlogs, where reordering is frequent, this is fatal.
3. Concurrency conflicts
When multiple users reorder simultaneously, the sequence can break. If you tighten locks, scalability suffers; loosen them, and consistency suffers. Either way, you lose.
4. Lack of flexibility
A simple operation like “insert between item 2 and 3” can’t be expressed with integers. Using decimals (e.g., 2.5, 2.25, 2.125…) works temporarily, but precision quickly breaks down.
🚧 What happens as a result
- Data updates become too slow
- User experience becomes unstable
- Database design grows more complex and harder to maintain
The sequence number approach feels intuitive, but it doesn’t scale to large datasets or frequent reordering.
✨ The savior: LexoRank
Enter LexoRank, the system used in Atlassian Jira.
Jira uses LexoRank to solve exactly these problems. The name “LexoRank” comes from two parts:
- Lexo — short for lexicographical, meaning dictionary/alphabetical ordering
- Rank — the order or ranking of items
What is LexoRank?
In LexoRank, each item is assigned a rank value represented as an alphanumeric string. When the order of items changes, the rank value is updated to be larger than the previous item and smaller than the next one.
Example with real data
Suppose you have:
[Task A: rank=aaa]
[Task B: rank=ccc]
If you want to insert “Task X” between them, the sequence number approach would require mass updates. With LexoRank, you just compute a middle value:
[Task A: rank=aaa]
[Task X: rank=bbb]
[Task B: rank=ccc]
No need to update everything else — only the new item is added.
Rebalancing example
However, if you keep inserting — “aaa”, “aab”, “aac”… — eventually you run out of space for middle values. At that point, rebalancing occurs.
Example:
[Task A: rank=aaa]
[Task B: rank=aab]
[Task C: rank=aac]
[Task D: rank=aad]
...
When all values starting with “a” are filled, LexoRank reorganizes everything to create wider gaps.
After rebalancing:
[Task A: rank=aaa]
[Task B: rank=gaa]
[Task C: rank=maa]
[Task D: rank=taa]
This restores plenty of space for future insertions and keeps ordering stable.
Conclusion
If you’re stuck thinking, “I just want to reorder a little, but every time I have to update tens of thousands of rows…”, it’s time to adopt the LexoRank approach. It frees you from renumbering hell and gives you scalable, reliable order management.
Top comments (0)