3 ms·
It's also important to put as much thought into any synthetic key. It's easy to just use an auto-generated sequence... but then you start having to export/impo
by mobiuscog 2y ago
It's also important to put as much thought into any synthetic key.
It's easy to just use an auto-generated sequence... but then you start having to export/import or otherwise merge data, and other manipulations that often use the primary key and find there are collisions everywhere.
There can also be problems when needing to support multiple databases, or update versions.
UUIDs (or equivalent synthetic keys that are independent of the database itself) are often the best answer for this reason.
Having been bitten by sequences so many times in the past, I find them to often be more trouble than natural keys in the first place - just a lazy approach.
- jonathan-re 2y agoWell, UUIDs bring their own challenges. Dropping that one here: Be Careful with UUID or GUID as Primary Keys https://news.ycombinator.com/item?id=14523523 https://news.ycombinator.com/item?id=14523523
- globular-toast 2y agoIt's pretty easy to migrate to UUIDs later if required, as long as you don't leak your keys. My work made their own uuid-like scheme, similar to UUIDv1 which incorporates several elements like machine ID and timestamp. The mistake was twofold: first they exposed them (so people started using them) but, worse, they made them easily reversible, so people started decoding the information in them. People would naturally see one and think "oh this is a record from place X". Of course that might not be true following subsequent data corrections, but the key can't change.
- akvadrako 2y agoUUIDs are only good when you don't care about ergonomics or performance. Snowflake IDs are much more reasonable. 41 bits timestamp (ms) + 10 bit machine id + 12 bit serial. But if you care about ergonomics and privacy the most then short and random IDs are really the best.