←back to thread

Postgres UUIDv7 and per-back end monotonicity

(brandur.org)
230 points craigkerstiens | 2 comments | 02 Jan 25 16:32 UTC | HN request time: 0.41s | source
Show context
fngjdflmdflg ◴[02 Jan 25 17:09 UTC] No.42576248[source]▶
>The Postgres patch solves the problem by repurposing 12 bits of the UUID’s random component to increase the precision of the timestamp down to nanosecond granularity [...]

>It makes a repeated UUID between processes more likely, but there’s still 62 bits of randomness left to make use of, so collisions remain vastly unlikely.

Does it? Even though the number of random bits has decreased, the time interval to create such a duplicate has also decreased, namely to an interval of one nanosecond.

replies(3): >>42576319 #>>42576330 #>>42577456 #
1. michaelt ◴[02 Jan 25 18:54 UTC] No.42577456[source]▶
Imagine if you were generating 16 UUIDs per nanosecond, every nanosecond.

According to [1] due to the birthday paradox, the probability of a collision in any given nanosecond would be 3E−17 which of course sounds pretty low

But there are 3.154e+16 nanoseconds in a year - and if you get out your high-precision calculator, it'll tell you there's a 61.41% chance of a collision in a year.

Of course you might very well say "Who needs 16 UUIDs per nanosecond anyway?"

[1] https://www.bdayprob.com/

replies(1): >>42579069 #
2. Horffupolde ◴[02 Jan 25 21:22 UTC] No.42579069[source]▶
So what if there’s a collision? If the column is UNIQUE at most it’ll ROLLBACK on INSERT. 16 INSERTS per nanosecond is 16 billion TPS. At that scale you’ll have other problems.