UUIDv7 Comes to PostgreSQL 18

https://www.thenile.dev/blog/uuidv7

by sierikov • 10 months ago

125 67 10 months ago

thenile.dev

Recursing• 10 months ago

Interesting comment from a previous thread on UUIDv7 in Postgres: https://news.ycombinator.com/item?id=39262286

bearjaws7• 10 months ago

It's 300% more likely the competitor took out one of the sales guys and had him dump the Salesforce database.

This happens way more often than companies want to admit.

OP unsurprisingly left out the details of how they caught them.

ajdude• 10 months ago

OP give more context in one of the child comments:

Our founder asked me "Is there anything we're sending over the API that might clue someone in when someone signs up? Are we sending a createdAt field or something?" and I said "No, but we do have a timestamp in one of the IDs..." -- well, we removed the field and this behavior stopped soon after.

jph• 10 months ago

We evaluated UUIDv7 and determined that it's unwise to use it as a primary key.

We have applications where we control the creation of the primary key, and where the primary key will be exposed to end users, such as when using a typical web app framework built with Rails, Phoenix, Loco, Laravel, etc. For these applications, UUIDv7 time is too problematic for security, so we prefer binary-stored UUIDv4 even though it's less efficient.

We also have applications where we control the creation of the primary key, and where we can ensure the primary key is never shown to users. For these applications, UUIDv7 is slower at inserts and joins, so we prefer BIGSERIAL for primary key, and binary-stored UUIDv4 for showing to users such as in URLs.

wpollock• 10 months ago

I wonder if the issue is with exposing internal IDs to end users. I'm sure the experts here have already thought of this, but could someone explain why using encryption or even an HMAC for external views of a primary key doesn't make sense? Maybe because the extra processing is more expensive than just using UUIDv4? Using a KDF such as argon2id on the random bits of a UUIDv7 seems like it might work well for external IDs.

(And why the heck are different types or variants of UUIDs called "versions"?)

gfody3• 10 months ago

but could someone explain why using encryption or even an HMAC for external views of a primary key doesn't make sense?

it does make sense and it's what you should do instead of using a UUID as PK for this purpose.

Hizonner5• 10 months ago

Because now, for the rest of eternity, every single person who writes any code that moves data from this table to somewhere else, for any purpose, has to remember that the primary key gives away the creation time of something, which can potentially be linked to something else. A lot of people won't notice that, and a lot of people who do notice it will get the remediation wrong. And you can now forget using a simple view on the database to give any information to any person or program that shouldn't get the creation times.

You've embrittled your system.

bri3d• 10 months ago

It does make all kinds of sense and is a majorly underutilized tool.

kasperset• 10 months ago

Currently evaluating UUIDv7 as primary key for some inventory origin. I think it should be ok to use it for such use case since it will indicate the time of creation? Any thoughts?

gtowey• 10 months ago

You have to ask what problems exactly are you solving? Unless there is a compelling reason to use them, sticking with auto increment IDs is much simpler.

And I say this as someone who recently has to convert some tables from auto increment IDs to uuid. In that instance, they were sharded tables that were relying on the IDs to be globally unique, and made heavy use of the IDs to scan records in time order. So uuids were something which could solve the first problem while preserving the functionality of the second requirement.

andy_ppp4• 10 months ago

It’s a perfectly good choice, most of the complaints here are exaggerated. If your inventory has SKUs use those externally/for links and for API lookups if possible.

wongarsu2• 10 months ago

Deploying UUIDv7 certainly requires more thought about the implications. In many cases leaking the creation time of a key is completely fine, in some cases it isn't

An interesting compromise is transforming the UUIDv7 to a UUIDv4 at the API boundary, like e.g. UUIDv47 [1]. On the other hand if you are doing that you can also go with u64 primary keys and transform those

1: https://github.com/stateless-me/uuidv47

bricss4• 10 months ago

If knowing IDs has a negative impact on security, then application system design is probably a trash.

bearjaws7• 10 months ago

Yeah I am trying to imagine a universe where having the creation time of an item breaks your security model and every path I go down is that the system has terrible security.

dietr1ch4• 10 months ago

The actual concern is privacy.

Privacy wise,

- Knowing sequential IDs leaks the rate of creation and amount of said entity which can translate in number of customers or rate of sales.

- Knowing timed IDs leaks activity patterns. This gets worse as you cross reference data.

- Random IDs reveal nothing.

---

Security wise,

- Sequential IDs can be guessed.

Performance wise,

- Sequential IDs may result in self-inflicted hotspots.

   - Spanner doesn't like  writing rows first keyed with timestamps, https://cloud.google.com/spanner/docs/schema-design#primary-key-prevent-hotspots.

- Random IDs lends themselves to sharding, but make indexing, column-compression, and maintaining order after inserts hard.

laughing_snyder• 10 months ago

Why would exposing any primary key be bad for security? If your system's security *in any way* depends on the randomness of a database private key, you have other problems. It's not the job of a primary key to add to security. Not to mention that UUIDv7 has 6 random bytes, which, for the vast majority of web applications, even finance, is more than enough randomness. Just imagine how many requests an attacker would need to make to guess even one UUID (281 trillion possible combinations for 6 random bytes, and he also would need to guess the unix timestamp in ms correctly). The only scenario I can think of is that you use the primary as a sort of API key.

Hizonner5• 10 months ago

Because anything that knows the primary key now knows the timestamp. The UUID itself leaks information. It's not that it's not adding security. It's that it's actually subtracting security.

btown• 10 months ago

One of the big things here is de-anonymization and account correlation. Say you have an application where users'/products' affiliation with certain B2B accounts is considered sensitive; perhaps they need to interact with each other anonymously for bid fairness, perhaps people might be scraping for "how many users does account X have onboarded" as metadata for a financial edge.

If users/products are onboarded in bulk/during B2B account signup, then, leaking the creation times of each of them with any search that returns their UUIDs, becomes metadata that can be used to correlate users with each other, if imperfectly.

Often, the benefits of a UUID with natural ordering outweigh this. But it's something to weigh before deciding to switch to UUIDv7.

sz4kerto• 10 months ago

Example: if user IDs are not random but eg Bigserial (autoincremented) and they're exposed through some API, then API clients can infer the creation time of said users in the system. Now if my system is storing eg health data for a large population, then it'll be easy to guess the age of the user. Etc. This is not a security problem, this is an information governance problem. But it's a problem. Now if you say that I should not expose these IDs - fine, but then whatever I expose is essentially an ID anyway.

8organicbits2• 10 months ago

system's security in any way depends on the randomness of a database private key

Unlisted URLs, like YouTube videos are a popular example used by a reputable tech company.

UUIDv7 has 6 random bytes

Careful. The spec allows 74 bits to be filled randomly. However you are allowed to exchange up to 12 bits for a more accurate timestamp and a counter of up to 42 bits. If you can get a fix on the timestamp and counter, the random portion only provides 20 bits (1M possiblities).

Python 3.14rc introduces a UUIDv7 implementation that has only 32 random bits, for example.

Basically, you need to see what your implementation does.

rhplus• 10 months ago

The German Tank Problem springs to mind. While not precisely the same problem, it’s still a case where more information that necessary is leaked in seemingly benign IDs. For the Germans, they leaked production volumes. For UUID v7, you’re leaking timing and timestamps.

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

echelon• 10 months ago

Depends how much entropy is in your primary keys.

If your primary keys are monotonic or time based, bad actors can simply walk your API.

moron4hire• 10 months ago

This is why the UUID versions should have been labeled by letter rather than number. Each UUID version doesn't replace the last. They do different things. The numbered versioning gives the impression that "higher numbers = better" and that's neither the case nor the intention.

nighthawk454• 10 months ago

Recently someone shared a method for encrypting the timestamp portion as well:

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

halayli• 10 months ago

I think privacy fits better rather than security. If your primary key is being used as a secret then you probably got your schema wrong. how will you encrypt them when required?

alex_duf• 10 months ago

UUIDv7 makes sense when a distributed system needs to insert vast amount of data that will be consumed chronologically. Typically an event log table.

Anything else, as you're rightly pointing it out, is a bit of a stretch.

dietr1ch4• 10 months ago

The distributed part is what forces creating Ids outside of the server, where UUIDs become useful, and also where systems become reliable.

Last year I went to renew my Id and they told me, sorry, the (centralised) system is down, but before computers things were done in a more resilient local, offline authoring + sync when convenient way that didn't result in "Sorry, computer says no. Schedule a new appointment."

gm678• 10 months ago

Does anyone know if there are any sorts of optimizations (either internally or available to the user) for a table with a UIIDv7 PK and a `date_created` column?

gfody3• 10 months ago

various engines have what's called "fast key" optimization specifically for integer sequences - if you're testing performance between an int/serial pk and a uuid the impact is profound to digusting depending on the engine.

raminf• 10 months ago

One of the main points of using a UUID as a record identifier was to make it so people couldn't guess adjacent records by incrementing or decrementing the ID. If you add sequential ordering, won't that defeat the purpose?

Seems like it would be wise to add caveats around using this form in external facing applications or APIs.

Demiurge• 10 months ago

The next ID can't be found just by adding 1, can it? How would you guess the next value?

bearjaws7• 10 months ago

There's still 62 bits of random data, even if you know the EXACT milisecond the row was created (you likely won't), you still need to do 1 billion guesses per second for 73 years.

Ideally you have some sort of rate limit on your APIs...

wongarsu2• 10 months ago

UUIDv7 has a 48 bit timestamp, 12 bits that either provide sub-millisecond precision or are random (in pg they provide precision) and another 62 bits that are chosen at random.

The A UUIDv7 leaks to the outside when it was created, but guessing the next timestamp value is still completely unfeasible. 62 bits is plenty of security if each attempt requires an API request

Hizonner5• 10 months ago

... and the next person working on the system thinks "well, this thing is unpredictable, so it's OK if I leak an unsalted hash of it". If they think at all, which is far from certain.

Why does everybody want to find excuses to leave footguns around?

crest• 10 months ago

"Unguessable public identifiers" press (X) to doubt

This depends very much on the type of UUID e.g. a type 1 UUID is just a timestamp, a MAC address and a "collision" counter to use if your clock ticks too slowly or you want batches of UUIDs.

hans_castorp• 10 months ago

The article is about UUID v7, so any criticism of v1 is irrelevant here (plus Postgres has supported v4 out of the box for quite a while now)

sergeyprokhoren• 10 months ago

95% of the comments here have nothing to do with reality.

What May Surprise You About UUIDv7 https://medium.com/@sergeyprokhorenko777/what-may-surprise-y...

smartbit• 10 months ago

Fun With UUIDs goes more into depth, presentation at PGday Lowlands sept 12, 2025.

UUIDs have a bad reputation, mostly based on randomly allocated UUIDs effect on indexes. But UUIDs also give us 16 bytes of space to play with, which can be to our advantage. We can use the space of UUIDs to structure and encode data into our identifiers. This can be really useful for multi-tenant applications, for sharding or paritioning. UUIDs can also help improve you web app security, by not leaking sequentially allocated ids.
We'll take a look at index performance concerns with randomly allocated UUIDs, sequential ids and sensibly structured UUIDs.
Seeing how we can go about extracting information from these UUIDs and how to build these UUIDs inside PostgreSQL, and how we can look at the performance of these functions.
Finally looking at adding support for bitwise operations on UUIDs using an extension.

Slides https://www.postgresql.eu/events/pgdaynl2025/schedule/sessio...

Live stream https://youtube.com/watch?v=tJYEuIpzch4&t=2h36m

Fire-Dragon-DoL• 10 months ago

Don't uuids have the big advantage of being easy to shard,over auto incrementing ids? That's usually a big deal.

The other big deal is that uuids can be created on the client and supplied by the client. That can make a lot of code drastically simpler (idempotency comes to mind)

bricss4• 10 months ago

All correct

sixdimensional• 10 months ago

There is a good solution to concerns over this...

Use UUIDv7 for your primary key but only for large scale databases and only for internal keys. Don't expose these keys to anything outside the database or application.

Use UUIDv4 columns with unique indexes over them as "external IDs" which are what is exposed via APIs to other systems.

Basically, create two IDs for one record - one random but not the primary key, and one sequential, that is the primary key.

I have done this in real systems.. and it works.

UUIDv7 Comes to PostgreSQL 18

Related Stories