3 ms·

I feel like `serial` has got to be pretty fast for writes and gives you some nice properties like sorted ordering based on insertion time, a smaller key, and a

I feel like `serial` has got to be pretty fast for writes and gives you some nice properties like sorted ordering based on insertion time, a smaller key, and a key that can be compressed far better than a uuid. I get the idea of a ULID/UUID7 encoding a timestamp so you get sorted order, but I wonder at what scale that beats just using serial.

stormbrew 4y ago
At any scale where you have to have multiple writers generating IDs or need to merge results from multiple sources, basically. Synchronizing a serial increment across hosts and across time is a pain. Obviously you can composite a timestamp to an incrementing id, but then the serial part of it is kind of useless for ordering, so you may as well use a random number and avoid needing to synchronize at all. And then you've just reinvented a time ordered uuid (but to be fair, without the endian compatibility bs mentioned elsewhere).
- staticassertion 4y ago
  I'm wondering what that performance actually looks like though. The closest benchmark I have is a single postgres instance with a uuid pkey and a bigint column, transactionally incrementing the integer in the column. I mean I guess mathing it out, updating an atomic integer is ~2-100ns, depending on contention. If you need to coordinate the writes you have anywhere from ~250μs-10ms. We can basically throw away the increment at that point since the network is hundreds/thousands of times slower. So at 10ms, that's 100 op/s. At 250μs more like 40kops/s. That ignores the fact that your db can perform those writes concurrently and then batch the writes off to the other database, so long as it doesn't pretend that they're all committed at once. psql HOT updates would presumably be a thing here idk. If I had to guess, I'd lean towards the "40kop/s" being closer than the "100op/s" but idk! I wish we had benchmarks but I can't find anything :\

stormbrew 4y ago

At any scale where you have to have multiple writers generating IDs or need to merge results from multiple sources, basically. Synchronizing a serial increment across hosts and across time is a pain. Obviously you can composite a timestamp to an incrementing id, but then the serial part of it is kind of useless for ordering, so you may as well use a random number and avoid needing to synchronize at all. And then you've just reinvented a time ordered uuid (but to be fair, without the endian compatibility bs mentioned elsewhere).

staticassertion 4y ago
I'm wondering what that performance actually looks like though. The closest benchmark I have is a single postgres instance with a uuid pkey and a bigint column, transactionally incrementing the integer in the column. I mean I guess mathing it out, updating an atomic integer is ~2-100ns, depending on contention. If you need to coordinate the writes you have anywhere from ~250μs-10ms. We can basically throw away the increment at that point since the network is hundreds/thousands of times slower. So at 10ms, that's 100 op/s. At 250μs more like 40kops/s. That ignores the fact that your db can perform those writes concurrently and then batch the writes off to the other database, so long as it doesn't pretend that they're all committed at once. psql HOT updates would presumably be a thing here idk. If I had to guess, I'd lean towards the "40kop/s" being closer than the "100op/s" but idk! I wish we had benchmarks but I can't find anything :\