How to store a uuid type into bigint effectively?

Question

In my application in Python, I have a function that accepts (_id: uuid.UUID) which is 128-bit value. I need to store this value into postgres database as a advisory lock using pg_advisory_xact_lock which expects bigInt type which is a 64-bit integer. What would be the best way to go about this? Should I use some sort of hash table?

Answer 1

I don't know what You've tried but you can use a hashing function to reduce the size of the UUID while preserving uniqueness. Here's a general approach you can follow:

1. Hash the UUID: A hash function such as SHA-256 can be used to turn the 128-bit UUID into a fixed-size 256-bit hash. Because this hash is larger than 64 bits, it must be further reduced.
2. Truncate the Hash: You can truncate the hash to fit it into a 64-bit integer. Keep in mind, however, that this truncation will result in a loss of individuality. The shortened hash may not be unique for all UUIDs, but it should be unique enough for the majority of practical use cases and then convert the truncated hash to a BigInt in PostgreSQL. Below is some Python code illustrating this approach using the uuid and hashlib libraries:

import hashlib
import uuid

def uuid_to_int(uuid_val):
    # Convert the UUID to bytes
    uuid_bytes = uuid_val.bytes

    # Calculate a hash of the UUID bytes (using MD5 in this example)
    md5_hash = hashlib.md5(uuid_bytes)

    # Take the first 8 bytes of the MD5 hash and convert them to an integer
    result = int.from_bytes(md5_hash.digest()[:8], byteorder='big')

    return result