Use 64-bit ints. Prefer not exposing them to users, but it’s not a problem for most software.
If you ever get so big you need to shard, use Snowflake or a 64bit scheme that encodes the shard.
Don’t overcomplicate things. You’re already using either SQLite or PostgreSQL and it already gives you auto-incrementing integer keys by default, without the need to encode/decode UUIDs in whatever software you’re writing to interface with it.
When near 1 million users (yagni), reset sequence and do the same with 10 million (or one billion).
Doesn't solve the upside of 128-bit random numbers (ala uuid): the ability to generate remotely and expect no collision.
I don’t see how remote generation is an upside. If you’re using UUID as a database key, you’re hitting the DB anyway, so it doesn’t save a trip to the DB.
There are plenty of uses for UUID like peer-to-peer apps where that makes sense, but the article is talking about database keys.
I don't imagine there are many valid use cases where you want a list of users sorted by their database record ID though, and if you're suggesting an auto-incremented int then the creation timestamp will give you the same order anyway.
By the time I compared UUIDs to sorted UUIDs (in the special pattern that was supposed to work for SQL Server) and ints, however, I didn’t find any difference in performance.
Perhaps by the time I tested it had been fixed, or perhaps it was only an issue in certain circumstances, but never an issue with a few million rows for me.