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

91–100 of 179 posts

Re: Do you really need foreign keys?

#91

Post by seadan83 »

norir wrote: post #6

I once worked somewhere that used rails in lieu of foreign keys. The result was a brittle nightly delete_orphaned records script as well as obscure user visible bugs. My team started adding foreign keys to our records and unsurprisingly caught bugs in our application code that otherwise would have been missed. Personally, I think the default should always be to usr foreign keys and only if you have a genuine scalabil…

This anecdote is not very surprising to me. Foreign keys are on a scale of DB constraints. I opt to add a maximum of constraints where I can. Adding a date that should be in the future - I'm adding a constraint! Did you mess up your time zone conversions & are trying to add a date in the past - the constraint catches it. At some point the constraints are strong enough where with high confidence the following statement can be said: "if the data exists in the database at all, it is correct & complete." This is the "database as fortress" principle, that only correct data should ever be allowed into the database to begin with.

Re: Do you really need foreign keys?

#92

Post by HideousKojima »

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?

Re: Do you really need foreign keys?

#93

Post by claytongulick »

jamestimmins wrote: post #33

Foreign keys are often the best (and only) documentation of the data model, and the only one guaranteed not to decay, so this is an important consideration when rethinking them. That said, I quite like this post and the way the author is thinking critically about software decisions most of us take for granted. Even if you decide that foreign keys are always best, this type of thinking is a great way to improve your u…

I agree - and I also appreciate the author's objectivity and focus on making the correct technical decision for the problem. Dogma is the bane of good architecture.

I completely agree with the idea that foreign keys aren't always recommended. For example, one of the reasons that MongoDB was so great at edge writes was because of the lack of referential integrity and the "document" as a row concept - denormalize the data into a document and store everything you need there.

That works great for high volume edge writes that you want to operationalize later, asynchronously. Sure, the data can be messy and may have errors - but many use cases tolerate this well.

The nice thing about modern postgres is that you get the best of both worlds, RDBMS stuff when you need it, and NoSQL stuff if you don't.

The author's point, which I think is an excellent one, is that just because it's in a database, it doesn't necessarily make sense to go full 4th normal form on everything.

Re: Do you really need foreign keys?

#94

Post by butlerm »

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…

If you do not particularly care about performance or have a great deal of headroom then database enforcement of referential integrity is great. Alternatively you could just write test cases to check for it and not pay the severe performance penalty.

The other major downside of database enforcement of referential integrity is the common need to drop and re-create foreign keys during database schema upgrades and data conversions.

Re: Do you really need foreign keys?

#95

Post by seadan83 »

jamestimmins wrote: post #33

Foreign keys are often the best (and only) documentation of the data model, and the only one guaranteed not to decay, so this is an important consideration when rethinking them. That said, I quite like this post and the way the author is thinking critically about software decisions most of us take for granted. Even if you decide that foreign keys are always best, this type of thinking is a great way to improve your u…

+1 for that documentation.

I was recently working on a system that split data across multiple database instances (unnecessarily) and that means referential integrity is lacking and sometimes a huge problem.

To expound, I was tracking down something call a "slotid" and the absence of foreign keys does not mean anything. That data could very well live in some other table on the other DB instance. It turned out though that "slot-id" instead referred to a start time of day as counted by 'minutes-from-midnight'. Thus "slot-id=375" was just "6:15am"... Yup.. 3 hours of my life I will not get back to realize that data did not refer to anything at all. If everything that could have had foreign keys did, then it would have been a super quick investigation to realize the data was not a reference to anything at all.

Re: Do you really need foreign keys?

#96

Post by arp242 »

binarymax wrote: post #80
arp242 wrote: post #77

Earlier quoted context omitted.

There's also an impact on inserts; it's not just deletes. Essentially a foreign key constraint is an on {insert,update,delete} trigger which checks whether the target check exists, so that's a select on the target table. I'm not sure if that's still the case, but I believe that for a long time foreign keys were just implemented as triggers in PostgreSQL. For a lot of things that has a minimal performance impact and i…

FKs are typically primary keys/clustered indices on the related table, so in those cases the integrity overhead would be very minimal. So in the vast majority of cases, the integrity is worth it.

I assume you mean "performance overhead"? For insert-heavy workloads the difference very much is not "very minimal". In a quick test it's ~100ms vs. ~20ms with two foreign keys which check against a "countries" table (250 rows) – one column for nationality and one for residence.

±a lot because this is a very quick test I ran just twice, but it fits what I measured before when I very much ran in to this performance penalty a few years back.

And look, 20ms vs. 100ms is very much fine for a lot of use cases. But it's also not for a lot of others. And it's certainly not very minimal, and with many inserts it does amplify a lot.

Re: Do you really need foreign keys?

#97

Post by forinti »

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…

That's the best advice: data will outlive the application.

It will also serve other applications.

So treat it as its own thing, not as an appendix of the application.

Re: Do you really need foreign keys?

#98

Post by hermanradtke »

butlerm wrote: post #94
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…

If you do not particularly care about performance or have a great deal of headroom then database enforcement of referential integrity is great. Alternatively you could just write test cases to check for it and not pay the severe performance penalty. The other major downside of database enforcement of referential integrity is the common need to drop and re-create foreign keys during database schema upgrades and data c…

> you could just write test cases to check for it

This effectively means you are building an embedded database in your application and using the networked database for storage. There are a few reasons to do this and a million reasons not to.

Re: Do you really need foreign keys?

#99

Post by forinti »

A colleague once insisted in not using FKs because he read somewhere that that was the modern way of doing things.

So the universe decided to punish him and a few weeks later some data got deleted that would have been protected by an FK.

This idea is no longer discussed.

Re: Do you really need foreign keys?

#100

Post by dasil003 »

butlerm wrote: post #94
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…

If you do not particularly care about performance or have a great deal of headroom then database enforcement of referential integrity is great. Alternatively you could just write test cases to check for it and not pay the severe performance penalty. The other major downside of database enforcement of referential integrity is the common need to drop and re-create foreign keys during database schema upgrades and data c…

"just write test cases to check for [referential integrity]" is doing some awful heavy lifting in this comment.

Assuming a standard n-tier application architecture, how do you guarantee the test prevents race conditions?

Post reply on HN