SERIAL vs IDENTITY vs UUID

Three numbering schemes

PostgreSQL gives you three ways to auto-generate primary keys. They are not equivalent — pick the wrong one and you inherit the wrong set of trade-offs forever.

SERIAL — legacy, avoid in new code

SERIAL is the original "auto-increment integer." It creates a sequence behind the scenes and uses it as the column's default. It works, but the column is technically nullable, the sequence is loosely owned, and users can override the value silently. The SQL standard replacement (IDENTITY) is strictly better.

IDENTITY — the modern default

The SQL-standard way to auto-generate integers. GENERATED ALWAYS AS IDENTITY prevents application code from accidentally inserting an explicit ID. GENERATED BY DEFAULT AS IDENTITY lets you override when you need to (rare). Cleaner semantics, cleaner pg_dump output, no sequence-ownership weirdness.

UUID — globally unique identifiers

128-bit identifiers safe for distributed systems, public APIs, and merging data from multiple sources without collision. Two flavours matter: uuidv4() (random; good but not sortable) and uuidv7() (PG 18+; embeds a timestamp, sortable, B-tree-friendly).

Picking

Internal primary keys: use INTEGER GENERATED ALWAYS AS IDENTITY by default. Public-facing identifiers (URL slugs, API tokens, anything visible to users): use UUID, ideally uuidv7(). Many real schemas have both — internal id for joins, public uuid for external references.

Code

Old style (SERIAL — avoid in new code)·sql

CREATE TABLE items_legacy (
    id   SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

Modern style (IDENTITY — recommended)·sql

CREATE TABLE items (
    id   INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL
);

-- To override the generated value (rare), use OVERRIDING:
INSERT INTO items (id, name)
OVERRIDING SYSTEM VALUE
VALUES (999, 'Special Item');

UUIDs for public IDs·sql

-- v4: random; uniqueness without coordination, but bad for B-tree locality
CREATE TABLE sessions_v4 (
    id   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id INTEGER NOT NULL
);

-- v7: time-sortable; combines UUID safety with index-friendly ordering
CREATE TABLE sessions_v7 (
    id   UUID PRIMARY KEY DEFAULT uuidv7(),
    user_id INTEGER NOT NULL
);

Three numbering schemes

SERIAL — legacy, avoid in new code

IDENTITY — the modern default

UUID — globally unique identifiers

Picking

Code

External links

Exercise

Progress

Comments 0