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.
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.
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.
There is a perf hit. You can not help it when you are slugging that many bytes around. An int/int64 fits into a register easy and the instruction for comparison is cheap. However, when you add in replication and other items UUID becomes more desirable due to the property having extra information embedded into it to make them unique. You can get some of the same benefits with more complexity if you are using auto increment. Such as using a stride that is similar to the number of machines you have in the cluster. But even that can get weird, and depending on your db can be a pain to setup. Using them as a cluster index and you probably will most certainly create a hotspot index and poor lookup performance, but decent write. Just due to the fact that items probably are not grouped the same as your where clause. SSD's have hidden most of this for most databases. But the issues are the same.
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.
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.
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.
>> 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.
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.