sql - How to store ordered items which often change position in DB -

I should be able to store a large list of items ordered in DB. So far it is straight-forward:

  id position other fields 1 45 ... 2 4736 ... 3 514 ... ...  

When asked in these, I always need to get some things (filtered based on other fields), but in the correct order. By putting an index on the basis of the situation and also using the "order by position".

Issue now : Items often change their position, and not only to 1 or 2. The situation in ID2 varies from 4736 to 2000, I need to update the status of all the elements between our position and the old condition 2000 and 4735, adding 1 to each line. And this is not just an ID that changes per transaction, but there may be several transactions within a few more times.

I think the best way to handle the update issue would be using a linked list instead of the status column, where I added my predecessor to its old I can remove the ID2 from the position and then add it somewhere between its new predecessor and successor. This will be a continuous and small number per place per update and it will also be the preferred way of handling my changes (in my case in Java). Although it raises N + 1 problem in the right order for query - for some elements too, I have to go to the full list in the worst case to know my exact order.

So my question is : What would you recommend to get a good balance between necessary updates and query performance?

By now I see two promising directions:

1. Is there any DBMS (ideal open source), which not only signifies written lists with syntactic sugar Can handle with good performance, e.g. By using internal index for connected elements?

2. It may be the only option of a blob where the entire linked list will be stored! How many such a linked list can be / how much memory will be used in DB and when we say it is brought to 1.000.000 entries? I'm using Java + Hibernate if it matters. I think that after getting Blob, it should be very fast to process the entire list in memory!

But certainly other ideas are welcome too!

If you rest the constraint that is in the status column from 1 to N Should be an integer and instead allow to include any number in it, you can do both searches and updates efficiently.

By computing the average (A + B) DIVA 2, you can insert an item between two other items with position A and B. For example if A is 10000 and B12000 then your new position is 11000. Occasionally you will exit the interval due to clustering, at which point you can redistribute more similar situations in the entire table.