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."
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.
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
(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.)
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.