Use identity columns instead of serial

Primary keys are hard to change once other tables point at them, so start with the modern default.

GENERATED ALWAYS AS IDENTITY is the SQL standard way to auto-number a column and the replacement for the older serial pseudo-type:

• The sequence is tied to the column, so permissions are simpler.
• ALWAYS stops code from inserting its own IDs by accident.

When you need UUIDs

Sometimes IDs must be generated outside the database, or be hard to guess in URLs. PostgreSQL 18 adds uuidv7(), which creates time-ordered UUIDs.

They index far better than random version 4 UUIDs, because new values land at the end of the B-tree instead of anywhere in it.

A common pattern is a compact bigint identity key for joins plus a uuidv7() public ID for URLs, as in the example.

CREATE TABLE users (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  public_id  uuid NOT NULL DEFAULT uuidv7() UNIQUE,
  email      text NOT NULL
);