UUIDs also introduce performance challenges. Using them as the primary key will absolutely torpedo your insert performance. They also subtly harm performance on pretty much every other operation. You can fit half (or 1/4, if you can live with 32-bit ints) as many UUIDs in a cache line, and can't even fit one of them in an integer register. The impact on join performance can be pervasive at production scale even when it's not visible at dev scale. And UUIDs tend to result in records being physically arranged in ways that defeat the page cache.
> If you order things by id (often useful for pagination, since incrementing ids are usually ordered by creation date) uuid are of no help.
The use of ordering by a surrogate key is to have a stable ordering with no semantic meaning, so UUIDs work just as well as IDs there.
> You would need to sort by created_at and this would require an additional index.
Yes, if you want to sort by a semantically important data element in a table with surrogate primary key, you'll probably want an index on the data element. So?
> No to speak of pagination by id ranges or whatever is used sometimes.
Assuming a serial column is creation-ordered is not a good idea, but it's usually not too wrong; assuming it's dense, as well, is going to be wrong more often than it is right, except where data is never deleted. Assuming it is dense over the range of a query is even less reliable.
Paginating by serial ID ranges is, almost without exception, a horrible idea.
So, for example, one previous database I worked on had an access pattern where we always wanted to get results sorted by the order in which they were inserted, and we needed this to be a completely stable ordering. Even two rows that get inserted during the same microsecond, as part of the same transaction, needed to have some official (even if arbitrary) designation of which one is first and which one is second. And both of those requirements also meant that the ordering key needed to be unique. And we also needed table access to be as fast as possible, and disk-friendly, and tend to offer the fastest access for the most recent data.
All of this points to using serial numeric IDs as both a unique key and the clustering key. UUIDs do nothing to help with any of those requirements, and generally hurt all of them. They do that because of certain characteristics they have that are specifically there in order to solve one of the few problems we didn't have to worry about.
Tangentially, in software systems design, I've long since realized that the word "always", when left to roam around freely, unchaperoned by any qualifiers to limit its universality, is an indicator of limited breath of experience. So, when you encounter it, it's useful to mentally insert "in my experience" as a stand-in qualifier. Having done so, the next conundrum is that "in my experience" advice is only actionable to the extent that you know what experience the advice giver has to draw on.
UUIDs are not always a good solution.
If you order things by id (often useful for pagination, since incrementing ids are usually ordered by creation date) uuid are of no help.
You would need to sort by created_at and this would require an additional index.
No to speak of pagination by id ranges or whatever is used sometimes.
UUIDs are mpossible to remember and thus somewhat cumbersome to use too.
The weird id mixed up with index problem from the article is something which never happened to me ever in over 10 years of web development.
I stay away from PHP as much as possible so that may be the reason ;)