18 ms·
Exploring PostgreSQL 18's new UUIDv7 support
- morshu9001 1y agoThe article compares UUIDv7 vs v4, but doesn't say why you'd do either instead of just serial/bigserial, which has always been my goto. Did I miss something?
- edoceo 1y agoSo the client side can create the ID before insert - that's the case that (mostly) drives it for me. The other is where you have distributed systems and then later want to merge the data and not have any ID conflicts.
- jrochkind1 1y agoyup, I'd say those are the two biggies.
- saagarjha 1y agoAllowing the client to generate IDs for you seems like a bad idea?
- morshu9001 1y agoClient = backend here, right? So you could make a bunch of rows that relate to each other then insert, without having to ping the DB each time to assign a serial ID. Normally the latter is what I do, but I can imagine a scenario where it'd be slow.
- wongarsu 1y agoThe usual flow would be INSERT ... RETURNING id, which gives you the db-generated id for the record you just inserted with no performance penalty. That doesn't work for circular dependencies and it limits the amount of batching you can do. But typically those are smaller penalties than the penalty from having a 128 bit primary key vs a 64 bit key
- morshu9001 1y agoYeah, that's what I do
- coolspot 1y ago“client” here may refer to a backend app server. So you can have 10-100s of backend servers inserting into a same table without having a single authority coordinating IDs.
- morshu9001 1y agoThat table is still a single authority, isn't it? But I guess fewer steps is still faster.
- tracker1 1y agoExcept if you're using a sharding or clustering database system, where the record itself may be stored to separate servers as well as the key generation itself.
- morshu9001 1y agoIn those cases yes. There's still a case for sequential there depending on the use pattern, but write-heavy benefits from not waiting on one server for IDs.
- markstos 1y agoWhy?
- bramhaag 1y agoIt can be quite elegant. You can avoid the whole temporary or external ID mess when the client generates the ID, this is particularly useful for offline-first clients. Of course you need to be sure the server will accept the ID, but that is practically guaranteed by the uniqueness property of UUIDs.
- clintonb 1y agoClient-generated IDs are necessary for distributed or offline-first systems. My company, Vori, builds a POS for grocery stores. The POS generates UUIDv7 IDs for all data it creates and that data is eventually synced to our backend. The sync time can range from less than 1 second for a store with fast Internet to hours if a store is offline. Is a collision possible? Yes, but the likelihood of a collision is so low that it's not worth agonizing over (although I did when I was designing the system).
- martinky24 1y agoYou don’t scale horizontally, do you?
- morshu9001 1y agoThis is Postgres. There is Citus, but that still supports (maybe recommends?) serial PKs.
- rcfox 1y agoDo most people? Not everyone is Google.
- martinky24 1y agoMany people have more than 1 server that need to generate coherent identifiers amongst one another. That's not a "Google scale" thing.
- rcfox 1y agoYour comment heavily implied (to me) scaling databases horizontally. Yes, it's not necessarily "Google scale" either, but it's a ton of extra complexity that I'm happy to avoid. But a Google employee is probably going to approach every public-facing project with the assumption of scaling everything horizontally. With multiple servers talking to a single database, I'd still prefer to let the database handle generating IDs.
- morshu9001 1y agoYeah, there's too much advice jumping straight to uuid4 or 7 PKs for no particular reason. If you're doing a sharded DB, maybe, and even then it depends. Speaking of Google, Spanner recommends uuid4, and specifically not any uuid that includes a timestamp at the start like uuid7.
- Deadron 1y agoFor when you inevitably need to expose the ids to the public the uuids prevent a number of attacks that sequential numbers are vulnerable to. In theory they can also be faster/convenient in a certain view as you can generate a UUID without needing something like a central index to coordinate how they are created. They can also be treated as globally unique which can be useful in certain contexts. I don't think anyone would argue that their performance overall is better than serial/bigserial though as they take up more space in indexes.
- morshu9001 1y agoBut these are internal IDs only, and public ones should be a separate col. Being able to generate uuid7 without a central index is useful in distributed systems, but this is a Postgres DB already. Now, the index on the public IDs would be faster with a uuid7 than a uuid4, but you have a similar info leak risk that the article mentions.
- rcfox 1y ago"Distributed systems" doesn't have to mean some fancy, purpose-built thing. Just correlating between two Postgres databases might be a thing you need to do. Or a database and a flat text file.
- morshu9001 1y agoI usually just have a uuid4 secondary for those correlations, with a serial primary. I've done straight uuid4 PK before, things got slow on not very large data because it affected every single join.
- xienze 1y agoPeople really overthink this. You can safely expose internal IDs by doing a symmetric cipher, like a Feistel cipher. Even sequential IDs will appear random.
- whiskey-one 1y ago
- nextaccountic 1y agouuids can be generated by multiple services across your stack bigserial must by generated by the db
- coolspot 1y agoBut what if we just use milliseconds as our bigserial? And maybe add some hw-random number at the end to avoid conflicts? Wait
- tracker1 1y agoSomehow +1 on this comment just doesn't feel like enough.
- crazygringo 1y agoOh yeah, it would be an identifier but it would be unique. Across the universe of all devices, effectively. Should come up with a name for that
- mhuffman 1y ago>why you'd do either instead of just serial/bigserial, which has always been my goto. Did I miss something? So the common response is sequential ID crawling by bad actors. UUIDs are generally un-guessable and you can throw them into slop DBs like Mongo or storage like S3 as primary identifiers without worrying about permissions or having a clever interested party pwn your whole database. A common case of security through obscurity.
- simongr3dal 1y agoI believe the concern is if your primary key in the database is a serial number it might be exposed to users unless you do extra work to hide that ID from any external APIs and if there are any flaws in your authorization checks it can allow enumeration attacks exposing private or semi-private info. With UUIDs being virtually unguessable that makes it less of a concern.
- morshu9001 1y agouuid7 is still guessable though, as the article says. The assumption is that these are internal only PKs.
- tracker1 1y agoFar, far less than sequential Ids, and the random part is some pretty big values numerically... I mean there's billions of possible values for every MS on the generating server... you aren't going to practically "guess" at them.
- molf 1y agoThere is a big difference though. Serial keys allow attackers to guess the rate at which data is being added. UUID7 allows anyone to know the time of creation, but not how many records have been created (approximately) in a particular time frame. It leaks data about the record itself, but not about other records.
- e12e 1y agoGuessable with 80 bits of entropy?
- ibejoeb 1y agoIf you need an opaque ID like a uuid because, for example, you need the capability to generate non-colliding IDs generated by disparate systems, the best way I've found is to separate these two concerns. Use a UUIDv4 for public purposes and a bigint internally. You don't need to worry about exposing creation time, and you can still manage your data in the home system with all the properties that a total ordering affords.
- tracker1 1y agoNow coordinate those sequential ids on a sharded or otherwise clustered database system.
- ibejoeb 1y agoThat's the point. Those are only system-unique, not universally. It's a lower-level attribute that is an implementation detail, like for referential integrity in an rdbms. At that point, if you need it, you have atomic increment.
- molf 1y agoGood question. There's a few reasons to pick UUID over serial keys: - Serial keys leak information about the total number of records and the rate at which records are added. Users/attackers may be able to guess how many records you have in your system (counting the number of users/customers/invoices/etc). This is a subtle issue that needs consideration on a case by case basis. It can be harmless or disastrous depending on your application. - Serial keys are required to be created by the database. UUIDs can be created anywhere (including your backend or frontend application), which can sometimes simplify logic. - Because UUIDs can be generated anywhere, sharding is easier. The obvious downside to UUIDs is that they are slightly slower than serial keys. UUIDv7 improves insert performance at the cost of leaking creation time. I've found that the data leaked by serial keys is problematic often enough; whereas UUIDs (v4) are almost always fast enough. And migrating a table to UUIDv7 is relatively straightforward if needed.
- MBCook 1y agoNot only can you make a good guess at how many customers/etc exist, you can guess individual ones. World’s easiest hack. You’re looking at /customers/3836/bills? What happens if you change that to 4000? They’re a big company. I bet that exists. Did they put proper security checks EVERYWHERE? Easy to test. But if you’re at /customers/{big-long-hex-string}/bill the chances of you guessing another valid ID are basically zero. Yeah it’s security through obscurity. But it’s really good obscurity.
- morshu9001 1y agoYou normally aren't supposed to expose the PK anyway.
- bruce511 1y agoThat advice was born primarily _because_ of the bigint/serial problem. If the PK is UUIDv4 then exposing the PK is less significant. In some use cases it can be possible to exclude, or anonymize the PK, but in other cases a PK is necessary. Once you start building APIs to allow others to access your system, a UUIDv4 is the best ID. There are some performance issues with very large tables though. If you have very large tables (think billions of rows) then UUIDv7 offers some performance benefits at a small security cost. Personally I use v4 for almost all my tables because only a very small number of them will get large enough to matter. But YMMV.
- gopalv 1y agoUUIDv7 is only bad for range partitioning and privacy concerns. The "naturally sortable" is a good thing for postgres and for most people who want to use UUID, because there is no sorted distribution buckets where the last bucket always grows when inserting. I want to see something like HBase or S3 paths when UUIDv7 gets used.
- vlovich123 1y ago> UUIDv7 is only bad for range partitioning and privacy concerns. It's no worse for privacy than other UUID variants if the "privacy" you're worried about leaking is the creation time of the UUID. As for range partitioning, you can of course choose to partition on the hash of the UUIDv7 at the cost of giving up cheaper rights / faster indices. On the other hand, that of course gives up locality which is a common challenge of partitioning schemes. It depends on the end-to-end design of the system but I wouldn't say that UUIDv7 is inherently good or bad or better/worse than other UUID schemes.
- ibejoeb 1y agoUUIDv4 doesn't leak creation time.
- saghm 1y agoIsn't it at least a bit worse than v4, which has no timestamp at all? There might be concerns around non-secure randomness being used to generate the bits, but I don't feel like it's accurate to claim that's indistinguishable from a literal timestamp.
- wara23arish 1y agoconfused why it would be worse for range partitioning? I assume there would be some type of index on the timestamp portion & the uuid portion? wouldn’t that make it better for partitioning since we’d only need to query partitions that match the timestamp portion
- parthdesai 1y agoWhy is it bad for range partitioning? If anything, it's better? With UUIDv7, you basically can partition on primary key, thus you can have "global" unique constraint.
- 6r17 1y agoGreat read - short, effective ; I know what I learned. Very good job
- pqdbr 1y agoGreat article, specially for this part: > What can go wrong with using UUIDv7 Using UUIDv7 is generally discouraged for security when the primary key is exposed to end users in external-facing applications or APIs. The main issue is that UUIDv7 incorporates a 48-bit Unix timestamp as its most significant part, meaning the identifier itself leaks the record's creation time. > This leakage is primarily a privacy concern. Attackers can use the timing data as metadata for de-anonymization or account correlation, potentially revealing activity patterns or growth rates within an organization. While UUIDv7 still contains random data, relying on the primary key for security is considered a flawed approach. Experts recommend using UUIDv7 only for internal keys and exposing a separate, truly random UUIDv4 as an external identifier.
- themafia 1y ago> growth rates I honestly don't see how.
- andy_ppp 1y agoI wish Postgres would just allow you look up records by the random component of the field, what are the chances of collisions with 80 bits of randomness? My guess is it’s still enough.
- jagged-chisel 1y agoYou can certainly create that index.
- andy_ppp 1y agoYes, just obviously if it’s automated and part of Postgres people will use it without having to think too much and it removes one of the objections to what I think for most large systems is a sensible way to go rather than controversial because security.
- mamcx 1y agoWhat could be better is to allow to create a type with custom display, in/out and internally set the native type IN SQL (this require to do it in c)
- crazygringo 1y ago> Using UUIDv7 is generally discouraged for security when the primary key is exposed to end users in external-facing applications or APIs. The main issue is that UUIDv7 incorporates a 48-bit Unix timestamp as its most significant part, meaning the identifier itself leaks the record's creation time... Experts recommend using UUIDv7 only for internal keys and exposing a separate, truly random UUIDv4 as an external identifier. So this basically defeats the entire performance improvement of UUIDv7. Because anything coming from the user will need to look up a UUIDv4, which means every new row needs to create an extra random UUIDv4 which gets inserted into a second B-tree index, which recreates the very performance problem UUIDv7 is supposedly solving. In other words, you can only use UUIDv7 for rows that never need to be looked up by any data coming from the user. And maybe that exists sometimes for certain data in JOINs... but it seems like it might be more the exception than the rule, and you never know when an internal ID might need to become an external one in the future.
- tracker1 1y agoThis is only really true if leaking the creation time of the record is itself a security concern.
- deleted 1y ago[deleted]
- qntmfred 1y agoany thoughts on uuidv7 vs ulid, nanoid, etc for url-safe encodings?
- thewisenerd 1y agoi guess that depends on what you mean by url-safe uuidv7 (-) and nanoid (_-) have special characters which urlencode to themselves. none are small enough that you want someone reading them over the phone; but from a character legibility, ulid makes more sense.
- nikisweeting 1y agoULID is the best balance imo, it's more compact, can be double clicked to select, and case-insensitive so it can be saved on macOS filesystems without conflicts. Now someone should make a UUIDv7 -> ULID adapter lib that 1:1 translates UUIDv7 <-> ULID preserving all the timestamp resolution and randomness bits so we can use the db-level UUIDv7 support to store ULIDs.
- masklinn 1y agoA uuid is a 128b number with a specific structure. You can encode them in base32 if you want, there is no need for any sort of conversion scheme.
- nikisweeting 1y agoYou need to convert it to perserve the timestamp info correctly so that a ULID library reading the base32 format would reproduce the same timestamp.
- masklinn 1y agoWhat I'm saying is that ULID is irrelevant and unnecessary, if you want "double clicked to select, and case-insensitive" you just encode your UUIDs in base32. They're still UUIDs.
- stickfigure 1y agoIt never occurred to me that Postgres is more efficient when inserting monotonic values. It's the nature of B+ trees so it makes sense. But in the world of distributed databases, monotonic inserts create hot partitions and scalability problems, so evenly-distributed ids are preferred. In other words, "don't try this with CRDB".
- baq 1y agoLeaky abstractions in databases are one of the reasons every developer should read the table of contents of the hot databases used by the things he’s working on. IME almost no one does that.
- chuckadams 1y agoIt's the nature of B+ trees, multiplied by the nature of clustered indexes: if you use a UUIDv4 as a primary key, your entire row gets moved to random locations, which really sucks when you normally retrieve them sequentially. With a non-clustered index (say, your UUIDv4 id you use for public APIs when you don't want to leak the v7 info) then you'll still get more fragmentation with the random data, but it's something autovacuum can usually keep up with. But it's more work it has to do on top of everything else it does.
- masklinn 1y agoGp mentioned Postgres, which does not have clustered indexes. It has table clustering, which is a point operation rewriting the entire table but not a persistent property.
- chuckadams 1y agoAh, I forgot CLUSTER was something run by hand on PG. Same footgun then, but you have to load and aim it yourself instead of being fully automatic like it is in MySQL, where it appears you can't opt out of clustering by the PK (similar story in SQL Server, but you can change which index it clusters by). Thanks for the clarification.
- gnatolf 1y agoFor me, the shear length of uuids is annoying in payloads of tokens etc. I wish there was a common way to abbreviate those, similar to the git way.
- pmontra 1y agoIt's a 128 bit number. If you express that number in base 62 (26 upper case letters + 26 downcase letters + 10 digits) you need only a bit more than 20 characters. You can compress it further by increasing the base and using other 8 bit ASCII characters.
- Merad 1y agoCrockford base32 [0] is the best compromise, IMO. Reasonable length of 26 chars. Uses only alphanumeric characters and avoids issues with case sensitivity and confusing characters (0 vs O, etc.). 0: https://www.crockford.com/base32.html https://www.crockford.com/base32.html
- pmontra 1y agoMy customers return created_at attributes in all their API calls so UUIDv7 won't harm them at all. They also use sequential ids. Only one of them ever used UUIDv4 as primary key. We didn't have any performance problem but the whole production system was run by one psql insurance and one Elixir application server. Probably almost any architectural choice is good at that scale.
- lucasyvas 1y agoThese are all non-issues - don’t allow an end user to determine a serial primary key as always. And the amount of information it leaks is negligible - they might know the oldest and the newest and there’s an infinite gulf in between. It’s better and more practical than SERIAL or BIGSERIAL in every way - if you need a random/external ID, add a second column. Done.
- morshu9001 1y agoWhy not serial PK with uuid4 secondary? Every join uses your PK and will be faster.
- Biganon 1y ago> if you need a random/external ID, add a second column. Done. As others have stated, it completely defeats the performance purpose, if you need to lookup using another ID.
- caymanjim 1y agoTangential, but I'm grateful to this article for teaching me that Postgres has "table foo" as shorthand for "select * from foo". I won't use that in code, but I'll happily use it for interactive queries.
- rvitorper 1y agoDoes anyone have performance issues with uuidv4? I worked with a db with 10s of billions of rows, no issues whatsoever. Would love to hear the mileage of fellow engineers
- cipehr 1y agoWhat database were you using? For example with SQL server, by default it clusters data on disk by primary key. Random (non-sequential) PKs like uuidv4 require random cluster shuffling to insert a row “in the middle” of a cluster, increasing io load and causing performance issues. Postgres on the other hand doesn’t do clustered indexing on the PK… if I recall correctly.
- rvitorper 1y agoPostgres. It was also a single instance, which made it significantly easier. But nice to know that this is an issue on SQL Server
- masklinn 1y agoPostgres is not immune to uuid issues, just less sensitive, uuidv4 still does not play well with btree indexes, bloating them and impacting their performance.
- mfrye0 1y agoI can confirm on the performance benefits. I wanted to start with uuidv7 for a new DB earlier this year, so I put together a function to use in the meantime. Once the function is available natively, we'll just migrate to use it instead. For anyone interested: CREATE FUNCTION uuidv7() RETURNS uuid AS $$ -- Get base random UUID and overlay timestamp select encode( set_bit( set_bit( overlay(uuid_send(gen_random_uuid()) placing substring(int8send((extract(epoch from clock_timestamp())*1000)::bigint) from 3) from 1 for 6), 52, 1), -- Set version bits to 0111 53, 1), 'hex')::uuid; $$ LANGUAGE sql volatile;
- burnt-resistor 1y agoSequential primary keys are pretty important for scalable, stable sorting by record creation time using the primary keys' index similar to serial (int) but avoids the guessing vulnerability. For this use-case, an UUID "v9"-like approach can be a better option: https://uuidv9.jhunt.dev https://uuidv9.jhunt.dev
- deleted 1y ago[deleted]
- deleted 1y ago[deleted]
- deleted 1y ago[deleted]
- deleted 1y ago[deleted]
- deleted 1y ago[deleted]
- bearjaws 1y agoI really disagree that the privacy risk is enough to not use it at all, even in a healthcare setting. There are wild scenarios you can come up with where you may leak something, but that assumes the information isn't coming over anyway. "Reveals account creation time" - most APIs return this in API responses by default. When have you seen just a list of UUIDs and no other more revealing metadata? Meanwhile what pwns 99% of companies? Phishing.
- sverhagen 1y agoAPI responses should be limited to authenticated users. IDs are often present in hyperlinks that are included in insecure emails, or in URLs that, being routed through all sorts of networking hops may be captured and available as metadata.
- delifue 1y agoI disagree with this > While UUIDv7 still contains random data, relying on the primary key for security is considered a flawed approach The correct way is 1. generate ID on server side, not client side 2. always validate data access permission of all IDs sent from client Predictable ID is only unsafe if you don't validate data access permission of IDs sent from client. Also, UUIDv7 is much less predictable than auto-increment ID. But I do agree that having create time in public-facing ID can leak analytical information.
- perrygeo 1y agoIs there an unavoidable tradeoff here? Keys that order nicely (auto-incrementing integers, UUIDv7) naturally leak information. Keys that are more secure (UUIDv4) can have performance problems because they have poor locality. Or are there any random id generators that can compromise, remain sequential-ish without leaking exact timestamps and global ordering?
- mjb 1y agoYes. The spatial locality benefits drop off quite quickly. A hashed uuidv7-like scheme with a rotating salt, for example, would keep short term locality and it's performance benefits while not having long term locality and it's downsides.
- AlotOfReading 1y agoThe tradeoff is unavoidable. At one end is UUIDv4. At the far end is a gray code with excellent locality, but inherently allows you to know which half of the indices the record is from (even without inverting it). UUIDv7 is a pretty good middle ground.
- inopinatus 1y agoSymmetric encryption of IDs at the edge. Optional embedded HMAC. Optional text encoding. For monotonic bigserial values I'm somewhat fond of base58(AES_K1(id{8} || HMAC_K2(id{8})[0..7])) with purpose/table-salted HKDF subkeys from a scrypt'd system passphrase. The hot path of this is pretty fast. As with all cryptographic solutions it comes with a whole new jungle of pitfalls, caveats, and tradeoffs, but it works.
- morshu9001 1y agoHow big would the resulting public ID be?
- inopinatus 1y agoThat depends on exact scheme and text encoding, but in the example I give above, they are 22 characters, and I will even pad them in the text encoder for length consistency.
- pilif 1y agoOne thing that’s not quite clear to me is how safe it is to generate v7 uuids on the client. That’s one of the nice properties of v4 uuids: you can make up a primary key of a new entity directly on the client and the database can use it directly. Sure: there is tiny collision risk, but it’s so small, you can get away with mostly ignoring it With v7 however, such a large chunk of the uuid is based on the time, so I’m not sure whether it’s still safe to ignore collisions in any application, especially when you consider client’s clocks to probably be very inaccurate. Am I overthinking things here?
- PhilippGille 1y agoHow many clients requests do you get in the same millisecond? With UUIDv7 it's split into: - 48 bits: Unix timestamp in milliseconds - 12 bis: Sub-millisecond timestamp fraction for additional ordering - 62 bits: Random data for uniqueness - 6 bits: Version and variant identifiers So >4,600,000,000,000,000,000 IDs per fraction of a millisecond. And unprecise time on the client doesn't matter, because some are ahead and some behind, vut that doesn't make them more likely to clash.
- cenamus 1y agoDoes that factor in the birthday paradox?
- qeternity 1y agoIf the client can generate a uuid4 they can also reuse a known uuid4
- MaKey 1y agoInteresting that aiven is still around after they've lost customer data a few years back.
- oskari 1y agoI believe you're referring to our January 2020 Kafka incident where a logic bug caused data loss for a customer. It was a serious failure and a huge learning moment for us. The platform we operate today is fundamentally different and far more resilient than it was five years ago. We've scaled significantly (recently passing $100M ARR) because we took those early lessons seriously and continue to prioritize reliability.
- Rafert 1y ago> Using UUIDv7 is generally discouraged for security when the primary key is exposed to end users in external-facing applications or APIs. I would not call this “generally discouraged” when APIs generally surface a created_at timestamp in their responses. A real life example are Stripe IDs which have similar properties (k-sorted) as UUIDv7: https://brandur.org/nanoglyphs/026-ids#ulids https://brandur.org/nanoglyphs/026-ids#ulids
- turrini 1y agoSomething like this [1] or an adaptation may address their security considerations. Discussed here [2] [1] https://github.com/stateless-me/uuidv47 https://github.com/stateless-me/uuidv47 [2] https://news.ycombinator.com/item?id=45275973 https://news.ycombinator.com/item?id=45275973
- klysm 1y agoI don’t care at all about “leaking” the creation time for records. I think the documentation is overly zealous