Hacker News Reader

Comment by cpburns2009 | Hacker News Reader

cpburns2009

The point of shoving the timestamp into it is to make it work well with database indexes. UUIDv4 is awful in that regard.

View on HN

Sounds like the problem here is that databases need to index on more than one thing at once

They already do. UUIDv7 neatly solves a common problem: a random, unguessable, public id that works well with databaes. UUIDv4 is infamous for this.

This very much sounds like databases do not, in fact, support indexing on more than one thing at once.

Yes, 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.

You should make another index is my point. The database layer properly implements this extra index instead of just being a stupid dictionary that you have to massage with the one key value you have

That doesn't do anything to help with the issue being discussed, which is index fragmentation and random IO affecting performance. It just means now you have two indexes to maintain.

It doesn’t have anything to do with indexing more than one thing.

The 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

If 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.

Sure 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

How would it internally sort your index by (timestamp, ID) without you later having to provide the timestamp to locate the ID?

Reply on news.ycombinator.com