New UUID Formats (opens in new tab)

(ietf.org)

36 pointsdimfeld4y ago10 comments

10 comments

I 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$

AgentK204y ago

Previous discussion https://news.ycombinator.com/item?id=28088213

jasonhansel4y ago

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.

lilyball4y ago

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.

mewpmewp24y ago

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.

1 more reply

orf4y ago

If the information has no requirements to be kept secret then they are very, very useful.

q-rews4y ago

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.

junon4y ago

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.

BugsJustFindMe4y ago

> 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?

j / k navigate · click thread line to collapse