Compare natural keys and surrogate keys, and compare bigint identity columns with UUIDs as primary keys in PostgreSQL.

Usually use a surrogate primary key plus a UNIQUE constraint on the natural key. For the id type, bigint identity is smaller and faster, and UUIDs can be generated anywhere.

Natural vs surrogate keys. A natural key comes from the data, like an email. It can change, be reused, or turn out not to be unique. When it changes, every foreign key that points to it must change too. A surrogate key is a meaningless id that never changes.

bigint GENERATED ALWAYS AS IDENTITY:

• It is 8 bytes.
• Each new id is bigger than the last, so new entries go to the right edge of the B-tree index. This keeps the index compact and cache-friendly.
• But the ids are guessable, and only the database can create them.

UUID:

• It is 16 bytes.
• Any machine can generate it, so it suits offline clients and merged data.
• It does not reveal how many rows you have.
• Random version 4 UUIDs spread inserts all over the index. This causes more page splits and cache misses.
• Time-ordered UUIDv7 keeps most of the ordering benefit.
• gen_random_uuid() is built into PostgreSQL.