8 ms·

Postgres UUIDv7 and per-back end monotonicity

230 points by craigkerstiens 2y ago

urronglol 2y ago
What is a v7 UUID. Why do we need more than 1. uuid from a random seed and 2. one derived from that and a timestamp (orderable)
- n2d4 2y ago
  UUID v7 is the latter, whereas v4 is the former. All the other versions are somewhat legacy, and you shouldn't use them in new systems (besides v8, which is "custom format UUID", if you need that.)
  - elehack 2y ago
    UUID v5 is quite useful if you want to deterministically convert external identifiers into UUIDS — define a namespace UUID for each potential identifier source (to keep them separate), then use that to derive a V5 UUID from the external identifier. It's very useful for idempotent data imports.
    - jandrewrogers 2y ago
      Both UUIDv3 and UUIDv5 are prohibited for some use cases in some countries (including the US), which is something to be aware of. Unfortunately, no one has created an updated standard UUID that uses a hash function that is not broken. While useful it is not always an option.
      - kbolino 2y ago
        Could you provide an example of such a prohibition? I've never heard of that before. I doubt that the quality of the hash function is the real issue. The problem with MD5 and SHA1 is that it's easy (for MD5) and technically possible (for SHA1) to generate collisions. That makes them broken for enforcing message integrity. But a UUID is not an integrity check. Both MD5 and SHA1 are still very good as non-cryptographic hash functions. While a hash-based UUID provides obfuscation, it isn't really a security mechanism. Even the existence of UUIDv5 feels like a knee-jerk reaction from when MD5 was "bad" but SHA1 was still "good". No hash function will protect you against de-obfuscation of low-entropy inputs. I can feed your social security number through SHA3-512 but it's not going to make it any less guessable than if I fed it through MD5. Moreover, a UUID only has 122 bits of usable space. Even if we defined a new SHA2- or SHA3-based UUID version, it's still going to have to truncate the hash output to less than half of its full size. This significantly alters the security properties of the hash function, though I'm not sure if much cryptanalysis has been done on the shorter forms to see if they're more practically breakable yet. There is one area where the collision resistance of the hash function could be a concern, though. If all of the inputs to the hash are under the control of a potential attacker, then maliciously constructed data could produce the same UUID. I still wouldn't think this would be a major issue, since most databases will fail to insert a duplicate key, but it might allow for various denial of service attacks. This still feels like quite a niche risk, though, and very circumstance-dependent.
        jandrewrogers 2y ago
        Systems where a sophisticated attacker may engineer collisions are precisely why UUIDv3/5 are prohibited. SHA1 is deemed broken by some government authorities and not to be used in any critical systems, including as UUID (this is where I’ve seen it expressly prohibited). The entire point of UUIDs in many systems is that collisions should be impossible, system integrity is predicated on it. Many systems exist in a presumptively adversarial environment. Similarly, UUIDv4 is also prohibited in many contexts because people using weak entropy sources has been a recurring problem in real systems. It isn’t a theoretical issue, it has actually happened repeatedly. Decentralized generation of UUIDv4 is not trusted because humans struggle to implement it correctly, causing collisions where none are expected. There are also contexts where probabilistic collision resistance is disallowed because collision probabilities, while low, are high enough to be theoretically plausible. Most people aren’t working on systems this large yet. Ironically, there are many reasonable ways to construct reasonable and secure 128-bit identity values but the standards don’t define one. Some flavor of deterministic generation + encryption are not uncommon but they are also non-standard. That said, many companies unavoidably have a mix of standard and non-standard UUIDs internally. To mitigate collisions, they have to transform those UUIDs into something else UUID-like, at which point it is pretty much guaranteed to be non-standard. Not ideal but that is the world we live in.
        kbolino 2y ago
        Ok, that makes sense. As far as I can tell, even truncated to "just" 122 bits, there's still no known way to generate a SHA-256 collision, so the MD5/SHA1 versions are comparatively vulnerable vs an hypothetical SHA256 UUID version. However, it's starting to feel like UUIDs may not be long enough in general to meet the need for secure, distributed ID generation.
- cube2222 2y ago
  UUID v7 is what you numbered #2. For the others, it’s best to read up on Wikipedia[0]. I believe they all have their unique use-cases and tradeoffs. E.g. including information about which node of the system generated an ID. [0]: https://en.m.wikipedia.org/wiki/Universally_unique_identifier https://en.m.wikipedia.org/wiki/Universally_unique_identifie...
- mind-blight 2y ago
  A deterministic uuid based off of a hash of bits is also very useful (UUID5). I've used that for deduping records from multiple sources
- chimpontherun 2y ago
  As it is usual in many areas of human endeavor, newcomers to the field tend to criticize design decisions that were made before them, only to re-invent what was already invented. Sometimes it leads to improvements in the field, via rejection of the accumulated legacy crud, or just simply affording a new perspective. Most other times it's a well-intentioned, but low-effort noise. I, personally, do it myself. This is how I learn.
- paulddraper 2y ago
  [dead]
- urronglol 2y ago
  [flagged]
  - purerandomness 2y ago
    It comes off as a low-effort question that seems to try to evoke a reply from a peer, while it's the kind of question that is best answered by an LLM, Google, or Wikipedia.
    - a3w 2y ago
      Well, a LLM could answer it or write total bullshit. But yes, Wikipedia or other research quick internet research will help generally. And excactly here, it will tell you that there are competing standards since we have competing use cases.
      - kraftman 2y ago
        For simple questions like this with unambiguous answers, it is statistically very unlikely that you'll get a bullshit answer from an LLM.
  - treve 2y ago
    I didn't downvote you, but the terseness made it for me immediately come off as a kind of criticism, e.g.: "Why would we ever need it". May not have been your intent but if it was a genuine question, form matters.
    - urronglol 2y ago
      Be kind. Don't be snarky. Converse curiously; don't cross-examine. Edit out swipes. Comments should get more thoughtful and substantive, not less, as a topic gets more divisive. When disagreeing, please reply to the argument instead of calling names. "That is idiotic; 1 + 1 is 2, not 3" can be shortened to "1 + 1 is 2, not 3." Please don't fulminate. Please don't sneer, including at the rest of the community. Please respond to the strongest plausible interpretation of what someone says, not a weaker one that's easier to criticize. Assume good faith.
deleted 2y ago
[deleted]
kingkilr 2y ago
I would strongly implore people not to follow the example this post suggests, and write code that relies on this monotonicity. The reason for this is simple: the documentation doesn't promise this property. Moreover, even if it did, the RFC for UUIDv7 doesn't promise this property. If you decide to depend on it, you're setting yourself up for a bad time when PostgreSQL decides to change their implementation strategy, or you move to a different database. Further, the stated motivations for this, to slightly simplify testing code, are massively under-motivating. Saving a single line of code can hardly be said to be worth it, but even if it were, this is a problem far better solved by simply writing a function that will both generate the objects and sort them. As a profession, I strongly feel we need to do a better job orienting ourselves to the reality that our code has a tendency to live for a long time, and we need to optimize not for "how quickly can I type it", but "what will this code cost over its lifetime".
- 3eb7988a1663 2y ago
  I too am missing the win on this. It is breaking the spec, and does not seem like it offers a significant advantage. In the eventual event where you have a collection of UUID7 you are only ever going to be able to rely on the millisecond precision anyway.
  - sbuttgereit 2y ago
    You say it's breaking the spec, but is it? From https://www.rfc-editor.org/rfc/rfc9562.html#name-uuid-version-7 https://www.rfc-editor.org/rfc/rfc9562.html#name-uuid-versio...: "UUIDv7 values are created by allocating a Unix timestamp in milliseconds in the most significant 48 bits and filling the remaining 74 bits, excluding the required version and variant bits, with random bits for each new UUIDv7 generated to provide uniqueness as per Section 6.9. Alternatively, implementations MAY fill the 74 bits, jointly, with a combination of the following subfields, in this order from the most significant bits to the least, to guarantee additional monotonicity within a millisecond: 1. An OPTIONAL sub-millisecond timestamp fraction (12 bits at maximum) as per Section 6.2 (Method 3). 2. An OPTIONAL carefully seeded counter as per Section 6.2 (Method 1 or 2). 3. Random data for each new UUIDv7 generated for any remaining space." Which the referenced "method 3" is: "Replace Leftmost Random Bits with Increased Clock Precision (Method 3): For UUIDv7, which has millisecond timestamp precision, it is possible to use additional clock precision available on the system to substitute for up to 12 random bits immediately following the timestamp. This can provide values that are time ordered with sub-millisecond precision, using however many bits are appropriate in the implementation environment. With this method, the additional time precision bits MUST follow the timestamp as the next available bit in the rand_a field for UUIDv7."
fngjdflmdflg 2y ago
>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.
- paulddraper 2y ago
  Depends if you think sub-millisecond locality is significant.
- londons_explore 2y ago
  I could imagine that certain nanoseconds might be vastly more likely than other nanoseconds. For example, imagine you have a router that sends network packets out at the start of each microsecond, synced to wall time. Or the OS scheduler always wakes processes up on a millisecond timer tick or some polling loop. Now, when those packets are received by a postgres server and processed, the time to do that is probably fairly consistent - meaning that X nanoseconds past the microsecond you probably get most records being created.
  - UltraSane 2y ago
    But only one nanosecond slower or faster and you get another set of 4.611 billion billion random IDs. I think random variations in buffer depths and CPU speeds will easily introduce hundreds of nanoseconds of timing variations. syncing any two things to less than 1 nanosecond is incredibly hard and doesn't happen by accident.
    - zamadatix 2y ago
      The important part is the events in time aren't going to be as random as the actual random source. The chances of an actual collision remain low but the distribution of events over time is a weaker (in relative terms) source of random bits compared to proper "random" sources which won't have obvious bias at all.
deleted 2y ago
[deleted]
dotdi 2y ago
My org has been using ULID[0] extensively for a few years, and generally we've been quite happy with it. After initially dabbing with a few implementations, I reimplemented the spec in Kotlin, and this has been working out quite well for us. We will open-source our implementation in the following weeks. ULID does specifically require generated IDs to be monotonically increasing as opposed to what the RFC for UUIDv7 states, which is a big deal IMHO. [0]: https://github.com/ulid/spec https://github.com/ulid/spec
- willvarfar 2y ago
  Having used a lot of the ULID variants that the UUIDv7 spec cites as prior art, including the ULID spec you link to, I've gotta say that UUIDv7 has some real advantages. The biggest advantage is that it is hex. Haven't yet met a database system that doesn't have functions for substr and from_hex etc, meaning you can extract the time part using vanilla sql. ULID and others that use custom variants of base32 or base62 or whatever are just about impossible to wrangle with normal tooling. Your future selfs will thank you for being able to manipulate it in whatever database you use in the future to analyse old logs or import whatever data you generate today.
  - mixmastamyk 2y ago
    Aren't they stored as 16 bytes in binary? How to format it later as text is then your choice.
    - WorldMaker 2y ago
      It's that eternal push/pull "war" between "we need a sproc that can report this directly from the SQL server" and "please don't do things directly on the SQL server because you'll route around important application code" and "it's a feature not a bug that you can't just look things up by ID in the DB without a little bit of extra work". I did work on a project using ULIDs in SQL Server. They were stored in uniqueidentifier fields with a complex byte swap from ULID to fake-UUID to get better storage/indexing out of SQL Server [1]. There was an attempt to use SQL Functions to display/search the ULID form directly in the database, but it was never as bug free as the C# byte order code and so it was definitely not recommended doing it directly in the DB and that if a "report" was missing it should be a part of the application (which was already almost nothing but a bloated "reporting" tool) or in a related "configuration" application. It did feel more like a feature than a bug because it did keep some meddling and drama out of the DB. I also see the arguments for why in some different types of applications it makes debugging a lot harder and those arguments make sense and it is definitely a trade-off to consider. [1] The rabbit hole into SQL Server's ancient weird UUID sort order: https://devblogs.microsoft.com/oldnewthing/20190426-00/?p=102450 https://devblogs.microsoft.com/oldnewthing/20190426-00/?p=10...
lordofgibbons 2y ago
What benefit does this have over something like Twitter's Snowflake, which can be used to generate distributed monotonically increasing IDs without synchronization? We've been using an implementation of it in Go for many years in production without issues.
- WorldMaker 2y ago
  UUIDv7 interoperates with all the other versions of UUID. The v7 support in Postgres doesn't add a new column type, it makes the existing column type more powerful/capable. Applications that had been using UUIDv4 everywhere can get cheap Snowflake-like benefits in existing code just from switching the generator function. Most languages have a GUID or UUID class/struct that is compatibly upgradable from v4 to v7, too.
  - akvadrako 2y ago
    Snowflake is a 64 bit integer. It doesn't need a new column type and works everywhere.
pphysch 2y ago
The naming of "rand_a" and "rand_b" in the spec is a bit misleading. They don't have to be generated randomly. I'm sure there's a historical reason for it. "extra_" or "distinct_" would be a more accurate prefix for UUIDv7. UUIDv7 is actually quite a flexible standard due to these two underspecified fields. I'm glad Postgres took advantage of that!
mmerickel 2y ago
Remember even if timestamps may be generated using a monotonically increasing value that does not mean they were committed in the same order to the database. It is an entirely separate problem if you are trying to actually determine what rows are "new" versus "previously seen" for things like cursor-based APIs and background job processing. This problem exists even with things like a serial/autoincrement primary key.
- shalabhc 2y ago
  +1 What could be useful here is if postgres provided a way to determine the latest frozen uuid. This could be a few ms behind the last committed uuid but should guarantee that no new rows will land before the frozen uuid. Then we can use a single cursor track previously seen.
willvarfar 2y ago
Ordering for UUIDv7s in the same millisecond is super useful when some rows represent actions and others reactions. I have used this guarantee for events generated on clients. It really simplifies a lot of reasoning.
- fc417fc802 2y ago
  [dead]
Glyptodon 2y ago
On one hand I too am looking forward to more widespread use of UUIDv7, but on the other I don't really get the problem this is solving for their spec. If you care about timestamp ordering I don't think doing it in a way that forces you to fake a PK if you insert an earlier dated record at a future point makes sense. But I guess I'm implicitly assuming that human meaningful dates differ from insertion times in many domains.
samatman 2y ago
I maintain that people are too eager to use UUIDv7 to begin with. It's a dessert topping and a floor wax. Let's say you need an opaque unique handle, and a timestamp, and a monotonically increasing row ID. Common enough. Do they have to be the same thing? Should they be the same thing? Because to me that sounds like three things: an autoincrementing primary key, a UUIDv4, and a nanosecond timestamp. Is it always ok that the 'opaque' unique ID isn't opaque at all, that it's carrying around a timestamp? Will that allow correlating things which maybe you didn't want hostiles to correlate? Are you 100% sure that you'll never want, or need, to re-timestamp data without changing its global ID? Maybe you do need these things unnormalized and conflated. Do you though? At least ask the question.
- user3939382 2y ago
  Re-timestamp would be a new one for me. What’s a conceivable use case? An NTP fault?
- fastball 2y ago
  Also if you have a nanosecond timestamp, do you actually need a monotonically increasing row ID? What for?
  - peferron 2y ago
    Perhaps as a tie breaker if you insert multiple rows in a table within one transaction? In this situation, the timestamp returned by e.g. `now()` refers to the start of the transaction, which can cause it to be reused multiple times.
    - fastball 2y ago
      I meant in a context with a random ID and a timestamp. If you need to retrieve rows by time/order, you use the timestamp. If you need a row by ID, you use the ID. The use-cases where you actually need to know which row was inserted first seem exceedingly rare (mostly financial / auditing applications), and even then can probably be handled with separate transactions (as you touch on).
scrollaway 2y ago
UUID7 is excellent. I want to share a django library I wrote a little while back which allows for prefixed identity fields, in the same style as Stripe's ID fields (obj_XXXXXXXXX): https://github.com/jleclanche/django-prefixed-identity-field/ https://github.com/jleclanche/django-prefixed-identity-field... This gives a PrefixedIdentityField(prefix="obj_"), which is backed by uuid7 and base58. In the database, the IDs are stored as UUIDs, which makes them an efficient field -- they are transformed into prefixed IDs when coming out of the database, which makes them perfect for APIs. (I know, no documentation .. if someone wants to use this, feel free to file issues to ask questions, I'd love to help)
nikisweeting 2y ago
I implemented this in pure Python a few days ago in case anyone finds it helpful, here it is: https://gist.github.com/pirate/7e44387c12f434a77072d50c52a3d18e https://gist.github.com/pirate/7e44387c12f434a77072d50c52a3d... My implementation supports graceful degradation between nanosecond scale resolution, microsecond, and millisecond, by using 12 bits for each and filling up the leftmost bits of rand_a and rand_b. Not all environments provide high resolution system clocks with no drift, so it's is important to maintain monotonicity when generating IDs with a low-res timestamp as input. You still want the bits that would've held the nanosecond value to be monotonic. Neither of the existing uuid_utils and uuid7 python libs that can generate UUID7s support this monotonicity property. Am planning on using this for ArchiveBox append-only "snapshot" records, which are intrinsically linked to time, so it's a good use-case imo. There's another great resource here that I think is one of the best explainers of UUIDv7: https://antonz.org/uuidv7/ https://antonz.org/uuidv7/ Whatever you do, don't implement the cursed 36-bit whole-second based time UUIDv7 variant that you occasionally see on StackOverflow / blog posts, stick to 48!
- tomComb 2y ago
  This looks great, thanks. But I think gists are better for unimportant stuff - for this I think it deserves its own repo.
  - nikisweeting 2y ago
    It's in the ArchiveBox git repo and I may give it it's own library eventually, but for quick linking it's easier to read / less dependent on the rest of that codebase as a standalone script.
Dylan16807 2y ago
> 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 (filling rand_a above), which in practice is too precise to contain two UUIDv7s generated in the same process. A millisecond divided by 4096 is not a nanosecond. It's about 250 nanoseconds.
dfee 2y ago
I have an implementation function that computes N v7 UUIDs, sorts them, and returns them. This makes testing possible. Collection<UUID> generate(final int count); I also have an interface that I can back with a RNG that generates auto incrementing values, sorts for testing, I have the experience of ints, but for production, my non-timestamp component is random.
wslh 2y ago
This post makes me think (keep thinking) if we can use a solution that I used for another project in another context: using a Cryptographic Feistel Network to compute UUIDS so they are reversible if you need to know the original order. Each entity uses another key for the generation but if they know the keys they can know the order of the other party. Basically is using an existing cryptographic function if the block size is the same and if not adaping it to a specific block size via a Feistel Network.
hardwaresofton 2y ago
Been waiting for UUIDv7 for years -- maybe it's time to archive pg_idkit[0], or maybe instead just switch the UUIDv7 version to do the native thing rather than the Rust code. [0]: https://github.com/VADOSWARE/pg_idkit https://github.com/VADOSWARE/pg_idkit
kagitac 2y ago
After reading this I went ahead and added the extra 12 bits to my Go UUID library. Thanks for the write up on the PostgreSQL patch. If anyone is interested, here is the package: https://github.com/cmackenzie1/go-uuid https://github.com/cmackenzie1/go-uuid. It also includes a CLI similar to that of `uuidgen`, but supports version 7.

What is a v7 UUID. Why do we need more than 1. uuid from a random seed and 2. one derived from that and a timestamp (orderable)

n2d4 2y ago
UUID v7 is the latter, whereas v4 is the former. All the other versions are somewhat legacy, and you shouldn't use them in new systems (besides v8, which is "custom format UUID", if you need that.)
- elehack 2y ago
  UUID v5 is quite useful if you want to deterministically convert external identifiers into UUIDS — define a namespace UUID for each potential identifier source (to keep them separate), then use that to derive a V5 UUID from the external identifier. It's very useful for idempotent data imports.
  - jandrewrogers 2y ago
    Both UUIDv3 and UUIDv5 are prohibited for some use cases in some countries (including the US), which is something to be aware of. Unfortunately, no one has created an updated standard UUID that uses a hash function that is not broken. While useful it is not always an option.
    - kbolino 2y ago
      Could you provide an example of such a prohibition? I've never heard of that before. I doubt that the quality of the hash function is the real issue. The problem with MD5 and SHA1 is that it's easy (for MD5) and technically possible (for SHA1) to generate collisions. That makes them broken for enforcing message integrity. But a UUID is not an integrity check. Both MD5 and SHA1 are still very good as non-cryptographic hash functions. While a hash-based UUID provides obfuscation, it isn't really a security mechanism. Even the existence of UUIDv5 feels like a knee-jerk reaction from when MD5 was "bad" but SHA1 was still "good". No hash function will protect you against de-obfuscation of low-entropy inputs. I can feed your social security number through SHA3-512 but it's not going to make it any less guessable than if I fed it through MD5. Moreover, a UUID only has 122 bits of usable space. Even if we defined a new SHA2- or SHA3-based UUID version, it's still going to have to truncate the hash output to less than half of its full size. This significantly alters the security properties of the hash function, though I'm not sure if much cryptanalysis has been done on the shorter forms to see if they're more practically breakable yet. There is one area where the collision resistance of the hash function could be a concern, though. If all of the inputs to the hash are under the control of a potential attacker, then maliciously constructed data could produce the same UUID. I still wouldn't think this would be a major issue, since most databases will fail to insert a duplicate key, but it might allow for various denial of service attacks. This still feels like quite a niche risk, though, and very circumstance-dependent.
      - jandrewrogers 2y ago
        Systems where a sophisticated attacker may engineer collisions are precisely why UUIDv3/5 are prohibited. SHA1 is deemed broken by some government authorities and not to be used in any critical systems, including as UUID (this is where I’ve seen it expressly prohibited). The entire point of UUIDs in many systems is that collisions should be impossible, system integrity is predicated on it. Many systems exist in a presumptively adversarial environment. Similarly, UUIDv4 is also prohibited in many contexts because people using weak entropy sources has been a recurring problem in real systems. It isn’t a theoretical issue, it has actually happened repeatedly. Decentralized generation of UUIDv4 is not trusted because humans struggle to implement it correctly, causing collisions where none are expected. There are also contexts where probabilistic collision resistance is disallowed because collision probabilities, while low, are high enough to be theoretically plausible. Most people aren’t working on systems this large yet. Ironically, there are many reasonable ways to construct reasonable and secure 128-bit identity values but the standards don’t define one. Some flavor of deterministic generation + encryption are not uncommon but they are also non-standard. That said, many companies unavoidably have a mix of standard and non-standard UUIDs internally. To mitigate collisions, they have to transform those UUIDs into something else UUID-like, at which point it is pretty much guaranteed to be non-standard. Not ideal but that is the world we live in.
        kbolino 2y ago
        Ok, that makes sense. As far as I can tell, even truncated to "just" 122 bits, there's still no known way to generate a SHA-256 collision, so the MD5/SHA1 versions are comparatively vulnerable vs an hypothetical SHA256 UUID version. However, it's starting to feel like UUIDs may not be long enough in general to meet the need for secure, distributed ID generation.
cube2222 2y ago
UUID v7 is what you numbered #2. For the others, it’s best to read up on Wikipedia[0]. I believe they all have their unique use-cases and tradeoffs. E.g. including information about which node of the system generated an ID. [0]: https://en.m.wikipedia.org/wiki/Universally_unique_identifier https://en.m.wikipedia.org/wiki/Universally_unique_identifie...
mind-blight 2y ago
A deterministic uuid based off of a hash of bits is also very useful (UUID5). I've used that for deduping records from multiple sources
chimpontherun 2y ago
As it is usual in many areas of human endeavor, newcomers to the field tend to criticize design decisions that were made before them, only to re-invent what was already invented. Sometimes it leads to improvements in the field, via rejection of the accumulated legacy crud, or just simply affording a new perspective. Most other times it's a well-intentioned, but low-effort noise. I, personally, do it myself. This is how I learn.
paulddraper 2y ago
[dead]
urronglol 2y ago
[flagged]
- purerandomness 2y ago
  It comes off as a low-effort question that seems to try to evoke a reply from a peer, while it's the kind of question that is best answered by an LLM, Google, or Wikipedia.
  - a3w 2y ago
    Well, a LLM could answer it or write total bullshit. But yes, Wikipedia or other research quick internet research will help generally. And excactly here, it will tell you that there are competing standards since we have competing use cases.
    - kraftman 2y ago
      For simple questions like this with unambiguous answers, it is statistically very unlikely that you'll get a bullshit answer from an LLM.
- treve 2y ago
  I didn't downvote you, but the terseness made it for me immediately come off as a kind of criticism, e.g.: "Why would we ever need it". May not have been your intent but if it was a genuine question, form matters.
  - urronglol 2y ago
    Be kind. Don't be snarky. Converse curiously; don't cross-examine. Edit out swipes. Comments should get more thoughtful and substantive, not less, as a topic gets more divisive. When disagreeing, please reply to the argument instead of calling names. "That is idiotic; 1 + 1 is 2, not 3" can be shortened to "1 + 1 is 2, not 3." Please don't fulminate. Please don't sneer, including at the rest of the community. Please respond to the strongest plausible interpretation of what someone says, not a weaker one that's easier to criticize. Assume good faith.

deleted 2y ago

[deleted]

kingkilr 2y ago

I would strongly implore people not to follow the example this post suggests, and write code that relies on this monotonicity. The reason for this is simple: the documentation doesn't promise this property. Moreover, even if it did, the RFC for UUIDv7 doesn't promise this property. If you decide to depend on it, you're setting yourself up for a bad time when PostgreSQL decides to change their implementation strategy, or you move to a different database. Further, the stated motivations for this, to slightly simplify testing code, are massively under-motivating. Saving a single line of code can hardly be said to be worth it, but even if it were, this is a problem far better solved by simply writing a function that will both generate the objects and sort them. As a profession, I strongly feel we need to do a better job orienting ourselves to the reality that our code has a tendency to live for a long time, and we need to optimize not for "how quickly can I type it", but "what will this code cost over its lifetime".

3eb7988a1663 2y ago
I too am missing the win on this. It is breaking the spec, and does not seem like it offers a significant advantage. In the eventual event where you have a collection of UUID7 you are only ever going to be able to rely on the millisecond precision anyway.
- sbuttgereit 2y ago
  You say it's breaking the spec, but is it? From https://www.rfc-editor.org/rfc/rfc9562.html#name-uuid-version-7 https://www.rfc-editor.org/rfc/rfc9562.html#name-uuid-versio...: "UUIDv7 values are created by allocating a Unix timestamp in milliseconds in the most significant 48 bits and filling the remaining 74 bits, excluding the required version and variant bits, with random bits for each new UUIDv7 generated to provide uniqueness as per Section 6.9. Alternatively, implementations MAY fill the 74 bits, jointly, with a combination of the following subfields, in this order from the most significant bits to the least, to guarantee additional monotonicity within a millisecond: 1. An OPTIONAL sub-millisecond timestamp fraction (12 bits at maximum) as per Section 6.2 (Method 3). 2. An OPTIONAL carefully seeded counter as per Section 6.2 (Method 1 or 2). 3. Random data for each new UUIDv7 generated for any remaining space." Which the referenced "method 3" is: "Replace Leftmost Random Bits with Increased Clock Precision (Method 3): For UUIDv7, which has millisecond timestamp precision, it is possible to use additional clock precision available on the system to substitute for up to 12 random bits immediately following the timestamp. This can provide values that are time ordered with sub-millisecond precision, using however many bits are appropriate in the implementation environment. With this method, the additional time precision bits MUST follow the timestamp as the next available bit in the rand_a field for UUIDv7."

fngjdflmdflg 2y ago

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

paulddraper 2y ago
Depends if you think sub-millisecond locality is significant.
londons_explore 2y ago
I could imagine that certain nanoseconds might be vastly more likely than other nanoseconds. For example, imagine you have a router that sends network packets out at the start of each microsecond, synced to wall time. Or the OS scheduler always wakes processes up on a millisecond timer tick or some polling loop. Now, when those packets are received by a postgres server and processed, the time to do that is probably fairly consistent - meaning that X nanoseconds past the microsecond you probably get most records being created.
- UltraSane 2y ago
  But only one nanosecond slower or faster and you get another set of 4.611 billion billion random IDs. I think random variations in buffer depths and CPU speeds will easily introduce hundreds of nanoseconds of timing variations. syncing any two things to less than 1 nanosecond is incredibly hard and doesn't happen by accident.
  - zamadatix 2y ago
    The important part is the events in time aren't going to be as random as the actual random source. The chances of an actual collision remain low but the distribution of events over time is a weaker (in relative terms) source of random bits compared to proper "random" sources which won't have obvious bias at all.

deleted 2y ago

[deleted]

dotdi 2y ago

My org has been using ULID[0] extensively for a few years, and generally we've been quite happy with it. After initially dabbing with a few implementations, I reimplemented the spec in Kotlin, and this has been working out quite well for us. We will open-source our implementation in the following weeks. ULID does specifically require generated IDs to be monotonically increasing as opposed to what the RFC for UUIDv7 states, which is a big deal IMHO. [0]: https://github.com/ulid/spec https://github.com/ulid/spec

willvarfar 2y ago
Having used a lot of the ULID variants that the UUIDv7 spec cites as prior art, including the ULID spec you link to, I've gotta say that UUIDv7 has some real advantages. The biggest advantage is that it is hex. Haven't yet met a database system that doesn't have functions for substr and from_hex etc, meaning you can extract the time part using vanilla sql. ULID and others that use custom variants of base32 or base62 or whatever are just about impossible to wrangle with normal tooling. Your future selfs will thank you for being able to manipulate it in whatever database you use in the future to analyse old logs or import whatever data you generate today.
- mixmastamyk 2y ago
  Aren't they stored as 16 bytes in binary? How to format it later as text is then your choice.
  - WorldMaker 2y ago
    It's that eternal push/pull "war" between "we need a sproc that can report this directly from the SQL server" and "please don't do things directly on the SQL server because you'll route around important application code" and "it's a feature not a bug that you can't just look things up by ID in the DB without a little bit of extra work". I did work on a project using ULIDs in SQL Server. They were stored in uniqueidentifier fields with a complex byte swap from ULID to fake-UUID to get better storage/indexing out of SQL Server [1]. There was an attempt to use SQL Functions to display/search the ULID form directly in the database, but it was never as bug free as the C# byte order code and so it was definitely not recommended doing it directly in the DB and that if a "report" was missing it should be a part of the application (which was already almost nothing but a bloated "reporting" tool) or in a related "configuration" application. It did feel more like a feature than a bug because it did keep some meddling and drama out of the DB. I also see the arguments for why in some different types of applications it makes debugging a lot harder and those arguments make sense and it is definitely a trade-off to consider. [1] The rabbit hole into SQL Server's ancient weird UUID sort order: https://devblogs.microsoft.com/oldnewthing/20190426-00/?p=102450 https://devblogs.microsoft.com/oldnewthing/20190426-00/?p=10...

lordofgibbons 2y ago

What benefit does this have over something like Twitter's Snowflake, which can be used to generate distributed monotonically increasing IDs without synchronization? We've been using an implementation of it in Go for many years in production without issues.

WorldMaker 2y ago
UUIDv7 interoperates with all the other versions of UUID. The v7 support in Postgres doesn't add a new column type, it makes the existing column type more powerful/capable. Applications that had been using UUIDv4 everywhere can get cheap Snowflake-like benefits in existing code just from switching the generator function. Most languages have a GUID or UUID class/struct that is compatibly upgradable from v4 to v7, too.
- akvadrako 2y ago
  Snowflake is a 64 bit integer. It doesn't need a new column type and works everywhere.

pphysch 2y ago

The naming of "rand_a" and "rand_b" in the spec is a bit misleading. They don't have to be generated randomly. I'm sure there's a historical reason for it. "extra_" or "distinct_" would be a more accurate prefix for UUIDv7. UUIDv7 is actually quite a flexible standard due to these two underspecified fields. I'm glad Postgres took advantage of that!

mmerickel 2y ago

Remember even if timestamps may be generated using a monotonically increasing value that does not mean they were committed in the same order to the database. It is an entirely separate problem if you are trying to actually determine what rows are "new" versus "previously seen" for things like cursor-based APIs and background job processing. This problem exists even with things like a serial/autoincrement primary key.

shalabhc 2y ago
+1 What could be useful here is if postgres provided a way to determine the latest frozen uuid. This could be a few ms behind the last committed uuid but should guarantee that no new rows will land before the frozen uuid. Then we can use a single cursor track previously seen.

willvarfar 2y ago

Ordering for UUIDv7s in the same millisecond is super useful when some rows represent actions and others reactions. I have used this guarantee for events generated on clients. It really simplifies a lot of reasoning.

fc417fc802 2y ago
[dead]

Glyptodon 2y ago

On one hand I too am looking forward to more widespread use of UUIDv7, but on the other I don't really get the problem this is solving for their spec. If you care about timestamp ordering I don't think doing it in a way that forces you to fake a PK if you insert an earlier dated record at a future point makes sense. But I guess I'm implicitly assuming that human meaningful dates differ from insertion times in many domains.

samatman 2y ago

I maintain that people are too eager to use UUIDv7 to begin with. It's a dessert topping and a floor wax. Let's say you need an opaque unique handle, and a timestamp, and a monotonically increasing row ID. Common enough. Do they have to be the same thing? Should they be the same thing? Because to me that sounds like three things: an autoincrementing primary key, a UUIDv4, and a nanosecond timestamp. Is it always ok that the 'opaque' unique ID isn't opaque at all, that it's carrying around a timestamp? Will that allow correlating things which maybe you didn't want hostiles to correlate? Are you 100% sure that you'll never want, or need, to re-timestamp data without changing its global ID? Maybe you do need these things unnormalized and conflated. Do you though? At least ask the question.

user3939382 2y ago
Re-timestamp would be a new one for me. What’s a conceivable use case? An NTP fault?
fastball 2y ago
Also if you have a nanosecond timestamp, do you actually need a monotonically increasing row ID? What for?
- peferron 2y ago
  Perhaps as a tie breaker if you insert multiple rows in a table within one transaction? In this situation, the timestamp returned by e.g. `now()` refers to the start of the transaction, which can cause it to be reused multiple times.
  - fastball 2y ago
    I meant in a context with a random ID and a timestamp. If you need to retrieve rows by time/order, you use the timestamp. If you need a row by ID, you use the ID. The use-cases where you actually need to know which row was inserted first seem exceedingly rare (mostly financial / auditing applications), and even then can probably be handled with separate transactions (as you touch on).

scrollaway 2y ago

UUID7 is excellent. I want to share a django library I wrote a little while back which allows for prefixed identity fields, in the same style as Stripe's ID fields (obj_XXXXXXXXX): https://github.com/jleclanche/django-prefixed-identity-field/ https://github.com/jleclanche/django-prefixed-identity-field... This gives a PrefixedIdentityField(prefix="obj_"), which is backed by uuid7 and base58. In the database, the IDs are stored as UUIDs, which makes them an efficient field -- they are transformed into prefixed IDs when coming out of the database, which makes them perfect for APIs. (I know, no documentation .. if someone wants to use this, feel free to file issues to ask questions, I'd love to help)

nikisweeting 2y ago

I implemented this in pure Python a few days ago in case anyone finds it helpful, here it is: https://gist.github.com/pirate/7e44387c12f434a77072d50c52a3d18e https://gist.github.com/pirate/7e44387c12f434a77072d50c52a3d... My implementation supports graceful degradation between nanosecond scale resolution, microsecond, and millisecond, by using 12 bits for each and filling up the leftmost bits of rand_a and rand_b. Not all environments provide high resolution system clocks with no drift, so it's is important to maintain monotonicity when generating IDs with a low-res timestamp as input. You still want the bits that would've held the nanosecond value to be monotonic. Neither of the existing uuid_utils and uuid7 python libs that can generate UUID7s support this monotonicity property. Am planning on using this for ArchiveBox append-only "snapshot" records, which are intrinsically linked to time, so it's a good use-case imo. There's another great resource here that I think is one of the best explainers of UUIDv7: https://antonz.org/uuidv7/ https://antonz.org/uuidv7/ Whatever you do, don't implement the cursed 36-bit whole-second based time UUIDv7 variant that you occasionally see on StackOverflow / blog posts, stick to 48!

tomComb 2y ago
This looks great, thanks. But I think gists are better for unimportant stuff - for this I think it deserves its own repo.
- nikisweeting 2y ago
  It's in the ArchiveBox git repo and I may give it it's own library eventually, but for quick linking it's easier to read / less dependent on the rest of that codebase as a standalone script.

Dylan16807 2y ago

> 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 (filling rand_a above), which in practice is too precise to contain two UUIDv7s generated in the same process. A millisecond divided by 4096 is not a nanosecond. It's about 250 nanoseconds.

dfee 2y ago

I have an implementation function that computes N v7 UUIDs, sorts them, and returns them. This makes testing possible. Collection<UUID> generate(final int count); I also have an interface that I can back with a RNG that generates auto incrementing values, sorts for testing, I have the experience of ints, but for production, my non-timestamp component is random.

wslh 2y ago

This post makes me think (keep thinking) if we can use a solution that I used for another project in another context: using a Cryptographic Feistel Network to compute UUIDS so they are reversible if you need to know the original order. Each entity uses another key for the generation but if they know the keys they can know the order of the other party. Basically is using an existing cryptographic function if the block size is the same and if not adaping it to a specific block size via a Feistel Network.

hardwaresofton 2y ago

Been waiting for UUIDv7 for years -- maybe it's time to archive pg_idkit[0], or maybe instead just switch the UUIDv7 version to do the native thing rather than the Rust code. [0]: https://github.com/VADOSWARE/pg_idkit https://github.com/VADOSWARE/pg_idkit

kagitac 2y ago

After reading this I went ahead and added the extra 12 bits to my Go UUID library. Thanks for the write up on the PostgreSQL patch. If anyone is interested, here is the package: https://github.com/cmackenzie1/go-uuid https://github.com/cmackenzie1/go-uuid. It also includes a CLI similar to that of `uuidgen`, but supports version 7.