5 ms·
UUIDs are way over used. There is almost always a better key to use, usually a bigint for databases. If you're making some kind of leaderless distributed data s
by blopker 4mo ago
UUIDs are way over used. There is almost always a better key to use, usually a bigint for databases. If you're making some kind of leaderless distributed data store, then maybe, but even then there are other ID sharding strategies I'd go for first depending on the constraints.
For a single database, bigints are smaller and faster, with less footguns.
UUIDs can be nice for an opaque public ID, however I'd still prefer something like a Sqid for space and usability.
- deleted 4mo ago[deleted]
- bob1029 4mo agoI am finding UUIDs help a lot if your primary schema consumer is an LLM. Inappropriate aliasing of integer keys allows for silent errors in queries because it will actually return some result a lot of the time. A UUID is immune to this problem. The model recognizes its mistake a lot more reliably when previously non-empty tables start showing up empty after attempting a join.
- JamesSwift 4mo agoUUIDs also have a nice benefit of it being impossible to query the wrong table with one if you mixup what an FK goes to
- pyuser583 4mo agoYeah this is nice - also helps with grepping dump files.
- mamcx 4mo agoHow is this done?
- sudoshred 4mo agoStatistically impossible to inadvertently generate a collision using UUID keys. UUID is designed to be unique when generated across any computer system. Practically speaking if you have an exactly matching pair of UUIDs from disparate system you have found the exact record match. The name gives a hint "Universally unique identifier". -Not a cryptographer.
- usrnm 4mo agoIt definitely is possible, just very improbable
- echoangle 4mo agoThat’s probably what’s meant by statistically impossible.
- ErroneousBosh 4mo agoIt definitely is possible, just very much a "woah, shit, guys come and look at this!" moment.
- beagle3 4mo agoMore like a moment that the guys can’t come because each one was independently struck by a lightning.
- pavo-etc 4mo ago"very" is underselling it
- 1659447091 4mo agoYou might find this thread interesting. UUIDv4 should probably be avoided https://news.ycombinator.com/item?id=48060054 https://news.ycombinator.com/item?id=48060054
- nickpeterson 4mo agoThey just mean you catch incorrect joins more easily because there is usually no overlap in keys between unrelated tables. Using int, you’re usually going to have some shared values between two unrelated tables.
- deleted 4mo ago[deleted]
- masklinn 4mo agoThe U means if you join the wrong table your join will always come up empty. It does not actually make it impossible to query the wrong table it just tells you quickly when you’ve done so.
- chrismorgan 4mo agoYou can achieve this with numeric sequences too, by having a consistent step and unique offset in all your sequences. For example, if you will never exceed 16 types, reserve four bits as the type discriminant. (You don’t have to use powers of two, but it may be convenient.) All sequences use step 16. Type A has discriminant/offset 0, yielding IDs {0, 16, 32, 48, 64, …}. Type B has discriminant/offset 1, mapping to IDs {1, 17, 33, 49, 65, …}. All the way up to Type P with discriminant/offset 15 and IDs {15, 31, 47, 63, 79, …}. This is also trivially invertible so that you can determine the type from the ID. A more common approach is to make IDs opaque strings and put a type prefix—A0, B12, P34, that kind of thing. But this way you can keep it as a number, if you wish.
- throwawayo2oe 4mo agoAlternatively just use a shared sequence for all tables.
- sgarland 4mo agoOr just write tests, instead of relying on statistical improbability to prevent disaster.
- deleted 4mo ago[deleted]
- crubier 4mo agoNo one ever got fired for using UUIDs
- Fabricio20 4mo ago> bigints are smaller and faster, with less footguns But be careful!! Javascript WILL interpret your bigints as Number() and round them down because they are too big without telling you!!! Famously seen by every snowflake user that has interacted with Javascript, quite an annoying problem.
- spiffytech 4mo agoFortunately we're seeing more JS DB libraries offering to read large numbers as the BigInt type.
- shakna 4mo agoBut frustratingly, a JS BigInt is nothing like a BigInt in any other language. In JS - BigInt is 64bit integer. In anything else - BigInt is a arbitrarily large integer.
- anematode 4mo agoHm? JavaScript BigInts are arbitrary precision, and you need to use methods like BigInt.asIntN(64, a) to convert them to 64 bits
- mort96 4mo agoI hate this so much because you can’t nicely serialise a BigInt as JSON. Using a string is nicer but it only makes sense where int64 is used as an ID, not where it’s used as a number; and you don’t wanna have to configure this per field per query.
- andersmurphy 4mo agoYes this matters even more if you are doing a lot of joins. Naive string UUIDs are 32 bytes (though I use binary uuid in the post which is 16) compared to 8 bytes for a 64-bit int. This matters even more with sqlite as it uses varint encoding. The upshot of all this is your indexes take up a lot less space in memory.
- PUSH_AX 4mo agoWhat are uuid foot guns?
- JamesSwift 4mo agoThey are generally distributed in such a way that values created at nearly the same time dont cluster together which is less efficient for DBs
- deleted 4mo ago[deleted]
- Fire-Dragon-DoL 4mo agoProviding an ID from the client is a big advantage that's missing though. Especially if you want a UI with optimistic rendering that's dealing with something async
- willtemperley 4mo agoUUIDs make client code so much simpler. Just create a UUID, use it client side to create your object graph and commit or not as appropriate. No need to retrieve an incremented integer.
- sgarland 4mo agoEvery DB, even MySQL can return the autoincrementing integer for you as part of the insert. Postgres, SQLite, and MariaDB (likely others, I’m just not familiar) can even return the rest of the data, should you need that. IME, most of the arguments for why UUIDs make things better are due to developer ignorance of RDBMS features (or B+tree performance).
- willtemperley 4mo agoI’m aware of insert returning. That’s still more work than “mint a UUID”. Once the incremented id is returned it then has to be set on the model, in some cases like GRDB in Swift it requires the id to be optional which is just annoying.
- tannenfreund87 4mo agoOn the contrary, the need for UUIDs is growing. Once you have multiple users collecting and editing data for a central database, you'll run into primary key conflicts. Of course not, if your users are constantly online and never lose the connection to the database. But modern usecases have a remote database over the internet and distributed users, often with slow or spotty connection. I've developed a field survey app for foresters. They use it on toughbooks, tablets and phones. They are collecting spatial data, so the geometry column in the tables gets quite big. The app on the device uses a SQLite (Spatialite) database, the central database is Postgres (PostGIS). They will often edit the same area, so without UUIDs, there will be duplication of primary keys, thus making the database inconsistent. Then I will be flooded in support tickets and it will cause more slowdown than just using UUID4. And the performance drop of UUID7 is negligible compared to bigint for primary key.