5 ms·

Every time I have seen a database use foreign keys there has been data corruption, because everyone thought foreign keys were declarative and not procedural. Ju

Every time I have seen a database use foreign keys there has been data corruption, because everyone thought foreign keys were declarative and not procedural. Just because you have a foreign key doesn't mean it was always there or that it applied on every transaction. You can turn them off at the connection level and you in fact must turn them off for almost any kind of bulk data load. You should read shlomi's post he gives good reasons. Specifically this is a github issue on his tool gh-ost which is for online schema migration, and FK's pose lots of problems for online schema migrations.

munk-a 7y ago
I am a bit doubtful of this, especially within PostgreSQL you need to specifically go out of your way to create a NOT VALID constraint. I know that MySQL of old would default to an engine that didn't actually enforce key relationships (which was terrible but at least well documented) but in the modern world DBs will tend toward enforcement unless you specifically work against it.
spookthesunset 7y ago
Maybe shitty toy database systems like MySQL let you disable constraints on a per session basis. A real database system might let you defer them until the end of a transaction—which is the only correct way to operate. Once you commit that transaction, what you put into the system better fucking make sense and it is the job of the database system (and only the database system) to enforce that. Like I said before if your database system makes using constraints hard or lets you shoot yourself in the foot (lol at disabling constraints per session), don’t just walk but run away from that system.
- mixedCase 7y ago
  > Maybe shitty toy database systems like MySQL While appropiate to define in few words some of MySQL's colossal mistakes, this isn't the kind of language that will sway heads that have been comfortably using MySQL because those defects are just "what DB's do".
  - spookthesunset 7y ago
    Any database that would let you disable constraints on a session basis is a toy database. Such an operation doesn’t even make sense because at some point the relational integrity has to be enforced for the entire table. You can’t just have parts of a table be relationally correct. That is like saying 1 + 1 = 3. It is a completely illogical statement. However I would not at all be surprised to learn MySQL supports such a thing. Which supports my assertion it is a toy used (or at least installed by) people who have no understanding of relational database architecture.
    - evanelias 7y ago
      So are you asserting that the following products are all built on top of a "toy" database, and their engineers have no idea what they're doing: Facebook, YouTube, Wikipedia, Pinterest, Slack, GitHub, Etsy, Yelp, LinkedIn, Shopify, Dropbox, Wordpress, Wix, Tumblr, Square, Uber, Booking.com, Box, Venmo, SendGrid, Okta, SurveyMonkey, WePay, Alibaba, SoundCloud, among countless others... An alternative view is that your statements are incorrect. Do you have much direct experience with high-volume OLTP database workloads, or are you basing your views of MySQL on something else?
      - spookthesunset 7y ago
        Once you are stuck with MySQL it is very, very, very hard to get an organization to switch--not only from a technical standpoint but a political one. I bet you any competent engineer who knows their shit about DB in those companies regrets using MySQL. I bet their code is full of hacks, crappy schemas, and all kinds of work arounds because they chose mysql. I've seen it in every company that uses MySQL. The lengths people go to avoid schema changes is astonishing. It is much, much better to start with a real database like PostgreSQL because whatever you pick is going to be what your entire org uses from now until eternity.
        evanelias 7y ago
        Cool, so I'm going to assume that means your answer to my question of "Do you have much direct experience with high-volume OLTP database workloads?" is "no". Given your "bet" as well as comments about schema change difficulty, I'm also going to assume you did not click through to my profile...
- rhinoceraptor 7y ago
  > MySQL What other database has been used at such a giant scale at so many companies, than MySQL? I'm sure Oracle and SQL Server are used at big companies, but nowhere near Facebook scale.
  - papln 7y ago
    You aren't Facebook. Facebook has put massive work into adding layers on top of MySQL that your startup has not. Also, you seem to be forgetting PostgreSQL
- dkhenry 7y ago
  This really makes it seem like you have never used a database system at scale. There are reasons why systems like MySQL let you turn them off, and they are some of the same reasons why pretty much everyone who uses a database at scale has settled on MySQL. Also its why as is mentioned in the issue once you actually scale your database foreign keys become a nightmare. If all you have is a toy project you can feel free to use any database you want, but if you actually need to serve traffic you might want to rethink your priorities.
  - papln 7y ago
    > everyone who uses a database at scale has settled on MySQL hmm? I've only every seen people using MySQL at scale if they started with MySQL in prototype and never had the energy to migrate.
    - dkhenry 7y ago
      Well here is one case of a large scale user migrating https://eng.uber.com/mysql-migration/ https://eng.uber.com/mysql-migration/ And there are tons more. In fact here is a tool to help you do it https://github.com/pivotal-cf/pg2mysql https://github.com/pivotal-cf/pg2mysql
      - spookthesunset 7y ago
        Uber also has a giant team dedicated to re-inventing slack. I'd take their engineering prowess with a grain of salt. Lots of people do dumb things for dumb reasons. Most developers I interact with, even the really good ones, are profoundly stupid when it comes to databases.

munk-a 7y ago

I am a bit doubtful of this, especially within PostgreSQL you need to specifically go out of your way to create a NOT VALID constraint. I know that MySQL of old would default to an engine that didn't actually enforce key relationships (which was terrible but at least well documented) but in the modern world DBs will tend toward enforcement unless you specifically work against it.

spookthesunset 7y ago

Maybe shitty toy database systems like MySQL let you disable constraints on a per session basis. A real database system might let you defer them until the end of a transaction—which is the only correct way to operate. Once you commit that transaction, what you put into the system better fucking make sense and it is the job of the database system (and only the database system) to enforce that. Like I said before if your database system makes using constraints hard or lets you shoot yourself in the foot (lol at disabling constraints per session), don’t just walk but run away from that system.

mixedCase 7y ago
> Maybe shitty toy database systems like MySQL While appropiate to define in few words some of MySQL's colossal mistakes, this isn't the kind of language that will sway heads that have been comfortably using MySQL because those defects are just "what DB's do".
- spookthesunset 7y ago
  Any database that would let you disable constraints on a session basis is a toy database. Such an operation doesn’t even make sense because at some point the relational integrity has to be enforced for the entire table. You can’t just have parts of a table be relationally correct. That is like saying 1 + 1 = 3. It is a completely illogical statement. However I would not at all be surprised to learn MySQL supports such a thing. Which supports my assertion it is a toy used (or at least installed by) people who have no understanding of relational database architecture.
  - evanelias 7y ago
    So are you asserting that the following products are all built on top of a "toy" database, and their engineers have no idea what they're doing: Facebook, YouTube, Wikipedia, Pinterest, Slack, GitHub, Etsy, Yelp, LinkedIn, Shopify, Dropbox, Wordpress, Wix, Tumblr, Square, Uber, Booking.com, Box, Venmo, SendGrid, Okta, SurveyMonkey, WePay, Alibaba, SoundCloud, among countless others... An alternative view is that your statements are incorrect. Do you have much direct experience with high-volume OLTP database workloads, or are you basing your views of MySQL on something else?
    - spookthesunset 7y ago
      Once you are stuck with MySQL it is very, very, very hard to get an organization to switch--not only from a technical standpoint but a political one. I bet you any competent engineer who knows their shit about DB in those companies regrets using MySQL. I bet their code is full of hacks, crappy schemas, and all kinds of work arounds because they chose mysql. I've seen it in every company that uses MySQL. The lengths people go to avoid schema changes is astonishing. It is much, much better to start with a real database like PostgreSQL because whatever you pick is going to be what your entire org uses from now until eternity.
      - evanelias 7y ago
        Cool, so I'm going to assume that means your answer to my question of "Do you have much direct experience with high-volume OLTP database workloads?" is "no". Given your "bet" as well as comments about schema change difficulty, I'm also going to assume you did not click through to my profile...
rhinoceraptor 7y ago
> MySQL What other database has been used at such a giant scale at so many companies, than MySQL? I'm sure Oracle and SQL Server are used at big companies, but nowhere near Facebook scale.
- papln 7y ago
  You aren't Facebook. Facebook has put massive work into adding layers on top of MySQL that your startup has not. Also, you seem to be forgetting PostgreSQL
dkhenry 7y ago
This really makes it seem like you have never used a database system at scale. There are reasons why systems like MySQL let you turn them off, and they are some of the same reasons why pretty much everyone who uses a database at scale has settled on MySQL. Also its why as is mentioned in the issue once you actually scale your database foreign keys become a nightmare. If all you have is a toy project you can feel free to use any database you want, but if you actually need to serve traffic you might want to rethink your priorities.
- papln 7y ago
  > everyone who uses a database at scale has settled on MySQL hmm? I've only every seen people using MySQL at scale if they started with MySQL in prototype and never had the energy to migrate.
  - dkhenry 7y ago
    Well here is one case of a large scale user migrating https://eng.uber.com/mysql-migration/ https://eng.uber.com/mysql-migration/ And there are tons more. In fact here is a tool to help you do it https://github.com/pivotal-cf/pg2mysql https://github.com/pivotal-cf/pg2mysql
    - spookthesunset 7y ago
      Uber also has a giant team dedicated to re-inventing slack. I'd take their engineering prowess with a grain of salt. Lots of people do dumb things for dumb reasons. Most developers I interact with, even the really good ones, are profoundly stupid when it comes to databases.