Using [KEY] Id= Guid.CreateVersion7();

[Linkens]

I'm using the uuidv7 as PK.

Pomelo is mapping them to char(36) in MariaDB, wouldn't it be better to bind them to bin(16) ?
I feel that would be more efficient no ?

Please tell me if I'm missing something !

[lauxjpn]

You can control the Guid style by setting the GuidFormat connection string option to any supported value, e.g. to Binary16.

[Linkens]

That's really practical thank you !
But would it be better ? I have the intuition that it would still be accurately incremented (thanks to v7) on insert AND take less storage space. Would there be any downsides ?

[lauxjpn]

UUIDs are currently specified in RFC 9562.

In section 6: UUID Best Practices: 6.13 - DBMS and Database Considerations, they state that:

For many applications, such as databases, storing UUIDs as text is unnecessarily verbose, requiring 288 bits to represent 128-bit UUID values. Thus, where feasible, UUIDs SHOULD be stored within database applications as the underlying 128-bit binary value.

For other systems, UUIDs MAY be stored in binary form or as text, as appropriate. The trade-offs to both approaches are as follows:

• Storing in binary form requires less space and may result in faster data access.
• Storing as text requires more space but may require less translation if the resulting text form is to be used after retrieval, which may make it simpler to implement.

DBMS vendors are encouraged to provide functionality to generate and store UUID formats defined by this specification for use as identifiers or left parts of identifiers such as, but not limited to, primary keys, surrogate keys for temporal databases, foreign keys included in polymorphic relationships, and keys for key-value pairs in JSON columns and key-value databases. Applications using a monolithic database may find using database-generated UUIDs (as opposed to client-generated UUIDs) provides the best UUID monotonicity. In addition to UUIDs, additional identifiers MAY be used to ensure integrity and feedback.

Also, in section 6: UUID Best Practices: 6.11 - Sorting, they state that:

UUIDv6 and UUIDv7 are designed so that implementations that require sorting (e.g., database indexes) sort as opaque raw bytes without the need for parsing or introspection.

Time-ordered monotonic UUIDs benefit from greater database-index locality because the new values are near each other in the index. As a result, objects are more easily clustered together for better performance. The real-world differences in this approach of index locality versus random data inserts can be one order of magnitude or more.

UUID formats created by this specification are intended to be lexicographically sortable while in the textual representation.

UUIDs created by this specification are crafted with big-endian byte order (network byte order) in mind. If little-endian style is required, UUIDv8 is available for custom UUID formats.

I generally agree with that assessment and there should be no downsides and only upsides to storing UUIDs in binary format, if you are processing them mostly programmatically.

---

Pomelo also ships its own client-side UUID/GUID generator in MySqlSequentialGuidValueGenerator, so you don't have to manually generate your own UUIDs/GUIDs.

The implementation is still based on RFC 4122 however, and should be slightly modified to specify the newer version variants introduced in RFC 9562 instead of version 4 (random).