4 ms·

I use a mix: UIDs (auto-incrementing primary keys) for internal app use (e.g. joins), but use UUIDs for referencing records outside the app (anything sent out o

I use a mix: UIDs (auto-incrementing primary keys) for internal app use (e.g. joins), but use UUIDs for referencing records outside the app (anything sent out over the API). The UIDs just make for easier to read logs, and easier to inspect and hand-write db queries. The UUIDs just seem much more secure when communicating with client applications. I also agree that authing with a DB is preferable, if you can afford to do that at the scale your app needs (which is most apps out there).

kchoudhu 9y ago
This right here. The ORM I wrote (and use...) automatically generates a an internal and an external identifier for every persisted object. Works pretty well in practice.
sudhirj 9y ago
Aren't UIDs effectively the same as integers in this case? Seems like it's conceptually similar to using the MD5 hash of the integer ID. Wouldn't it still cause exactly the same issues mentioned in the article? Anything non-random would.
- IAmEveryone 9y ago
  UUIDs (two 'U') are random.
  - lvh 9y ago
    UUIDv4s are. The other kinds are deterministic. (And to be fair, quite rare ;))
  - Promarged 9y ago
    To be precise only v4 UUIDs are (pseudo-)random, and not the entire thing, there are some version bytes.
dalore 9y ago
Why not just UUID for the primary key also and only have one column? Having 2 columns seems like extra complexity for not much gain. Modern databases usually have a native UUID type column which stores and compares better then using the string/char type. One more benefit of using UUIDs for primary keys is that clients can generate models along with the primary key and know what the key will be BEFORE they submit it to the database. That is, it works really well with distributed systems. Auto incrementing primary key is really state stored at the database level and a source of contention I would even argue that UUIDs are far better to read logs. You do a grep for that UUID in the log and it's easier to find then to grep for integer primary keys. If your contains disparate models then UUIDs will find your entries easier then integers. If you ever have to rollback your database but keep some of the new data, it's easier also. You just export the data you want to keep, do your rollback, and them import that data knowing that auto incrementing primary keys will never be an issue. You can copy objects easily from one database to another and know the primary key will be the same, and not clash. For hand writing db queries, it's also better to use a UUID since then I can use that same query cut and pasted into other databases with the same data and know that I don't have to check primary keys. The UUID is just a copy/paste.
- bearjaws 9y ago
  A UUID primary key has to live in every index in the table and every foreign key that references it. We have a table with a UUID as its primary and it alone consumes 22GB, then a index reference it, so now another 22GB... That one primary key uses over 100GB of storage, a developer recently went to add another table that reference it and we had to decide whether we were okay taking another hit. If we used your example of using a char type (we use blob) it would be double the size... It doesn't sound like much, but in a database with all primary keys being UUIDs you are going to inflate the size of your DB quickly, or have to forego using foreign keys. I imagine if we used only UUIDs we would have to double or triple our database infrastructure. Additionally, we would be forced to introduce partitioning sooner. Now we have a tech debt ticket to add an auto increment to that table so we can reclaim disk / memory.
  - bogomipz 9y ago

kchoudhu 9y ago

This right here. The ORM I wrote (and use...) automatically generates a an internal and an external identifier for every persisted object. Works pretty well in practice.

sudhirj 9y ago

Aren't UIDs effectively the same as integers in this case? Seems like it's conceptually similar to using the MD5 hash of the integer ID. Wouldn't it still cause exactly the same issues mentioned in the article? Anything non-random would.

IAmEveryone 9y ago
UUIDs (two 'U') are random.
- lvh 9y ago
  UUIDv4s are. The other kinds are deterministic. (And to be fair, quite rare ;))
- Promarged 9y ago
  To be precise only v4 UUIDs are (pseudo-)random, and not the entire thing, there are some version bytes.

dalore 9y ago

Why not just UUID for the primary key also and only have one column? Having 2 columns seems like extra complexity for not much gain. Modern databases usually have a native UUID type column which stores and compares better then using the string/char type. One more benefit of using UUIDs for primary keys is that clients can generate models along with the primary key and know what the key will be BEFORE they submit it to the database. That is, it works really well with distributed systems. Auto incrementing primary key is really state stored at the database level and a source of contention I would even argue that UUIDs are far better to read logs. You do a grep for that UUID in the log and it's easier to find then to grep for integer primary keys. If your contains disparate models then UUIDs will find your entries easier then integers. If you ever have to rollback your database but keep some of the new data, it's easier also. You just export the data you want to keep, do your rollback, and them import that data knowing that auto incrementing primary keys will never be an issue. You can copy objects easily from one database to another and know the primary key will be the same, and not clash. For hand writing db queries, it's also better to use a UUID since then I can use that same query cut and pasted into other databases with the same data and know that I don't have to check primary keys. The UUID is just a copy/paste.

bearjaws 9y ago
A UUID primary key has to live in every index in the table and every foreign key that references it. We have a table with a UUID as its primary and it alone consumes 22GB, then a index reference it, so now another 22GB... That one primary key uses over 100GB of storage, a developer recently went to add another table that reference it and we had to decide whether we were okay taking another hit. If we used your example of using a char type (we use blob) it would be double the size... It doesn't sound like much, but in a database with all primary keys being UUIDs you are going to inflate the size of your DB quickly, or have to forego using foreign keys. I imagine if we used only UUIDs we would have to double or triple our database infrastructure. Additionally, we would be forced to introduce partitioning sooner. Now we have a tech debt ticket to add an auto increment to that table so we can reclaim disk / memory.
- bogomipz 9y ago