Uuid

Problem

Due to their randomness UUID v4 might be sub-optimal for database operations, see https://www.cybertec-postgresql.com/en/unexpected-downsides-of-uuid-keys-in-postgresql/.

Constraints

good performance in the database
reduce index size
future proof

Assumptions

we cannot easily change the UUIDs later in production
frontends have reasonable clock precision and performance that uuidv7 usually works for us

Solutions

UUIDv7

https://www.ietf.org/archive/id/draft-peabody-dispatch-new-uuid-format-04.html (Expired 25 December 2022)

The non-expired version in PDF: https://www.ietf.org/rfc/rfc9562.pdf

Compromise

Keep uuidv4 as the stable external identifier and introduce a new internal key (maybe even auto-increment int or uuidv7) if needed for ordering etc.

Pro: less changes in frontend/backend
Con: needs more space (fails constraint 2.)
Con: unclear performance (might fail constraint 1.)

ULIDs

https://github.com/ulid/spec

Pro: readability maybe better
Pro: a bit more bits for randomness and a bit more elegant design
Con: no version bits
Con: would require ULID to be converted to UUID when querying in SQL
Con: doesn't mention uuidv7 on their page (might fail constraint 3.)
Con: little initiative with only

Others

Were not checked:

NanoID
KSUID
SnowflakeID

Decision

We use UUIDv7.

Rationale

https://ardentperf.com/2024/02/03/uuid-benchmark-war/
https://dev.to/umangsinha12/postgresql-uuid-performance-benchmarking-random-v4-and-time-based-v7-uuids-n9b The TL;DR is that for tables with many millions of rows uuidv7 is
- significantly faster (insert time ~35% better) and
- has less latency (point lookup ~4x faster)
- index size is ~22% smaller
- not worse in any point that was measured (pure randomness in uuidv4 is basically worst case)

Implications

we should reject non-v7 uuids in backend/DB
for initial conversion we could use either PostgreSQL 18 or https://github.com/fboulnois/pg_uuidv7
1. Add new column (id_v7)
2. Backfill with new-version UUIDs
3. Update all foreign keys to reference the new column
4. Add user_id_v7 to referencing tables
5. Switch app code to use the new columns
6. Make id_v7 the primary key

Notes