Personally I've never experienced the "whole class of bugs" that starting with a big integer is supposed to solve. I'm not using PHP so maybe that's why?
However if you want any sort of efficient lookup on the external key (UUID), your database still needs an index on the UUID, and you are back at square one.
I choose to forego the integer PK and just use UUID since I have to create index over it anyway.
That is, unless you decide to put the same interface into your database and your API, what is not rare for OOM-only programmers to do, but always ends in tears.
Yes and no. It depends on whether your database treats primary keys differently than other indexes. For example, in InnoDB primary keys are always clustered indexes: the row data is directly stored in a btree arranged by the PK; secondary indexes just store PK values in their leaf nodes, so that they can do a lookup on the clustered index.
As a result, in InnoDB smaller PKs are preferable. So performance is generally better when using an incremental ID as PK and then UUID as a secondary index, as opposed to the reverse. Assuming you have multiple secondary indexes, the total table size will also be smaller in InnoDB with integer PK than with UUID PK.
Well. Many systems already do a form of this. They store a session ID (the browser cookie) in something like redis which maps to an internal database id which is often incremented. The internal database ids are never seen outside the DB.
In this case it's fine because the external IDs are ephemeral (relatively) and centralization is a hard criteria (you typically can't have two people creating an account with the same user name, or having one email address linked to multiple record IDs, etc.).
This is really why these discussions are pointless without specifics and a concrete system.
A common disadvantage of integer keys is that programs will have bugs and use a foo_id as a bar_id. If most ids are small integers then it is likely that a valid foo_id may be a valid bar_id, whereas uuids probably won’t collide. This can be somewhat mitigated with a sufficiently strong type system. Even in a dynamic language like lisp you can represent your ids as e.g. (foo . <id>), and only need to get the tags right on the boundary.
An advantage of integer ids is density: if your ids are likely close together, there are probably some better or more compressed data structures you can use.
Assuming you can stomach the latency hit, the best solution is usually a lookup table where you take all the friendly URL keys and map them to internal identifiers. So if you're making a multiplayer game site and want to create a page where folks can find their friends, then you might support yourgame.net/user/username, yourgame.net/character/charactername, yourgame.net/steam/steamlogin, yourgame.net/xbox/xboxgamertag, etc. Internally you have an inverted index that maps [type, string] to the internal ID for the player, then proceed normally.
the "big integer" stops this happening for other integers that might be used in your code and that you might accidentally send to the database as a user_id.
Unless you might also use big integers in your code. 32-bits is big enough for all numbers-used-as-numbers-instead-of-ids is...a risky assumption.