Do you really need foreign keys? • Hacker News Board

Re: Do you really need foreign keys?

#51

Post by brlewis » Thu, Dec 21, 2023, 6:19 PM UTC

lrobinovitch wrote: post #14

This github issue is often linked when this topic is discussed: https://github.com/github/gh-ost/issues/331 > Personally, it took me quite a few years to make up my mind about whether foreign keys are good or evil, and for the past 3 years I'm in the unchanging strong opinion that foreign keys should not be used. Main reasons are: > * FKs are in your way to shard your database. Your app is accustomed to rely on FK to…

Note that this was written in 2016 in the context of a mysql-centric project. You will not find an "unchanging strong opinion that foreign keys should not be used" outside that context.

I haven't kept up with mysql enough to know if there are still good reasons to avoid foreign keys. I just stick with postgresql.

Re: Do you really need foreign keys?

#52

Post by shayonj » Thu, Dec 21, 2023, 6:20 PM UTC

BenoitP wrote: post #49

My rule of thumb has been: enable them strictly in DEV and INT environments, disable in PROD. They can catch schema discrepancies, but can impede ingestion rates. Also some referential errors are sort of ok in PROD, as long as it's only about not dropping user data; which can be dealt with later on (INT gets reset with PROD user data from a backup each week, it also helps in the restore plan, fk are enabled, errors a…

Very interesting! Thanks for sharing

Re: Do you really need foreign keys?

#53

Post by arrowleaf » Thu, Dec 21, 2023, 6:21 PM UTC

davidw wrote: post #31

Start with them, and if you're lucky enough to scale to a point where they're hurting things, reconsider.

And then, don't just drop the constraints because you read an article about how FK constraints don't work with a distributed relational model, take a careful look at the docs of whatever DB engine you're using. PostgreSQL 12 for example supports foreign keys with either side being a partitioned table.

Re: Do you really need foreign keys?

#54

Post by binarymax » Thu, Dec 21, 2023, 6:23 PM UTC

How often is data actually DELETEd from production databases? Unless following through with regulatory removal such as GDPR, it’s far better to use an isdeleted flag IMO (so you can un-delete if the action was a mistake).

Re: Do you really need foreign keys?

#55

Post by arrowleaf » Thu, Dec 21, 2023, 6:23 PM UTC

rpgmaker wrote: post #13

50 years onward and every innovation seems to break, rather than improve, the tried and true relational database model.

Implementations with skin in the game are still innovating and improving the 'tried and true' relational database model, IMO. PostgreSQL, MS SQL, MySQL, etc. consistently churn out new features that actually make sense, like FK constraint support for sharded/partitioned tables relevant to this discussion.

Re: Do you really need foreign keys?

#56

Post by somehnguy » Thu, Dec 21, 2023, 6:24 PM UTC

BenoitP wrote: post #49

My rule of thumb has been: enable them strictly in DEV and INT environments, disable in PROD. They can catch schema discrepancies, but can impede ingestion rates. Also some referential errors are sort of ok in PROD, as long as it's only about not dropping user data; which can be dealt with later on (INT gets reset with PROD user data from a backup each week, it also helps in the restore plan, fk are enabled, errors a…

To me that seems like an invitation for catastrophic bugs or problems to creep through and surprise you in prod, since the lower envs are running different a different configuration.

I suppose it's entirely dependent on the type of data you're working with though.

Re: Do you really need foreign keys?

#57

Post by mavelikara » Thu, Dec 21, 2023, 6:24 PM UTC

cogman10 wrote: post #40

nonethewiser wrote: post #34

Earlier quoted context omitted.

What would be the advantage of implementing it in the application? I only see disadvantages: - no data integrity check in the database - more complicated definition of foreign relation, or none at all, leaving possibility for deviant data - scatteted schema. Cant look at the db table and understand the entire model. Have to hunt in code an potentially across a lot of code. It just seems like a data integrity issue th…

The main advantage is it's WAY faster if you are writing a lot. The big disadvantage of foreign keys is they do verify integrity. That means making lookups on every write/update which can be very costly especially as the model becomes more complex. If you have a read heavy application with low levels of writes then by all means put in foreign keys to you hearts content. But if you are at a point where you have millio…

But you pay the cost in checking it in the application, as GP said. If so, it simply is moving the cost from db to application layer. Is there a reason the checks can be implemented more efficiently in the application than the DB can?

Re: Do you really need foreign keys?

#58

Post by dkjaudyeqooe » Thu, Dec 21, 2023, 6:25 PM UTC

endisneigh wrote: post #12

You don’t need foreign keys, no. But you do need referential integrity and foreign keys implemented and enforced by the database is usually the easiest. If you’re disciplined it’s not difficult to implement yourself in the application, but the challenge is if folks can access the database directly. If so, good luck, since it’s inevitable that they end up making modifications and break integrity. If one insists on not…

It's basically like saying you're ok with dangling pointers or orphaned records, or that you're awesome enough not to need these "slow", annoying safety features.

That's all fine, but we know how this ends up in reality.

Checking that child records that can exist only if there is a parent record are deleted when the parent is deleted and that the foreign key you're using in a record actually exists in the pointed-to table, for the price of a single index you very likely need anyway, is hardly a problem.

You don't need to cascade deletes or anything, that's a red herring, as is migration issues where you can turn off referential integrity while the process is performed.

Re: Do you really need foreign keys?

#59

Post by buttercraft » Thu, Dec 21, 2023, 6:25 PM UTC

goostavos wrote: post #24

Follow this advice with caution. Dropping foreign keys is effectively giving up part of the C in ACID. It should be done with very, very open eyes to the downsides. I'm not sure the author is selling the "when" side of this very well. Migrations are "hard" because the database is forcing you to handle correctness criteria that are easy to ignore. "Lock contention" is the database covering your sloppy ill-thought out…

> The data will very likely outlive the application that created it.

Yeah, if your front end is garbage, you can fix or rewrite it. If your data is garbage, you've lost. Depending on your requirements, of course.

Re: Do you really need foreign keys?

#60

Post by magicalhippo » Thu, Dec 21, 2023, 6:25 PM UTC

ww520 wrote: post #41

The performance gain by dropping foreign keys doesn’t hold water. You still have to do the referential checks in application or in the ORM code. Unless you meant dropping referential checks.

Might be a performance penalty as well.

We had some missing FK constraints, along with missing cleanup in code for a given table. Some customers had many millions of orphaned rows, while only thousands of live rows.

Certainly didn't help index scans...