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
Chronological 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	Native in PG 16+, pg_uuidv7 extension otherwise	3rd Party Extension pg_ulid
Suprabase Support	Yes, uuid-ossp extension	Yes, seamless ingration with uuid columns	No. Store as TEXT or CHAR(26) column
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
  • Included in PostgreSQL 16. Use gen_random_uuidv7() to generate a value. Requires pgcrypto extension.
• Cons
  • For Postgres 14-15 you need a 3rd party extension to generate values.
  • 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

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 V. 16 has a built-in extension that generates UUIDv7 values:

CREATE TABLE readings {
    id      UUID DEFAULT gen_random_uuidv7() PRIMARY KEY,

For earlier versions get the extension pg_uuidv7 from Github https://github.com/fboulnois/pg_uuidv7.git and build it yourself. Then do:

-- add extension 
CREATE EXTENSION IF NOT EXISTS pg_uuidv7;

CREATE TABLE readings (
    id        UUID DEFAULT uuid_generate_v7() PRIMARY KEY,

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

### Test For Built-in UUIDv7 Support

Start an interactive Postgres session and enter:
```sql
pg>  SELECT gen_random_uuidv7();

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:

# Requires extra package for "ulid"
import ulid
import uuid
import psycopg2
from psycopg2.extras import register_uuid

# 1) Generate a new ULID
new_ulid = ulid.new()      # WRONG!  Should be:  ulid.ULID()

# 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.

Which Python Package for ULID Support?

ChatGPT and Deepseek disagree. One of these:

• ulid-py. pip install ulid
• ulid2 (More actively maintained, additional functions)
• python-ulid Deepseek suggested this. Not sure.