5 ms·
There are simpler solutions. When you recover your database, simply add a large constant to all autoincrement columns, that should prevent any overlap. Atleast
by zaarn 9y ago
There are simpler solutions. When you recover your database, simply add a large constant to all autoincrement columns, that should prevent any overlap. Atleast for existing setups this is ideal.
The other option is to use snowflake IDs with a random suffix. They will still sort like normal IDs but won't collide if you roll back (or are unlikely to collide).
UUIDs or fully random strings are a bit meh as primary column in a database. They disconnect all meaning and potential sorting from the records (sorting by primary key is meaningless and random PKs may ruin database performance depending on setup).
Snowflakes will behave like a UUID or random string when properly engineered but are still sortable.
Identifying users by usernames also leads to all kinds of sillyness, internally you should be using the ID and not the username. Usernames are meant for human consumption, not machine consumption.
- geofft 9y ago"Snowflakes" meaning like Twitter Snowflake, an autoincrement with a timestamp folded in (and maybe some ability for multiple read/write database servers to generate IDs without actively coordinating with each other)? That would work - can I easily get Postgres or something to generate these?
- zaarn 9y agoI 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 agoI’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 agoFirst, 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 agoA 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.