For postgres I think the timestamp prefix offers ordering, such that new rows are added to the end. The hash function here seems like it would cause new rows to jump around, which would be an improvement for databases like Spanner, but I think would hurt postgres perf.
I'd kind of be interested in an ID that does combines an autoincrement prefix with a random suffix. Every ID would be unique and monotonically increasing due to the autoincrement and the random suffix would make it unguessable (or less guessable, depending on the number of bits used). It's an odd choice for a UUID, which usually worrys about decentralized ID generation, but so many smaller apps use UUIDs with a single central postgres database...
These kinds of experiments are well suited to UUIDv8.
mebcitto•3mo ago
Yeah, I don't think "fixing" is really the right term here, since this brings a different set of tradeoffs. You don't leak timestamps, you get ok page locality, but you lose the ability to sort by the IDs. I do see how this might be a good choice for a general PK default (i.e. by default, don't leak stuff).
8organicbits•3mo ago
I'd kind of be interested in an ID that does combines an autoincrement prefix with a random suffix. Every ID would be unique and monotonically increasing due to the autoincrement and the random suffix would make it unguessable (or less guessable, depending on the number of bits used). It's an odd choice for a UUID, which usually worrys about decentralized ID generation, but so many smaller apps use UUIDs with a single central postgres database...
These kinds of experiments are well suited to UUIDv8.
mebcitto•3mo ago