UUID and uuidv7()

~10 min · extensions, uuid

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

UUIDs as first-class identifiers

PostgreSQL has built-in UUID type and built-in generators: gen_random_uuid() (v4) and uuidv7() (PG 18+). Pre-PG 18 you'd add the uuid-ossp extension; PG 13+ has gen_random_uuid in the core.

v4 vs v7

v4: 122 random bits. Globally unique, but completely unsorted — every insert into a B-tree index lands at a random position, fragmenting the index over time. Fine for IDs you read by exact lookup; not great for primary keys.

v7: 48-bit timestamp + 74 random bits. Globally unique AND time-sortable. New rows land at the end of the index (just like a sequential integer), and ORDER BY the UUID gives chronological order for free.

When to use which

Internal primary keys: integer IDENTITY (cheap to join, smaller indexes). Public-facing IDs you expose in URLs/APIs: uuidv7() if available, else gen_random_uuid(). Many tables have both — internal id for joins, external uuid for exposure.

Code

Generate UUIDs·sql

SELECT gen_random_uuid();   -- v4 random
SELECT uuidv7();             -- v7 time-sortable (PG 18+)

-- Default for a column
CREATE TABLE api_tokens (
    id          UUID PRIMARY KEY DEFAULT uuidv7(),
    user_id     INTEGER NOT NULL REFERENCES users(id),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

Pre-PG 18 — add the extension·sql

-- For installations without uuidv7 in core
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v4();
SELECT uuid_generate_v1();   -- time-based but leaks MAC address

Two-id pattern (internal + public)·sql

CREATE TABLE invoices (
    id          INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  -- joins
    public_id   UUID NOT NULL UNIQUE DEFAULT uuidv7(),               -- URL
    user_id     INTEGER NOT NULL REFERENCES users(id),
    amount      NUMERIC(10,2) NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

External links

Exercise

Add a public_id UUID column to a table. Default it to uuidv7() if you can; gen_random_uuid() otherwise. Insert 1000 rows; compare the size of the index on public_id (v7 should be smaller and faster to write).