4 ms·

Databases are my interest, so if you don't mind… How would removal of foreign keys speed up read access? I'm also going to disagree with you a little, again on

Databases are my interest, so if you don't mind… How would removal of foreign keys speed up read access? I'm also going to disagree with you a little, again on the issue of reading, if you have foreign keys then you can do optimisations like cutting out chunks of joins. Adding constraints means you know more which you can feed to the optimiser which means often you can get better performance (read performance, that is).

robmccoll 4y ago
Not OP, but they said that you pick up performance, not specifically in reading. The performance you gain is larger aggregate write throughput (and read generally - although reads that require spreading queries across shards and aggregating results will likely perform a little worse). You also gain scale in that you have turned something that was limited by scale up into something limited by scale out (while giving up some data guarantees and performance on some queries). As to why you can't really have foreign keys, basically, sharding schemes like this sit at an intermediate layer between the application and the various DB clusters that are the shards. You CAN have strong data consistency (and useful foreign keys) within a shard because it's all inside a single database; however across shards, you CAN NOT. The sharding layer doesn't perform checks for you, so if you have two logical tables that are partitioned differently across the shards, you can't have a foreign key that will be enforced correctly as the foreign key of a given row in one table may live on a different shard in the other table. The local database within the shard would reject the insert. Transactions across shards can also be tricky to impossible.
- _a_a_a_ 4y ago
  In my experience most databases are read-heavy so that's what I was asking about. That said, thanks for a clear explanation.

robmccoll 4y ago

Not OP, but they said that you pick up performance, not specifically in reading. The performance you gain is larger aggregate write throughput (and read generally - although reads that require spreading queries across shards and aggregating results will likely perform a little worse). You also gain scale in that you have turned something that was limited by scale up into something limited by scale out (while giving up some data guarantees and performance on some queries). As to why you can't really have foreign keys, basically, sharding schemes like this sit at an intermediate layer between the application and the various DB clusters that are the shards. You CAN have strong data consistency (and useful foreign keys) within a shard because it's all inside a single database; however across shards, you CAN NOT. The sharding layer doesn't perform checks for you, so if you have two logical tables that are partitioned differently across the shards, you can't have a foreign key that will be enforced correctly as the foreign key of a given row in one table may live on a different shard in the other table. The local database within the shard would reject the insert. Transactions across shards can also be tricky to impossible.

_a_a_a_ 4y ago
In my experience most databases are read-heavy so that's what I was asking about. That said, thanks for a clear explanation.