This covers a good amount of ground, but you entirely missed composite primary keys, which is a brilliant tool to solve some of this locality vs serial id issue.

You can have a composite primary keys with hierarchy.

The first column is serial, but broad enough that it does not give too much info about your data cardinality. Most of the indexing work is done on this column (think reducing the search size by 100x or more). The second key can be a uuid, so you get all the goodies (generated externally, nicely obfuscated, dev don't make bad assumption like they are continuous or something).

An example would be if you have an online store (let's say home depot). You are trying to create a PK for the product table.

Your first column could be a serial identifying the broad category of the product.

Like "paint" is id 123. It's OK to expose that.

Then your second key is the specific paint, and that's a UUID.

If you have ~100 category, and 1000 product per categories, the locality problem would not be too bad, and you don't expose a product serial that would allow, say, you competitor to easily scrap all your products info/prices.

The down side is that now all your joins have to mention both columns, and that some basic SQL drivers don't support composite primary keys