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

151–160 of 179 posts

Re: Do you really need foreign keys?

#151

Post by setr »

butlerm wrote: post #132
mavelikara wrote: post #57

Earlier quoted context omitted.

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?

Referential integrity problems usually happen due to missing deletes, improper deletes, or references that should be cleared. The overhead of checking for the existence of referred to records in ordinary inserts and updates in application code is unnecessary in most cases, and that is where the problem is. Either you have to check to have any idea what is going on, because your key values are being supplied from an o…

> If you actually need to delete a row that might be referred to, the best thing to do is not to do that, because you will need application level checks to make the reason why you cannot delete something visible in any case. 'Delete failed because the record is referred to somewhere' is usually an inadequate explanation. The application should probably check so that delete isn't even presented as an option in cases like that.

I feel like this belongs to the same strategy as duplicating form-validation on frontend/backend. The frontend validations can't be trusted (they can be skipped over with e.g. curl POST), so backend validation must be done. But you choose duplicate it to the frontend for user-convenience / better reporting / faster feedback loop. The backend remains the source of truth on validations.

The same between database and application; the database is much more likely to be correct when enforcing basic data constraints and referential integrity. The application can do it, its just a lot more awkward because they're also juggling other things and have a higher-level view of the data (and the only real way to check you didn't screw up is to make your testcase do exactly the same thing... but be correct about it -- no one else is going to tell you your dataset got fucked. Also true in an RDBMS, but it's trivial to verify by eye, and there's only one place to check per relationship). Thus in my world-view, the database must validate, and the application can choose to duplicate validation for user-convenience / better reporting. The database remains the source of truth on validations. As an optimization, you remove the database validations, but at your own risk.

And then in a multi-app, single db world, then you really can't trust the application (validations can be skipped), so even that optimization is likely illegal. Or you do many-apps *-> single-api -> db, and maintain the optimization at the cost of pretty much completely dropping the flexibility of having an RDBMS in the first place

Re: Do you really need foreign keys?

#152

Post by _a_a_a_ »

senderista wrote: post #136
_a_a_a_ wrote: post #123

A better post than I expected. The only thing I'd add is that foreign keys can actually improve read times because the optimiser knows it can safely skip certain joins e.g. if you have inner equi-joins between tables a, b and c a join b join c If there is an FK from a to b, and likewise from b to c, and you don't use anything in b, then the optimiser can rewrite this to a join c YMMV

Yes, I remember using foreign key relationships to optimize symmetric hash join back when I worked on an OLAP db. The idea was that for a 1:1 relationship you can immediately discard both sides of a joined tuple, while for a 1:many relationship you can immediately discard the "many" side of a joined tuple.

thanks for the detail!

Re: Do you really need foreign keys?

#153

Post by oxfordmale »

cdcarter wrote: post #115

The Salesforce CRM application somewhat "famously" does not use native DB foreign keys to model most relationships, and has a custom relational integrity and indexing layer. Mostly, this is in order to support our complex custom schema functionality, described in the Multitenant Whitepaper here ( https://www.developerforce.com/media/ForcedotcomBookLibrary/... ) But interestingly, this also lets our internal data mode…

Salesforce storage is about 1000 times more expensive than regular storage, so might not be the best example.

Data volume and Salesforce also don't belong in the same sentence, as it is not comparable to the data volume a basic Postgres database can handle.

Until recently Salesforce didn't provide a data backup facility, other than exporting CSVs, so not even sure you can call it a database.

Re: Do you really need foreign keys?

#154

Post by marcosdumay »

karmakaze wrote: post #73

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 als…

> this has rarely been an issue

I have seen many people running MySQL make that claim about lots of things... But what I have never seen is a MySQL database for business data without major issues.

Re: Do you really need foreign keys?

#155

Post by sgarland »

karmakaze wrote: post #73

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 als…

Depends on your definition of large-scale, but I managed a 120K QPS MySQL 8.x cluster that heavily used FKs. After heavy optimization and parameter tuning, I was also able to halve the instance size while improving query performance.

I’m a big fan of FKs. They stop you from doing stupid shit, and if the time ever truly comes that you have to drop them, you can do so.

Re: Do you really need foreign keys?

#156

Post by arrowleaf »

pixl97 wrote: post #147
arrowleaf wrote: post #42

Earlier quoted context omitted.

My assumption (with modern applications!) is that nothing but the role directly owning the data will access the data. The development and DBA teams will likely have a role they can assume after performing a carefully-audited breakglass procedure to use in an emergency (rare) or to fulfill audit tasks. At least in my org this is a well-known problem with legacy applications sharing databases. Limit access to the datab…

>Limit access to the database to a single role, used by a single application, and you absolve so many issues. This kinda sounds like "Get rid of 90% of the usefulness of having a database" Of course, maybe you mean make the same data the DB has available via API, or make other users of the data read only.

Yes, if you have downstream services that need to make use of your data, you would provide an API. That way you can manage the number of connections, how the queries are formed, any sort of encoding/decoding, authorization, caching, etc. Definitely not limited to read-only.

Re: Do you really need foreign keys?

#157

Post by yen223 »

cube2222 wrote: post #4

On that note, has anybody figured out a nice way to combine foreign keys and soft deletion (that is, a deleted_at column)? Soft deletion is occasionally useful, but losing foreign keys for it is a big pain. EDIT: got a bunch of responses, thanks! To be clear, the issue I have in mind is e.g. you want to have a foreign key that makes sure the “singular” side of a one-to-many relationship isn’t soft-deleted (on delete…

The `deleted_at` column approach to soft deletion has enough downsides that I would choose to just move "deleted" records into a different table.

Having a separate table for deleted records means that

- FK references to the main table will just work

- the main table can be kept clean.

- it avoids the class of problems where a user or the application treats soft-deleted records as real records, because they weren't aware of the `deleted_at` column.

Re: Do you really need foreign keys?

#158

Post by mavelikara »

SkyPuncher wrote: post #112
mavelikara wrote: post #64

Earlier quoted context omitted.

+1. When I started in the industry, it was common for more experienced developers to drill the “data outlives the application that generated it” principle into you. Somewhere in the transition to NoSQL and back we lost this.

Context is key. If you have an established business or startup, data will outlive the application. However, you need a product that lives long enough for either data or application to matter. In the startup world, that means making decisions that help you ship now, at the expense of debt/costs down the road.

IME, Foreign Keys do not slow down development velocity, after you factor out the upfront time investment it takes to understand them, normal forms, and other RDBMS concepts. Other posters in this thread have argued that they take CPU cycles during program execution - which is factual correct.

Re: Do you really need foreign keys?

#159

Post by yen223 »

iamflimflam1 wrote: post #89

Whenever I read posts like this I know, despite all the caveats at the start of the article, a large number of people will take away "we shouldn't have any foreign keys because they impact performance". I suggest that anyone thinking that has a look at a database where there are no foreign keys.

Looking at the comments here, almost everyone here are saying "you must ALWAYS use foreign key constraints", which I think is also not correct.

Re: Do you really need foreign keys?

#160

Post by mavelikara »

endisneigh wrote: post #63
mavelikara wrote: post #57

Earlier quoted context omitted.

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?

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

Your argument, if I understand it correctly, is that the constraints can be implemented in the application layer more efficiently - either in dev time or CPU time - than it can be in the DB layer. Why is that so, and under what circumstances?

(Asking to learn, not to argue)

Post reply on HN