4 ms·

The author comes across one of the issues with using UUIDv4 as a (primary) key — they are not great for btree performance. Particularly for clustered indexes li

The author comes across one of the issues with using UUIDv4 as a (primary) key — they are not great for btree performance. Particularly for clustered indexes like in MySQL, but also in non clustered indexes.

deleted 4y ago
[deleted]
kevincox 4y ago
On the other hand if you ever want to shard based on ID full randomness is fantastic. There is a funny tradeoff where if all of your IDs are sequential then you get very good locality in your storage system. But that backfires if it creates too much of a hotspot. Maybe the best option is something like a UUIDv7 (time + random) ID and you can tune your shard keys by shuffling the bits. Maybe you want something like moving the last 4 bits to the front of the shard key so that you have 16 hot spots, which is good for performance without any being too hot.
- tremon 4y ago
  What's the use case of sharding on a technical key? I thought most sharding is done on business keys (e.g. per customer, region or year)?
  - crazygringo 4y ago
    In my experience sharing is generally done on a "technical" key because it's easy to arbitrarily partition into any desired number of shards. Something like per-year could result in different years having vastly different disk usage requirements, which would be a headache. While per-region or per-customer isn't really sharding at all in my understanding. It's just giving each customer (or region) their own dedicated/private database. Sharding is generally about dividing a single table (for business logic purposes) among (usually) equally-sized servers for performance/capacity reasons, according to some function of the primary key (modulus, hash, etc.).
  - kevincox 4y ago
    A customer ID could well be a UUID. And sharding by year is similar to sharding by a UUID or ULID with a timestamp prefix. But yes, in what dimension you shard will usually depend on the application.

deleted 4y ago

[deleted]

kevincox 4y ago

On the other hand if you ever want to shard based on ID full randomness is fantastic. There is a funny tradeoff where if all of your IDs are sequential then you get very good locality in your storage system. But that backfires if it creates too much of a hotspot. Maybe the best option is something like a UUIDv7 (time + random) ID and you can tune your shard keys by shuffling the bits. Maybe you want something like moving the last 4 bits to the front of the shard key so that you have 16 hot spots, which is good for performance without any being too hot.

tremon 4y ago
What's the use case of sharding on a technical key? I thought most sharding is done on business keys (e.g. per customer, region or year)?
- crazygringo 4y ago
  In my experience sharing is generally done on a "technical" key because it's easy to arbitrarily partition into any desired number of shards. Something like per-year could result in different years having vastly different disk usage requirements, which would be a headache. While per-region or per-customer isn't really sharding at all in my understanding. It's just giving each customer (or region) their own dedicated/private database. Sharding is generally about dividing a single table (for business logic purposes) among (usually) equally-sized servers for performance/capacity reasons, according to some function of the primary key (modulus, hash, etc.).
- kevincox 4y ago
  A customer ID could well be a UUID. And sharding by year is similar to sharding by a UUID or ULID with a timestamp prefix. But yes, in what dimension you shard will usually depend on the application.