3 ms·
> Compare to entirely random global ids, like UUIDv4, that generally suffer from poor database locality. What does this mean in more words?
by onlypositive 3y ago
> Compare to entirely random global ids, like UUIDv4, that generally suffer from poor database locality.
What does this mean in more words?
- armchairhacker 3y agoBecause the bytes are all random, UUIDv4 is sorted randomly. So whenever you insert a database entry with a new UUID, it ends up getting put in some random memory location. In practice, you often want to select database entries which were inserted near each other in time. Ex: you would like to select the most recent entries or entries within a time frame. Even when selecting entries by other information, entries inserted closer to each other in time are generally more likely to be related. Fetching entries near each other in memory is faster, so it would be nice to insert entries sequentially in time; we want entries inserted near each other in time to be located near each other in memory. This is what counter-based indexing does: the database has a counter which increments on each insertion, and the current value becomes the inserted entry's id. But the problem with counters is when the database is distributed and insertions are happening in parallel, and you definitely don't want to sync the counter because that's way too slow. UUIDv7 combines a sort of counter (Unix time) with a randomly-generated number. The counter bytes are first, so they determine the sort order; but in case 2 entries get inserted at the same time, the randomly-generated number keeps them distinct and totally ordered.
- hot_gril 3y agoRight, but worth noting that distributed DBs don't necessarily play nice with sequential pkeys. Spanner explicitly tells you not to use a time-based pkey.