5 ms·

Can someone ELI5 what that "UUID v7 support" actually means in the title? I don't know how to navigate commitfest (nor would I probably understand the source c

by s4i 3y ago

Can someone ELI5 what that "UUID v7 support" actually means in the title? I don't know how to navigate commitfest (nor would I probably understand the source code to begin with), but the reason I'm confused is that you can already use all the proposed draft UUID implementations in Postgres (as long as you generate the ID application-side). In fact, PG will happily accept any 128 bit ID to be inserted into a UUID column, as long as it's hex encoded – even the dashes are optional.

Skinetio 3y ago
Yes my expectation is that Postgres can do that than for you. Its still easier to have an autogenerate on than doing it externally
Scandiravian 3y ago
Postgres can now auto-generate uuidv7, so you can define it in your schema and don't have to add the ID on the client side when doing an insert
wongarsu 3y ago
The patch is adding functions for generating UUIDv7 in the database. Which you can already do, for example with the PL/pgSQL function from [1]. Or you can just generate them in your application code. As you mentioned everything else about UUIDv7 already works and didn't require any changes. It's really more about convenience, and maybe a bit of speed. 1: https://gist.github.com/fabiolimace/515a0440e3e40efeb234e12644a6a346 https://gist.github.com/fabiolimace/515a0440e3e40efeb234e126...
- s4i 3y ago
  Thank you!
didntcheck 3y ago
I'm amazed at how "we" have managed to turn such a simple idea as "128 bits is a large enough address space for uncoordinated generation to be essentially collision free" into such a "heavy" concept with 7 different versions If you want to do something smart like encoding your node ID within the value, or prefixing a timestamp for sortability, then sure, do that in your application. No one else really needs to care how you produced your 16 bytes. Just do some napkin math to make sure you're keeping sufficient entropy I'm not sure "UUID" even needed to be a column type, versus a "INT16" and some string formatting/parsing functions for the conventional representation (should you choose to use that in your application). You could also put IPv6 addresses in the same type. Though I guess this depends on how much you think the database should encode intention versus raw storage in types
- hombre_fatal 3y ago
  > No one else really needs to care how you produced your 16 bytes UUID isn't about how it's done, it's about what it is. Instead of everyone doing something differently, everyone can just comply with UUID. Instead of having to repeat it across your docs that the IDs of this entity are sortable, you can just say they are UUIDv7. If someone wants to extract the timestamp from your ID, they don't need to figure out which (32, 48, 50?) bits are the timestamp nor what resolution the timestamp has because you can tell them UUIDv7. You don't have to write your own validation functions because you can tell the database that this hex string is a UUID and it can do it for you. You're probably making the case for Sqlite here which is very minimal, but something more full-featured like Postgres, I prefer these conveniences. I can tell because whenever I use Sqlite in a case where I could've used Postgres, I regret it!
  - 015a 3y ago
    But I feel the point is: None of that is a relevant concern IDs should take on. Most functional things related to e.g. embedding the record creation time within the ID is one of those "that's cool, but I've never seen anyone do it" kind of things. If you need to sort records by when they were created, there are probably three or four happened_at fields on the record you'd use (created_at in this case). If you need the exact time; those are there for that. Counter-argument: Well, you can save a few bytes on every record by getting rid of the created_at field and just using a UUIDv7. Maybe, but I've never seen anyone do it. What if you need to change the time the record was created? Are you planning to explain to all your integration providers the process of extracting a timestamp from a UUIDv7? What if you need to run complex SQL timestamp functions on created_at? Etc. Its cool, but it never actually happens. Once we enter the domain of "using the node id or timestamp or something to reduce the probability of ID collision", that's a totally reasonable responsibility within an ID's set of concerns. But, that's a very different need. > You don't have to write your own validation functions Why are we validating IDs? > but something more full-featured like Postgres, I prefer these conveniences. Agreed. I am a vocal UUID hyper-hater. UUIDs should be destroyed, and humanity would be (oh so slightly) better off if they had never existed. But, they're still a thing, and I think its cool that databases have hyper-specific types like this. My wish is Postgres would have other more sane automatic ID types and gen capability, in addition to uuid & autoincrement.
figassis 3y ago
On a distributed application, your application might need proper time synchronization for the time component. Not when it is generated DB side.

by s4i 3y ago

Skinetio 3y ago

Yes my expectation is that Postgres can do that than for you. Its still easier to have an autogenerate on than doing it externally

Scandiravian 3y ago

Postgres can now auto-generate uuidv7, so you can define it in your schema and don't have to add the ID on the client side when doing an insert

wongarsu 3y ago

The patch is adding functions for generating UUIDv7 in the database. Which you can already do, for example with the PL/pgSQL function from [1]. Or you can just generate them in your application code. As you mentioned everything else about UUIDv7 already works and didn't require any changes. It's really more about convenience, and maybe a bit of speed. 1: https://gist.github.com/fabiolimace/515a0440e3e40efeb234e12644a6a346 https://gist.github.com/fabiolimace/515a0440e3e40efeb234e126...

s4i 3y ago
Thank you!

didntcheck 3y ago

I'm amazed at how "we" have managed to turn such a simple idea as "128 bits is a large enough address space for uncoordinated generation to be essentially collision free" into such a "heavy" concept with 7 different versions If you want to do something smart like encoding your node ID within the value, or prefixing a timestamp for sortability, then sure, do that in your application. No one else really needs to care how you produced your 16 bytes. Just do some napkin math to make sure you're keeping sufficient entropy I'm not sure "UUID" even needed to be a column type, versus a "INT16" and some string formatting/parsing functions for the conventional representation (should you choose to use that in your application). You could also put IPv6 addresses in the same type. Though I guess this depends on how much you think the database should encode intention versus raw storage in types

hombre_fatal 3y ago
> No one else really needs to care how you produced your 16 bytes UUID isn't about how it's done, it's about what it is. Instead of everyone doing something differently, everyone can just comply with UUID. Instead of having to repeat it across your docs that the IDs of this entity are sortable, you can just say they are UUIDv7. If someone wants to extract the timestamp from your ID, they don't need to figure out which (32, 48, 50?) bits are the timestamp nor what resolution the timestamp has because you can tell them UUIDv7. You don't have to write your own validation functions because you can tell the database that this hex string is a UUID and it can do it for you. You're probably making the case for Sqlite here which is very minimal, but something more full-featured like Postgres, I prefer these conveniences. I can tell because whenever I use Sqlite in a case where I could've used Postgres, I regret it!
- 015a 3y ago
  But I feel the point is: None of that is a relevant concern IDs should take on. Most functional things related to e.g. embedding the record creation time within the ID is one of those "that's cool, but I've never seen anyone do it" kind of things. If you need to sort records by when they were created, there are probably three or four happened_at fields on the record you'd use (created_at in this case). If you need the exact time; those are there for that. Counter-argument: Well, you can save a few bytes on every record by getting rid of the created_at field and just using a UUIDv7. Maybe, but I've never seen anyone do it. What if you need to change the time the record was created? Are you planning to explain to all your integration providers the process of extracting a timestamp from a UUIDv7? What if you need to run complex SQL timestamp functions on created_at? Etc. Its cool, but it never actually happens. Once we enter the domain of "using the node id or timestamp or something to reduce the probability of ID collision", that's a totally reasonable responsibility within an ID's set of concerns. But, that's a very different need. > You don't have to write your own validation functions Why are we validating IDs? > but something more full-featured like Postgres, I prefer these conveniences. Agreed. I am a vocal UUID hyper-hater. UUIDs should be destroyed, and humanity would be (oh so slightly) better off if they had never existed. But, they're still a thing, and I think its cool that databases have hyper-specific types like this. My wish is Postgres would have other more sane automatic ID types and gen capability, in addition to uuid & autoincrement.

figassis 3y ago

On a distributed application, your application might need proper time synchronization for the time component. Not when it is generated DB side.