3 ms·
The 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?
by morshu9001 1y ago
The 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.