This is why I’ll probably just always use a UUIDv7 primary key and a secondary UUIDv4 indexed external identifier… which is extremely close to how I tend to do things today (I’ve been using ULID and UUIDv4)
Lookup speed. Direct mappings are faster. If something needs an external identifier that can be looked up for URL/API queries and such things…
Internal sorts and index on write with ULID/UUIDv7 which reveal potentially sensitive timestamp information, and so when that’s not appropriate a separate column can be used for…
External opaque identifiers which are UUIDv4, and if indexing speeds become an issue, can be switched to deferred indexing…
It’s a good balance and everything I’ve ever used supports this (I only needed a pretty strong representation wrapper for ULIDs since PG doesn’t validate UUID bit field structures so i can just miss-use UUID columns)
It looks like Snowflake has the same information exposure issues that using UUIDv7 or ULID for a publicly visible identifier.