UUID v7

https://commitfest.postgresql.org/47/4388/

by Recursing • 2 years ago

231 195 2 years ago

commitfest.postgresql.org

lordgilman2• 2 years ago

Is the headline true at all? It was punted to the next commitfest on 2/1. It could easily keep getting punted like it has been for a ~year now.

coldtea• 2 years ago

Is it that hard to implement? Supporting an additional UUID version in PostgreSQL sounds like the most trivial change to implement (compared to anything that touches core backend, table management, replication, query schedulling, and so on).

lordgilman2• 2 years ago

The patch is already written, it's on that page. The bottleneck in Postgres is reviewer bandwidth which is why it's been moved out of several commitfests.

asabil• 2 years ago

The UUID spec update has not been finalized yet.

It would be quite unfortunate to end up with a UUID v7 in PostgreSQL that’s not quite the standardized one because the patch got merged too quickly.

EDIT: here is the IETF working group page https://datatracker.ietf.org/wg/uuidrev/about/

Their milestone seems to submit the final proposal by March.

bebna• 2 years ago

looks like v7 is basically v1 updated. v6 looks interesting with the db optimisation in mind.

UUIDv7 spec: https://www.ietf.org/archive/id/draft-peabody-dispatch-new-u...

michaelmior• 2 years ago

Systems that do not involve legacy UUIDv1 SHOULD consider using UUIDv7 instead.

The optimization relevant to v6 also applies to v7. The difference is that v7 UUIDs are not directly compatible with v1 UUIDs.

yrro• 2 years ago

FYI that's a link to a now very old internet draft.

The current draft can be found here: https://datatracker.ietf.org/doc/draft-ietf-uuidrev-rfc4122b...

naranha• 2 years ago

Does this stay compatible with the old uuid column type, as uuidv7() uses the same 128 bit format as gen_random_uuid()?

That would mean it's easy to update old apps, as we would only have to change the default value for the columns.

pgaddict3• 2 years ago

Yes, internally it's the same as every other UUID (16 bytes, passed by reference). There's no reason to store / represent it differently.

mplanchard2• 2 years ago

Yes, we have already updated our in-app UUID generation to use v7 UUIDs and are storing them in regular postgres UUID columns (postgres 14). Works great!

ghusbands• 2 years ago

Note that, as jmull says in https://news.ycombinator.com/item?id=39262286 , embedding timestamps in every uuid can potentially expose private information.

Merad• 2 years ago

Yes, you can use v7 today with uuid columns, you just need to add a custom function (or the more performant pg_uuidv7 extension) to generate them.

throw0101a• 2 years ago

Related on the front page, "UUID Benchmark War":

* https://ardentperf.com/2024/02/03/uuid-benchmark-war/

* https://news.ycombinator.com/item?id=39254871

zukzuk• 2 years ago

No thread about UUID is complete without a plug for NanoID! https://github.com/ai/nanoid/blob/main/README.md

__s• 2 years ago

Not sure it's too relevant for postgres, where uuid is 16 bytes in db & can be generated by db

swrobel• 2 years ago

Also not comparable to UUIDv7 because it isn't sortable

sltkr• 2 years ago

That's a lot of words to say: “encode 126 random bits in base64 (url-safe variant)”.

It seems the algorithm is equivalent to:

    function nanoid(alphabet="ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789-_") { 
      return Array.from(crypto.getRandomValues(new Uint8Array(21)), i => alphabet.charAt(i & 63)).join('');
    }

(Maybe String.fromCharCode() is slightly faster than Array.join(), but I doubt it matters much.)

On node.js it's even easier, since url-safe base-64 encoding is supported natively:

    Buffer.from(crypto.getRandomValues(new Uint8Array(16))).toString('base64url').substring(0, 21)

Do we really need an entire Github project dedicated to a 1-liner?

didntcheck2• 2 years ago

Agreed, and I actually share the same view about "UUID" as a named concept itself. I wrote more in another comment, but in summary

* Using uncoordinated random generation in a large address space for IDs is often a very useful idea

* 128 bits is a good rule of thumb for "will never ever collide"

* Making this into a heavyweight "UUID" concept, with it's own bespoke string format, and 7 different standard ways to generate them, feels like a ridiculous waste of cognitive effort that makes such a simple concept appear opaque and magic. If you want to encode other data (timestamp, node ID) in 16 bytes you can still do that of course. There's no need for anyone else to even know. Just do some quick calculations to ensure you haven't eliminated too much entropy

hans_castorp• 2 years ago

I wonder why the size of the table with uuid7 is smaller than the one with uuid4? Both are using the uuid data type.

sparsely• 2 years ago

The main data is the same, it's the primary key which is smaller, presumably a result of the structure of the uuid7 being more regular.

uhoh-itsmaciek• 2 years ago

As a sibling comment guessed, btree indexes in Postgres can store ordered data much more efficiently than random data. Inserting randomly ordered data leads to fragmentation, with lots of empty space on your index pages.

nivertech7• 2 years ago

Nice, but somewhat pointless, since the UUID (or any other type of ID representing an external entity) better be generated by the actor creating that entity, i.e. the FE client, or in the worst case, the BE/application server. If it is a database server generated ID, it could as well be a serial autoincrement bigint ;)

EDIT:

For those who misunderstood: I'm very much pro-UUID, and against serial autoincrement server-generated IDs. With the exception when you need to heavily optimize for speed and/or index storage space. And even then there are hybrid solutions like using UUID externally, and serial IDs internally.

https://news.ycombinator.com/item?id=39261435

arghwhat5• 2 years ago

There are many reasons for not using a bigserial index, such as avoiding the data leak associated with publicly visible sequential database indexes. These reasons apply regardless of where an index is generated.

Generating IDs away from the database is mainly done due to design restrictions demanding it, not because it is beneficial.

beeboobaa2• 2 years ago

Trusting your clients to generate your database IDs... What could go wrong?

piaste• 2 years ago

Who said anything about trust? Server-side validation still applies; you don't just DELETE /user/{id} without verifying ownership, regardless of where the id comes from.

But client-generated IDs make idempotency easier and remove whole classes of errors. They're typically a huge win.

nivertech7• 2 years ago

1. you're not trusting anyone

2. the UUID by itself doesn't authenicate or authorize anything

3. there is a small chance of collision, and it can be handled on the backend/persistance/DB layer, i.e. return error to the client in case of collision and ask to generate a new UUID

4. many non-trivial and/or CQRS/ES apps work like this

5. if you are really paranoid, you can push down UUID generation logic to BFF (Backend-For-Frontend) layer

6. Lots of DistSys problems can be solved with client-generated IDs. But most people mistakenly think that DistSys applies to backend only, and exclude clients from the picture.

7. Scaling RDBMS (especially Postgres) is hard. UUID generation is slower than serial bigint, so it's best to keep it outside DB layer fo this reason also.

8. Client-generated UUIDs help to make client requests idempotent, and enable error handling with retries (although it's better to add an additional layer of request idempotency with IDEMPOTENCY_KEY HTTP header, or GraphQL Relay's clientMutationID).

tsarchitect2• 2 years ago

why not both? Have the table column autogenerate UUID if insert does not have one or else insert if it does. This provides an added flexibility for scenarios mentioned in the comments.

mythhabit• 2 years ago

A serial auto increment bigint is not globally unique. A traditional uuid, is not sequential. This particular uuid are both globally unique and increasing and can be used for primary key. It simplifies certain schemas and architectures.

kiitos7• 2 years ago

Clients have/supply meaningful data. An ID isn't meaningful data.

nivertech7• 2 years ago

Parents have/supply meaningful data. A name of their newborn child isn’t meaningful data ;)

s4i2• 2 years 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• 2 years 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• 2 years 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• 2 years 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/515a0440e3e40efeb234e126...

s4i2• 2 years ago

Thank you!

didntcheck2• 2 years 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

pixl97• 2 years ago

Because it turns out that trying to index/sort things by UUID doesn't work great. UUID, at least somewhere after version one isn't just some large number. Different parts of the field have different meanings depending on the specification.

hombre_fatal• 2 years 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!

jandrewrogers5• 2 years ago

In practice, UUIDs are treated as an opaque 128-bit field. In any sufficiently complex system, there is no practical way to standardize on a single blessed version. Furthermore, all of the standardized UUIDs are deficient in various ways for some use cases, so there are a large number of UUID-like types used in large enterprises that are not "standard" UUIDs but which are better fit for purpose. This is deemed okay because there is no way to even standardize on a single UUID version among the official ones. Furthermore, there are environments where some subset of the UUID standard types (including each of v3/v4/v5 in various contexts) are strictly forbidden for valid security reasons.

The practical necessity of mixing UUID versions, along with other 128-bit UUID-like values, means that the collision probabilities are far higher in many non-trivial systems than in the ideal case of having a single type of 128-bit identifier. There is a whole separate element of UUID-like type engineering that happens around trying to mitigate collision probabilities when using 128-bit identifiers from different sources, some of which you may not control.

Having 128-bits is the only common thread across these identifiers which everyone seems to agree on.

zeroxfe• 2 years ago

I think you're drastically underestimating the purpose and management of UUIDs in large scale systems.

If you're building for a single application or data type, sure do your thing, have at it. If you're trying to coordinate UUID spaces and generation across thousands of different applications and data types, like large data pipelines, then this matters a lot.

Also, having native database support (like indexing, filtering, etc.) improves efficiency for these types of workloads.

figassis• 2 years ago

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

sonium• 2 years ago

You probably should not use UUIDs to start with in your database at least not as an ID. UUIDv7 aims solve some of the issues of UUIDv4 that are even less suitable in for databases. 99% of times using BigInt for an ID is better.

kkzz99• 2 years ago

Huh, why would using uuidv4 be a problem? Collisions?

davydog187• 2 years ago

My understanding is that they cause a lot of page fragmentation, which leads to excessive writes to the WAL

brodo3• 2 years ago

You can't use them as cursors, because they are not inherently ordered like integer ids.

CooCooCaCha5• 2 years ago

Sorry but that’s terrible advice. I’ve worked on projects that started with integer ids and it caused nothing but problems.

giva3• 2 years ago

What kind of problems did you encounter?

cjblomqvist• 2 years ago

There are some nice features of using UUIDs rather than ints. It's been written about before, a few on the top of my head: Client side generation of ids. No risk of faulty joins (using the wrong ids to join 2 tables can never get any hits with UUIDs, it can with ints).

Those two sucks for us right now (planning to move to UUIDs).

mdasen• 2 years ago

No risk of faulty joins

Wouldn't Snowflake IDs also solve that problem? A Snowflake ID will fit within a signed 64-bit int.

https://en.wikipedia.org/wiki/Snowflake_ID

The nice thing about a Snowflake ID is that you can encode it into 11 characters in base 62. If I have a UUID, I'm going to need 22 characters. Maybe that doesn't really matter given that 11 characters isn't something someone will want to be typing anyway and Snowflake IDs do require a bit of extra caution to make sure you don't get collisions (since the number you can make per second is limited to how big your sequence generation is).

dankebitte• 2 years ago

Uniqueness aside, UUIDs for public-facing IDs also prevent enumeration attacks and leaking business information other than timestamps.

throwaway83623• 2 years ago

The "faulty joins" can be solved by having a shared sequence for all tables. A bigint column should be enough for most use cases.

mbork_pl• 2 years ago

One reason you might want not to use integers for stuff like user ids is that you may leak the information about the magnitude of your userbase.

mrits2• 2 years ago

When working with distributed systems or compiling a IDs from different systems it is helpful to make sure the ID is unique

itslennysfault• 2 years ago

I would never advise this. I use UUIDv4 for basically everything. It adds minimal overhead to small systems and adds HUGE benefits if/when you need to scale. If you need to sort by creation date use a "created" column (or UUIDv7 if appropriate).

If your system ever becomes distributed you will sing the praises of whoever choose UUID over an int ID, and if it never becomes distributed UUID won't hurt you.

Note: this is for web systems. If it's embedded systems then the overhead starts to matter and the usefulness of UUID is probably nil.

jandrewrogers5• 2 years ago

It is worth mentioning that the reason UUIDv4 is strictly forbidden in some large decentralized systems is the myriad cases of collisions because the "random number" wasn't quite as random as people thought it was. Far too many cases of people not using a cryptographically strong RNG, both unwittingly or out of ignorance that they need to.

Less of an issue if you have total control of the operational environment and code base, but that is not always the case.

sgarland9• 2 years ago

It adds a lot of overhead at any scale, it’s just that the overhead is hidden due to the absurd speed of modern hardware.

I’ll again point out (I said this elsewhere in a post today on UUIDs) that PlanetScale uses int PKs internally. [0] That is a MASSIVE distributed system, working flawlessly with integers as keys. They absolutely can scale, it just requires more thoughtful data modeling and queries.

[0] https://github.com/planetscale/discussion/discussions/366

akvadrako2• 2 years ago

That's especially true if you care about performance and do a lot of joins, the hit can be over 10%.

nodesocket• 2 years ago

What’s a use-case for a v7 UUID vs using a v4 UUID which is entirely random based? Is it just that v7 includes a timestamp for better sorting?

vlucas• 2 years ago

Yes. UUIDv7 can be sorted by default in order of creation, which helps reduce fragmentation problems of randomness over time.

guptaneil• 2 years ago

Is it just that v7 includes a timestamp for better sorting?

Correct. The sortable nature of UUIDv7 improves database performance and index locality by helping the index be more efficient since rows are inserted in a predictable order instead of scattered randomly.

mrits2• 2 years ago

If nothing else (and there is plenty of else) it saves a lot of time knowing the order something was created when debugging.

mderazon• 2 years ago

Would you recommend starting a new project with UUID v7 ?

politelemon• 2 years ago

If you need some of its attributes.

Some comments: https://news.ycombinator.com/item?id=39261469

https://news.ycombinator.com/item?id=36433481

And have a look at this post from last year: https://news.ycombinator.com/item?id=36438367

cryptos• 2 years ago

If the included timestamp doesn't expose sensitive data, then using UUID v7 is a good default, because it has performance advantages on database operations and items might be sorted by ID in a meaningful way, what is sometimes desired (even if I never used it).

jmull• 2 years ago

Word to the wise: be very careful about adding semantics to unique ids that aren't inherent to the identity of the thing being identified.

Over time conflicts between the id's primary job (uniquely identifying something) and the extra semantics can arise, and the solutions tend to get pretty messy.

Here we have a unique id that embeds a timestamp. The classic conflict here is with privacy/security. A UUIDv7 user id tells you when the user was created. A UUIDv7 of a medical record tells you when some medical event occurred.

There are things whose identity is inherently time-based and not private, so I'm not giving a blanket recommendation to not use these. Just understand what you are signing up for.

For a database, you can use bigints for primary ids but only internally. Then you also have an external random (v4) uuid... and a timestamp if you want, for that matter -- now that it's a separate column, you can expose/hide it on a case-by-case basis, depending on need. So this gets you the benefits of a uuidv7 but maintains flexibility, though at the cost of some complexity and extra bytes/record.

Other conflicts can arise too, and they can be hard to always foresee, so generally be careful about extra semantics in unique ids.

personomas2• 2 years ago

It looks like you're explaining this well, but I still don't understand what you're saying.

sgarland9• 2 years ago

They're saying that in certain circumstances, if your API exposes the PK publicly, it may leak information you don't want leaked (the precise datetime something occurred, in the case of UUIDv7).

If that's an issue for you, you can get around this in a variety of ways, as they mention: you could use an associative table that maps the externally-exposed random ID to an internal-only ID.

nomercy400• 2 years ago

Don't use UUIDv7 if you want to keep the creation time of the event/id/entry private.

remus• 2 years ago

Be careful about combining 2 pieces of information in to 1 column. From the above examples, you may want something to uniquely identify a record in your db and you may want something that tells you when the record was created. If you combine these two things, you then have a problem if you want to give an untrusted party that unique reference without telling them when it was created.

jandrewrogers5• 2 years ago

The event timestamps embedded in the UUID can be correlated with external event streams, or even with other events within the same dataset, to de-anonymize the context of the event associated with the UUID. This is a common class of de-anonymization attack. Anything that allows temporal correlations to be inferred potentially leaks quite a lot of info about the data underlying the unique ids.

spamizbad3• 2 years ago

At my company we had a competitor scrape our API for various businesses. One of the fields was an bson ObjectId that represented when the customer entered our system. This unique identifier encodes a timestamp of its creation.

Our competitor was able to ascertain, based on that timestamp, when our customers contract was up and was (briefly) able to poach some customers by underbidding us until we corrected this.

javier2• 2 years ago

Usually when you want this property it is also a benefit that your events when ordered by primary key, also gives a rough ordering by time, no need for a secondary index

sroussey4• 2 years ago

For good security, you don't leak internal IDs at all, sure. It is rare to find people doing that. And random primary keys (or any key really, and even more so for clustered indexes PK) really trash a db cache and locking systems.

fastball2• 2 years ago

IDs should just be IDs. You should be able to hand out your IDs on the street corner without compromising security. If knowing IDs has a negative impact on security, you've designed your system improperly.

siva72• 2 years ago

For good security, you don't leak internal IDs at all, sure. It is rare to find people doing that.

We must live in a different universe. I'd wager to say that over 90% of all backends leak their primary key when speaking to the front-facing client.

mort962• 2 years ago

And random primary keys (or any key really, and even more so for clustered indexes PK) really trash a db cache and locking systems.

That sounds like a problem which should be solved by making database engines not assume keys have some sane ordering, not by putting timestamps in UUIDs.

BillinghamJ• 2 years ago

I'm not sure why that'd inherently be a problem? Knowledge that some record exists about a medical event at a particular time is not too problematic, compared to _who_ it happened to

joshgel• 2 years ago

very rare diseases may be an exception, especially in known geographic locations. that can become identifiable (and is governed by HIPAA in the US)

akira25014• 2 years ago

A UUIDv7 of a medical record tells you when some medical event occurred.

It tells you when the event was documented. If the event didn't contain a date time stamp itself, I would be highly surprised, because what other value is there in documenting it?

The security problem here is inherent in the practice and your choice of primary key isn't a material factor at all.

Do you imagine there's a public CRUD database with simple Rails style accessors that can drill all the way down to individual event records inside my health information? And that, somehow the leak of a primary key in a URL might give away the fact that _something_ happened to me, medically, 12 days ago?

ivan_gammel2• 2 years ago

And that, somehow the leak of a primary key in a URL might give away the fact that _something_ happened to me, medically, 12 days ago

Just as a side note: it may also leak the location, not just the time. An that is enough e.g. for disproving an alibi or leaking an important commercial secret (if you are in the same location as competitor HQ, for example).

hardwaresofton• 2 years ago

If you like this (I do very much), you might also like pg_idkit[0] which is a little extension with a bunch of other kinds of IDs that you can generate inside PG, thanks to the seriously awesome pgrx[1] and Rust.

[0] https://github.com/VADOSWARE/pg_idkit

[1] https://github.com/pgcentralfoundation/pgrx

personomas2• 2 years ago

When will this be available? (Thanks in advance)

ashconnor• 2 years ago

Useful explanation on UUIDv7 https://buildkite.com/blog/goodbye-integers-hello-uuids

ComputerGuru• 2 years ago

It would be nice to have either fully integrated support for returning the UUIDv7 encoded in crockford's base32 or else ship a crockford's base32 encode/decode functionality in the same release so that this can be compatible with Ulid (given that it's one the primary open source existing works UUIDv7 was modeled after).

simlevesque2• 2 years ago

If you need a uuid v7 right now you can get one here: https://uuidv7.app/

qingcharles• 2 years ago

Thank the Gods. I was almost out.

simlevesque2• 2 years ago

Always glad to help.

samatman• 2 years ago

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.

oxfordmale2• 2 years ago

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.

codr7• 2 years ago

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.

GoblinSlayer• 2 years ago

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

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

klysm• 2 years ago

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

sgarland9• 2 years ago

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.

The_Colonel• 2 years ago

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?

tsarchitect2• 2 years ago

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

UUID v7

Related Stories