Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

We do not use foreign keys (2016)

github.com

Post reply on HN
Print view View original

251–260 of 337 posts

Re: We do not use foreign keys (2016)

#251

Post by evanelias »

spookthesunset wrote: post #245
evanelias wrote: post #231

Earlier quoted context omitted.

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…

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 p…

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...

Re: We do not use foreign keys (2016)

#252

Post by wisnesky »

adrianmonk wrote: post #146
PopeDotNinja wrote: post #22

Because I've never thought about this, I'll ask the dumb question... A shopping cart has many items. An item belongs to a shopping cart. In a relational database, without foreign keys, how do you associate the shopping cart with the items?

It's confusion over the terminology. There are two definitions of "foreign key" in wide use: (1) When one table's key occurs as a value in another table, in that second table that column is sometimes called a foreign key. For example, table A has id, table B has A_id, and people refer to A_id as "a foreign key". (2) When you define this relationship explicitly in the database, so that the database can enforce it (and…

I agree, whether or not a given data model has foreign keys is a math question with a correct answer (up to isomorphism of the data model); if it does, where in a system they should or should not be enforced is an entirely separate, implementation (vs semantic) level issue.

Re: We do not use foreign keys (2016)

#253

Post by davidw »

barrkel wrote: post #210
davidw wrote: post #11

How about this: if your service gets as big as github, then maybe consider doing odd things to eke out more performance or shard or whatever. Otherwise: use FK's to maintain stronger data integrity. As hesk mentions below, in Postgres, you can do all kinds of table ALTERing if needs be.

You don't need a service as big as GitHub; you just need billions of rows. I think you should enable FKs for test and dev, and possibly QA, but disable them in production. Depending on your database, you'll get surprising interference in concurrent operations via other means than FKs, but FKs don't help.

"Just" need a billion rows.

Sure, some people get there, but it makes sense to do things the right way first, then figure out how to cut corners only when and if you need to.

Re: We do not use foreign keys (2016)

#254

Post by wisnesky »

Many people on this thread have asked if there is a rigorous way to migrate data between relational schemas that contain non-trivial foreign keys, and the answer is yes: In the open-source categorical query language CQL, data migrations necessarily respect foreign keys - a property guaranteed by the mathematics of category theory and the automated theorem prover that ships with CQL. http://categoricaldata.net

Re: We do not use foreign keys (2016)

#255

Post by macspoofing »

spookthesunset wrote: post #27

The number of times I’ve seen serious data corruption because “foreign keys are bad and we can just enforce it in code” is amazing. There is zero excuse to not use FK’s Any database that doesn’t use FK’s is almost guaranteed to have crap in it that didn’t get cleaned up, resulting in data corruption (and yes, dangling stuff in tables count as data corruption). Developers aren’t perfect. Shit will slip through even wi…

At the scale of GitHub, FKs may very well being more problems than benefits. For everyone else, FKs are a benefit.

Re: We do not use foreign keys (2016)

#256

Post by xref »

lugg wrote: post #211
spookthesunset wrote: post #34

Earlier quoted context omitted.

The application code isn’t enforcing FK constraints because that is impossible for the application to do. Their database is almost 100% guaranteed to be corrupt as a result. Application code has bugs. Application code can fail in ways that result in corruption. A primary job of the database is to keep itself from getting corrupted. Enforcing foreign key violations is only something the database can do correctly. Punt…

Will result in corruption. Yes. Will result in more fault tolerant software, also yes. It's a trade off and one I make willingly at every scale. I stopped using foreign keys after university and have never wanted them since. Non nullable database fields are far more useful than worrying about fks.

> Will result in more fault tolerant software

More fault tolerant because you have to waste time debugging the faults and monkeypatching them in code just to avoid FKs?

> Non nullable database fields are far more useful than worrying about fks

Can’t even count how many non-nullable fields I’ve seen packed with “” empty strings to get around that requirement

Re: We do not use foreign keys (2016)

#257

Post by rukuu001 »

Let's just consider some of the 'non-technical' issues surrounding the use of foreign keys.

I've encountered resistance because

- Using them would require up-skilling existing devs

- They'd highlight crap data coming in from other parts of the org, which would be politically uncomfortable

- DB would need to be re-configured

- We're agile now and we can't be locked into those kind of constraints

edit: formatting

Re: We do not use foreign keys (2016)

#258

Post by joevandyk »

hrktb wrote: post #107
sturgill wrote: post #78

Earlier quoted context omitted.

I’m pretty confident that GitHub doesn’t use foreign keys because it was built as a Rails app. And the “Rails Way” is to create these constraints in the model. Foreign key constraints weren’t a first-class member in Rails until v4 (if memory serves correctly). I was once a full-time Rails dev and really loved the framework (I don’t write as many user facing applications these days). Most of the Omakase trade offs did…

It’s at its core a case by case decision, I think FK are also a net negative in data ingestion scenari where the data set is big enough. Trying to make sure everything is where it needs to be at any given time, everything is inserted in the right order and the data is always consistent brings exponential amount of conplexity when it could all be checked at the end and pruned for invalid data. And usually DB integrity…

PostgreSQL supports deferring constraints. https://begriffs.com/posts/2017-08-27-deferrable-sql-constra...

Re: We do not use foreign keys (2016)

#259

Post by DaiPlusPlus »

evanelias wrote: post #163
matt4077 wrote: post #142

Earlier quoted context omitted.

It's been a while, but IIRC at the time Rails got started MySQL actually did not even support foreign key constraints. Since that was the DBMS of choice, it wasn't much of choice.

InnoDB's foreign key support predates the existence of Rails by several years. However, InnoDB wasn't the default storage engine for MySQL at the time, so that may be a factor.

I wonder how different the state of database application development would be today if all those cheap whitelabel webhosts powered by cPanel or Plesk (where most of us got started, I imagine) opted for PostgreSQL instead of MySQL - which would have influenced the major MySQL adopters like phpNuke, phpBB, WordPress, etc.

Re: We do not use foreign keys (2016)

#260

Post by jayd16 »

I'm curious what these comments think they're saying. What effort went into GitHub such that they feel they can make the claim that FKs are an anti pattern. Do they think they're just better than everyone else or is there some other practice that makes this claim more interesting than simple hubris? Me a
Post reply on HN