4

According to postgresql uuid-ossp documentation uuid_generate_v1() is based on Mac address + timestamp:

https://www.postgresql.org/docs/9.4/static/uuid-ossp.html

On a distributed database scenario where we have hundreds of databases generating records with UUID keys and syncing back to a central database.

Suppose we detect a machine has a wrong date/time in the future and we change it back to the correct date/time. May it generate a conflicted UUID key on this particular machine?

One scenario is the summer time / daylight savings.

| improve this question | |
  • 4
    "One scenario is the summer time / daylight savings." -- no, it is not. Everything should be stored as UTC. Time always goes forwards. – Philᵀᴹ May 29 '17 at 15:08
  • @Philᵀᴹ except in the case of leap seconds. – Evan Carroll May 29 '17 at 20:50
2

There is no reason, that I know of to use uuid_generate_v1() over uuid_generate_v4(). That said, Philᵀᴹ is largely correct when he says

"One scenario is the summer time / daylight savings." -- no, it is not. Everything should be stored as UTC. Time always goes forwards. – Philᵀᴹ

Even if your display time is set to DST, (or something like CSTCDT), the underlying storage mechanism should be UTC.

That said, though everything should be stored as UTC, it's not always that way. There are exceptions, like with XP, or Windows 7, you may see some kind of abnormality, especially if you permit the other OS to modify the RTC.

And, there is always the problem of leap seconds if you really want to nitpick, when some kernels or daemons will roll back the second and it's basically up to them to see if they support this. Linux-proper and OSX does, Android does not afaik humorous NDT on Leap seconds

For reference, the spec details the process of generating a UUID v1 time-based UUID, 4.2 Algorithms for Creating a Time-Based UUID.

| improve this answer | |
  • Well, uuid_generate_v4() is entirely from random numbers. On my scenario we have about 430 machines generating primary keys. In this scenario it seems more prone to conflict than uuid_generate_v1(). Am I right? – Thiago Sayão May 29 '17 at 23:17
  • 2
    To put things into perspective, consider a file containing 2.3 quintillion Version 4 UUIDs, with a 50% chance of containing one UUID collision. It would be 36 exabytes in size, assuming no other data or overhead. Beyond that it doesn't matter. v4 is certainly the more popular and secure use-case. – Evan Carroll May 29 '17 at 23:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.