The Case for Fractional Indexing in Drag-and-Drop UIs

Manual reordering is a staple of modern UIs—kanban boards, task lists, playlist editors. But implementing it efficiently is tricky. The naive approach (integer position field) requires reindexing every row below the dropped item. Insert at position 3 in a 1000-item list? Enjoy updating 997 rows. This is where fractional indexing shines.

The idea is simple: instead of integer positions (1, 2, 3, ...), use floating-point numbers and always insert at the midpoint between two neighbors. Drop an item between positions 2.0 and 3.0? Assign it 2.5. Drop between 2.5 and 3.0? Assign it 2.75. With DOUBLE PRECISION (IEEE 754 double, ~15 decimal digits of precision), you can perform trillions of bisections before running out of space.

In Velocity, we use a sort_order DOUBLE PRECISION column for all reorderable entities: issues in a backlog, cards in a kanban column, milestones in a project. When a user drags an item, we compute newSortOrder = (prevSortOrder + nextSortOrder) / 2 and update a single row. No bulk updates, no deadlocks, no reindexing. The database sorts by sort_order and the UI reflects the new position instantly.

The midpoint calculation handles edge cases gracefully. Dropping at the start? Use firstSortOrder / 2. Dropping at the end? Use lastSortOrder + 1. Dropping in an empty list? Use 1.0. These rules ensure sort_order always has a valid value and maintains total ordering.

One caveat: floating-point precision is finite. After ~50 consecutive insertions at the same position (e.g., always dropping between 1.0 and 2.0), you'll exhaust precision and get collisions. The solution is periodic rebalancing: if abs(sortOrder1 - sortOrder2) < 1e-10, reindex the entire list with integer spacing (1.0, 2.0, 3.0, ...). In practice, this happens rarely—users don't repeatedly drag items to the exact same spot.

We integrated fractional indexing with @dnd-kit, our drag-and-drop library. On drop, the UI computes the new sort_order, fires a GraphQL mutation, and optimistically updates the cache. The server validates the mutation, writes to the database, and returns the updated item. If the mutation fails (network error, validation failure), the UI reverts to the original order.

Fractional indexing is one of those ideas that feels like magic once you understand it. It's simple, efficient, and scales to massive lists. If you're building a drag-and-drop UI, skip the integer position headache and go fractional from the start. Your database—and your users—will thank you.