1. Concurrency: if you have two services which concurrently consume two records that are logically the same (e.g. the records of a person having the same SSN), but such "natural" constraint isn't enforced (neither on the primary key nor as a unique column), then you get duplication.
2. Performance: if you have an API that creates a record and then returns it to the user (with the generated ID), then you'll probably have to do an insert-and-select for each inserted record. Unless you generate the ID itself in the application - which comes with another set of issues.
3. Upsert: if the primary key doesn't depend on any natural identifier, then it's hard to tell when you should insert a new record or update an existing one, and mechanisms such as INSERT...ON CONFLICT UPDATE can't be leveraged.
4. Sharding: especially if the primary key is auto-increment or random, sharding on it becomes challenging.
Fabio Manganiello /
npub1s9…jdnmu
2024-07-11 11:27:04
in reply to nevent1q…vx89
Author Public Key
npub1s9uc08n58mxqk5umvapqulwzng0sja635q86r36d8n4rr9r9ygaskjdnmuPublished at
2024-07-11 11:27:04Event JSON
{
"id": "3c53750817d6b329df4b3c15848809be5d181a295f8a608b2afbfe79c0ce9134",
"pubkey": "8179879e743ecc0b539b67420e7dc29a1f097751a00fa1c74d3cea319465223b",
"created_at": 1720697224,
"kind": 1,
"tags": [
[
"p",
"8179879e743ecc0b539b67420e7dc29a1f097751a00fa1c74d3cea319465223b"
],
[
"e",
"057e2fd4a9454f33a0a030061c2e4eac40751985beaad793943bb492e1ca60c8",
"",
"root"
],
[
"p",
"371820b7120114ecf6d3cc048f32ead77dd6e3080d9372adfd1c9bca1eac68ff"
],
[
"e",
"320380137126c126e145db2182dd66a54a51008576ceb3bfdd872d2a59ca1dea",
"",
"reply"
],
[
"proxy",
"https://manganiello.social/objects/4609fc84-e533-463e-9387-72a304671d89",
"activitypub"
],
[
"L",
"pink.momostr"
],
[
"l",
"pink.momostr.activitypub:https://manganiello.social/objects/4609fc84-e533-463e-9387-72a304671d89",
"pink.momostr"
],
[
"-"
]
],
"content": "1. Concurrency: if you have two services which concurrently consume two records that are logically the same (e.g. the records of a person having the same SSN), but such \"natural\" constraint isn't enforced (neither on the primary key nor as a unique column), then you get duplication.\n\n2. Performance: if you have an API that creates a record and then returns it to the user (with the generated ID), then you'll probably have to do an insert-and-select for each inserted record. Unless you generate the ID itself in the application - which comes with another set of issues.\n\n3. Upsert: if the primary key doesn't depend on any natural identifier, then it's hard to tell when you should insert a new record or update an existing one, and mechanisms such as INSERT...ON CONFLICT UPDATE can't be leveraged.\n\n4. Sharding: especially if the primary key is auto-increment or random, sharding on it becomes challenging.",
"sig": "63f7df9f06b9a91a484980abcdb824cdc5ba53909f227b34ee920b3977ce28a73ded4b1e71e0a6e7f5a29a816e9053a0a1052215727065c5de2ae13f72bfbe33"
}