Reordering a list without renumbering it

You've built a kanban board. A user drags a card from the bottom of a column to the second position. It takes them a quarter of a second and no thought at all.

Now: what does your UPDATE statement look like?

This turns out to be one of those problems that seems trivial until you actually write it down, and then quietly turns into a small research project. This post is about why, and about the approach that solves it - how it works underneath, and the decisions that shaped a library I built around it.

If you just want the API, the README covers that. This is the why.

The problem, stated properly

Say each task has an integer position:

<--TABLE-1-->

A user drags Review to second place. To keep position meaningful you now write:

<--CODE-1-->

Three writes for one drag. And that number isn't fixed - it's proportional to how many items sit below the drop point. On a 500-card backlog, dragging something to the top rewrites 500 rows. Inside a transaction. While other users are dragging their own cards around the same column.

The bug this produces in practice isn't a crash. It's a board that feels sluggish, occasionally deadlocks under concurrent edits, and every so often loses an ordering because two transactions interleaved. The fix is not a better index. The fix is to stop writing rows that didn't move.

What we actually want: dragging one card writes exactly one row. The card that moved. Nothing else.

The obvious fixes, and where they break

Leave gaps

Number things 1000, 2000, 3000 instead of 1, 2, 3. To insert between the first two, use 1500. One write.

This works beautifully - about ten times. Then you insert between 1000 and 1001 and you're out of room, and you're renumbering the whole column again. You've bought yourself a delay, not a solution. And the delay is shortest exactly where users insert most: the top of the list.

Use floats

Between 1.0 and 2.0 sits 1.5. Between 1.0 and 1.5 sits 1.25. Halving never runs out, mathematically.

Except a double has 52 bits of mantissa, so it runs out after roughly 50 halving. That sounds like plenty until you realize "always drop it at the top" is a completely normal user behavior, and 50 of those is one afternoon. When it breaks, it breaks silently: two rows end up with the same float and the ordering becomes whatever your database's tiebreaker feels like. You get a bug report saying "the cards keep swapping" and no idea why.

Both approaches fail the same way. The space between two numbers is finite, and you can't add more of it.

The idea: make the rank a string

Here's the shift. Take these two strings: