UUID Variants

UUID are globally unique identifiers that are 16-bytes long. Downsides of UUID are:

• database indices are fragmented
• not chronologically ordered
• use 16 bytes compared to 4 bytes for SERIAL, INTEGER, or GENERATED AS IDENTITY

Several variants exist: UUIDv1, UUIDv7, and ULID.

Below is a comparison of their structure, properties, privacy considerations, and typical use-cases.

Feature	UUIDv1	UUIDv7	ULID
Specification	RFC 4122	IETF draft (UUIDv7, time-ordered)	ULID spec
Bit Layout	60 bit timestamp + 48 bit node id + 14 bit sequence	48 bit timestamp + 74 bit random + 6 bit version/variant	48 bit timestamp + 80 bit random
Timestamp Resolution	100 ns increments since 1582-10-15	1 ms since Unix epoch (1970-01-01)	1 ms since Unix epoch
Lexicographic Order	Yes, if compared as raw bytes	Yes, natural time ordering	Yes, sorts by timestamp then random
Global Uniqueness	Yes (node/MAC + sequence)	Yes, almost surely	Yes, almost surely
Privacy	Leaks MAC or machine identifier	No machine identifier; only time + random	No machine identifier; only time + random
Spoof Resistance	Weak (MAC can be spoofed; collisions if clock moves backward)	Stronger (random cushion; monotonic if implemented carefully)	Strong (monotonic cushion; collision risk only within same ms and same monotonic sequence)
Size on Disk/Index	16 bytes	16 bytes	16 bytes
Ease of Generation	Widely supported in standard libraries	Emerging support; requires up-to-date libraries	Widely supported (multiple languages)
Postgres Support	Native uuid-ossp	PG pg_uuidv7 extension	3rd Party Extension pg_ulid
Use-Cases	Legacy systems; when backward compatibility with UUIDv1 is required	New applications needing RFC-style UUIDs with time order	Time-sortable IDs with simple spec; especially JS/browser use

---

UUIDv1

• Structure
  • 60 bits of a 100-nanosecond timestamp (since 1582-10-15)
  • 14 bit sequence (to avoid collisions within the same timestamp)
  • 48 bit “node” (MAC address or random fallback)
• Pros
  • Naturally ordered by generation time
  • Supported everywhere (standard in most UUID libraries)
• Cons
  • Reveals hardware/MAC address (privacy leak)
  • Vulnerable to clock regression (requires sequence bump)

UUIDv7 (Time-Ordered UUID)

• Structure
  • 48 bit Unix-millisecond timestamp
  • 74 bit cryptographically random payload
  • 6 bit version/variant markers
• Pros
  • Millisecond ordering of IDs (good for time-series indexing)
  • No hardware identifier
  • Fully compliant with the overall UUID format
• Cons
  • Library support is still emerging (you may need a custom generator)
  • Less granular timestamp (millisecond vs. 100 ns)

ULID (Universally Unique Lexicographically Sortable Identifier)

• Structure
  • 48 bit Unix-millisecond timestamp
  • 80 bit cryptographically random payload
• Pros
  • Lexicographically sortable when encoded as Crockford’s Base32 (26 chars)
  • No hardware identifier, simpler spec than UUIDv7
  • Built-in monotonicity (“monotonic ULID”) to avoid collisions within the same ms
• Cons
  • Not a UUID; may not integrate with libraries expecting RFC-4122 format
  • Slightly larger random payload than UUIDv7 (80 bits vs. 74 bits)

When to Choose Which?

• UUIDv1: Legacy compatibility or when library support and fine-grained (100 ns) timestamps matter—and you can tolerate the MAC leak (or use a random node).

• UUIDv7: If you want to stick with the UUID standard, need lexicographic time ordering, and don’t mind millisecond resolution (and you have a library that supports v7).

• ULID: When you need simple, time-sortable IDs in environments like JavaScript/browser, prefer a compact Base32 string, and can adopt a non-UUID identifier.

Practical Tip

If you’re storing and indexing large, time-ordered logs (e.g. meter readings), time-ordered IDs (UUIDv7 or ULID) help keep your B-tree indexes hot at the “right” end—minimizing page splits and improving insert throughput. For most new projects, ULID is often the easiest to adopt; if you need strict UUID compatibility, go with UUIDv7.

Postgres Support for UUIDv7

Postgres has a built-in extension for UUIDv7:

-- Install extension (requires PostgreSQL 16+ or pg_uuidv7 extension)
CREATE EXTENSION IF NOT EXISTS pg_uuidv7;

-- Table definition
CREATE TABLE readings (
    id UUID DEFAULT uuid_generate_v7() PRIMARY KEY,
    source_id UUID NOT NULL,
    value FLOAT NOT NULL,
    timestamp TIMESTAMPTZ DEFAULT NOW()
);

-- Index for time-sorted queries
CREATE INDEX idx_readings_id ON readings (id);  -- Sorted by time implicitly

Note: the comment is inconsistent with early code that claimed UUIDv7 supported in PostgreSQL 14+.

Postgres Support for ULID

ULID is not a built-in type in PostgreSQL. There are several ways to implement it in PostgreSQL:

1. pg_ulid (or similar) Postgres extension adds a true ULID column type and generator functions (ulid_generate() and gen_ulid()):

   -- As superuser install the extension in your database
   CREATE EXTENSION IF NOT EXISTS pg_ulid;
   
   -- Usage in a table
   CREATE TABLE users {
      id     ulid    PRIMARY KEY DEFAULT ulid_generate(),

2. Store ULIDs as a Fixed-Length String.
   In Database:

   CREATE TABLE users {
      id     CHAR(26) PRIMARY KEY,

   in FastAPI:

   user_id = str(ulid.new())
   # pass user_id to INSERT commands for users

3. Store ULIDs in the UUID Type In Database:

   CREATE TABLE users {
      id     UUID PRIMARY KEY  DEFAULT uuid_nil(),   -- placeholder

   in Python using the psycopg2 extension, generate a ULID, convert it to a uuid.UUID, and insert into a new user row in database:

import ulid import uuid import psycopg2 from psycopg2.extras import register_uuid

1) Generate a new ULID

new_ulid = ulid.new()

2) Convert ULID bytes to UUID

This treats the 128-bit ULID payload as a UUID.

new_uuid = uuid.UUID(bytes=new_ulid.bytes)

3) (Optional) Register UUID adapter so psycopg2 knows how to send uuid.UUID

register_uuid()

4) Connect to your Postgres database

conn = psycopg2.connect( dbname="your_db", user="your_user", password="your_password", host="your_host", port=5432, ) cur = conn.cursor()

5) Insert the new user

cur.execute( """ INSERT INTO users (id, email, display_name, created_at) VALUES (%s, %s, %s, NOW()) """, ( new_uuid, # will be sent as PostgreSQL UUID "alice@example.com", "Alice Doe", ), )

conn.commit() cur.close() conn.close()

### Which approach to choose?

- For simplicity and full ULID semantics inside the database, install a ULID extension (`pg_ulid`).
- For tight control or if you can’t install extensions on a managed service, store ULIDs as CHAR(26) and generate them in FastAPI.
- If you already rely heavily on UUID and want to reuse indexes and tooling, pack ULIDs into UUID columns with application-level conversion.

All three approaches preserve lexicographic sort-order by timestamp and global uniqueness. 

For a managed Supabase deployment, you can either install the extension via their SQL editor (if they support it) or default to the CHAR(26) column strategy.