UUID Strategies

Postgres supports UUIDs natively as a 16-byte type. The questions are: which UUID version, when to use UUIDs vs bigint, and how to keep index performance acceptable.

Table of Contents

• UUID vs bigint
• UUID v4: Random
• UUID v7: Time-Ordered
• The Hybrid Pattern: bigint Internal, UUID External
• Storage and Index Considerations
• Choosing

UUID vs bigint

Don't pick UUIDs because they "feel modern." Pick them when you need:

• Distributed ID generation without coordinating with the DB (mobile clients, multiple write regions)
• Public-facing identifiers where leaking a sequence is a problem
• Merge-friendly IDs across systems that share data

For internal data with a single writer, bigint (or composite keys per the skill's conventions) is faster, smaller, and easier to debug.

UUID v4: Random

The standard random UUID. Generated by gen_random_uuid() from pgcrypto:

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE document (
    document_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    ...
);

Index fragmentation gotcha. B-tree indexes on UUID v4 fragment badly because each insert lands at a random position in the index. Compared to a sequential bigint, you get:

• Higher write amplification (more page splits)
• Worse cache locality (recent rows scattered across pages)
• Slower bulk inserts

For low-volume tables this is fine. For high-write tables (event logs, audit, queues), the cost shows up under load.

UUID v7: Time-Ordered

UUID v7 (RFC 9562) encodes a millisecond timestamp in the leading bits, so v7 values sort roughly by creation time. This makes the index behave like a bigint — new IDs land at the right side of the B-tree, no fragmentation:

-- Hand-rolled v7 generator (since gen_random_uuid is v4)
CREATE OR REPLACE FUNCTION fn_gen_uuid_v7()
RETURNS uuid LANGUAGE plpgsql AS $$
DECLARE
    v_ts bytea := int8send(EXTRACT(EPOCH FROM clock_timestamp())::bigint * 1000);
    v_rand bytea := gen_random_bytes(10);
BEGIN
    -- Layout: 48 bits timestamp + 4 bits version + 12 bits random + 2 bits variant + 62 bits random
    RETURN (
        SUBSTRING(v_ts FROM 3 FOR 6) ||
        SET_BYTE('\x00'::bytea, 0, ((127 & 7) | 112))  -- version 7
        -- ... etc; in practice use an extension
    )::uuid;
END;
$$;

In practice use an extension or library — pg_uuidv7 is one option. Some managed Postgres hosts (Supabase) ship it.

Tradeoff: the timestamp leaks creation time. For most apps that's fine or even useful; for adversarial settings where timing is sensitive, prefer v4.

The Hybrid Pattern: bigint Internal, UUID External

Get the best of both: bigint PK for internal joins, UUID for the public boundary:

CREATE TABLE customer (
    customer_no customer_no PRIMARY KEY,           -- internal bigint
    public_id   uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    ...
);

• Internal queries, FKs, joins use customer_no — small, fast, friendly to indexes
• API responses and public links use public_id — opaque, leak-free
• A unique index on public_id gives O(log n) lookup at the API boundary

This is the most common production pattern. The cost is one extra column (16 bytes per row) and a UNIQUE index.

Storage and Index Considerations

• uuid type is 16 bytes. Never store UUIDs as text (37 bytes plus overhead) or varchar(36).
• A UUID PK on a 100M-row table costs ~6GB just for the PK index. A bigint PK costs ~3GB. Plan accordingly.
• Partial indexes still work with UUIDs — same patterns apply (soft delete, status filters).
• Cluster on a UUID PK is mostly meaningless with v4 — no useful order to cluster by. v7 clusters by time.

Choosing

• Internal, single-writer DB → bigint or composite keys. The skill's existing PK conventions (hierarchical composite keys, base/subtype PK inheritance) work here.
• Public-facing IDs → add a UUID column alongside the internal PK. Index UNIQUE. Use for the API boundary only.
• Distributed writes / multi-region → UUID PK (preferably v7 to keep index performance reasonable).
• High-write append-only table needing UUIDs → UUID v7, not v4.
• Don't reach for UUID v4 PK unless you've ruled out the hybrid pattern. The index cost is real.