UUID v7 and Binary Storage for Primary Keys

[rolandbeisel]

Migrate from UUID v4 (stored as VARCHAR(255)) to UUID v7 (stored as BINARY(16) or native UUID type) for all primary key columns to improve database performance.

Motivation

Current Implementation

id VARCHAR(255) NOT NULL PRIMARY KEY

• UUID v4: Completely random, no time-based ordering
• VARCHAR(255): 36 bytes + length prefix, string comparisons

Problems with UUID v4 + VARCHAR

1. Poor B-Tree Index Performance

   • Random UUIDs scatter inserts across the entire B-Tree
   • Causes frequent page splits and index fragmentation
   • More disk I/O, worse cache utilization

2. Storage Overhead

   • VARCHAR stores 36 characters (+ overhead) vs. 16 bytes for binary
   • String comparisons are slower than binary comparisons
   • Larger index size = more memory needed

3. No Natural Ordering

   • UUID v4 has no relationship to creation time
   • Requires separate created_at column and index for time-based queries

Proposed Solution

UUID v7 (RFC 9562) provides:

• Time-ordered: Contains millisecond-precision Unix timestamp
• Monotonically increasing: New IDs are always "greater" than older ones
• Sequential inserts: Append-only behavior in B-Tree indexes

Expected Performance Gains

Metric	Improvement
Insert throughput	20-40% faster
Index size	~50% smaller
Primary key lookups	10-20% faster
Storage space	~50% less for ID columns

Alternatives Considered

UUID v7 with VARCHAR storage (no schema change)

Pros:

• No breaking change
• Still improves insert performance (sequential ordering)

Cons:

• Misses storage and comparison performance gains
• Half-measure

Verdict: Could be done in v1.x as a quick win, but full migration should happen in v2.0.
| Storage space | ~50% less for ID columns |

[coder966]

Hi Roland, great work in this library. And I like you are now considering performance. As this was my intention when I opened #164.

I saw a lot of areas where we could have significant performance gains with little (but could be breaking) changes, and wanted to submit PRs with benchmark numbers.

Regarding the PK, why are we even using a UUID ? Looking at the code, there's no need for us to generate the ID at the client side (no use before persistence). And since the IDs are not user facing. They don't need randomness part for security. A db sequence offers higher performance and at least 50% lower storage.

The reason for storage is obvious as UUID requires 16 bytes in supporting RDMSs, or 36 bytes in non-supporting ones.

The storage size affects not only the main table structure, but also the btree indexes (so the savings add up).

The reasons for performance are:

1- puller and other select statement workloads will benefit from smaller indexes meaning fewer I/O.

2- page splits can still happen even with v7 uuid because in extremely high throughout scenarios, concurrent clients can send insert statements where the random part of the uuid becomes the deciding factor of the order because all of the inserts were initiated at the same millisecond (the time precision of the time part in v7).

I guess a sequence is a better fit here. What do you think?

[rolandbeisel]

@coder966 Hey Khalid, great points!

You're right that a DB sequence would be more efficient for this use case:

• No client-side ID generation needed - IDs are only used internally for processing
• Storage: 8 bytes (BIGINT) vs 36 bytes (String UUID) - significant savings on table + indexes
• Guaranteed ordering - no millisecond-collision issues like UUIDv7
• Better performance - smaller indexes = fewer I/O, no page splits

The original UUID choice was likely "safe default" thinking, but you're correct it's unnecessary overhead for an ephemeral outbox table.

We'll consider this for v2!