UUIDs: `gen_random_uuid()` vs `uuid-ossp`

UUIDs are the modern default choice for primary keys in SaaS (vs SERIAL/BIGSERIAL). They distribute better (no contention on a single sequence), they don't expose creation order, and they're globally unique. But to generate them there are options, and many devs arrive confused about which to use.

This capsule gives you the simple decision matrix: PostgreSQL 13+ → gen_random_uuid() almost always. PostgreSQL <13 → uuid-ossp. Special cases (UUID v1 timestamp-based) → uuid-ossp.

The options

Option 1: gen_random_uuid() — modern (PG 13+)

Available natively in PostgreSQL 13+ via the pgcrypto extension (built into the core, no need to download anything extra).

-- Available natively since PG 13, no extension
SELECT gen_random_uuid();
-- Returns: '550e8400-e29b-41d4-a716-446655440000'

In PG 13+, gen_random_uuid() is part of the PostgreSQL core — available without installing anything.

In PG <13, it requires pgcrypto:

CREATE EXTENSION IF NOT EXISTS pgcrypto;
SELECT gen_random_uuid();

Option 2: uuid-ossp — legacy, full-featured

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- v4 random (similar to gen_random_uuid)
SELECT uuid_generate_v4();

-- v1 timestamp-based (includes MAC address)
SELECT uuid_generate_v1();

-- v1mc timestamp-based with random MAC
SELECT uuid_generate_v1mc();

-- v3/v5 hash-based (deterministic)
SELECT uuid_generate_v5(uuid_ns_url(), 'http://example.com/path');

uuid-ossp provides multiple UUID versions, not just v4 random.

Option 3: Generate in the application

import uuid
new_id = uuid.uuid4()  # v4 random

Generate in the app, pass it as a parameter to the INSERT.

Decision matrix

Simple rule: PG 13+ with UUID v4 → gen_random_uuid(). Done.

99% of cases are covered by v4 random. Only specific cases (you need ordering by timestamp, deterministic IDs based on input) require v1/v3/v5.

Why UUID v4 (random) vs v1 (timestamp)?

v4 (random):

• Random 128 bits, no information leak.
• Uniform distribution — good for sharding.
• Each UUID is independent.

v1 (timestamp + MAC):

• Encodes the timestamp and MAC address.
• Information leak: MAC, moment of creation.
• Better for "ordering by creation" without a secondary column.

v4 is the modern default because most cases don't need timestamp ordering (you use created_at for that) and the problems with v1 (MAC leak, especially when running in the cloud) rule it out.

uuid_generate_v1mc() mitigates the MAC leak with a random MAC, but you lose the "advantage" of v1.

Implementation with SQLAlchemy 2.0

PG 13+: native

from sqlalchemy import text
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm import Mapped, mapped_column
import uuid


class Post(Base):
    __tablename__ = "posts"

    # Default generates the UUID in the DB with gen_random_uuid()
    id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        primary_key=True,
        server_default=text("gen_random_uuid()"),
    )
    title: Mapped[str]

server_default=text("gen_random_uuid()") makes PostgreSQL generate the UUID on INSERT.

PG <13 with pgcrypto

class Post(Base):
    id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        primary_key=True,
        server_default=text("gen_random_uuid()"),  # same function after CREATE EXTENSION pgcrypto
    )

Generate in the app

class Post(Base):
    id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        primary_key=True,
        default=uuid.uuid4,  # Python-side default
    )

DB-side vs Python-side: which one

Server-side (server_default=text("gen_random_uuid()")):

• ✅ Atomic with INSERT.
• ✅ Doesn't depend on Python (a SQL script can insert).
• ✅ No extra round-trip.
• ❌ If you want to know the ID BEFORE the INSERT, you can't.

Python-side (default=uuid.uuid4):

• ✅ You know the ID before inserting.
• ✅ Works offline (no DB).
• ❌ Depends on Python (raw SQL inserts don't get the default).

Recommendation: Python-side. It lets you use the ID in your code (e.g., pass to the client, log) before the commit.

new_post = Post(title="Hello")  # ID generated in Python here
print(new_post.id)  # Available immediately
session.add(new_post)
await session.commit()  # ID was already known

If you use a server-side default, after the commit run await session.refresh(new_post) to get the ID.

Migration: add a UUID column

# alembic/versions/XXX_use_uuid.py
def upgrade() -> None:
    # PG 13+ doesn't require an extension. PG <13 does
    # op.execute("CREATE EXTENSION IF NOT EXISTS pgcrypto")  # only PG <13

    op.create_table(
        'posts',
        sa.Column('id', postgresql.UUID(as_uuid=True),
                  primary_key=True, server_default=sa.text("gen_random_uuid()")),
        sa.Column('title', sa.String(200), nullable=False),
    )

Migrating from SERIAL to UUID

Real case: an existing app with id SERIAL. You want to migrate to UUID.

# Migration
def upgrade() -> None:
    # 1. Add the new column
    op.add_column(
        'posts',
        sa.Column('uuid', postgresql.UUID(as_uuid=True),
                  server_default=sa.text("gen_random_uuid()"),
                  nullable=False)
    )

    # 2. Backfill (the DEFAULT covers it, but shown explicitly)
    # op.execute("UPDATE posts SET uuid = gen_random_uuid()")  # redundant

    # 3. Create a unique index
    op.create_unique_constraint('uq_posts_uuid', 'posts', ['uuid'])

    # After validating that the app can use 'uuid':
    # 4. Drop the old primary key, create a new one (more complex, expand-contract)

Migrating a primary key requires careful coordination — guide #13 module 5 (zero-downtime migrations) covers the pattern.

Comparison with SERIAL/BIGSERIAL

SERIAL is faster and more compact. UUID is more distributed and secure.

For a typical SaaS, UUID wins because:

• It doesn't expose the number of records.
• It's trivial to create records "offline" (mobile apps).
• Multi-DB merging is trivial.
• IDs stay stable when exporting/importing.

Trade-off: 4x more space in indexes, slightly worse insert performance.

Performance: insert with UUID v4 random

Random UUIDs have poor cache locality — each new UUID lands on a random index page. Each INSERT can be a random page write.

Mitigation with UUID v7 (timestamp-prefixed random) — future:

UUID v7 (in draft RFC) has the timestamp as a prefix. It provides uniqueness + temporal ordering + cache locality. PostgreSQL doesn't have it natively yet, but there are extensions (pg_uuidv7).

For PG <17, an alternative: use ULID-style UUIDs from Python:

import ulid
new_id = ulid.new().uuid  # ULID converted to UUID — timestamp-prefixed

For typical apps, it's not a problem. It only matters at throughput >> 10k inserts/sec where cache locality is the limit.

Traps and common mistakes

1. Assuming that gen_random_uuid() requires uuid-ossp.

PG 13+: built-in (via the pgcrypto core). PG <13: requires CREATE EXTENSION pgcrypto. It does NOT require uuid-ossp.

2. Using uuid_generate_v4() when gen_random_uuid() is enough.

Functionally equivalent. gen_random_uuid() is newer and simpler. uuid_generate_v4 only if you need OTHER versions (v1/v3/v5).

3. Type UUID vs String(36).

# ❌ String(36) — you lose type safety, takes more space
id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))

# ✅ Native UUID
id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)

Native UUID is 16 bytes vs 36 bytes (String). And type-safe in Python.

4. as_uuid=True vs False.

UUID(as_uuid=True): SQLAlchemy returns uuid.UUID objects in Python. UUID(as_uuid=False): returns strings.

Use as_uuid=True for type safety.

5. UUID v1 in the cloud without thinking.

UUID v1 includes the MAC address. In cloud containers, the MAC is the host's, not the container's. Multiple instances can have the same MAC → not unique. uuid_generate_v1mc (random MAC) mitigates it.

6. Generating the UUID server-side when you need the ID beforehand.

If you want to log the ID before the commit, use a Python-side default. Server-side requires flush + refresh.

7. UUID in URLs without thinking.

/users/550e8400-e29b-41d4-a716-446655440000 is ugly. Consider:

• Separate slugs (/users/john-doe).
• Base62 encoding (shorter).
• Internal ID only, a different display.

8. JSON serialization of UUID.

Pydantic v2 handles UUID natively. For raw JSON, json.dumps(uuid_obj) fails. Cast to string if needed.

import json
data = {"id": str(my_uuid)}
json.dumps(data)

Exercise: implement UUIDs

Setup: a Post model with a UUID PK.

import uuid
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm import Mapped, mapped_column
from sqlalchemy import text


class Post(Base):
    __tablename__ = "posts"

    # Server-side default
    id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        primary_key=True,
        server_default=text("gen_random_uuid()"),
    )
    title: Mapped[str]

Step 1: create the migration and apply it.

alembic revision --autogenerate -m "add posts with uuid pk"
alembic upgrade head

Step 2: insert and verify.

INSERT INTO posts (title) VALUES ('Hello');
SELECT * FROM posts;
-- id is a UUID generated automatically

Step 3: Python-side version.

class Post2(Base):
    __tablename__ = "posts2"
    id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        primary_key=True,
        default=uuid.uuid4,  # Python default
    )
    title: Mapped[str]


# Test
new_post = Post2(title="Test")
print(new_post.id)  # Already available before commit

Step 4: compare index sizes.

-- Table with SERIAL
CREATE TABLE posts_serial (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200)
);

-- Table with UUID
CREATE TABLE posts_uuid (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title VARCHAR(200)
);

-- Insert 10k rows in each
-- Compare PK index size

SELECT pg_size_pretty(pg_relation_size('posts_serial_pkey'));
SELECT pg_size_pretty(pg_relation_size('posts_uuid_pkey'));

How much bigger is UUID?

Step 5: measure insert performance.

-- Insert 100k in each
EXPLAIN ANALYZE
INSERT INTO posts_serial (title)
SELECT 'Post ' || generate_series FROM generate_series(1, 100000);

EXPLAIN ANALYZE
INSERT INTO posts_uuid (title)
SELECT 'Post ' || generate_series FROM generate_series(1, 100000);

Any difference?

posts_serial_pkey: 1.4 MB
posts_uuid_pkey: 4.2 MB

SERIAL: 1234ms for 100k
UUID:   1567ms for 100k (~30% slower)

Summary and next step

What you learned:

• gen_random_uuid() — modern, native in PG 13+. Recommended default.
• pgcrypto — for PG <13, same function but requires the extension.
• uuid-ossp — legacy, multiple versions (v1/v3/v4/v5). Only if you need more than v4.
• SQLAlchemy: UUID(as_uuid=True) with server_default=text("gen_random_uuid()") or default=uuid.uuid4.
• Python-side default lets you use the ID before the commit.
• Trade-offs: UUID ~3-4x larger, ~30% slower insert. OK for typical cases.
• UUID v7 (timestamp-prefixed) is the future — not native in PG yet.

In the next capsule we go to hstore — the extension that predates JSONB. You'll learn why it exists, when (rarely) it wins over JSONB, and the simple rule: JSONB always, except legacy.

Resources

1. PostgreSQL Docs — UUID Type — reference.
2. PostgreSQL Docs — pgcrypto — reference.
3. PostgreSQL Docs — uuid-ossp — reference.
4. RFC 4122 — UUIDs — the standard.
5. SQLAlchemy — UUID type — reference.
6. pg_uuidv7 — extension for timestamp-prefixed v7 UUIDs.

Capsule 04 of 08 — Module 7 — Advanced PostgreSQL for Backend Guide