Best practices on primary key, auto-increment, and UUID in RDBMs and SQL databases

Question

We're designing a table for user entity. The only non-trivial requirement is that there should be a permanent URL to the user entity (for example their profile). There's a lot about int/long vs UUID on the web. But it is still unclear to me.

1. Considering the fact that the profile contains private information, it's not a good idea to have a predictable ID embedded in the URL. Am I right?
2. To satisfy the first I can have primary key as UUID and embed it in the URL. But there's two question. Should I be worried about the performance penalty of having UUID as primary key in anyway; indexing, inserting, selecting, joining?

Having that said, which one of the following is better (with respect to the above)?

CREATE TABLE users(   pk UUID NOT NULL,   .....   PRIMARY KEY(pk) ); 

or

CREATE TABLE users(   pk INT NOT NULL AUTO_INCREMENT,   id UUID NOT NULL,   .....   PRIMARY KEY(pk),   UNIQUE(id) ); 

Answer 1

It's a matter of choice actually and this question can raise opinion based answers from my point of view. What I always do, even if it's redundant is I create primary key on auto increment column (I call it technical key) to keep it consistent within the database, allow for "primary key" to change in case something went wrong at design phase and also allow for less space to be consumed in case that key is being pointed to by foreign key constraint in any other table and also I make the candidate key unique and not null.

Technical key is something you don't normally show to end users, unless you decide to. This can be the same for other technical columns that you're keeping only at database level for any purpose you may need like modify date, create date, version, user who changed the record and more.

In this case I would go for your second option, but slightly modified:

CREATE TABLE users(   pk INT NOT NULL AUTO_INCREMENT,   id UUID NOT NULL,   .....   PRIMARY KEY(pk),   UNIQUE(id) );