8 ms·

furthermore, foreign keys are not performant at mass scale on both pg or mysql or any traditional RDBMS. if your trying to do several thousand ops per second,

by spotman 11y ago

furthermore, foreign keys are not performant at mass scale on both pg or mysql or any traditional RDBMS. if your trying to do several thousand ops per second, the vector of index contention greatly goes up if there is FKs littered all over your schema. nosql or sql large platforms almost always have some amount of work always ongoing to clean, shuttle, and maintain data integrity. that doesn't make me not a fan of them; for lower transaction rate applications ( which most probably are ), they are a small time investment to have some sanity checking. but it's a right tool for the job thing. this is yet another reason why people dislike FKs in best practice stuff. Edit: Hi down-voters. Curious to hear why I'm being down voted and possibly be offer a rebuttal?

ak39 11y ago
Foreign keys are not convenience artifacts. They are essential for enforcing data correctness. One doesn't simply opt out of their usage simply because one is "not a fan" of foreign keys.
- spotman 11y ago
  What if it is the difference between your schema working under high transaction rates or not? Or, the difference between buying much more database hardware and having smaller shards to avoid index contention. (which, is $$) Facebooks mysql architecture (at least used to, likely still is) based on this: https://backchannel.org/blog/friendfeed-schemaless-mysql https://backchannel.org/blog/friendfeed-schemaless-mysql Note the simple schema, and lack of FKs. FKs are rarely present in extremely high transactions per second systems that operate on the same few tables. This is due to index contention and locking. I say this with lots of experience working on different production systems that see hundreds of thousands of transactions per second. I have yet to see one arrive at these kind of numbers using foreign keys, unless it's something like a giant shared hosting platform, that is operating on hundreds and hundreds of different tables. (therefore, less index contention) Often, in these shops, data correctness is not validated in realtime in this way, but often in a way that is not in the critical path of answering queries. (more like, eventually consistent). Some places have entire teams for this. To your point on correctness, my experience ranges a lot in areas where correctness can be eventually consistent. For something like healthcare or banking, you would rather spend the money on way more hardware, because you can't afford correctness to be off. However, with things like globally scaled social apps, this is just not the case. Most people tend to still use Oracle in those situations. Which is legions slower , and legions more expensive than mysql or pgsql.
  - ak39 11y ago
    >To your point on correctness, my experience ranges a > lot in areas where correctness can be eventually consistent. "Eventually consistent"? Explain. (Do you mean in an ACID transaction?)
    - spotman 11y ago
      Let me explain better. As eventually consistent is probably the wrong term for what I mean. In things like a large social media app where the reality is no FKs, and 100k queries-per-second, for instance, you would write into your application logic how to deal with child rows and parent rows, and not rely on FKs to raise an exception, or perform cascading deletes and things. So if ( when ) there is either a bug, hiccup, or a variety of other reasons that can as you mention make things question correctness (or specifically: referential integrity, if we are going to nitpick), these are usually cleaned up out of band. So you may have a table you have to slowly iterate through later and remove rows in which their parent rows no longer exist anymore, as an example. In something like the friend feed schema used in the previous example, read up on how that works, as you will see they sort of turn SQL on its head a bit, to make it more flexible and deal with these shortcomings.
      - Pamar 11y ago
        On the other hand, if you deal with money, or physical products (like a car) or anything slightly more "important" than your "likes" count on a social site, eventual consistency would get you laughed out of the room pretty quickly.
        spotman 11y ago
        Agreed overall, see my comment above which addresses this. These types of systems typically will never see as many transactions/sec as their “likes” counterparts, for obvious reasons. Having said that, I have worked on some credit card infrastructure that does get up there, and it too, does not have FKs. Having or not having FKs does not magically mean your data will be accurate or in-accurate. ACID, as well as traditional database transactions, still apply, and keep your data integrity high. In conjunction with well designed application logic, and proper planning, FKs are not needed to maintain accurate data whatsoever. Also, there is commerce related things (Amazon) that work on truly eventually consistent data stores, like DynamoDB. Don’t see amazon getting laughed at much. (they use it for their shopping cart, source: http://www.allthingsdistributed.com/2007/10/amazons_dynamo.html http://www.allthingsdistributed.com/2007/10/amazons_dynamo.h... , this paper is also a great read fwiw) Anyways, I think we are getting a bit into the weeds here, as the OPs link is about a mysql bug. Cheers.
- delinka 11y ago
  I've experienced several highly-performant, correct databases with concurrent clients from around the globe ... and they had no enforcement of foreign keys. Yes, foreign keys were essential to data correctness. No, they were not being enforced in production. Stew on that just a bit. Not enforced in production. During client application development, development databases enforced the constraint. Any errors resulting in foreign key mismatches disqualified the client app for release. The applications were required to be aware of the constraint and to learn to work within it. Production gained the speed benefits of not having to enforce the foreign keys. You can indeed eat your cake and have it, too, iff you are disciplined.
  - spotman 11y ago
    Nicely put. 100% agreed.
  - contingencies 11y ago
    Great tale! Much respect to the development and operations teams here. Out of interest, what kind of purpose was the database?
    - delinka 11y ago
      It was in a financial institution. It held every transaction in the financial enterprise.
  - ThinkBeat 11y ago
    This might be the case if you have one and only one application interacting with the database, and your unit tests include each and every permutation/edge case. It is very common to have many applications, from different vendors and from different time periods interacting with a database. It often also happens that data can be modified directly in the database, not via the application. I agree this is not best practice but in larger enterprises it happens. Once you start implementing foreign keys in the client you are adding a lot of complexity to a problem that most mature relational database system do well enough to be trusted by a lot of traditional financial institutions.
Jweb_Guru 11y ago
I am afraid your premise is incorrect. Foreign keys can be enforced (particularly on insert-only or insert-mostly workloads) with very little contention, even in a distributed system: http://www.bailis.org/papers/ramp-sigmod2014.pdf http://www.bailis.org/papers/ramp-sigmod2014.pdf And lest you think there is some hidden constant factor here that destroys real performance at the cost of "scalability," the techniques from that paper were used to completely destroy the TPC-C benchmark: http://arxiv.org/pdf/1402.2237.pdf http://arxiv.org/pdf/1402.2237.pdf Admittedly this stuff is not yet implemented in traditional RDBMSes, but there is absolutely no reason why it could not be and I fully expect it to in the future. Certainly, if you are having performance problems now, this knowledge doesn't help. But I'd venture to guess (from personal experience) that most people removing foreign keys are doing it preemptively, rather than evaluating whether it is actually causing performance problems.

by spotman 11y ago

ak39 11y ago

Foreign keys are not convenience artifacts. They are essential for enforcing data correctness. One doesn't simply opt out of their usage simply because one is "not a fan" of foreign keys.

spotman 11y ago
What if it is the difference between your schema working under high transaction rates or not? Or, the difference between buying much more database hardware and having smaller shards to avoid index contention. (which, is $$) Facebooks mysql architecture (at least used to, likely still is) based on this: https://backchannel.org/blog/friendfeed-schemaless-mysql https://backchannel.org/blog/friendfeed-schemaless-mysql Note the simple schema, and lack of FKs. FKs are rarely present in extremely high transactions per second systems that operate on the same few tables. This is due to index contention and locking. I say this with lots of experience working on different production systems that see hundreds of thousands of transactions per second. I have yet to see one arrive at these kind of numbers using foreign keys, unless it's something like a giant shared hosting platform, that is operating on hundreds and hundreds of different tables. (therefore, less index contention) Often, in these shops, data correctness is not validated in realtime in this way, but often in a way that is not in the critical path of answering queries. (more like, eventually consistent). Some places have entire teams for this. To your point on correctness, my experience ranges a lot in areas where correctness can be eventually consistent. For something like healthcare or banking, you would rather spend the money on way more hardware, because you can't afford correctness to be off. However, with things like globally scaled social apps, this is just not the case. Most people tend to still use Oracle in those situations. Which is legions slower , and legions more expensive than mysql or pgsql.
- ak39 11y ago
  >To your point on correctness, my experience ranges a > lot in areas where correctness can be eventually consistent. "Eventually consistent"? Explain. (Do you mean in an ACID transaction?)
  - spotman 11y ago
    Let me explain better. As eventually consistent is probably the wrong term for what I mean. In things like a large social media app where the reality is no FKs, and 100k queries-per-second, for instance, you would write into your application logic how to deal with child rows and parent rows, and not rely on FKs to raise an exception, or perform cascading deletes and things. So if ( when ) there is either a bug, hiccup, or a variety of other reasons that can as you mention make things question correctness (or specifically: referential integrity, if we are going to nitpick), these are usually cleaned up out of band. So you may have a table you have to slowly iterate through later and remove rows in which their parent rows no longer exist anymore, as an example. In something like the friend feed schema used in the previous example, read up on how that works, as you will see they sort of turn SQL on its head a bit, to make it more flexible and deal with these shortcomings.
    - Pamar 11y ago
      On the other hand, if you deal with money, or physical products (like a car) or anything slightly more "important" than your "likes" count on a social site, eventual consistency would get you laughed out of the room pretty quickly.
      - spotman 11y ago
        Agreed overall, see my comment above which addresses this. These types of systems typically will never see as many transactions/sec as their “likes” counterparts, for obvious reasons. Having said that, I have worked on some credit card infrastructure that does get up there, and it too, does not have FKs. Having or not having FKs does not magically mean your data will be accurate or in-accurate. ACID, as well as traditional database transactions, still apply, and keep your data integrity high. In conjunction with well designed application logic, and proper planning, FKs are not needed to maintain accurate data whatsoever. Also, there is commerce related things (Amazon) that work on truly eventually consistent data stores, like DynamoDB. Don’t see amazon getting laughed at much. (they use it for their shopping cart, source: http://www.allthingsdistributed.com/2007/10/amazons_dynamo.html http://www.allthingsdistributed.com/2007/10/amazons_dynamo.h... , this paper is also a great read fwiw) Anyways, I think we are getting a bit into the weeds here, as the OPs link is about a mysql bug. Cheers.
delinka 11y ago
I've experienced several highly-performant, correct databases with concurrent clients from around the globe ... and they had no enforcement of foreign keys. Yes, foreign keys were essential to data correctness. No, they were not being enforced in production. Stew on that just a bit. Not enforced in production. During client application development, development databases enforced the constraint. Any errors resulting in foreign key mismatches disqualified the client app for release. The applications were required to be aware of the constraint and to learn to work within it. Production gained the speed benefits of not having to enforce the foreign keys. You can indeed eat your cake and have it, too, iff you are disciplined.
- spotman 11y ago
  Nicely put. 100% agreed.
- contingencies 11y ago
  Great tale! Much respect to the development and operations teams here. Out of interest, what kind of purpose was the database?
  - delinka 11y ago
    It was in a financial institution. It held every transaction in the financial enterprise.
- ThinkBeat 11y ago
  This might be the case if you have one and only one application interacting with the database, and your unit tests include each and every permutation/edge case. It is very common to have many applications, from different vendors and from different time periods interacting with a database. It often also happens that data can be modified directly in the database, not via the application. I agree this is not best practice but in larger enterprises it happens. Once you start implementing foreign keys in the client you are adding a lot of complexity to a problem that most mature relational database system do well enough to be trusted by a lot of traditional financial institutions.

Jweb_Guru 11y ago

I am afraid your premise is incorrect. Foreign keys can be enforced (particularly on insert-only or insert-mostly workloads) with very little contention, even in a distributed system: http://www.bailis.org/papers/ramp-sigmod2014.pdf http://www.bailis.org/papers/ramp-sigmod2014.pdf And lest you think there is some hidden constant factor here that destroys real performance at the cost of "scalability," the techniques from that paper were used to completely destroy the TPC-C benchmark: http://arxiv.org/pdf/1402.2237.pdf http://arxiv.org/pdf/1402.2237.pdf Admittedly this stuff is not yet implemented in traditional RDBMSes, but there is absolutely no reason why it could not be and I fully expect it to in the future. Certainly, if you are having performance problems now, this knowledge doesn't help. But I'd venture to guess (from personal experience) that most people removing foreign keys are doing it preemptively, rather than evaluating whether it is actually causing performance problems.