How to index elements to change queue frequently?

Question

PostgreSQL

Hello Toaster!
In short, there is an ordered list of elements, and each element has its own serial number for sorting. Now, in this list, somewhere in the middle, you need to insert a new element, and increase the ordinal number of all the elements following it by i + 1. Similar operation and at removal of an element. Everything would be fine, but there can be hundreds of such elements, or even a thousand, and the operation to change the index does not fit here.
Then I thought, what if I sort not by numbers, but lexicographically or by fractions. Those. to insert in between the first and second element, you can simply insert the index 2.5, and so on.
What confuses me, what if there are even more correct approaches? The task that typical, but sensible decisions did not find.

Answer 1

If you do not have constantly changing indexes many times per second, and also not a table with a million rows, then a mass update of all indexes above or below the new value is a good solution.
That is, if you want to insert an element and give it an index of 5, then before inserting, you need to execute the query
With this scheme, when deleting an element, nothing needs to be done with the rest of the elements.
The decision to leave the "gap between indices" at 10-100-1000, etc., or make the indices fractional, is completely unacceptable. It is not reliable in the long run.
And yes, if we talk about typical tasks, then this task is typical.
There is a nice gem acts_as_list that does just that. He uses the method that I described yours.

Answer 2

Back in the old days, when BASIC instructions had to be numbered, they numbered them through 10 so that you could insert them without problems and not have to deal with incrementing i + 1. So you should then do not fractional numbers, but after a hundred. And when somewhere the distance will be running out, rebalance again.