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

71–80 of 179 posts

Re: Do you really need foreign keys?

#71

Post by eddd-ddde »

arrowleaf wrote: post #66
eddd-ddde wrote: post #61

Earlier quoted context omitted.

I strongly believe the opposite. Your business IS your database. How do you handle access to the data without access to the data? It is inevitable that 2 "organizations" will at some point need access to the same data. You could just have each of them decide how they interact with it. It doesn't matter how they do it because the database itself makes sure invariants are kept true (such as FKs). What's the alternative…

Have a single application clearly owning the data. All access to the data is done through that application. Why would you _want_ multiple applications deciding how to handle data in a common store? What about access authorization to sensitive data?

Because that single application already exists, in my case its postgres.

Pgweb is one of my interfaces, admin dashboard for free, and I am sure the changes I make there are as valid as changes through any other interface.

I can implement a website as a SSR app that talks directly to the db. Maybe tomorrow I decide I need to work on a web scraper that will use python, instead of adding more API endpoints to allow the scraper to talk to the database, I just talk to the database...

The database is that "single appplication", no need to write custom endpoints for every operation when SQL is good enough.

It's all a spectrum of course, today you can even go as far as to use something like pg_graphql and you don't even need to write a REST api yourself.

Edit: I forgot to answer the last section, but postgres can totally handle autorization with RLS for instance, allowing users to only see their own data, or maybe data marked as public, etc.

Re: Do you really need foreign keys?

#72

Post by endisneigh »

LtWorf wrote: post #70
endisneigh wrote: post #63

Earlier quoted context omitted.

The reason is because the application knows what’s actually happening with the data.

It's supposed to know, but it actually doesn't. At my first job they used mongodb for no reason other than it was the fad at the moment, with the big data and so on. There were often crashes in the application because our records followed several different schemas, because of bugs in the application that were later fixed, behaviour that got changed, ORM got replaced with hand written code that was much faster… Basica…

There are plenty of counter examples. It’s simply a fact that your application can be faster without foreign keys.

Re: Do you really need foreign keys?

#73

Post by karmakaze »

Large scale MySQL databases I've worked on typically do not use foreign keys. The payoff is higher write performance. They're implicit based on table/column naming and relationships defined in code, e.g. Rails associations or process/operation classes.

With a certain level of team maturity and thoughtful reviews, this has rarely been an issue. Sometimes there are orphaned rows (from incomplete/buggy writes) which also have clean-ups that tend to have jobs for pruning data that has gone 'out of the retention window'.

Re: Do you really need foreign keys?

#74

Post by majkinetor »

mavelikara wrote: post #57
cogman10 wrote: post #40

Earlier quoted context omitted.

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?

Yes there is although way more complex.

U could use caching so not to hit db often and also app scales way easier than db.

Re: Do you really need foreign keys?

#76

Post by LtWorf »

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…

> as long as it's only about not dropping user data

Can you tell the name of the company? It's for my lawyer.

Re: Do you really need foreign keys?

#77

Post by arp242 »

binarymax wrote: post #54

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

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 it's not a big deal. For some other things it can really add up.

Re: Do you really need foreign keys?

#78

Post by tacone »

out_of_protocol wrote: post #67
shayonj wrote: post #18

Earlier quoted context omitted.

That is great and I noticed it’s something I have always assumed should be the case - no indirect access to the database outside your application / ORM. Because then you open a can of worms.

Doesn't help much though. It only takes not wrapping related inserts into transaction in one place in your code. i know zero frameworks/ORMs that can help you with that

I usually wrap everything (including reads) in a transaction at middleware level. Not perfect, has its downsides but for many projects it's perfectly ok.

Of course it's not a real substitute for foreign keys, but definitely better than nothing.

Re: Do you really need foreign keys?

#80

Post by binarymax »

arp242 wrote: post #77
binarymax wrote: post #54

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

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.

Post reply on HN