I think I would simply use json to store the list in a field. Makes it very easy to read and replace the json but then again, I would not be using SQL for this so I'm cheating.
It depends a lot on how many items are in the list. If there are less than 10000 items in each sorted set you should probably let the client handle the ordering and just do bulk inserts with order index in a denormalised JSON col or something, but after that you probably want something db-native.
What is the use case where your app would need to let the user sort such a large list? I cannot think of any use case. At that point I would let the user set the order field (integer) as it would not be feasible to have a drag and drop UI anymore. That would be cumbersome to use.
It's not 10k but I have Spotify playlists with 4k+ tracks (queue of new albums I listen to as radio at the office) and I always fiddle with the order of the albums at the tail of the playlist.
you can just do ids.join(",") to store just the ordering separately from how you store the list itself. It could still use sql it's just not as normalized. Once you switch to nosql you can just store the list in an array or linked list.
