6 ms·

Or model it as a linked list and you can sidestep the limitations / complexity of some kind of numeric (or bytes / text based ordering field) ------------ pla

Or model it as a linked list and you can sidestep the limitations / complexity of some kind of numeric (or bytes / text based ordering field) ------------ playlists { id } ------------ playlist_members { id playlist_id prev_playlist_member_id (and/or next_playlist_member_id) song_id } ------------ you could then just select * from playlist_members where playlist_id = ... and sort on the client side. you'd probably add an application limit where playlists have a max length of some kind. re-orders can be done in a fixed number of row updates and typical application queries are still possible / fast.

danielheath 4y ago
Writing a linked list in the database might sound good in theory; as someone maintaining a system that uses that technique, please please please do not ever do it. You lose access to basically every database-provided consistency technique.
forrest2 4y ago
or perhaps for some applications it would be sufficient to do playlists { id song_ids [] } ------------- and just store the ordering in an array. Some postgres drivers might start shitting the bed though at some gigantic array sizes, but a playlist probably has reasonable enough limits that you wouldn't have a big problem.
- Tostino 4y ago
  This is the exact solution I came up with.
- danielheath 4y ago
  ID arrays can't use FK constraints (requested elsewhere in these comments), otherwise that would be pretty good. Performance-wise it means more IO than optimal, but that's not necessarily a huge problem.
tshaddox 4y ago
If you were going to enforce a maximum size so that you could sort on the client, I bet you could just use an integer and rewrite the entire sequence from 1 to N on every reorder operation. Unless you were expecting to have way more writes than reads, which is a little hard to imagine.
- eatonphil 4y ago
  > so that you could sort on the client You could probably also sort in the database with a recursive cte or with PL/pgSQL.
  - tshaddox 4y ago
    Indeed, but AFAIK that is still much slower than sorting on an indexed integer column, which probably means you still need to enforce a maximum list length.
KyeRussell 4y ago
Usual downsides of linked lists apply (i.e. traditional limit/offset pagination isn’t really viable). I’ve never considered doing this though! I imagine I must’ve come across some problem where this would’ve been better than whatever I came up with.
topicseed 4y ago
What if two rows use the same prev_playlist_member_id?
- forrest2 4y ago
  You make that impossible with application logic or constraints. Just like any linked list, you need to update the other pointers Example constraint would be a unique index for the Playlist member table on the combination of Playlist id and previous Playlist member ID.

danielheath 4y ago

Writing a linked list in the database might sound good in theory; as someone maintaining a system that uses that technique, please please please do not ever do it. You lose access to basically every database-provided consistency technique.

forrest2 4y ago

or perhaps for some applications it would be sufficient to do playlists { id song_ids [] } ------------- and just store the ordering in an array. Some postgres drivers might start shitting the bed though at some gigantic array sizes, but a playlist probably has reasonable enough limits that you wouldn't have a big problem.

Tostino 4y ago
This is the exact solution I came up with.
danielheath 4y ago
ID arrays can't use FK constraints (requested elsewhere in these comments), otherwise that would be pretty good. Performance-wise it means more IO than optimal, but that's not necessarily a huge problem.

tshaddox 4y ago

If you were going to enforce a maximum size so that you could sort on the client, I bet you could just use an integer and rewrite the entire sequence from 1 to N on every reorder operation. Unless you were expecting to have way more writes than reads, which is a little hard to imagine.

eatonphil 4y ago
> so that you could sort on the client You could probably also sort in the database with a recursive cte or with PL/pgSQL.
- tshaddox 4y ago
  Indeed, but AFAIK that is still much slower than sorting on an indexed integer column, which probably means you still need to enforce a maximum list length.

KyeRussell 4y ago

Usual downsides of linked lists apply (i.e. traditional limit/offset pagination isn’t really viable). I’ve never considered doing this though! I imagine I must’ve come across some problem where this would’ve been better than whatever I came up with.

topicseed 4y ago

What if two rows use the same prev_playlist_member_id?

forrest2 4y ago
You make that impossible with application logic or constraints. Just like any linked list, you need to update the other pointers Example constraint would be a unique index for the Playlist member table on the combination of Playlist id and previous Playlist member ID.