Postgres UUIDv7 and per-back end monotonicity

Original Article ↗ Hacker News Discussion ↗

PostgreSQL’s new support for UUID version 7, a time-ordered identifier format, is prompting debate over how much developers should rely on its monotonic ordering guarantees. Commenters examine what the UUIDv7 RFC actually promises, how Postgres uses extra bits to improve within-millisecond ordering, and the trade-offs this creates for portability, collision risk, and long-term stability. They also compare UUIDv7 to alternatives like ULID and Snowflake IDs, questioning when it’s beneficial to blend timestamps, randomness, and uniqueness into a single 128‑bit value versus keeping those concerns separate.

UUID Versions and Use Cases

  • UUIDv4: purely random; widely used but can fail when entropy sources are weak.
  • UUIDv3/v5: namespace+hash based; useful for deterministic IDs and idempotent imports but controversial because they use MD5/SHA‑1.
    • Some participants claim certain high‑assurance/regulated contexts ban SHA‑1 (and even v4 with poor entropy) for critical IDs where collisions must be “impossible”.
    • Others argue MD5/SHA‑1 remain fine as non‑cryptographic hashes and that UUIDs are not integrity or confidentiality mechanisms.
  • UUIDv7: time‑ordered (timestamp in high bits, remaining bits mostly random) and intended as the “modern” sortable UUID.

UUIDv7 Monotonicity and RFC Interpretation

  • The RFC states that time‑based UUIDs “will be monotonic” due to embedded timestamps and describes optional methods to provide additional monotonicity within a millisecond (using extra time precision and/or counters in rand_a/rand_b).
  • Disagreement exists:
    • One side: monotonicity is a core guarantee; non‑monotonic behavior is an implementation bug.
    • Other side: only partial monotonicity is guaranteed; sub‑millisecond ordering is explicitly optional, so depending on it is risky.

Postgres Implementation and Reliance on Behavior

  • Postgres’s v7 implementation uses RFC “Method 3”: replace 12 random bits with higher‑precision time (down to ~250 ns steps per ms), yielding per‑backend monotonicity at high rates.
  • Critics argue:
    • This is not (yet) a documented API guarantee; relying on it couples application code to a particular DB version/engine.
    • Tests should explicitly sort or use sets rather than depend on implicit ID ordering.
  • Others counter that:
    • Using well‑documented implementation details is acceptable, especially in tests.
    • In practice, widely used behaviors often become de facto specs (Hyrum’s Law).

Entropy, Collisions, and Time Bias

  • Using 12 bits of timestamp reduces randomness but also shortens the time window per bucket; consensus is collisions remain extremely unlikely for realistic workloads.
  • Some note real clocks aren’t uniformly random at nanosecond scale, so time‑derived bits are a weaker randomness source than true PRNG output, but still good enough for most databases.

Comparisons: ULID, Snowflake, and Alternatives

  • ULID:
    • Guarantees per‑process monotonicity by incrementing low bits; spec mandates monotonic generation.
    • Uses base32 encoding; harder to manipulate in vanilla SQL compared to hex UUIDv7.
    • Monotonicity requires serialized generation; only per process, not global.
  • Snowflake:
    • 64‑bit, millisecond timestamp + machine ID + counter; good for distributed monotonic integers.
    • UUIDv7’s advantage is compatibility with existing UUID ecosystems and DB types while giving Snowflake‑like ordering.
  • Some suggest separating concerns instead of using v7 for everything: keep an autoincrement PK, an opaque random ID (v4), and a high‑precision timestamp, rather than conflating all three into one value.

Practical Concerns and Code Longevity

  • One camp emphasizes long‑lived systems: avoid depending on undocumented/optional behavior to “save a line of code”.
  • Another camp notes much business code is short‑lived; absolute robustness may be overkill, but even then, simple explicit sorting is a low‑cost safeguard.
  • Several commenters remind that ID order does not equal commit order; cursor‑based APIs and background jobs need stronger invariants (e.g., a “frozen” watermark) beyond monotonic UUIDs.