8 ms·
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 suitab
by sonium 3y 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 3y agoHuh, why would using uuidv4 be a problem? Collisions?
- davydog187 3y agoMy understanding is that they cause a lot of page fragmentation, which leads to excessive writes to the WAL
- brodo 3y agoYou can't use them as cursors, because they are not inherently ordered like integer ids.
- dventimi 3y agoI have never wanted to use database cursors and I predict that I never will.
- brodo 3y agoSure, if you don't offer pagination or only have small tables, you can get away with offsets. I tend to go for cursors as a default because I like to build applications with performance in mind and it’s the same effort.
- dventimi 3y agoWe may be talking about different things. I thought you were referring specifically to [database cursors](https://en.wikipedia.org/wiki/Cursor_(databases) https://en.wikipedia.org/wiki/Cursor_(databases)) so that's what I was talking about. If you're talking about something else, like the concept of so-called "cursor-based pagination" in general, then that is still an option even with even randomly-generated primary keys, so long as there are other attributes that can be used to establish an order (which attributes need not be visible to the a user)
- dventimi 3y agoI have offered pagination over large tables, without database cursors or non-random keys, without offsets, while keeping performance in mind, with little effort.
- mariusor 3y agoHow about the rest of us? I don't have a resource of the top of my head to present to you, but in the least keyset pagination is superior to the offset one because it does not get invalidated by new inserts.
- dventimi 3y agoThe rest of you also don't need database cursors or non-random keys to have keyset pagination.
- kiitos 3y agoIDs are one thing, cursor-able fields/columns are a different thing. You cursor on timestamps, or serial numbers, or etc., not IDs.
- CooCooCaCha 3y agoSorry but that’s terrible advice. I’ve worked on projects that started with integer ids and it caused nothing but problems.
- giva 3y agoWhat kind of problems did you encounter?
- brodo 3y agoI encountered this once: If you use integer IDs, try to scale horizontally, and do not generate the IDs in the database, you'll get in deep trouble. The solution for us was to let the DB handle ID generation.
- giva 3y agoYes, but the only sane way to generate integer IDs is in the database.
- welder 3y agoNot OP, but I can answer this: Integers don't scale because you need a central server to keep track of the next integer in the sequence. UUIDs and other random IDs can be generated distributed. Many examples, but the first one that comes to mind is Twitter writing their own custom UUID implementation to scale tweets [0] [0]: https://blog.twitter.com/engineering/en_us/a/2010/announcing-snowflake https://blog.twitter.com/engineering/en_us/a/2010/announcing...
- lazide 3y agoThis doesn’t really help you in this case, because the patch is to generate the UUIDs in the database?
- welder 3y agoNow you can use PG to generate the UUIDv7 in the beginning then easily switch to generating in the client if you need in the future, but I think OP was talking about UUID vs auto-incrementing integer in general not specific to Postgres.
- cjblomqvist 3y agoThere 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 3y 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 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).
- j16sdiz 3y agothe same idea, but this is a IETF standard.
- dankebitte 3y agoUniqueness aside, UUIDs for public-facing IDs also prevent enumeration attacks and leaking business information other than timestamps.
- throwaway83623 3y agoThe "faulty joins" can be solved by having a shared sequence for all tables. A bigint column should be enough for most use cases.
- j16sdiz 3y agoshared sequence for all tables can't be parallelized
- mbork_pl 3y agoOne 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.
- mrits 3y agoWhen working with distributed systems or compiling a IDs from different systems it is helpful to make sure the ID is unique
- itslennysfault 3y agoI 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.
- jandrewrogers 3y agoIt 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.
- mort96 3y agoHow does this happen? Are people implementing UUIDv4 themselves using rand() or equivalent? Or has widely used UUIDv4 libraries had such bugs?
- jandrewrogers 3y agoIt comes in a couple common flavors. Most commonly it is people just rolling their own implementation and using a PRNG or similar. Not every environment has a ready-made UUIDv4 implementation, and not all UUIDv4 implementations in the wild are strict. A rarer horror story I've heard a couple times is discovering that the strong RNG provided by their environment is broken in some way. Both of these cases are particularly problematic because they are difficult to detect operationally until something goes horribly wrong. The main reason non-probabilistic UUID-like types are used for high-reliability environments is that it is easy to verify the correctness of the operational implementation. It isn't that difficult to deterministically generate globally unique keys in a distributed system unless you have extremely unusual requirements.
- akvadrako 3y agoThat's especially true if you care about performance and do a lot of joins, the hit can be over 10%.