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

221–230 of 337 posts

Re: We do not use foreign keys (2016)

#221

Post by spookthesunset »

lugg wrote: post #214
Aeolun wrote: post #174

Earlier quoted context omitted.

The whole fact that you made a foreign key implies that that is now a rule of your database. If anything is in that column that does not adhere to the foreign key, that means your database is corrupted.

Yes, and what if you don't make a foreign key?

Then you have an implicit rule that is enforced by a hope and a prayer that some junior dev never makes a mistake, your senior engineers are clairvoyant and understand every single aspect of your systems 100% with zero off-days, your code review process catches every single possible edge case (especially the edge cases that you never knew existed), your QA process is 100% and never makes mistakes, your servers never crash in ways that leave things in an inconsistent state, etc.

Or you could, you know, simply add a foreign key constraint and never, ever, ever have corrupt table relations.

Why people fight their tools is beyond me. There is almost zero reason to defend not using foreign keys.

Re: We do not use foreign keys (2016)

#222

Post by spookthesunset »

LaGrange wrote: post #162
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…

> Reporting and bi data might get hosed. > Account management might get hosed. LOL, if I read the data directly from the db instead of via the application's API then sure, I lose the application's guarantees. But, y'know, same might be said if I just go and read the DBs files from a sidecar shell script or something. > It puts all applications on top into a undefined state ...that's just an assumption that's false.

If you want to operate assuming your data is always corrupt because your engineers don't understand how to use the tools provided by their database.... Seems like an awful lot of work to re-invent a wheel that your DB server can solve for you. I guess that is on you though....

Re: We do not use foreign keys (2016)

#223

Post by yellowapple »

Hamuko wrote: post #61
brlewis wrote: post #31

> It may even rely on FK to cascade deletes (shudder) Is this just the author's personal taste, or is there something about mysql that makes ON DELETE CASCADE a bad idea? In postgresql it's a useful tool for maintaining database consistency.

I'm guessing it's the possibility for data loss. So someone accidentally deletes a user and it cascade deletes all invoices referenced to that user (deleting invoices is a big no-no in accounting).

Okay, then don't do ON DELETE CASCADE. If you really want to allow the user to be deleted entirely, then ON DELETE SET NULL (or ON DELETE SET DEFAULT pointing to the ID of some "null" user) should do the trick. Otherwise, if you want to retain the customer info (which if you're saving the invoices you almost certainly do), then ON DELETE RESTRICT is what you want. This is all defined in the context of the invoice's schema, mind you, so you'd have to make a conscious effort to cascadedly delete your invoices on user deletion when defining that foreign key constraint.

Use the database to do your bidding. Easier to stay sane that way :)

Re: We do not use foreign keys (2016)

#224

Post by madelyn »

corebit wrote: post #71
taurath wrote: post #25

Earlier quoted context omitted.

In the same vein, I'd like to remind people that you are probably not a "temporarily low-scale big-data company", in the same vein as a temporarily embarrassed millionaire. 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. CQRS is one of the…

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…

Exactly, CQRS gets such a bad rep because of all the pieces that (typically) surround it. But really as just a way of organizing code, it's so handy.

The freedom and flexibility to create your read models and write models the way they are needed just completely sidesteps a lot of design issues that creep up. Plus, it makes the code so easy to follow when everything follows the pattern; picking out where state changes happen becomes trivial for example.

Re: We do not use foreign keys (2016)

#225

Post by spookthesunset »

dkhenry wrote: post #216
papln wrote: post #189

Earlier quoted context omitted.

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

Well here is one case of a large scale user migrating 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

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.

Re: We do not use foreign keys (2016)

#226

Post by michelinman »

conroy wrote: post #5

When posts like these come up, I'd like to remind people that context matters when making technical decisions. What works for large companies with huge scale (GitHub, Google, Facebook) may not work for you. As a counter point to the linked issue, I operate a few small applications. Foreign-keys (and constraints in general) are great at ensuring that invalid data doesn't find its way into your database. Yes, they have…

I agree. No two systems are comparable. Must be very confusing for the people ramping up. A solution fits the problem, not the other way around. Keep it simple stupid. KISS

Re: We do not use foreign keys (2016)

#227

Post by hinkley »

taurath wrote: post #25
conroy wrote: post #5

When posts like these come up, I'd like to remind people that context matters when making technical decisions. What works for large companies with huge scale (GitHub, Google, Facebook) may not work for you. As a counter point to the linked issue, I operate a few small applications. Foreign-keys (and constraints in general) are great at ensuring that invalid data doesn't find its way into your database. Yes, they have…

In the same vein, I'd like to remind people that you are probably not a "temporarily low-scale big-data company", in the same vein as a temporarily embarrassed millionaire. 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. CQRS is one of the…

I like where you're going for that. Why don't we just call it 'temporarily embarrassed big data company'?

On one project where we seemed to handle engineering strategy well, one of the considerations we'd make is whether paying the tech debt would accompany an inflow of cash or not. If it didn't we'd better tackle it now so we don't bleed out.

It's probably another way to say "spend money to make money". Management is more willing to spend out of a problem when they've had a fresh fix.

The trick is, though, can you pay down that debt fast enough that the customers don't get upset while waiting. If someone's firing up a big ad push you'd better be prepared. And people, like me, who get caught flat-footed once (or have friends who were), don't want it to happen a second time. So they get opinions on 'last responsible moment' that might differ from the idealists.

Re: We do not use foreign keys (2016)

#228

Post by spookthesunset »

barrkel wrote: post #212
munk-a wrote: post #83

Earlier quoted context omitted.

This approach is quite scary to me and I would have argued very vocally against their rejections of FKs. There are ways to rely on replication for read serving, periodic disabling of FKs during batch inserts, FK integrity checks on replications, ... these can all address the performance issues inherent in FKs, it's also (generally) quite possible to attempt to architecturally disentangle too large networks of interde…

FKs add read locks to referenced rows. It limits concurrency and it is observable. FKs constrain your ability to incrementally widen 32-bit FKs once you go over 2 billion rows, if you start out with 32-bit PKs. Two concrete reasons to avoid FKs in production, or at least disable them for longer running transactions. I think this perspective is something you only get once you've run bigger databases in production.

This sounds like an implementation specific detail (no doubt for mysql) that for all you know has been fixed in later versions but gets passed around as if it is a permeant truth that applies to all database servers.

Re: We do not use foreign keys (2016)

#229

Post by redact207 »

rhinoceraptor wrote: post #144
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…

I had a job where the database was intentionally lacking FKs so that users could input data out of order, for example create a shipping route Foo that goes to a warehouse Bar, before the warehouse existed. Let's just say it was a suboptimal design.

this just sounds like eventual consistency which can form part of a perfectly optimal design

Re: We do not use foreign keys (2016)

#230

Post by yellowapple »

redact207 wrote: post #95

One great thing about not enforcing FKs is that it makes integration testing a lot easier. You can load just the data you need to test with, and none of the FKs need to point to rows that exist that aren't within the testing scope. This approach isn't for everyone. It works well with DDD where aggregates form contextual table boundaries and is eventually consistent by default.

If you ain't testing against the full system state, then I'd be hard-pressed to call that "integration testing".

If you're generating the test data from scratch, then it shouldn't be hard to generate the dependent data while you're at it. If you're testing against (anonymized) production data, then it shouldn't be hard to pull the dependent data while you're at it. In either case, you should be validating the integrity of those data relationships as part of the test criteria.

Post reply on HN