modulo operation on uuid to determine shard_id

Question

We have Postgresql database and we are doing some sharding there. Also we are doing READ load-balancing. To do that, first we check on which shard data is located.

We are getting shard id based on this formula:

entry_id % num_of_shards = shard_id

and it works good. Currently we have auto increment ids (int).

We want to switch to uuid v4. What formula will be in case of uuid to determine shard_id?

Thanks

Answer 1

The simplest is to turn the shard_id into a text value. If you want 16 shards then use only the first char from the uuid. For 256 shards use the first 2 chars and so on.

select substring(uuid_generate_v4()::text from 1 for 2) as shard_id;
 shard_id 
----------
 c6

For just two shards get the least significant bit of the first char:

select
    substring(
        ('x' || substring(uuid_generate_v4()::text from 1 for 1))::bit(4) 
        from 4 for 1
    ) as shard_id
;
 shard_id 
----------
 0

For four shards get 2 bits (from 3 for 2) and so on. Or for an integer as commented:

select 
    (
        'x' || substring(uuid_generate_v4()::text from 1 for 1)
    )::bit(4)::int % 2 as shard_id