4 ms·

I disagree that UUIDs are generally preferable over integers. For one, they take up more space (on disk and in memory). And for something like a key, it is like

I disagree that UUIDs are generally preferable over integers. For one, they take up more space (on disk and in memory). And for something like a key, it is likely that there will be multiple copies of that value stored, since it will exist in the table itself, at least one index (possibly more) and foreign keys. More space means fewer records per page on disk, more I/O and more memory usage (potentially leading to more I/O). I would wager that for most users (including this case), the cost of this additional I/O is far greater than some theoretical scalability limitation on generating IDs. Most database vendors make sequence generation (whether through explicit SEQUENCE objects or auto-incrementing columns) performant by making a few compromises: * Numbers may not always be sequential (you might get 1, 3, 2 - in that order) * There may be gaps (you might get 1, 2, 5, 6) But since these are supposed to be opaque identifiers, neither of these compromises should be a concern for most users. But this means that these sequences can live outside of a transaction (you might grab an ID, rollback, and that ID is gone) and that systems with multiple nodes can be allocated a "block" of numbers from which they can quickly pull new values, without needing to coordinate with a master node.

extrapickles 9y ago
UUIDs are the best choice when developer time is more important than space usage. Also, they can be generated and used by the client when the connection to the db is frequently down (eg: clients store/query data locally in SQLlite and replicate to master).
- luhn 9y ago
  UUIDs are a great option for creating IDs offline, but I don't understand your first comment: How do UUIDs optimize for developer time? In my experience, autoincrementing integers are the easiest choice for primary keys.
  - Groxx 9y ago
    Sometimes yes, sometimes no, but mostly I agree that uuids are better for dev time. The first time something is written, they're roughly the same (or a very small bias towards autoincrement), but UUIDs have fewer dev-problems in the long run. When you're starting out and don't have (m)any simultaneous writes to your DB, autoincrements are often perfectly sequential and identically sorted as a created_at column... which is convenient, but too often I see systems implicitly relying on this and having subtle problems when they start growing. E.g. pagination that doesn't account for gaps, or a missing "order by" that leads to the 1, 3, 2 issue mentioned above. It's also not often caught with tests, since they're frequently not run in parallel or at high enough velocity, and that lack of warning, time-coincidence with when the buggy code was introduced, or easily-testable reproducibility can make them hard to track down and fix. There are a bunch more fairly minor things that others have mentioned (e.g. UUIDs make it much harder to write joins incorrectly, as they'll just always be empty until you do it right), but generally I'd just call it "death by a thousand papercuts". Autoincrements are great and I love them, but generally I lean towards UUIDs since they're a bit more bug-resistant. And performance-wise, if you're having problems I generally doubt you'd be able to fix it by switching to autoincrements (assuming it was just flipping a switch). For a short time, possibly, but generally at that point you'll be beyond the "pick whatever is easiest" stage and can make an informed decision that'll have a far larger impact. --- tl;dr: I disagree with primary keys being "opaque identifiers" because they do have an affect on behavior in buggy code. Autoincrements mask more problems than UUIDs, and fixing even one of those costs more time than autoincrement saves.
- RobertRoberts 9y ago
  I am not a db expert, which is why I like read these kinds of comment threads. But what about this? https://en.wikipedia.org/wiki/Universally_unique_identifier#Collisions https://en.wikipedia.org/wiki/Universally_unique_identifier#... "The random nature of standard version 3, 4, and 5 UUIDs and the ordering of the fields within standard version 1 and 2 UUIDs may create problems with database locality or performance when UUIDs are used as primary keys. For example, in 2002 Jimmy Nilsson reported a significant improvement in performance with Microsoft SQL Server when the version 4 UUIDs being used as keys were modified to include a non-random suffix based on system time. This so-called "COMB" (combined time-GUID) approach made the UUIDs non-standard and significantly more likely to be duplicated, as Nilsson acknowledged, but Nilsson only required uniqueness within the application."
  - l_t 9y ago
    I'm also not a DB expert, but I definitely think locality is a concern with UUIDs (at least in Postgresql, which stores records in sorted order). But, storage locality has always struck me as a use-case-specific optimization, in the sense that it's hard to take locality into account without knowing what traffic patterns the table is expected to experience. So, although UUIDs provide locality for exactly no types of queries, that may not be a problem for you. For instance, you could use an autoincrementing sequence, but that only provides good locality for queries over sequential records. The COMB method would provide good locality for queries over time periods. But if you aren't performing those types of queries, you might be better off with a different type of PK.
  - WorldMaker 9y ago
    This article mentions this question. Database locality is a complex scaling question that may not be a problem depending on how read/write heavy your operations are. On the one hand, randomly distributed UUIDs may be a bad idea on a single SQL server trying to balance a single B-Tree index (and thrashing that index with a lot of incoming data); on the other hand it can be somewhat ideal for partition sharding across multiple servers. As with any trade-offs in database design, your mileage will vary with your application needs and resource availability. Also, there are other options for time-ordered GUID/UUID alternatives. ULID is the one I've been heavily using in projects lately: https://github.com/ulid https://github.com/ulid (ULID uses a timestamp prefix and random suffix for reasonable time-ordered database locality; an interesting compromise between V1 and V4 UUIDs, though not directly compatible with either. The L stands for "lexicographic" in that its also meant to be sortable in string indices as well, which can be important for database locality in many document/NoSQL databases.)
lobster_johnson 9y ago
Client-side ID generation (e.g. UUIDs) can be very useful. For one, it lets the client send an entire batch payload of one or more inserts, plus updates that reference the new inserts, in a single round trip. It doesn't need to wait to get the inserted IDs back to make updates, or send more requests to other services. All it needs is an OK back. This is very convenient in systems where, say, you ingest a bunch of data that needs to be dumped into a final database. The entire batch can be ready, including IDs, at the sender side. This is particularly useful in some other scenarios such as distributed, eventually-consistent systems that are able to resolve concurrency conflicts. Send out changes optimistically (with those pregenerated IDs), resolve conflicts by untangling the ones that didn't work.
rotten 9y ago
The uuid data type in PostgreSQL _is_ an integer.

extrapickles 9y ago

UUIDs are the best choice when developer time is more important than space usage. Also, they can be generated and used by the client when the connection to the db is frequently down (eg: clients store/query data locally in SQLlite and replicate to master).

luhn 9y ago
UUIDs are a great option for creating IDs offline, but I don't understand your first comment: How do UUIDs optimize for developer time? In my experience, autoincrementing integers are the easiest choice for primary keys.
- Groxx 9y ago
  Sometimes yes, sometimes no, but mostly I agree that uuids are better for dev time. The first time something is written, they're roughly the same (or a very small bias towards autoincrement), but UUIDs have fewer dev-problems in the long run. When you're starting out and don't have (m)any simultaneous writes to your DB, autoincrements are often perfectly sequential and identically sorted as a created_at column... which is convenient, but too often I see systems implicitly relying on this and having subtle problems when they start growing. E.g. pagination that doesn't account for gaps, or a missing "order by" that leads to the 1, 3, 2 issue mentioned above. It's also not often caught with tests, since they're frequently not run in parallel or at high enough velocity, and that lack of warning, time-coincidence with when the buggy code was introduced, or easily-testable reproducibility can make them hard to track down and fix. There are a bunch more fairly minor things that others have mentioned (e.g. UUIDs make it much harder to write joins incorrectly, as they'll just always be empty until you do it right), but generally I'd just call it "death by a thousand papercuts". Autoincrements are great and I love them, but generally I lean towards UUIDs since they're a bit more bug-resistant. And performance-wise, if you're having problems I generally doubt you'd be able to fix it by switching to autoincrements (assuming it was just flipping a switch). For a short time, possibly, but generally at that point you'll be beyond the "pick whatever is easiest" stage and can make an informed decision that'll have a far larger impact. --- tl;dr: I disagree with primary keys being "opaque identifiers" because they do have an affect on behavior in buggy code. Autoincrements mask more problems than UUIDs, and fixing even one of those costs more time than autoincrement saves.
RobertRoberts 9y ago
I am not a db expert, which is why I like read these kinds of comment threads. But what about this? https://en.wikipedia.org/wiki/Universally_unique_identifier#Collisions https://en.wikipedia.org/wiki/Universally_unique_identifier#... "The random nature of standard version 3, 4, and 5 UUIDs and the ordering of the fields within standard version 1 and 2 UUIDs may create problems with database locality or performance when UUIDs are used as primary keys. For example, in 2002 Jimmy Nilsson reported a significant improvement in performance with Microsoft SQL Server when the version 4 UUIDs being used as keys were modified to include a non-random suffix based on system time. This so-called "COMB" (combined time-GUID) approach made the UUIDs non-standard and significantly more likely to be duplicated, as Nilsson acknowledged, but Nilsson only required uniqueness within the application."
- l_t 9y ago
  I'm also not a DB expert, but I definitely think locality is a concern with UUIDs (at least in Postgresql, which stores records in sorted order). But, storage locality has always struck me as a use-case-specific optimization, in the sense that it's hard to take locality into account without knowing what traffic patterns the table is expected to experience. So, although UUIDs provide locality for exactly no types of queries, that may not be a problem for you. For instance, you could use an autoincrementing sequence, but that only provides good locality for queries over sequential records. The COMB method would provide good locality for queries over time periods. But if you aren't performing those types of queries, you might be better off with a different type of PK.
- WorldMaker 9y ago
  This article mentions this question. Database locality is a complex scaling question that may not be a problem depending on how read/write heavy your operations are. On the one hand, randomly distributed UUIDs may be a bad idea on a single SQL server trying to balance a single B-Tree index (and thrashing that index with a lot of incoming data); on the other hand it can be somewhat ideal for partition sharding across multiple servers. As with any trade-offs in database design, your mileage will vary with your application needs and resource availability. Also, there are other options for time-ordered GUID/UUID alternatives. ULID is the one I've been heavily using in projects lately: https://github.com/ulid https://github.com/ulid (ULID uses a timestamp prefix and random suffix for reasonable time-ordered database locality; an interesting compromise between V1 and V4 UUIDs, though not directly compatible with either. The L stands for "lexicographic" in that its also meant to be sortable in string indices as well, which can be important for database locality in many document/NoSQL databases.)

lobster_johnson 9y ago

Client-side ID generation (e.g. UUIDs) can be very useful. For one, it lets the client send an entire batch payload of one or more inserts, plus updates that reference the new inserts, in a single round trip. It doesn't need to wait to get the inserted IDs back to make updates, or send more requests to other services. All it needs is an OK back. This is very convenient in systems where, say, you ingest a bunch of data that needs to be dumped into a final database. The entire batch can be ready, including IDs, at the sender side. This is particularly useful in some other scenarios such as distributed, eventually-consistent systems that are able to resolve concurrency conflicts. Send out changes optimistically (with those pregenerated IDs), resolve conflicts by untangling the ones that didn't work.

rotten 9y ago

The uuid data type in PostgreSQL _is_ an integer.