4 ms·
Have you considered using a 32 bit time value with second resolution? That way you still get sort-ability, you get 16 bit more entropy, fare better on 32 bit s
by j-pb 6y ago
Have you considered using a 32 bit time value with second resolution?
That way you still get sort-ability, you get 16 bit more entropy, fare better on 32 bit systems, and still get 130 years of usage, out of them.
Also you actively discourage people from trying funny stuff like synchronisation and conflict resolution via the time component. Because 1ms "that sounds kinda good enough :?", while 1s "that'll never work!!!"
Ammendment:
Thinking about it.
Using a 16bit timestamp prefix with second resolution and rollover should probably provide a good tradeoff between improving insertion performance (most expensive in terms of index touching), a 112 bit random value, and a dense usage of the timstamp field.
It also allows you to do thing like "forgetting" old values,
within a 18h timeframe.
- Tostino 6y agoI personally use: https://github.com/tvondra/sequential-uuids https://github.com/tvondra/sequential-uuids The time component resolution is configurable, and you can make it wrap around every so often, filling in gaps left by page splits, updates, deletes, etc.
- j-pb 6y agoNice! This is kinda what I had in mind, while writing that ammendum! Thanks for sharing :D
- Sytten 6y agoIts just a bit annoying because it is a C extension and that can't be used in most managed DB in the cloud.
- Tostino 6y agoI had to re-write the time based function from that extension in PL/PGSQL earlier in the week for someone on my team who couldn't work with the C extension: https://gist.github.com/Tostino/ca104bff40e704b6db70b9af492664ef https://gist.github.com/Tostino/ca104bff40e704b6db70b9af4926... This can be used in RDS, etc.
- dkarl 6y ago> Because 1ms "that sounds kinda good enough :?" Ugh. You wouldn't think so, but I caught a case of this in production, using millisecond timestamps as unique ids for browser analytics. The developer felt that even though two users could in rare cases click at exactly the same millisecond, their clocks weren't likely to be in perfect sync with each other, and that this "additional level" of randomness made a collision virtually impossible.
- Quekid5 6y agoOh dear god. I suppose it can be counter-intuitive, but people really need to learn about timer precision... and, more importantly, statistics (basics, at least!). I absolutely hate the 'combinatorics' part of statistics, but distributions, etc. seemed pretty learnable to me... but then I like continuous math, so...
- j-pb 6y agoThat's not even statistics. That's simply the pigeon hole principle. A second has 1000ms. If you have more than 1000 users doing things per second, there MUST be a collision.
- Quekid5 6y agoAh, but now you're discretizing! :D ... also, it Technically depends on your throughput (something something queue theory).
- Can_Not 6y agoThen you have https://en.m.wikipedia.org/wiki/Birthday_problem https://en.m.wikipedia.org/wiki/Birthday_problem Even with a small number of users per second (like 20) it becomes likely to happen often.
- mywittyname 6y agoI worked on an analytics system where we appended a random 3 digits to the millisecond resolution timestamp for exactly this reason. It turns out, collisions are far, far more common that the math would suggest. Especially back in the aughts when browser had a lot of individual quirks to deal with.
- Bogdanp 6y agoYou can also pick your own epoch and use centiseconds since that epoch for the time component. It's what I do in [1]. [1]: https://docs.racket-lang.org/buid/index.html?q=buid#%28part._.Spec%29 https://docs.racket-lang.org/buid/index.html?q=buid#%28part....
- j-pb 6y agoYeah, I think the regular overlfow is actually an advantage. A.) you pracice having overflows regularly, so the issue is resolved now and not left to some pooor schmuck world war XXVII Survivor in the year 35.000. B.) It uses every bit, just like a fully random ID, as the key space is filled more densely with each overflow.