Fractional Indexing: The Smart Way to Sort Your Lists

At first, I thought I could just assign each activity a simple integer position: 1, 2, 3, and so on. But the problem shows up as soon as you start dragging cards around. If I move a card into the middle of the list, do I need to renumber everything after it? What happens after multiple reorders? Suddenly, a simple system becomes messy and inefficient.

Renumbering every activity in the database each time a card moves clearly wasn’t going to cut it. I needed something smarter, a way to insert items anywhere without rewriting the whole list.

Potential solutions

I did a little bit of research and came across a few solutions to this problem.

Linked list

One way we could have handled this scenario is with a linked list. Rather than storing positions in our table, we instead store a reference to the ID of the item that comes next. For example, let's say we have 4 activities:

A1 -> A2
A2 -> A3
A3 -> A4
A4 -> null

Great, easy to understand, and for my use-case, this would serve me absolutely fine. But now let's sort these, and move A4 between A1 and A2.

A1 -> A4
A4 -> A2
A2 -> A3
A3 -> null

We’d have to update three different entries in the database - A1, A4, and A3. Every time a card moves, that’s two or three separate updates - two if we're moving to the start or the end of a column, and 3 if we move it between 2 cards.

Gap buffer

Instead of incrementing positions by 1, what if we gave each activity a big “gap” between numbers, like thousands or tens of thousands? This is exactly what Trello does.

For example, when we create four tasks on Trello, the network requests show their positions are assigned in increments of 16,384 (2¹⁴).

T1 -> 140737488355328
T2 -> 140737488371712
T3 -> 140737488388096
T4 -> 140737488404480

When we drag T4 between T1 and T2 - the system calculates the midpoint between the surrounding positions and assigns that as the new position for the moved task. This way, we don’t need to renumber every item in the list.

T1 -> 140737488355328
T4 -> 140737488363520
T2 -> 140737488371712
T3 -> 140737488388096

Effectively, you could keep inserting between two cards up to 16,384 times before you’d need to renumber or refresh the positions. That’s more than sufficient for enterprise-level apps like Trello.

Fractional indexing

While gap buffering works well, I wanted a slightly more flexible approach that didn’t rely on arbitrary large numbers.

Fractional indexing works in a similar way to gap buffering, but instead using floats rather than fixed integers. In my case, I updated my Activity Prisma model to use floats instead of integers for the position.

For example, imagine we start with activities like this:

A1 -> 0
A2 -> 1
A3 -> 2
A4 -> 3

Now, if we drag A3 between A1 and A2, we can calculate its new position like so:

• New activity → (1 + 2) / 2 = 1.5

The list still sorts correctly, and only one record needed updating.

This same logic works no matter where the activity lands. Between 10 and 11? That’s 10.5. Between 1.25 and 1.5? That’s 1.375.

In practice, this means I can reorder items “infinitely” for all practical purposes - the only real limitation is the precision of floating-point numbers, but in most real-world cases (especially for a side project), you’d never hit those limits.

Precision and limits of fractional indexing

When I first started playing with fractional indexing, I wondered: won’t we eventually run out of numbers to squeeze in between two activities? After all, if you keep taking averages, those numbers get closer and closer together.

The truth is: yes, if you only ever halved the same gap over and over, you’d eventually hit floating-point precision limits. A 64-bit float (used by both PostgreSQL’s DOUBLE PRECISION and JavaScript’s Number) gives you around 15-17 decimal digits of accuracy. Starting with positions 1 and 2, that works out to about 53-54 consecutive halvings before the gap becomes too small to represent.

We can create our own example of this, to understand things a bit better:

let a = 1.0;
let b = 2.0;

for (let i = 1; i <= 60; i++) {
  let mid = (a + b) / 2;
  console.log(i + ":", mid);
  b = mid; // keep moving closer to `a`
}

// Result
// ...
// 50: 1.0000000000000009
// 51: 1.0000000000000004
// 52: 1.0000000000000002
// 53: 1
// 54: 1
// ...

But in practice, that worst-case scenario almost never happens. You’re not always halving the same pair of numbers - you’re inserting items across the whole list. That means gaps open up elsewhere, and the system has plenty of room to keep assigning new positions without collisions. For a side project like Trippie, and even for most production-level apps, you’ll never get anywhere near the precision limit.

Making a decision

After ruling out the linked list, I had to choose between gap buffering and fractional indexing, which are conceptually very similar. I settled on fractional indexing because of its simplicity and flexibility. With gap buffering, you need to decide on a step size up front (like Trello’s choice of 16,384), which works perfectly well but feels a bit arbitrary. Fractional indexing, on the other hand, calculates new positions dynamically by averaging neighbouring values, so there’s no need to predefine increments. Each reorder only updates the moved item - no extra database entries touched. For a personal project like Trippie, it’s clean, efficient, and future-proof.

Implementing fractional indexing in Trippie

Once I decided on fractional indexing, implementing it in Trippie was surprisingly straightforward. My Activity model in Prisma now uses a Float type for the position field:

model Activity {
  id       Int     @id @default(autoincrement())
  name     String
  date     DateTime
  position Float
}

Whenever a user drags an activity to a new spot, I calculate its new position based on the activities immediately before and after it:

export function calculateFractionalIndex(
  beforePosition?: number,
  afterPosition?: number,
) {
  // No existing items in the column
  if (beforePosition === undefined && afterPosition === undefined) return 1;
  // First item in the column
  else if (beforePosition === undefined) return afterPosition! - 1;
  // Last item in the column
  else if (afterPosition === undefined) return beforePosition + 1;
  // Between 2 items
  else return (beforePosition + afterPosition) / 2;
}

This function handles every scenario: moving an item to the start, the end, or anywhere in between. Only the moved activity’s position needs to be updated in the database - no renumbering of the entire column required.

On the frontend, the Kanban-style columns simply sort activities by this position field whenever they render. This keeps the UI in sync with the database and ensures smooth, instant drag-and-drop reordering.

Conclusion

Fractional indexing turned out to be an elegant and practical solution for Trippie. It lets me reorder activities freely without touching unrelated records, keeps the code simple, and avoids unnecessary database churn.

While linked lists or gap buffers could also work, fractional indexing struck the right balance of simplicity, precision, and efficiency for a side project. It’s a cool example of how a small tweak to your data model can make a big difference in both performance and developer experience.

For anyone building sortable lists or Kanban-style interfaces, it’s definitely worth considering, especially when you want smooth, instant drag-and-drop without overcomplicating your backend.

If you want to see fractional indexing in action, you can try Trippie yourself and play around with planning your own trips, every card you move uses this same approach to keep things fast and flexible.