4 ms·

I'm a bit confused as to what the use case for UUIDs are compare to incrementing integer IDs. I assume there are some big upsides (aside from the primary key is

by jjice 5y ago

I'm a bit confused as to what the use case for UUIDs are compare to incrementing integer IDs. I assume there are some big upsides (aside from the primary key issue which seems to have quite a few solutions at this point), but I'm just not aware of them.

tomschlick 5y ago
Because they are unique on generation, you dont need to talk to the database before generating one. This is huge for distributed systems, or systems that have offline components (think mobile apps that sync). The ids can be generated client side and passed up to be stored in the database without worry.
tschellenbach 5y ago
incrementing ids requires a centralized server of some sort to hand out IDs when you get to a certain scale. you don't have that issue with UUIDs. for small apps just incrementing ids works fine though this approach also allows you to generate ids client side. which if you're working with a local db on ios/android etc this is very convenient
dangerlibrary 5y ago
From the "Introduction" section of the linked document: "The motivation for using UUIDs as database keys stems primarily from the fact that applications are increasingly distributed in nature. Simplistic "auto increment" schemes with integers in sequence do not work well in a distributed system since the effort required to synchronize such numbers across a network can easily become a burden. The fact that UUIDs can be used to create unique and reasonably short values in distributed systems without requiring synchronization makes them a good candidate for use as a database key in such environments. "
lolinder 5y ago
One big one is distributed computing: for example, an app that can create To Do items offline and periodically syncs with a server. If you have your To Do items identified by an incrementing integer, you have to come up with some other unique identifier to use for To Do items that have yet to be synced with the server's database (since you can't know which ID to use next). On the other hand, if you use UUIDs, your app can just create a new UUID for the To Do item, which will continue to be its ID for subsequent updates.
marcus_holmes 5y ago
The huge one for me is that if I make a mistake in code or admin and accidentally run "update users set permission = 'root' where id=$1" using an account id instead of a user id, it does nothing. If I'd used integers I'd have given root permission to a random user.
- medstrom 5y ago
  So, it's like creating a special data type for each "counter-style" identifier. For example suppose a StudentsId which is not an integer, it's a StudentsInteger, so it cannot be mixed up with UsersId. Admittedly that seems more clunky than just using uuids.
  - marcus_holmes 5y ago
    Yeah you could do that if you really want to use integers. But I'm not sure I'd trust the database layer to not just silently convert the type since they're both based on integers.
spyspy 5y ago
One non-performance reason is that incrementing IDs inherently leak information about your application and/or your company. If you create an account somewhere and you see that your user ID is 452 you now know how many users the company has. In the company's eyes they'd probably prefer to keep that obscured.
- rjsw 5y ago
  Also known as the German tank problem [1]. [1] https://en.wikipedia.org/wiki/German_tank_problem https://en.wikipedia.org/wiki/German_tank_problem
  - stavros 5y ago
    Not exactly, as seeing a fresh ID lets you pretty accurately tell how many items there are.
thamer 5y ago
One of the most important is that you can't iterate over all the UUIDs. How many data leaks have come from people incrementing an ID in a URL? This is so common, I've experienced this myself as a student with a summer job: the company I was working for used an online service for their payroll, I accessed my pay slip one day and changed ?employee=123 to 124 in the URL out of curiosity and it loaded the data for a different person in a different company. They fixed it incredibly quickly once I told them about it :)
alex_duf 5y ago
Incrementing an ID requires complete knowledge in a single place, which at scale becomes a challenge. This would typically be done in the database, but doesn't bode well with distributed systems as multiple servers might thing "5" is following "4" leading to multiple rows identified by the same ID => this is a collision. So UUIDs are great because multiple servers can generate plethora of them without having to worry about collisions. This is all explained in the first few pages of the document, although not in simple terms. Also I haven't spotted anything about UUIDv6 trying to avoid collisions, but I haven't read very far.
- bjt 5y ago
  The last 48 bits should do it. " node: 48-bit pseudo-random number used as a spatially unique identifier Occupies bits 80 through 127 (octets 10-15)"
remus 5y ago
As well as the other use cases mentioned, they're harder to guess than incrementing integer IDs so are a useful defense in depth to help stop people guessing URLs for resources.
- tatersolid 5y ago
  This benefit can’t be understated: missing or buggy authorization checks are present in almost all real-world web apps and APIs I’ve encountered. A long random component (such as the 80 bits provided by ULIDs) covers up a lot of sins. A ULID is 48 bits of timestamp plus 80 bits of entropy. These new formats just don’t have enough random-component entropy. 64 bits isn’t enough. Who cares in your millisecond timestamp wraps every 8900 years as they do in a ULID? https://github.com/ulid/spec https://github.com/ulid/spec

by jjice 5y ago

tomschlick 5y ago

Because they are unique on generation, you dont need to talk to the database before generating one. This is huge for distributed systems, or systems that have offline components (think mobile apps that sync). The ids can be generated client side and passed up to be stored in the database without worry.

tschellenbach 5y ago

incrementing ids requires a centralized server of some sort to hand out IDs when you get to a certain scale. you don't have that issue with UUIDs. for small apps just incrementing ids works fine though this approach also allows you to generate ids client side. which if you're working with a local db on ios/android etc this is very convenient

dangerlibrary 5y ago

From the "Introduction" section of the linked document: "The motivation for using UUIDs as database keys stems primarily from the fact that applications are increasingly distributed in nature. Simplistic "auto increment" schemes with integers in sequence do not work well in a distributed system since the effort required to synchronize such numbers across a network can easily become a burden. The fact that UUIDs can be used to create unique and reasonably short values in distributed systems without requiring synchronization makes them a good candidate for use as a database key in such environments. "

lolinder 5y ago

One big one is distributed computing: for example, an app that can create To Do items offline and periodically syncs with a server. If you have your To Do items identified by an incrementing integer, you have to come up with some other unique identifier to use for To Do items that have yet to be synced with the server's database (since you can't know which ID to use next). On the other hand, if you use UUIDs, your app can just create a new UUID for the To Do item, which will continue to be its ID for subsequent updates.

marcus_holmes 5y ago

The huge one for me is that if I make a mistake in code or admin and accidentally run "update users set permission = 'root' where id=$1" using an account id instead of a user id, it does nothing. If I'd used integers I'd have given root permission to a random user.

medstrom 5y ago
So, it's like creating a special data type for each "counter-style" identifier. For example suppose a StudentsId which is not an integer, it's a StudentsInteger, so it cannot be mixed up with UsersId. Admittedly that seems more clunky than just using uuids.
- marcus_holmes 5y ago
  Yeah you could do that if you really want to use integers. But I'm not sure I'd trust the database layer to not just silently convert the type since they're both based on integers.

spyspy 5y ago

One non-performance reason is that incrementing IDs inherently leak information about your application and/or your company. If you create an account somewhere and you see that your user ID is 452 you now know how many users the company has. In the company's eyes they'd probably prefer to keep that obscured.

rjsw 5y ago
Also known as the German tank problem [1]. [1] https://en.wikipedia.org/wiki/German_tank_problem https://en.wikipedia.org/wiki/German_tank_problem
- stavros 5y ago
  Not exactly, as seeing a fresh ID lets you pretty accurately tell how many items there are.

thamer 5y ago

One of the most important is that you can't iterate over all the UUIDs. How many data leaks have come from people incrementing an ID in a URL? This is so common, I've experienced this myself as a student with a summer job: the company I was working for used an online service for their payroll, I accessed my pay slip one day and changed ?employee=123 to 124 in the URL out of curiosity and it loaded the data for a different person in a different company. They fixed it incredibly quickly once I told them about it :)

alex_duf 5y ago

Incrementing an ID requires complete knowledge in a single place, which at scale becomes a challenge. This would typically be done in the database, but doesn't bode well with distributed systems as multiple servers might thing "5" is following "4" leading to multiple rows identified by the same ID => this is a collision. So UUIDs are great because multiple servers can generate plethora of them without having to worry about collisions. This is all explained in the first few pages of the document, although not in simple terms. Also I haven't spotted anything about UUIDv6 trying to avoid collisions, but I haven't read very far.

bjt 5y ago
The last 48 bits should do it. " node: 48-bit pseudo-random number used as a spatially unique identifier Occupies bits 80 through 127 (octets 10-15)"

remus 5y ago

As well as the other use cases mentioned, they're harder to guess than incrementing integer IDs so are a useful defense in depth to help stop people guessing URLs for resources.

tatersolid 5y ago
This benefit can’t be understated: missing or buggy authorization checks are present in almost all real-world web apps and APIs I’ve encountered. A long random component (such as the 80 bits provided by ULIDs) covers up a lot of sins. A ULID is 48 bits of timestamp plus 80 bits of entropy. These new formats just don’t have enough random-component entropy. 64 bits isn’t enough. Who cares in your millisecond timestamp wraps every 8900 years as they do in a ULID? https://github.com/ulid/spec https://github.com/ulid/spec