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

141–150 of 179 posts

Re: Do you really need foreign keys?

#141

Post by mastazi »

Despite the title, this article is about dropping foreign key constraints, not about dropping foreign keys altogether (which is possible, see below).

You can drop foreign key constraints and enforce referential integrity in your code, but from a logical standpoint your foo.barId column is still a foreign key.

Actually getting rid of foreign keys on the other hand can be done in a data store that allows nested structures, like e.g. a document based store. The price to pay is data duplication and denormalisation (and the related complexity you will have to deal with in all write operations), the pros are ease and speed of retrieval.

Re: Do you really need foreign keys?

#144

Post by mikl »

Do you really need referential integrity?

Do you really need consistent data?

Do you really need test coverage?

I mean, you can make do without it. But it’s generally better to have it than not.

Re: Do you really need foreign keys?

#145

Post by pmontra »

dasil003 wrote: post #120
erik_seaberg wrote: post #27

Earlier quoted context omitted.

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.

Interesting that this whole thread makes no distinction between read and write access, as those are dramatically different use cases. Read access is by far the more necessary, and is usually solved relative easily by saving snapshots to a data warehouse. This is no panacea as data can still easily be misinterpreted or replicated and used out of context in violation of expected production data lifecycle by the team th…

The no foreign keys advice tastes of 2005/2010 when the lack of support in some frameworks (Rails included) prompted developers to dismiss them as unnecessary. This piece of advice at least quantifies the terms of the tradeoff.

Regarding the correct "saving snapshots to a data warehouse", if there are one million web apps in the world, how many of them have the scale to noticeably benefit from either doing without foreign keys or from a data warehouse? I've been using many of them like everybody else but they are totally irrelevant to the long tail of apps and developers.

By the way, a good DBA can make miracles for the performances of most databases in that long tail. A few days of work are worth the cost especially if the team pays attention and learn the lesson. The best remark I got about a DB of mine was a "not bad for being only a developer". The DBA version was better.

Re: Do you really need foreign keys?

#146

Post by cogman10 »

ndriscoll wrote: post #138
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…

Batch processing is also WAY faster (like 1-2 orders of magnitude), but for whatever reason people don't talk about it much (cynically, I guess because it's too straightforward and old-hat). As far as I can tell, Rails doesn't have multithreading/async, so the OP is presumably not batching requests. That seems like a much better place to start than giving up referential integrity.

We are batch processing. I don't know why you assume we aren't. FK constraints are simply too much burden for us. I'm not the OP, however, my company has experienced the performance headaches of FKs.

Re: Do you really need foreign keys?

#147

Post by pixl97 »

arrowleaf wrote: post #42
erik_seaberg wrote: post #27

Earlier quoted context omitted.

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.

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.

Re: Do you really need foreign keys?

#148

Post by ttfkam »

pmontra wrote: post #145
dasil003 wrote: post #120

Earlier quoted context omitted.

Interesting that this whole thread makes no distinction between read and write access, as those are dramatically different use cases. Read access is by far the more necessary, and is usually solved relative easily by saving snapshots to a data warehouse. This is no panacea as data can still easily be misinterpreted or replicated and used out of context in violation of expected production data lifecycle by the team th…

The no foreign keys advice tastes of 2005/2010 when the lack of support in some frameworks (Rails included) prompted developers to dismiss them as unnecessary. This piece of advice at least quantifies the terms of the tradeoff. Regarding the correct "saving snapshots to a data warehouse", if there are one million web apps in the world, how many of them have the scale to noticeably benefit from either doing without fo…

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 that, and decades later where Oracle controls MySQL (and arguably is doing a better job), I still hold a grudge against MySQL that I have to actively suppress when the contract demands it.

Re: Do you really need foreign keys?

#149

Post by ndriscoll »

cogman10 wrote: post #146
ndriscoll wrote: post #138

Earlier quoted context omitted.

Batch processing is also WAY faster (like 1-2 orders of magnitude), but for whatever reason people don't talk about it much (cynically, I guess because it's too straightforward and old-hat). As far as I can tell, Rails doesn't have multithreading/async, so the OP is presumably not batching requests. That seems like a much better place to start than giving up referential integrity.

We are batch processing. I don't know why you assume we aren't. FK constraints are simply too much burden for us. I'm not the OP, however, my company has experienced the performance headaches of FKs.

I didn't assume anything about you; I said OP uses a framework that (as far as I know) makes it difficult to batch requests, so they're probably not batching requests. And they mention doing a `before_create` hook, which again suggests they're operating on individual models. If you're already batching and still running into a wall, then yeah you might need to remove constraint validation or shard. But batching alone can probably take most people far past the scale they'll ever see.

Re: Do you really need foreign keys?

#150

Post by blowski »

winnebago wrote: post #105
Pet_Ant wrote: post #84

Earlier quoted context omitted.

The _data_ yes, the _database_ no. The data will get migrated to different solutions at different times. I've lost track of how often I've been porting MySQL to SQL Server, SQL Server to Postgres, Postgres to Mongo, Mongo to cloud etc. EDIT: fixed capitalization of "PostGres"

This really depends on the size of your data set. If you have a large database (TBs+) it's likely to be very long-lived due the effort/hardware resources a migration requires - especially if you want to improve the schema when migrating.

I’ve seen a database migration project outlive application rewrites, multiple changes in CTO, and the company being acquired.
Post reply on HN