How to store a user-reorderable list in SQL: integer positions, fractional keys or a linked list

Store the order in an integer position column with a unique constraint on (list_id, position). When someone drags an item, rewrite the positions between its old and new slot in one transaction. Lists that people order by hand are short (playlists, photo carousels, menu sections, pipeline stages), so a move rewrites a few dozen rows at most, and the database guarantees that no two items share a slot. Switch to a fractional key only when several clients insert into the same list at once or a list runs to thousands of rows. In that case, use a string key with a binary collation, not a float or a fixed-scale decimal. Skip linked lists.

We checked this against the 100 schemas in the drawDB schema gallery. In 39 of them, 75 columns hold an order that a person chose. We left out 11 sequences that machines assign, like retry attempts, revision counters and stack frames. 65 of the 75 are integers. Ten are decimals: nine numeric(30,15) keys in the Trello and Notion schemas, and one decimal(5,2) for Goodreads series numbers. Only 13 of the 75 have a unique constraint on the list and the position. 48 default to 0, and not one of those 48 is unique, so nothing stops two items from claiming the same slot.

How do real schemas store sort order?

Almost always as an integer. Here is what the 75 hand-ordered columns in the gallery look like:

• Names: position 53 times, ordinal 10, display_order 6, sort_order 4 and sequence 2. The name doesn't matter. Pick one and use it everywhere.
• Types: 37 integer, 27 smallint, one tinyint. A list someone drags by hand never needs more than 32,767 slots, so smallint is enough. Use integer if you renumber with gaps.
• Scope: most positions mean something only inside a parent: tracks inside a playlist, items inside a menu section, stages inside a pipeline. The unique constraint has to include that parent column. A unique index on position alone makes every list in the table share one numbering.
• Uniqueness: only 13 of the 75 are enforced. The Spotify and YouTube playlists, the Instagram carousel, recipe steps, CRM and hiring pipeline stages, and feature-flag targeting rules all have a unique index on (parent_id, position). The other 62 rely on the application.

The Spotify schema is the reference shape. playlist_items has its own surrogate id, because the same track can appear twice in a playlist, plus an explicit zero-based position that is unique per playlist:

Media & content

Spotify music streaming database schema

How a music streaming service models its data: artists, albums and tracks, credited roles on a track, ordered playlists with collaborators, saved library, offline downloads and an append-only play log.

How do you move an item with an integer position?

Shift every item between the old slot and the new slot one place toward the gap, and put the moved item in the new slot. That fits in one UPDATE. In PostgreSQL the unique constraint must be DEFERRABLE, because PostgreSQL normally checks uniqueness after every row, not at the end of the statement. Without it, shifting 1→2 fails while the old 2 still exists:

CREATE TABLE "playlists" (
  "id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "name" VARCHAR(200) NOT NULL
);

CREATE TABLE "playlist_items" (
  "id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "playlist_id" BIGINT NOT NULL REFERENCES "playlists" ("id") ON DELETE CASCADE,
  "track_id" BIGINT NOT NULL,
  "position" INTEGER NOT NULL,
  CONSTRAINT "uq_playlist_items_position"
    UNIQUE ("playlist_id", "position") DEFERRABLE INITIALLY IMMEDIATE
);

INITIALLY IMMEDIATE still checks at the end of each statement, so a real duplicate fails right away. It just doesn't fail halfway through a shift. Now move item 2 from position 1 to position 3:

WITH move AS (SELECT 2 AS item_id, 1 AS from_pos, 3 AS to_pos)
UPDATE playlist_items p
SET position = CASE
  WHEN p.id = move.item_id THEN move.to_pos
  WHEN move.from_pos < move.to_pos THEN p.position - 1
  ELSE p.position + 1
END
FROM move
WHERE p.playlist_id = 7
  AND p.position BETWEEN LEAST(move.from_pos, move.to_pos)
                     AND GREATEST(move.from_pos, move.to_pos);

The same statement handles moves in both directions. Moving down shifts the items in between up by one, and moving up shifts them down. It touches only the rows between the two slots, so dragging a track one place down rewrites two rows, not the whole playlist.

Deletes don't need to renumber anything. A gap in the positions is harmless, because the list only has to sort correctly. If the UI shows "1, 2, 3", compute that at read time with row_number() OVER (ORDER BY position).

How do you renumber positions in MySQL and SQLite?

Neither has deferrable unique constraints, and both check uniqueness as each row changes. MySQL documents a workaround in its UPDATE reference: add ORDER BY, so rows are updated starting from the end the shift moves toward. Use DESC when adding 1 and ASC when subtracting 1.

UPDATE playlist_items
SET position = position + 1
WHERE playlist_id = 7 AND position >= 1
ORDER BY position DESC;

SQLite only allows ORDER BY on an UPDATE when it is compiled with SQLITE_ENABLE_UPDATE_DELETE_LIMIT, which the default builds leave off. A trick that works in every database is to move the affected rows out of the way first, by negating them, and then write the final values:

UPDATE playlist_items
SET position = -position - 1
WHERE playlist_id = 7 AND position BETWEEN 1 AND 3;

UPDATE playlist_items
SET position = CASE
  WHEN id = 2 THEN 3
  ELSE -position - 2
END
WHERE playlist_id = 7 AND position < 0;

The first statement can't collide, because positions start at 0 and all the negated values are below 0. The second statement turns -p - 1 back into p - 1, which is the shift for a downward move. For an upward move, use -position instead, which gives p + 1. Run both statements in one transaction.

Why does DEFAULT 0 break your sort order?

Because every row inserted without a position lands in slot 0, and ORDER BY position returns tied rows in no guaranteed order. 48 of the gallery's 75 hand-ordered columns default to 0 without a unique constraint. In practice, the order of new items changes between two loads of the same page, once the planner picks a different plan or an update rewrites the rows.

Two fixes. Always add a tie-breaker, ORDER BY position, id, so even bad data sorts the same way every time. And make appends compute the next slot explicitly under a lock on the parent row, so two people adding to the same list at once can't both read the same MAX:

BEGIN;

SELECT id FROM playlists WHERE id = 7 FOR UPDATE;

INSERT INTO playlist_items (playlist_id, track_id, position)
SELECT 7, 106, COALESCE(MAX(position) + 1, 0)
FROM playlist_items
WHERE playlist_id = 7;

COMMIT;

Take the same lock before a move. Without it, two moves in one playlist can each read the old positions and then interleave their shifts. With the unique constraint, the worst case is an error you retry. Without it, the result is two tracks in one slot that nobody notices until a customer does.

Is fractional indexing better than an integer position?

It is better for one thing: a move writes exactly one row. To insert between two items, store a value between their keys, for example 1.5 between 1 and 2. The Trello and Notion schemas in our gallery both work this way, for cards, blocks, pages and view tabs.

The catch is the column type. Every insert at the same spot halves the gap, and a number with fixed precision runs out of halves. We measured this in PostgreSQL 16, inserting over and over between the first item and the one we had just inserted:

• numeric(30,15), the type our own Trello and Notion examples use, takes 50 inserts between 1 and 2. The 51st midpoint rounds to 1.000000000000001, the value already stored, and the two items tie.
• double precision takes 52 inserts between 1 and 2, and only 43 between 1000 and 1001, because large numbers have less precision left for the fraction.
• SQLite has no exact decimal at all. A column declared NUMERIC(30,15) gets numeric affinity, so 1.5 is stored as an 8-byte float and 1.000000000000000000001 becomes the integer 1.

Fifty inserts sounds like plenty until a user keeps dropping new cards at the top of a column. Neither gallery schema puts a unique constraint on its fractional column, so when the gap runs out, the order just stops being stable and nothing reports it. An unconstrained PostgreSQL numeric doesn't run out, but it grows. After 200 halvings, a single value takes 48 bytes. Every fixed-size approach needs a rebalance job sooner or later.

How do you store a fractional index as a string?

Use a string key compared byte by byte. That is how Figma orders layers: each position is a fraction stored as a string, and averaging two of them is string arithmetic, so precision never runs out. The fractional-indexing library generates such keys: a0, a1, a2, then a1V between a1 and a2, and Zz before a0. The database only stores and sorts them:

CREATE TABLE "cards" (
  "id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "list_id" BIGINT NOT NULL,
  "sort_key" TEXT COLLATE "C" NOT NULL,
  CONSTRAINT "uq_cards_sort_key" UNIQUE ("list_id", "sort_key")
);

COLLATE "C" is the part that matters. These keys are designed to sort by raw byte value, where all uppercase letters come before all lowercase ones. A database created with an en_US.UTF-8 locale sorts them linguistically instead, and the order breaks. On the same five keys:

The card meant to go first shows up last, and only once someone has dragged a card above the first one. In MySQL, whose default collation is the case-insensitive utf8mb4_0900_ai_ci, declare the column binary. And don't call it rank, which became a reserved word with window functions:

CREATE TABLE cards (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  list_id BIGINT NOT NULL,
  sort_key VARCHAR(255) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  UNIQUE KEY uq_cards_sort_key (list_id, sort_key)
);

SQLite compares text bytewise by default, so a plain TEXT column works there.

Keep the unique constraint. Two clients that drop a card into the same gap at the same moment generate the same key. Figma's post names this case and resolves it on the server. With the constraint, the second insert fails and the client generates a new key, from the now-updated neighbours, and retries. Without the constraint, the two cards tie, and you're back to the DEFAULT 0 problem.

When does a string key need rebalancing?

Rarely. The key gets longer each time someone inserts at the same spot, but it never runs out, and a rewrite only matters once keys bloat the index. Jira's LexoRank is the same idea at very large scale: rank strings grow with reordering, and Jira schedules a rebalance once a rank reaches 128 characters, then rebalances immediately at 160. For a board, a document or a playlist, a rewrite with evenly spaced keys at write time (generateNKeysBetween) is all you'll ever need.

Gapped integers (10, 20, 30, insert 15) sit awkwardly in the middle. They need the move-one-row logic of fractional keys and the renumbering logic of integer positions, and they fall back to renumbering exactly when users are most active. If integer renumbering is too slow for you, skip straight to string keys.

Should you store a reorderable list as a linked list?

No. A prev_id column makes a move look cheap: unlink the item, then link it between its new neighbours. But reading the list in order now takes a recursive walk, the same query a tree stored as an adjacency list needs, instead of an indexed ORDER BY. One wrong pointer, from a bug or from two concurrent moves, cuts off everything after it, and nothing in the schema can detect a broken chain. None of the 39 schemas in the gallery orders a list by pointers. The one successor column, next_step_id in the email marketing automations, is a branch target that sits beside a unique position, not a replacement for it.

How do you let each user order the same items differently?

Move the position out of the item and into a per-user join table. A shared watchlist that every viewer can rearrange becomes (user_id, item_id, position) with the unique constraint on (user_id, position), the same shape as any many-to-many table with its own columns. The Robinhood schema stores each user's watchlist order this way: watchlist_items.display_order belongs to that user's list, not to the instrument.

The schema design best practices audit has the other constraints the gallery tends to leave to the application. To compare the two approaches side by side, open the Spotify and Trello schemas in the editor and export the DDL for your dialect.

Frequently asked questions