Sounds like the problem here is that databases need to index on more than one thing at once
replyThey already do. UUIDv7 neatly solves a common problem: a random, unguessable, public id that works well with databaes. UUIDv4 is infamous for this.
replyThis very much sounds like databases do not, in fact, support indexing on more than one thing at once.
replyYes, you cannot be in one order and also a different order at the same time. You can make another index, but that's something else entirely.
replyIt doesn’t have anything to do with indexing more than one thing.
replyThe issue with truly random IDs is that inserts are scattered throughout the B-tree index, which cause page splits, fragmentation, and poor cache locality. Using a timestamp prefix makes new IDs sequential, so inserts tend to land near the end of the index, which avoid all the B-tree trashing and improve write performance.
Yes, which means that you should probably be able to provide your timestamp to the database so it can use it for optimizing its index, rather than trying to shove it in your ID
replyIf you did that you would also have to provide the timestamp every time you wanted to look up an ID, and now we're back to UUIDv7 again.
replySure but at least you are being honest about it. For what it's worth the database can handle this internally without you needing to hand this back but if you have a bad one then yes you should probably have it give you back a location hint that you use for future queries
replyHow would it internally sort your index by (timestamp, ID) without you later having to provide the timestamp to locate the ID?
reply