Hacker News Reader

Comment by samatman | Hacker News Reader

I've said this before on HN, but we're talking about UUID v7 again, so it bears repeating: prefer UUID v4 for unique keying, of the sort that's transferable between databases. For timestamps, use timestamps, to sort by insertion, use an autoincrementing primary key. Disk space is not so expensive that we can't afford all three of these things.

Conflating timestamps and uniqueness is a conflation. These concerns are best separate. If you put a timestamp in your UUID, your UUID now has a timestamp. You can't remove it if you don't want the timestamp to be a part of the uniqueness any more.

View on HN

UUID7 offers several key advantages:

  - sortable by insertion
  - less vulnerable to sequence prediction attack
  - allows partitioning of tables in the future

Sequence prediction attack is a problem when you want to use identifiers in a public API. For example, a user or competitor can iterate through your product catalogue by incrementing the key. Sorting by inserting is a common use case as well. You can achieve this with an auto-incrementing primary key, however, this will create issues when you need to partition the table. Of course, you may never need this functionality, but there is a reason UUID7 have been added, as they are very useful in certain scenarios.

I get the public API argument, but it certainly feels backwards to structure the internals of your application for that specific use case. Generating and mapping an UUID to your identifier when you need it is pretty straight forward and allows you to avoid exposing the identifier at all.

Which leaves partitioning, something that very few applications will ever need, even if plenty of developers hope they will. A slightly less weird use case, but perfectly doable up to a point using regular sequences.

I see a solution looking for problems.

I have build many successful systems without ever using a GUID. However, at a certain scale they become very handy, and it then it definitely helps you can also sort them.

Just add a verifier: /item?id=123;vf=ixkdS1Xb

Then select * from item where id=123 and vf='ixkdS1Xb'

As a DBRE, I care mostly about DB performance, and not getting paged / blamed for things out of my control.

I fully agree and support that auto-incrementing integers should be used whenever possible. My preference for UUIDv7 over UUIDv4 is solely that they’re less likely to wreak havoc on the DB, if devs insist on having a UUID PK.

In most ORM apps you need to pre-allocate the integers to build the references in the object graph before actually executing these inserts. Doesn't this carry a performance penalty?

I know Django gives you an auto-incrementing integer field named id even if you don’t add one to your model. I have no idea about others, but I would hope they aren’t doing work the DB is already doing for you.

> but I would hope they aren’t doing work the DB is already doing for you.

Sadly this is very much a thing that ORMs do all the time.

(1)

    |------------------------------|-----------------------------|------------|----
    | primaryKey (bigInt-internal) | publicKey (uuidv4-external) | created_at | ...  
    |------------------------------|-----------------------------|------------|----

(2)

    |------------------------------|-----------------------------|------------|----
    | primaryKey (uuidv4-internal) | publicKey (uuidv4-external) | created_at | ...
    |------------------------------|-----------------------------|------------|----

(3)

    |------------------------------|-----------------------------|----
    | primaryKey (uuidv7-internal) | publicKey (uuidv4-external) | ...
    |------------------------------|-----------------------------|----

(4)

    (not recommended)
    |---------------------|----
    | primaryKey (uuidv7) | ...
    |---------------------|----

----

  (1) [X] sortable by insertion, [X] timestamp
  (2) All of (1) and [X] transferable between databases
  (3) Use UUIDv7 as a primary key for internal and UUIDv4 for external. App or SELECT statement will need to extract the timestamp from UUIDv7 if you need to use it. Also, if you're using a DB Client you can't just view the 'created_at' column to get an idea of when a row was created.
  (4) Use UUIDv7 as a primary key for internal & external use.

I don't think you've provided any actual reasons to not use uuidv7 or addressed any of the disadvantages of having multiple keys.

Reply on news.ycombinator.com