Identifying users/content by 64 bit Int (like Twitter) vs. 128 bit UUID (string) vs. 64 bit random string? Isn't 64 bit Int or random string far more efficient?

Really? I thought the whole point of using 128 bit UUID is to have them generated distributively. You can’t do this with only 64 bit because the odds of collision would be real.

See the birthday paradox:

Unless your system has some capacity for appropriately dealing with multiple things potentially sharing ID’s you are some risk with using these and not checking. I have read extensively on it and while I’m not an expert (just from what I’ve read), where uniqueness is necessary, “just trust me bro” is not a certain convention to be safe on this.

Don’t trust me, trust the math. And someone did the math:

By the way, generating short random binary and check for collision is trivial in Elixir; I did it myself (only 48 bit my application is small). It seems like you are already convinced, so why not just do it?

I am asking because I am wondering if my logic is sound. I asked numerous questions in my post above as well in terms of specifics for implementation, such as how to work with the Int64 type in Elixir, best way to generate a random one, whether I am correct about the better efficiency, etc.

I am also wondering why more people are not using 64 bit Int’s given they seem to have many advantages and no significant drawbacks.

Why do people work with 128 bit UUID’s when they are highly inefficient, take far more data to store and transit, can’t be compared or worked with as efficiently, and the risk of collisions is low in any reasonable system?

I would never design a system that can’t handle a collision either way (which I presume requires either check before inserting to database for same ID already, or have database return error if trying to insert something with same primary key ID as something that exists).

For example, as discussed here: https://stackoverflow.com/questions/65484022/is-there-a-way-to-make-guid-100-collision-safe

The only way to GUARANTEE uniqueness without a sequential identity column is to look for the ID in the table first, any generate a new ID if it already exists. That’s not a free operation, of course, so you need to weigh the performance hit of that check versus the (infinitesimally small) probability of a collision. You might be better off just trying an insert, and if a collision occurs, catching the error and trying again.

I can;t speak for others, I use all 3:

• sequential ids for local database primary keys
• UUID for distribution
• random short id with collision checking for ephemeral things

They are all fine and all have their pros and cons. There is no one true way to make ids.

The only advantage I can see to a UUID system is where presumably you have no central database that can easily check or return an error if you try to insert something with a primary key into a table that already has that primary key.

If you are storing identifiers into a main database that doesn’t already have an auto-incrementing int ID system (so you must manage your own ID, just like Twitter said they had to with Cassandra), then I can see no good reason to use UUID which is bulky and provides no clear added advantage over a shorter random string or likely best an Int64.

As long as the collisions are rare and they are handled (eg. database returns an error, you generate a new ID, and try again), you are paying no net penalty by using the shorter/faster ID type.

(I am not stating this as fact, I am just thinking about it out loud, as the broad convention of UUID’s makes no sense to me.)

I haven’t ever seen a system that stores UUIDs as VARCHAR(32) TBH; in my experience they are either old and use VARCHAR(36) (including the - characters) or new and use native UUID types (which store 16 bytes).

This is exactly why people don’t use 64-bit values this way, checking every time is expensive. Also, the birthday paradox is massively worse with 8-byte values vs 16-byte ones.

Here’s another nasty trap for the unwary with 64-bit integers: unless your JSON serializer is very careful about values larger than 2^53, you could end up transmitting them as integers in JSON and lose precision when the values are handled by Javascript. Roundoff error in foreign keys is no fun for anybody…

Yes, I could see a benefit of UUID if your database natively stores them as such at native 128 bit (mine doesn’t). But you’re still paying 32 or 36 bytes transit every time you send one back and forth to users as strings.

For example, Elixir can be used well for multiplayer gaming applications. Let’s say you have 100 users in a game.

If you want to update every user’s position/rotation/appearance at 10 fps (crude system, updating everyone every time), you are paying 36 * 100 = 3.6 KB on every update just on the UUID’s and 36 KB/s just sending the UUID’s each second. This is more than double the data than streaming a 128 kbps MP3.

Or you have to make a reference table for the users to map their ID’s to a shorter ID to reduce this which is just more work.

I just mean to say, there are reasons why one would not want to have IDs in such a bulky format if there is no good reason for it. I would have to see how my database system handles attempts at insertions on existing primary keys, but if it automatically returns an error, no pre-checking is necessary and one can then presumably use shorter identifiers with no cost.

Such collisions would still be very rare in any reasonable system. I am pretty sure every DB must return an error if you try inserting an existing primary key record. What else would they do? Overwrite? I can’t imagine. That would be potentially dangerous.

Identifying users/content by 64 bit Int (like Twitter) vs. 128 bit UUID (string)? Isn’t 64 bit Int far more efficient?

can you elaborate on what you mean by efficient here? as @al2o3cr pointed out there are optimizations that depends on what you’re looking for.
i’d personally start with first specifying what are your current constraints(like, DB system, network restrictions, etc), just so the discussion can go torwards what you really need.

This simply isn’t correct. PostgreSQL, MSSQL, and Oracle to name a few are not storing strings for UUID nor are they processing them as strings. These databases* store and process UUIDs as 16 bytes. Databases, such as PostgreSQL, will present the UUID in the canonical string form, but that’s just presentation. There are database products which will handle UUIDs as strings (The only one that comes to mind is MySQL, and I’m not sure it still does), but that’s not sufficient to make such a generalization.

(*OK, there are some exceptions where Oracle actually stores 19 bytes)

You’d be surprised at the subtle kinds of bugs that can crop up because of compatible, non-unique IDs across relations. Since most, especially older, systems will use some form of sequential integer to key records, you’ll sometimes see a bug where joins between header and detail records use the wrong keys to join on… (so, for example, instead of a join ON pohead_id = poitem_phead_id, you’ll see ON pohead_id = poitem_id… you can infer the tables from the column names). Because in header/detail relationships like this, the detail relation will tend to always have many more records than the header relation, I’ll almost certainly have matches in primary key of the detail records to header record IDs… so I’ll get data, but that data may be non-sensical (in the best case) because I’m getting headers and details that aren’t really associated because of the bad join condition… in the worst case the data between different headers will be close enough to the real header to be believable. With database-wide unique identifiers this problem doesn’t exist since I’ll never inadvertently be able to join records like I just described.

Of all the problems that keep me awake at night, UUID collisions are not one. In practice this just isn’t a concern; sure if I’m assuming UUIDv4 (or soon to be v7) and that I’ve got a poor RNG at work, then the chances of collision could become realistic… but I’d have other, probably more serious problems at that point. The reality is that I’m expecting many, many other failure modes to be more likely, including concurrent cosmic ray hits defeating my ECC memory protections. For any real world transaction I might attempt to process, anything I do to recover from those generally are good enough for the colliding UUID case: even if that means telling the user something went wrong ( ) and to try again. In reality, I could say the same for 64 bit numbers, too, though (unsurprisingly) the breathing room is naturally reduced. Finally, most of the time UUID uniqueness matters I’m enforcing that with a unique constraint on the column (which happens if it’s a primary key anyway) which will blow up if I try to enter a duplicate… so no special checks aside from dealing with transactions blowing up for any reason).

So… why UUIDs?

Because there’s typically good library support and broad understanding of how they work (you might call it a standard). Many product have UUID support in the box without needing to resort to libraries, specialized knowledge, or bespoke solutions. This isn’t to say that there aren’t those that need to push performance to the max well beyond what you can achieve with UUIDs, but for most, that’s just overthinking it.