> Also, you’ll still eventually need to go clean up the entire sequence
If you're using a numeric type, you get up to 16383 digits after the decimal. That's... Probably more precision than you'll be able to reasonably use up in almost any use case. Any time you're reordering in bulk, you're resetting the order value to a nice integer, so it would take many thousands of ad-hoc reordering operations near a single position to get it close to the precision limit, yeah?
https://www.postgresql.org/docs/current/datatype-numeric.htm...
Sure, but if it’s orders of magnitude more than you’ll ever need, you’re just using way more storage than you need. That’s what I meant by being more explicit about your plan and using more predictable storage. As a basic example, you could also use integer (or bigint) and start by number things like 1000, 2000, 3000, etc. Now you know exactly how many slots between items you have, and can more easily query for cases where you’re running low on slots.
Also, you’ll still eventually need to go clean up the entire sequence because you’ll run out of gaps between adjacent numbers. Because of this, I’d probably rather use a more predictable type (like one of the integer types) and explicitly plan my cleanup schedule.