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

281–290 of 337 posts

Re: We do not use foreign keys (2016)

#281

Post by evanelias »

evanelias wrote: post #251
spookthesunset wrote: post #245

Earlier quoted context omitted.

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

And to clarify, I'm not saying that to toot my own horn. My point was, I primarily work on open source schema management and related consulting. I talk to large companies about MySQL schema changes literally every single day. The comment about "the lengths people go to avoid schema changes is astonishing" simply does not gel with reality among large-scale MySQL users.

As for the random blind accusations about bad code, engineer incompetence, etc that's just rude, mean-spirited, and misinformed. I personally know a lot of exceptional database engineers who work on MySQL-related infrastructure at the companies I listed above. Why crap on other people's work that you haven't seen and know nothing about?

Re: We do not use foreign keys (2016)

#282

Post by trhway »

Quekid5 wrote: post #215
corebit wrote: post #71

Earlier quoted context omitted.

The biggest issue with CQRS I've seen is people thinking CQRS means you need multiple, duplicate data structures, mappers, a few Kafka topics and a PhD, when IN REALITY all it means is you put methods that return data without modifying it in one interface/class and methods that have side effects in another interface/class - which is really just a good application of interface segregation. Moreover, you now have a gre…

Like you, I've found the general idea of CQRS/ES[0] incredibly valuable because it both enforces a functional approach to state (e.g. 'current state' is a fold over events), and it forces people to really think about the domain. (E.g. which bits are really atomic units, or what can be fixed up if it goes wrong in some way.) It also forces people to think about something that's usually glossed over: Consistency. If yo…

> any responsible application should track when that data was read and reject updates if the backing data has changed since it was read... but very few applications even attempt to do this (it's really hard and a huge amount of boilerplate with most APIs).

it is done very simple by versioning. Usually implemented transparently for the data layer API clients.

Re: We do not use foreign keys (2016)

#283

Post by tomlu »

malvosenior wrote: post #98

So basically they don't use foreign keys because they're into premature optimization. If you need to shard your database, you're big enough that you'll have the resources to engineer the new solution. The vast, vast majority of use cases will benefit from the database ensuring the integrity of your data.

Github is probably past the stage of premature optimization.

Presume (for argument's sake) that we accept the premise that foreign keys do not scale. Then the question is whether there exists some sort of realistic migration strategy once usage grows past that. If it does, then using FK makes total sense until we hit that point, doesn't it? And if true this should apply to 99% of users.

But if there exists no such migration strategy, then maybe the guy has a point?

Re: We do not use foreign keys (2016)

#284

Post by evanelias »

sturgill wrote: post #263
evanelias wrote: post #185

Earlier quoted context omitted.

> I’m pretty confident that GitHub doesn’t use foreign keys because it was built as a Rails app Maybe originally, but lack of foreign key usage is certainly not Rails specific today. Large MySQL shops generally don't use foreign keys, full stop, for the exact reasons Shlomi described in the original comment. Facebook does not use foreign keys either. In my experience, same thing is true at all the other large MySQL-b…

“[S]harding alone is very painful even without introducing a ton of new application-level constraint logic at the same time.” I typically espouse app logic to check state and use foreign keys to ensure enforcement (eg, race conditions that are very hard to ensure at the app level but are built into many RDBMS). Foreign key failures are just treated like any other failure mode. But honestly, I haven’t been part of a c…

I agree that most companies won't ever need to shard, and pre-sharding (or worrying about extreme scalability challenges in general) is usually unwise premature optimization. But it does depend on the product category.

Social networking and user-generated content products (including GitHub) need to be built with scale somewhat in mind: if the product reaches hockey-stick growth, a lot of scalability work will need to be completed very quickly or the viral opportunity is lost and the company will fail. I'm not saying they should pre-shard, but it does make sense to skip FKs with future sharding in mind.

This was nearly a decade ago, but in one case I helped shard my employer's DBs (and roll out the related application-level changes) with literally only a few hours to spare before the main unsharded DB's drives filled up. If we also had to deal with removing FKs at the same time, we definitely wouldn't have made it in time and the product literally would have gone read-only for days or weeks, probably killing the company. Granted, these situations are incredibly rare, but they really do happen!

Re: We do not use foreign keys (2016)

#286

Post by Iv »

megablast wrote: post #274
whack wrote: post #193

Earlier quoted context omitted.

> In lots of cases going for the very long term scalable solution will be an impediment to your growth, and I'd suggest dealing with those issues when the chance that you need them is on the horizon, rather than across the globe. My favorite example is a bootstrapped startup that I co-founded. We had a couple thousand users and less than a gigabyte of data. The app was a run-of-the-mill CRUD app. I built the entire b…

> At some point, I left the project, and my co-founder hired an expensive consultant to review the system and provide feedback. People get pretty unhappy if they hire a consultant and don't get some drastic change recommendations. "Everything is good" doesn't sit well when handing over cash.

As a consultant I have never been hired by someone whose system was working. I am willing to bet the consultant was hired to add a feature, could not figure the installed code, and proposed to redo it the only way he or she was used to.

Re: We do not use foreign keys (2016)

#287

Post by Iv »

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…

Thanks. As a mere occasional user of databases I was confused and trying to figure out how you could not use FKs without recreating essentially the same thing in code.

Re: We do not use foreign keys (2016)

#288

Post by Iv »

zenhack wrote: post #268
cryptonector wrote: post #171

Earlier quoted context omitted.

It won't on Linux! But hey, random processes will get OOM-killed.

You can turn off overcommit, in which case you'll get a null return instead of OOM killing. But also, you can still get a malloc failure without actually be running out of memory if the allocator can't find a big enough contiguous chunk of address space. This is highly unlikely on a 64 bit system, but if you try to malloc gigabytes on a 32 bit machine you might see it.

And also, you never know when this theoretical case actually happens but it did happen to me: at one point someone may use your code on an Arduino and unexpectedly, it works! But memory allocation could fail more than you expect!

Re: We do not use foreign keys (2016)

#289

Post by hrktb »

joevandyk wrote: post #258
hrktb wrote: post #107

Earlier quoted context omitted.

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

This looks nice, it’s still limit to a single commit though. That’s where it’s a PITA for anything that won’t (or we don’t want to) fit a single commit.

In particular splitting commits allows to ingest data in parralel (for instance if we import stores and store owners, both could be ingested separately without caring at first if each references a valid entity)

Re: We do not use foreign keys (2016)

#290

Post by dragonwriter »

matt4077 wrote: post #132
spookthesunset wrote: post #97

Earlier quoted context omitted.

Foreign key violations are data corruption!!! It violates the rules of how the data relates and can and will screw up any number of things that depend on the rules being enforced. Reporting and bi data might get hosed. Account management might get hosed. Who knows what happens when FK rules are violated because by definition they should never be violated. It puts all applications on top into a undefined state, leadin…

> Foreign key violations are 100% data corruption Sure, yes, of course, who cares? These are self-imposed rules, so breaking them is wholly up to yourself. And there are trade-offs involved that may often make it acceptable relax them. You seem to be rather invested in one set of arbitrary definitions. If people do fine even in situations where they "by definition" shouldn't, it's the definitions that have been found…

> Sure, yes, of course, who cares?

The stakeholder from whom you got the requirement on which the logical FK constraint is based, for one.

Post reply on HN