Avoid UUID4 pkeys

For faster record insertion during transaction, a public_id could be generated on a auto increment int, rather than UUID4:

1. Convert a decimal integer like “2” into binary bits. E.g. a 4 byte, 32 bit integer: 00000000 00000000 00000000 00000010
2. Perform an exclusive OR (XOR) operation on all the bits using a key
3. Encode each bit using a base62 alphabet

Takes less space (4 vs 16 bytes), less cache / page misses, less IO, also still pseudo random (based on the key).

Or, if you still want to use UUID4 and suffering from poor query performance, use CLUSTER command on created_ts or on some other high cardinality field that was also already sorted. This neatly puts fragmented records together and reclaims wasted space. Significantly reduces page scans on order by queries (involving both UUID & ts field).

I wonder why people don’t use UUID7, it is much better suited for primary keys than UUID4. It’s already out in postgres18.

more on: https://andyatkinson.com/avoid-uuid-version-4-primary-keys
discussions: https://news.ycombinator.com/item?id=46272487