8 ms·

When you give up one of the major components of relational databases like foreign keys you pick up lots of performance opportunity. It is amazing what Vitess /

When you give up one of the major components of relational databases like foreign keys you pick up lots of performance opportunity. It is amazing what Vitess / PS will help you accomplish but they don't talk a lot about tradeoffs you make to get there (which are similar to what you face with sharding MySQL without additional tooling).

samlambert 4y ago
We have foreign keys coming soon!
- XCSme 4y ago
  Hi Sam! I stumbled upon your DB service several times, and considered it several times suggesting it as the DB of choice for my customers (uxwizz.com), but the pricing is a bit confusing/hard to estimate in my case, which makes it hard for me to recommend it. Any suggestions?
candiddevmike 4y ago
Things can be really fast when you throw out referential integrity
berkle4455 4y ago
Foreign keys are so overrated. Write good code and you don’t have to rely on the DB to enforce referential integrity. It’s the equivalent of running test cases with every db write.
- AdrianB1 4y ago
  From experience you are very right. Also from experience, most teams are not good enough to write the code with that quality that you can give up to referential integrity.
- vecter 4y ago
  I'm curious, how would you obviate the need for foreign keys with "good" code? Can you provide a toy example or a reference to an article so I can understand better? I've used NoSQL databases a long time ago and currently rely on on good ole PostgreSQL, but I'm having a hard time understanding how "good" code can be a better solution for managing relationships between data than a foreign key.
  - Yoric 4y ago
    I guess you can use indices instead of foreign keys? And somehow implement all the `ON DELETE CASCADE` manually within any transaction that removes the original row? Not sure how it's "good" code but it could be faster.
    - DasIch 4y ago
      A foreign key doesn't necessarily imply an index. If you are using postgres, you would have to add an index in addition to the foreign key, if you want one. On delete cascade, depending on how many rows it cascades to, can be problematic because it's a very long running blocking operation. That's something one might want to do as a background operation and in batches. Although that won't make it faster.
      - sroussey 4y ago
        Faster in aggregate throughput can often be different from faster for a specific operation. Personally, I find delete on cascade dangerous. I mean, lots of fun for a pen tester, sure…
        Yoric 4y ago
        > Personally, I find delete on cascade dangerous. I mean, lots of fun for a pen tester, sure… Intriguing. Can you tell me more?
      - Yoric 4y ago
        > A foreign key doesn't necessarily imply an index. If you are using postgres, you would have to add an index in addition to the foreign key, if you want one. Sure, I meant an index in which the key is primary. But, on second thought, that's probably me misreading the GP's message. > On delete cascade, depending on how many rows it cascades to, can be problematic because it's a very long running blocking operation. That's something one might want to do as a background operation and in batches. Although that won't make it faster. That can definitely be a problem (just like destructor deallocation stampedes in C++ or Rust). Still less risky than cascading manually and asynchronously, I suspect.
- kgeist 4y ago
  "Write good code and you won't have bugs" :) I think it's good practice to enforce consistency rules both in the DB and in code. If you make a mistake in your code, the DB won't allow it, and vice versa.
  - vp8989 4y ago
    In theory it is good practice, but in practice enforcing referential integrity is not free and it makes satisfying other non-functional requirements harder. Engineering is about tradeoffs. Foreign keys are overkill in modern architectures where DBs/tables are typically only written to by a single application. They solve a problem that lots of people don't really have anymore.
    - kgeist 4y ago
      In the legacy project our team inherited, there's a lot of data consistency issues stemming from the lack of foreign keys (we're adding them back now). Data consistency is important because we deal with financial data. Typically, code and data change more often than DB schemas, so every release has a chance that someone will forget to update all relations manually in code, introducing dangling references, for example. Foreign keys help as an additional safety measure, because they don't change as often. I think you should care about the performance of foreign keys only when speed is more important than data consistency. It's a good default.
      - sroussey 4y ago
        Personally, I thinks foreign keys are great in dev and bad at any scale that you have many db servers. And as you point out, there are exceptions, like financial data. But not marketing funnels where you might throw everything away. You should probably have different types of engineers working on such different projects as well.
    - erik_seaberg 4y ago
      > DBs/tables are typically only written to by a single application I’ve seen several teams regret making this assumption. Unless you enforce draconian access control over a database, you’re going to discover that customer support and accounting and biz dev have been quietly relying on it.
- iamflimflam1 4y ago
  In my experience almost every time I've heard people saying they don't want foreign keys because of "the performance hit" - they've been nowhere near the scale or the skill that would make it sensible.
- XCSme 4y ago
  I don't think foreign keys can be replaced by good code. Probably their main advantage is what happens if there are edge-cases or DB write fails, to make sure the referential integrity is maintained in outstanding circumstances.
_a_a_a_ 4y ago
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.
giovannibonetti 4y ago
IIRC CockroachDB had (has?) interleaved tables for this reason. It seemed to allow collocating shards with the same foreign key in the same machine, leveraging composite keys for that. For example, a multi-tenant application could have a tenants table with PK id, and an orders table with PK (tenant_id, id), and a FK from orders(tenant_id) to tenants(id). Then CockroachDB would keep the orders with tenant_id = 123 in the same machine where the tenant with id = 123 was. I have the impression they deprecated interleaved tables, but I can't find more info about it.
- doodlesdev 4y ago
  Here you go: https://github.com/cockroachdb/cockroach/issues/52009 https://github.com/cockroachdb/cockroach/issues/52009 Not only deprecated, but completely removed a few years ago as seen in: https://github.com/cockroachdb/cockroach/issues/69150 https://github.com/cockroachdb/cockroach/issues/69150
dub 4y ago
The reasons Vitess didn't have foreign key support historically actually weren't a bold performance tradeoff or anything like that. It was more of a classic, boring, backlog prioritization thing: everyone heavily using Vitess was using gh-ost for schema changes, and gh-ost didn't support foreign keys. Now that Vitess has native schema change tools it's more reasonable to revisit user-friendly, out-of-the-box foreign key support.
- samlambert 4y ago
  Kind of. It was definitely an opinionated choice at some point, justified with performance. Now it is just something we need to add and it's nearly done. It will still have trade offs associated like all distributed systems.

samlambert 4y ago

We have foreign keys coming soon!

XCSme 4y ago
Hi Sam! I stumbled upon your DB service several times, and considered it several times suggesting it as the DB of choice for my customers (uxwizz.com), but the pricing is a bit confusing/hard to estimate in my case, which makes it hard for me to recommend it. Any suggestions?

candiddevmike 4y ago

Things can be really fast when you throw out referential integrity

berkle4455 4y ago

Foreign keys are so overrated. Write good code and you don’t have to rely on the DB to enforce referential integrity. It’s the equivalent of running test cases with every db write.

AdrianB1 4y ago
From experience you are very right. Also from experience, most teams are not good enough to write the code with that quality that you can give up to referential integrity.
vecter 4y ago
I'm curious, how would you obviate the need for foreign keys with "good" code? Can you provide a toy example or a reference to an article so I can understand better? I've used NoSQL databases a long time ago and currently rely on on good ole PostgreSQL, but I'm having a hard time understanding how "good" code can be a better solution for managing relationships between data than a foreign key.
- Yoric 4y ago
  I guess you can use indices instead of foreign keys? And somehow implement all the `ON DELETE CASCADE` manually within any transaction that removes the original row? Not sure how it's "good" code but it could be faster.
  - DasIch 4y ago
    A foreign key doesn't necessarily imply an index. If you are using postgres, you would have to add an index in addition to the foreign key, if you want one. On delete cascade, depending on how many rows it cascades to, can be problematic because it's a very long running blocking operation. That's something one might want to do as a background operation and in batches. Although that won't make it faster.
    - sroussey 4y ago
      Faster in aggregate throughput can often be different from faster for a specific operation. Personally, I find delete on cascade dangerous. I mean, lots of fun for a pen tester, sure…
      - Yoric 4y ago
        > Personally, I find delete on cascade dangerous. I mean, lots of fun for a pen tester, sure… Intriguing. Can you tell me more?
    - Yoric 4y ago
      > A foreign key doesn't necessarily imply an index. If you are using postgres, you would have to add an index in addition to the foreign key, if you want one. Sure, I meant an index in which the key is primary. But, on second thought, that's probably me misreading the GP's message. > On delete cascade, depending on how many rows it cascades to, can be problematic because it's a very long running blocking operation. That's something one might want to do as a background operation and in batches. Although that won't make it faster. That can definitely be a problem (just like destructor deallocation stampedes in C++ or Rust). Still less risky than cascading manually and asynchronously, I suspect.
kgeist 4y ago
"Write good code and you won't have bugs" :) I think it's good practice to enforce consistency rules both in the DB and in code. If you make a mistake in your code, the DB won't allow it, and vice versa.
- vp8989 4y ago
  In theory it is good practice, but in practice enforcing referential integrity is not free and it makes satisfying other non-functional requirements harder. Engineering is about tradeoffs. Foreign keys are overkill in modern architectures where DBs/tables are typically only written to by a single application. They solve a problem that lots of people don't really have anymore.
  - kgeist 4y ago
    In the legacy project our team inherited, there's a lot of data consistency issues stemming from the lack of foreign keys (we're adding them back now). Data consistency is important because we deal with financial data. Typically, code and data change more often than DB schemas, so every release has a chance that someone will forget to update all relations manually in code, introducing dangling references, for example. Foreign keys help as an additional safety measure, because they don't change as often. I think you should care about the performance of foreign keys only when speed is more important than data consistency. It's a good default.
    - sroussey 4y ago
      Personally, I thinks foreign keys are great in dev and bad at any scale that you have many db servers. And as you point out, there are exceptions, like financial data. But not marketing funnels where you might throw everything away. You should probably have different types of engineers working on such different projects as well.
  - erik_seaberg 4y ago
    > DBs/tables are typically only written to by a single application I’ve seen several teams regret making this assumption. Unless you enforce draconian access control over a database, you’re going to discover that customer support and accounting and biz dev have been quietly relying on it.
iamflimflam1 4y ago
In my experience almost every time I've heard people saying they don't want foreign keys because of "the performance hit" - they've been nowhere near the scale or the skill that would make it sensible.
XCSme 4y ago
I don't think foreign keys can be replaced by good code. Probably their main advantage is what happens if there are edge-cases or DB write fails, to make sure the referential integrity is maintained in outstanding circumstances.

_a_a_a_ 4y ago

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.

giovannibonetti 4y ago

IIRC CockroachDB had (has?) interleaved tables for this reason. It seemed to allow collocating shards with the same foreign key in the same machine, leveraging composite keys for that. For example, a multi-tenant application could have a tenants table with PK id, and an orders table with PK (tenant_id, id), and a FK from orders(tenant_id) to tenants(id). Then CockroachDB would keep the orders with tenant_id = 123 in the same machine where the tenant with id = 123 was. I have the impression they deprecated interleaved tables, but I can't find more info about it.

doodlesdev 4y ago
Here you go: https://github.com/cockroachdb/cockroach/issues/52009 https://github.com/cockroachdb/cockroach/issues/52009 Not only deprecated, but completely removed a few years ago as seen in: https://github.com/cockroachdb/cockroach/issues/69150 https://github.com/cockroachdb/cockroach/issues/69150

dub 4y ago

The reasons Vitess didn't have foreign key support historically actually weren't a bold performance tradeoff or anything like that. It was more of a classic, boring, backlog prioritization thing: everyone heavily using Vitess was using gh-ost for schema changes, and gh-ost didn't support foreign keys. Now that Vitess has native schema change tools it's more reasonable to revisit user-friendly, out-of-the-box foreign key support.

samlambert 4y ago
Kind of. It was definitely an opinionated choice at some point, justified with performance. Now it is just something we need to add and it's nearly done. It will still have trade offs associated like all distributed systems.