New UUID Formats
ietf.orgI created a PostgreSQL function to generate a v7 uuid. On my laptop, it is about the same speed as generate_random_uuid
CREATE OR REPLACE FUNCTION public.new_uuid_v7ish3()
RETURNS uuid
LANGUAGE sql
AS $function$
SELECT (
lpad(
to_hex(
(
(((extract('epoch' from clock_timestamp()) * 4096) )::bigint << (64-36-12)) --36 bit seconds and 12 bit 4096th of a second
| (7::bigint << (64-36-12-4)) --Version 7
| (random() * 4096)::bigint --12 bit "sequence number"
)
),
16,'0')
||lpad(
to_hex(
(2::bigint << 62) --Variant
+ ((random() * x'4000'::bigint)::bigint << 48) --14 bits of randomness
+ ((random() * x'1000000000000'::bigint)::bigint) --48 bits of randomness
),
16,'0'
)
)::uuid
$function$Previous discussion https://news.ycombinator.com/item?id=28088213
Personally, I dislike UUIDs that encode timestamps. They embed information in identifiers that look opaque, and can therefore accidentally communicate information about the history of an object that is intended to be kept secret. Much better to just use a randomly generated ID and then provide a separate timestamp, so that you can provide one without revealing the other.
The whole point of using a UUID that embeds a timestamp is when you want the timestamp of the UUID to be public. Use UUIDv4 if you don’t need time, but if you do, it makes far more sense to put the timestamp in the UUID than to have a (UUID,timestamp) pair as the latter is equivalent to just having a longer UUID that embeds a timestamp.
Just curious when would you want timestamp to be in UUID rather than extra column that would make it easier to query, read, parse and modify in the future if it may be needed for whatever reason?
I usually include created_at, updated_at, and potentially deleted_at pretty much always in my tables as I don't think they affect storage and performance enough not to considering potential benefits down the line.
AFAIK many queries are mostly a lot more efficient if the UUID is in insertion order which is facilitated by including the timestamp at the front of the UUID
If the information has no requirements to be kept secret then they are very, very useful.
They can also be useful to the wrong people. Image URLs also don’t have the requirement of keeping the username secret, but they’ve been used to find the Facebook account that published them so Facebook changed that.
Agreed. MongoDB isn't UUID per se but apps that use the IDs directly somehow usually don't realize they're leaking information for the exact same reason.
> Much better to just use a randomly generated ID and then provide a separate timestamp, so that you can provide one without revealing the other.
If you're talking about allocating the same number of bits either way, your way vs their way just expresses a trade between never rolling the random number generator twice for the same entry because you can't generate collisions at different times (theirs) and demanding a uniqueness guarantee for just the random portion on its own and thus re-rolling on collision (yours).
Would subsequently encrypting the time-based IDs before sharing them satisfy your desire to not leak the time information?