How to store a list of frequently changing order in Postgres?

Question

PostgreSQL

Hello!

Please tell me the generally accepted approaches for storing elements in a table, which can change their order quite often.

For example:
id --- name
1 --- item_1
2 --- item_2
3 --- item_3

The task is to store items as efficiently as possible if tasks like: make item_3 the second in order.

When changing the id or another index (numerical), you either have to change the values ​​of a huge number of all the lower elements, or come up with crutches like indexes with large intervals, but this also does not solve the problem for any number of permutations.

Found another option with a string index

id --- name
'a' --- item_1
'b' --- item_2
'c' --- item_3

And after the swap, you only need to calculate the new index of the 1st item:
id --- name
'a' --- item_1
'ab' --- item_3
'b' --- item_2

And as I understand it, in the latter case, the logic for calculating the index falls on the server side, and not on the database?
Can you please tell me how to solve this problem in database?

Answer 1

the problem is thinking up
- from the point of view of the database, preferably {id, name, order}
- it is logical to change the order in the intermediate logic (depending on the architecture, it can be on the front, and on the back, and on the middle layer) .. and with the need to save changes
ps egorkozelskij , if you are satisfied with the key-value, there is https://www.aerospike.com/lp/aerospike-community-e... but it seems there, anyway, the starting hardware configuration is from 8 GB of RAM