Understanding Time-Ordered UUIDv7 Keys

Standard databases frequently rely on auto-incrementing integer keys (e.g. 1, 2, 3...) or random UUIDv4 string hashes. However, both of these introduce major drawbacks in multi-tenant enterprise architectures:

• Integer leaking: Sequential keys allow attackers to guess total records volume easily.
• Page Splits Fragmentation: Random UUIDv4 inserts occur randomly across the database B-Tree index, causing severe disk page splitting, high disk I/O, and indexing fragmentation under InnoDB.

UUIDv7 (defined in RFC 9562) solves this natively. It is a 128-bit string structured sequentially with a timestamp prefix:

 019ec4ee-fdda-7049-81d9-446133464390
 └───┬───┘ └──┬─┘ └──┬─┘ └──────┬─────┘
  48-bit     4-bit  12-bit   64-bit Random
 Timestamp    Ver  Variant     Entropy Bits

• First 48 bits: Millisecond Unix epoch timestamp (ensures time-ordered sorting).
• Entropy bits: Provides cryptographic randomness to guarantee absolute uniqueness across replication nodes.

Because the prefix is time-sequential, newly generated UUIDv7 keys are always appended at the very end of the InnoDB B-Tree index. This transforms random writes into fast sequential appends, matching integer-like speed while retaining standard UUID uniqueness!