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

Do you really need foreign keys?

shayon.dev

Post reply on HN
Print view View original

171–179 of 179 posts

Re: Do you really need foreign keys?

#171

Post by bborud »

erik_seaberg wrote: post #27
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…

Whenever anyone talks about “the application,” I immediately ask “what about all the other applications?” I promise you that you will find customer service and accounting and biz dev have also built stuff that uses the database to get their jobs done (probably not with the same ORM or even the same language) unless you have taken draconian measures to prevent them.

If people use the database as an integration layer, IMNSHO, they deserve what they get. Database is implementation - not integration interface.

Re: Do you really need foreign keys?

#172

Post by bhaak »

GoblinSlayer wrote: post #170
ch4s3 wrote: post #104

Earlier quoted context omitted.

This would be a total waste of effort when you need to be building a product and iterating. I hate articles like this because they do a poor job contextualizing the tradeoffs and when it might be appropriate to do the weird exceptional thing. IMHO if you have a performance critical case when foreign keys are in the way, load THAT data into an in memory DB on a recurring basis and server time sensitive requests from t…

Foreign keys are slow on delete, not on read. If you have a popular table, say, users, and all other tables refer to it, then deleting a user locks the database for time proportional to the number of foreign keys - good old linear scaling.

It doesn't lock the database, it only locks the rows of the tables that have foreign keys to the popular tables and are referencing the user you are deleting.

Rightly so because when deleting the user the database needs to do work to keep the referential integrity. Either it nulls the user_id, delete the rows, or it throws an error.

Re: Do you really need foreign keys?

#173

Post by shayonj »

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…

Hi! The author here. I appreciate your reply here and in few other threads for this post too. I think they are very well thought out. I also like how it brewed more branches of other good discussions.

I totally agree that this decision isn't to be taken lightly. That said, I figured I'd also take this opportunity to clarify a few things:

- My post aims to prompt developers to critically assess whether the constraints they employ are truly essential or just conventionally used without question. They should weigh these constraints against the considerations mentioned in the post.

- While I recognize that lock contention is the database's way of managing application code, I propose that if foreign keys aren't necessary from the outset, then lock contention may only serve to impede performance. If you can rewrite your application code, say with `FOR UPDATE SKIP LOCKED`, great!

- I am also not recommending to drop all FK constraints, but to challenge and see if each one of them is necessary, and if it is, also good.

- As always, there is no solution fits all!

Re: Do you really need foreign keys?

#174

Post by solumunus »

HideousKojima wrote: post #92
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…

>My rule of thumb has been: enable them strictly in DEV and INT environments, disable in PROD I mean I think the constraints should be on in all environments, but disabling them in prod but not dev seems utterly backwards? Protect your test data from getting corrupted but not your actual customer data?

I assume the idea is that data corruption issues will be spotted in dev testing.

Re: Do you really need foreign keys?

#175

Post by ttfkam »

pmontra wrote: post #167
ttfkam wrote: post #148

Earlier quoted context omitted.

If I recall correctly, "no foreign keys" hit its stride back in early PHP days when MySQL didn't support them properly. Rather than cop to the fact that MySQL just implemented them badly, MySQL AB went on a dev PR run telling folks that foreign keys weren't actually useful and just slowed a system down. Once MySQL implemented them less horribly, the PR push finally started to die down. I will never forgive them for t…

Nobody with a Computer Science or especially a Software Engineering degree should have felt for it but I know a number of good developers with a more varied background. Some of them inevitably became team leaders etc. By varied backgrounds I mean Philosophy, Agricultural Sciences, Graphic Design. Some of them know very well how a database work, some admit to never have learned SQL, go figure all the theory and the ra…

My degree is in literature. (I'm that dev who also writes docs, makes presentations, and serves as tech lead.) I still know that foreign key constraints should be the default. I even often have to remind those with CS and CE degrees that DBs aren't just dumb bit buckets and that database schemas are critical business logic.

    Bad programmers worry about the code. Good programmers worry about data structures and their relationships. – Linus Torvalds

Re: Do you really need foreign keys?

#176

Post by HideousKojima »

solumunus wrote: post #174
HideousKojima wrote: post #92

Earlier quoted context omitted.

>My rule of thumb has been: enable them strictly in DEV and INT environments, disable in PROD I mean I think the constraints should be on in all environments, but disabling them in prod but not dev seems utterly backwards? Protect your test data from getting corrupted but not your actual customer data?

I assume the idea is that data corruption issues will be spotted in dev testing.

That sounds like a terrible assumption to stake your business on though, especially for what are really marginal gains for the 99% of companies who don't need the tiny performance boost from not having foreign keys.

Re: Do you really need foreign keys?

#177

Post by ch4s3 »

GoblinSlayer wrote: post #170
ch4s3 wrote: post #104

Earlier quoted context omitted.

This would be a total waste of effort when you need to be building a product and iterating. I hate articles like this because they do a poor job contextualizing the tradeoffs and when it might be appropriate to do the weird exceptional thing. IMHO if you have a performance critical case when foreign keys are in the way, load THAT data into an in memory DB on a recurring basis and server time sensitive requests from t…

Foreign keys are slow on delete, not on read. If you have a popular table, say, users, and all other tables refer to it, then deleting a user locks the database for time proportional to the number of foreign keys - good old linear scaling.

Sorry I should have specified read/write and side that you can read from something in memory that is consistent after some short delay.

Re: Do you really need foreign keys?

#179

Post by Fire-Dragon-DoL »

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…

I am fine with the concept of foreign keys, but if you have a perfectly designed database, it should be really hard to insert a row in any table by definition.

What would solve this is defaulting to raise foreign key constraints errors at the end of transaction rather than when rows are inserted. I know postgres has an option for it, it should be the default

Post reply on HN