PostgreSQL User table, benefits of user

I am creating a table to store my user's info in a psql database, what are the benefits of saving a serialized user_id if the username has to be unique. In other words, why cant the username be the primary key?

This is directly from the PostgreSQL docs:

CREATE TABLE accounts (
    user_id serial PRIMARY KEY,
    username VARCHAR ( 50 ) UNIQUE NOT NULL,
    password VARCHAR ( 50 ) NOT NULL,
    email VARCHAR ( 255 ) UNIQUE NOT NULL,
    created_on TIMESTAMP NOT NULL,
        last_login TIMESTAMP 
);

CodePudding user response：

Integers are easier/faster/less expensive to sort, search, and compare than strings. If you only have 5 users you may not notice the difference. At 10,000 users you'll feel it. Big time. Internally, you should use that key for everything, the username is only for authentication. It's not user bobsmith it's user 347.

That's the primary reason everyone uses an integer as an id/primary key. There are cases for using uuids instead, but those are stored as byte arrays, not a string (unless it's nosql, which changes all the rules).

CodePudding user response：

why cant the username be the primary key?

While it should be unique, it's not suitable as a primary key in this case.

In general, primary key values should never change. Any user provided value is highly likely to change at some point or another. If you make the username the primary key and then have other tables referencing your accounts table, you would have to change the username in each and every table. Using a generated primary key saves you from that trouble. If you made username the primary key, you probably would have to tell your users they can't change their username to make things easier for your.

Foreign keys are another reason why people prefer numbers instead of strings, as they make the referencing tables a bit smaller. Probably not a huge difference and depending on the type of queries you run, it might not even be noticeable. But still.

Note that the use of the serial "data type" is disouraged in favor of standard compliant identity columns.