5 ms·

The article had a link to the PostgreSQL commitfest for UUIDv7 support, but as far as I can't tell it looks unlikely that it will actually be in PostgreSQL 17.

The article had a link to the PostgreSQL commitfest for UUIDv7 support, but as far as I can't tell it looks unlikely that it will actually be in PostgreSQL 17. The most recent action was the committer being removed from the task and I believe version 17 is already well past feature freeze.... Is my understanding correct? This is what I think is going on, but I can't find any substantiated facts too point me to a definite conclusion. I was hoping to see it in version 17, but can't get a really good read on what's going on with this feature.

klabb3 2y ago
What database support is needed? Assuming Postgres already has the uuid type and that you can (and often should) do the actual generation of them in application code?
- tyre 2y ago
  Why should they be done in application code?
  - eproxus 2y ago
    There’s no need since they are random, so you might as well generate them on your application servers which are easier to scale, to offload the write database.
    - bozey07 2y ago
      IMO marking the row as generated makes its intent clearer. It also means less code - no generating UUIDs, shorter queries = more robust. For my workloads at least, I think it's worth the tradeoff of forcing Postgres to muster 15 random bytes.
      - deleted 2y ago
        [deleted]
- sbuttgereit 2y ago
  > you can (and often should) do the actual generation of them in application code I can absolutely see scenarios where it would be desirable or convenient to create the these IDs in the application code. However, I can equally assert that you can, and often should, let the database be the source of truth for record identity when using database surrogate keys for that purpose... but I won't. The truth is there are many kinds of architectures and many kinds of applications the details of which have a direct bearing on where or where not certain operations should be conducted. In many environments in which I work, the idea of "the application" can be murky. In many enterprise environments databases can be driven by and integrated into many applications, possibly from different software developers and vendors with varying degrees of influence in how they internally operate. In such environments the database can be the great unifier and placing substantial control of data in the database itself can make sense in these heterogenous application environments: but it's very much a circumstantial call. I would expect if there really were one true way to develop application or how to use databases to achieve goals, we could well be asking why they built these functions in the first place... I suspect it's because there are a reasonable number of valid use cases to justify the lift. I'd like to think my use cases fit the envisioned valid use cases thus my interest and question.
  - klabb3 2y ago
    > I can absolutely see scenarios where it would be desirable or convenient to create the these IDs in the application code. I feel like I’m taking crazy pills. Isn’t the whole (or 99% of the) point of uuids that you can generate them client/application side for idempotency, because entity creation starts there and you want an id earlier than the first round trip completion? If they’re generated by the db (with world-knowledge) then you can auto increment a 64 bit is significantly more performant.
jrochkind1 2y ago
That is too bad, I too would be very eager to see natively supported in Postgres. It doesn't seem like it should be so complex to implement on top of pg's existing uuid support, but famous last words?
- sbuttgereit 2y ago
  It looks like the feature had made good progress with the contention really being around the fact that the standard hadn't yet become an RFC. However, the new UUID standard did reach that milestone back in early May, but the feature kinda went radio silent... at least insofar as any public record... up to a couple days ago when the assigned reviewer/committer was removed. But no word on if it's delayed, or there's some problem, etc. It's still tagged as 17... and some of the related work has been committed, but not the UUIDv7 itself so it seems.

klabb3 2y ago

What database support is needed? Assuming Postgres already has the uuid type and that you can (and often should) do the actual generation of them in application code?

tyre 2y ago
Why should they be done in application code?
- eproxus 2y ago
  There’s no need since they are random, so you might as well generate them on your application servers which are easier to scale, to offload the write database.
  - bozey07 2y ago
    IMO marking the row as generated makes its intent clearer. It also means less code - no generating UUIDs, shorter queries = more robust. For my workloads at least, I think it's worth the tradeoff of forcing Postgres to muster 15 random bytes.
    - deleted 2y ago
      [deleted]
sbuttgereit 2y ago
> you can (and often should) do the actual generation of them in application code I can absolutely see scenarios where it would be desirable or convenient to create the these IDs in the application code. However, I can equally assert that you can, and often should, let the database be the source of truth for record identity when using database surrogate keys for that purpose... but I won't. The truth is there are many kinds of architectures and many kinds of applications the details of which have a direct bearing on where or where not certain operations should be conducted. In many environments in which I work, the idea of "the application" can be murky. In many enterprise environments databases can be driven by and integrated into many applications, possibly from different software developers and vendors with varying degrees of influence in how they internally operate. In such environments the database can be the great unifier and placing substantial control of data in the database itself can make sense in these heterogenous application environments: but it's very much a circumstantial call. I would expect if there really were one true way to develop application or how to use databases to achieve goals, we could well be asking why they built these functions in the first place... I suspect it's because there are a reasonable number of valid use cases to justify the lift. I'd like to think my use cases fit the envisioned valid use cases thus my interest and question.
- klabb3 2y ago
  > I can absolutely see scenarios where it would be desirable or convenient to create the these IDs in the application code. I feel like I’m taking crazy pills. Isn’t the whole (or 99% of the) point of uuids that you can generate them client/application side for idempotency, because entity creation starts there and you want an id earlier than the first round trip completion? If they’re generated by the db (with world-knowledge) then you can auto increment a 64 bit is significantly more performant.

jrochkind1 2y ago

That is too bad, I too would be very eager to see natively supported in Postgres. It doesn't seem like it should be so complex to implement on top of pg's existing uuid support, but famous last words?

sbuttgereit 2y ago
It looks like the feature had made good progress with the contention really being around the fact that the standard hadn't yet become an RFC. However, the new UUID standard did reach that milestone back in early May, but the feature kinda went radio silent... at least insofar as any public record... up to a couple days ago when the assigned reviewer/committer was removed. But no word on if it's delayed, or there's some problem, etc. It's still tagged as 17... and some of the related work has been committed, but not the UUIDv7 itself so it seems.