3 ms·

I usually prefer Snowflakes with a random suffix behind a timestamp. 34bit UNIX Timestamp sidesteps the 2038 issue and 30 bits of randomness are good enough to

by zaarn 9y ago

I usually prefer Snowflakes with a random suffix behind a timestamp. 34bit UNIX Timestamp sidesteps the 2038 issue and 30 bits of randomness are good enough to prevent collisions with a p < 0.0001 of up to 400 inserts per second (or 1500 inserts per second for p < 0.001). And you can always tweak this to match your workloads (use a bit more randomness, use a bit more timestamp, maybe even use a custom epoch instead of 1970 to reduce the number of bits necessary or use millisecond accurate timestamps) PostgreSQL supports calling functions as the default value of a column so it should be possible to simply put in a function that returns such a snowflake (either in pure SQL or in PL/Python) and PG will use that.

moltar 9y ago
I’ve tried a similar approach and ran into an issue of id generation during tests. Tests run in parallel and quick succession. Ran into collisions right away. Any suggestions on how to mitigate that?
- zaarn 9y ago
  First, you should still use a UNIQUE column, that prevents the most urgent collision issues. Second, if your tests run a lot faster than actual loads, consider using a special test timestamp format that allows for higher throughput at the expense of longevity (ie, 10bit timestamp since start of test + 54 bit of random data) or alternatively throttle the tests to closer resemble real workloads (within one or two orders of magnitude) You can also replace the random part with a Node ID + atomic counter. Each Node that generates IDs gets a unique identifier as ID which is inserted after the timestamp. Then the last few bits of the ID are used by an atomic counter. This ensures that nodes don't generate colliding IDs with eachother (the NodeID part) and with themselves (the Atomic part).
  - moltar 9y ago
    > First, you should still use a UNIQUE column, that prevents the most urgent collision issues. Right, of course. I was using the UNIQUE/PK columns. When I said collision, I did mean that db threw errors on duplicates. Which is, of course, expected. > You can also replace the random part with a Node ID What is a "Node ID"? Thanks!
    - zaarn 9y ago
      A node ID would simply be a somewhat-unique ID for a node. Sonyflake implements a 16bit machine ID: https://github.com/sony/sonyflake https://github.com/sony/sonyflake It prevents multiple machines producing colliding numbers.

by zaarn 9y ago

moltar 9y ago

I’ve tried a similar approach and ran into an issue of id generation during tests. Tests run in parallel and quick succession. Ran into collisions right away. Any suggestions on how to mitigate that?

zaarn 9y ago
First, you should still use a UNIQUE column, that prevents the most urgent collision issues. Second, if your tests run a lot faster than actual loads, consider using a special test timestamp format that allows for higher throughput at the expense of longevity (ie, 10bit timestamp since start of test + 54 bit of random data) or alternatively throttle the tests to closer resemble real workloads (within one or two orders of magnitude) You can also replace the random part with a Node ID + atomic counter. Each Node that generates IDs gets a unique identifier as ID which is inserted after the timestamp. Then the last few bits of the ID are used by an atomic counter. This ensures that nodes don't generate colliding IDs with eachother (the NodeID part) and with themselves (the Atomic part).
- moltar 9y ago
  > First, you should still use a UNIQUE column, that prevents the most urgent collision issues. Right, of course. I was using the UNIQUE/PK columns. When I said collision, I did mean that db threw errors on duplicates. Which is, of course, expected. > You can also replace the random part with a Node ID What is a "Node ID"? Thanks!
  - zaarn 9y ago
    A node ID would simply be a somewhat-unique ID for a node. Sonyflake implements a 16bit machine ID: https://github.com/sony/sonyflake https://github.com/sony/sonyflake It prevents multiple machines producing colliding numbers.