5 ms·

> if you ever need to combine multiple data sources together in migration and recovery type scenarios This insane idea that combining data sources is a rare ev

by feoren 4y ago

> if you ever need to combine multiple data sources together in migration and recovery type scenarios This insane idea that combining data sources is a rare event in some unusual "migration and recovery" scenarios is one of the most poisonous and yet pervasive ideas in all of database design. You are always combining multiple data sources, all the time. Users submitting data from a form is a data source. Test, staging, and production deployments, with multiple of each. External APIs. Multiple clients. Eventual consistency. Replication. Microservices with distributed systems. Or even sharing any common data at all between different systems, like unit conversions, chemical data, country names, engineering constants, etc. Anyone who even considers using a single authoritative source for all entity identity either better be making a system in an underground bunker that will never talk to any other system. Otherwise they are making a serious and extremely avoidable mistake. Never use auto-incrementing IDs. It's even wrong in a monolith! Why does everyone abandon this idea of "separation of concerns" and "single responsibility principle" and "bounded contexts" and proper abstraction and limited communication between system parts and literally every design principle they've ever been taught when they go to design a database? It all just goes out the window! Why do you guys bother reading books about system design if you ignore them when you build a database? "Multiple systems communicating with each other" should apply recursively all the way from deployment and external integration down to individual functions. That means database, too. Auto-incrementing IDs are anathema to that.

bruce511 4y ago
While your tone might be a tad hyperbolic, I agree with your basic premise. If I could go back and tell my 30 year ago self one tip, it would be to use uuids over auto-increments. And this is back when that was expensive - in disk space and database time. Instead I'm stuck with my design, and as time has passed the real cost of auto-Inc has slowly revealed itself. What's interesting to me though is that this view is not universal. I get a lot of push-back when promoting uuids, but I can really only speak to my experience.
- acmecorps 4y ago
  You’re not alone. I’ve been migrating my tables to use uuid instead of integers and have been using uuid whenever I have new tables, unless I have very good reason not to. Experience was my teacher.
  - vlovich123 4y ago
    Don’t UUIDs as primary keys totally destroy the performance because UUIDs aren’t sortable and thus wreak havoc with the index for the primary key?
    - Akronymus 4y ago
      In my experience, thats a big NO. While there is some performance impact, in other ways it can actually be a benefit (spreading writes out to multiple pages, for example) so as with anything in regards to performance: benchmark, benchmark, benchmark. PS.: UUID's also work quite nicely to deal with certain system hiccups, specifically temporal ones. Could be thought of as something similar to the Erlang supervisor trees.
    - keosak 4y ago
      They don't utterly destroy performance, but there is some hit if you use UUID v4 random values due to database index scattering. That's why this new proposal exists. It adds new versions of UUID that are mostly incrementing in time, so that they group better.
    - matwood 4y ago
      Destroy is a strong word (and UUIDs can certainly be sorted, but locality is an issue), but all of software is a series of tradeoffs. I've used both auto-increment and UUIDs, and wish I had used UUIDs in almost every case. Distributed generation - no sending a record to a server to get a key then using it to generate other records. In a world with increasing use of services, this becomes more important every day. System wide unique - helps with logging, debugging, and avoiding general errors. Multi-master db replication - I know this depends on the RDBMS, but having a unique key on every record avoids clashes. Also super useful during data migrations (which will happen. I have another rant that data always outlives code, so plan accordingly). Validation - UUIDs have a form that can be a first level validation on input. For me, those advantages outweigh some extra space usage, possible performance impact, and ugly URLs. And on performance, if it's determined that it is an issue because of using UUIDs there are ways to make them more index friendly.
- netghost 4y ago
  Can you give an example of an issue you faced with auto incrementing keys? Though I can imagine scenarios, the worst I've ever run into was maxing out the integer size, bit that was easily remedied.
  - bruce511 4y ago
    There are a few, but I'll sumarize; A) data merging. Merging multiple data sets together with auto-Inc is tricky - especially in the case of related tables. B) data distribution /replication - especially on and off phones - especially if phones are creating data records offline, then syncing later. C) parent-child forms. Parent has to be committed to the dB before children can be added. D) data imports - again especially related data. I'm not doing justice in such a brief question, each item above deserves a whole exploration.
    - netghost 4y ago
      Thanks! Those are a great set of examples. Perhaps one through-line is that if IDs are not created by a single source, don't rely on auto incrementing IDs. The parent/child one is interesting. When I've worked with hierarchical data, I tend to wrap the whole process in a transaction, so it may be many commits (by depth), but one transaction.
      - bruce511 4y ago
        >> if IDs are not created by a single source, don't rely on auto incrementing IDs. Data design outlives programs, and environments by a long time. Circumstances change. So when I designed the db, there was a single database. But 30 years later we live in a world with smart phones. Making design decisions because of _current_ circumstances can bite you hard later on. Reading parent /child. Yes there are ways to mitigate the issue, but it's extra work and code to do so. Ultimately you need the parent id before you can add the children though.
  - dolmen 4y ago
    One danger with auto incrementing ids is to be tempted to use the key for sorting instead of using a separate creation timestamp. At some point you might have to create out of order records (ex: inserting missing data) and that will break the order.
remram 4y ago
A problem here is that UUIDs are not actually a silver bullet against collision. You will never generate a UUID that will collide with someone else's, but it is easy for someone else to collide with you, either on purpose or because they made changes to data from your platform before sending it back (PKs tend to leak in URLs, APIs, data exports, etc). I find that I have to implement a system for re-numbering incoming data on migration/import anyway, so the advantage of UUIDs is not that huge.

by feoren 4y ago

bruce511 4y ago

While your tone might be a tad hyperbolic, I agree with your basic premise. If I could go back and tell my 30 year ago self one tip, it would be to use uuids over auto-increments. And this is back when that was expensive - in disk space and database time. Instead I'm stuck with my design, and as time has passed the real cost of auto-Inc has slowly revealed itself. What's interesting to me though is that this view is not universal. I get a lot of push-back when promoting uuids, but I can really only speak to my experience.

acmecorps 4y ago
You’re not alone. I’ve been migrating my tables to use uuid instead of integers and have been using uuid whenever I have new tables, unless I have very good reason not to. Experience was my teacher.
- vlovich123 4y ago
  Don’t UUIDs as primary keys totally destroy the performance because UUIDs aren’t sortable and thus wreak havoc with the index for the primary key?
  - Akronymus 4y ago
    In my experience, thats a big NO. While there is some performance impact, in other ways it can actually be a benefit (spreading writes out to multiple pages, for example) so as with anything in regards to performance: benchmark, benchmark, benchmark. PS.: UUID's also work quite nicely to deal with certain system hiccups, specifically temporal ones. Could be thought of as something similar to the Erlang supervisor trees.
  - keosak 4y ago
    They don't utterly destroy performance, but there is some hit if you use UUID v4 random values due to database index scattering. That's why this new proposal exists. It adds new versions of UUID that are mostly incrementing in time, so that they group better.
  - matwood 4y ago
    Destroy is a strong word (and UUIDs can certainly be sorted, but locality is an issue), but all of software is a series of tradeoffs. I've used both auto-increment and UUIDs, and wish I had used UUIDs in almost every case. Distributed generation - no sending a record to a server to get a key then using it to generate other records. In a world with increasing use of services, this becomes more important every day. System wide unique - helps with logging, debugging, and avoiding general errors. Multi-master db replication - I know this depends on the RDBMS, but having a unique key on every record avoids clashes. Also super useful during data migrations (which will happen. I have another rant that data always outlives code, so plan accordingly). Validation - UUIDs have a form that can be a first level validation on input. For me, those advantages outweigh some extra space usage, possible performance impact, and ugly URLs. And on performance, if it's determined that it is an issue because of using UUIDs there are ways to make them more index friendly.
netghost 4y ago
Can you give an example of an issue you faced with auto incrementing keys? Though I can imagine scenarios, the worst I've ever run into was maxing out the integer size, bit that was easily remedied.
- bruce511 4y ago
  There are a few, but I'll sumarize; A) data merging. Merging multiple data sets together with auto-Inc is tricky - especially in the case of related tables. B) data distribution /replication - especially on and off phones - especially if phones are creating data records offline, then syncing later. C) parent-child forms. Parent has to be committed to the dB before children can be added. D) data imports - again especially related data. I'm not doing justice in such a brief question, each item above deserves a whole exploration.
  - netghost 4y ago
    Thanks! Those are a great set of examples. Perhaps one through-line is that if IDs are not created by a single source, don't rely on auto incrementing IDs. The parent/child one is interesting. When I've worked with hierarchical data, I tend to wrap the whole process in a transaction, so it may be many commits (by depth), but one transaction.
    - bruce511 4y ago
      >> if IDs are not created by a single source, don't rely on auto incrementing IDs. Data design outlives programs, and environments by a long time. Circumstances change. So when I designed the db, there was a single database. But 30 years later we live in a world with smart phones. Making design decisions because of _current_ circumstances can bite you hard later on. Reading parent /child. Yes there are ways to mitigate the issue, but it's extra work and code to do so. Ultimately you need the parent id before you can add the children though.
- dolmen 4y ago
  One danger with auto incrementing ids is to be tempted to use the key for sorting instead of using a separate creation timestamp. At some point you might have to create out of order records (ex: inserting missing data) and that will break the order.

remram 4y ago

A problem here is that UUIDs are not actually a silver bullet against collision. You will never generate a UUID that will collide with someone else's, but it is easy for someone else to collide with you, either on purpose or because they made changes to data from your platform before sending it back (PKs tend to leak in URLs, APIs, data exports, etc). I find that I have to implement a system for re-numbering incoming data on migration/import anyway, so the advantage of UUIDs is not that huge.