The gist “having both an auto-incremented integer as primary key and a UUID as a unique field in your entity” — which renders your title silly to say the least.

The bottom line is that the only drawback of autoincrement id’s is that they may leak dB structure if used publicly. So the main idea, which has nothing to do with Uuid, is to not do that. For API for example, the point is moot since you would be using authentication and authorization to regulate access. For front-end, you may abstract this on the front-end framework itself. Or you may simply use some convention to build an URL identifier.

Uuids have plenty of drawbacks which you can list under the ‘performance’ umbrella. Index size, search performance, etc. The very minimum mitigation is to store Uuid as binary, in addition to using it as second fiddle to a lighter autoincrement.

The index is not the devil, the devil is the architect calling the shots.