New UUID Formats

[timando]

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$

[AgentK20]

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

[jasonhansel]

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.

[lilyball]

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.

[orf]

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

[junon]

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.

[BugsJustFindMe]

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