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

111–120 of 179 posts

Re: Do you really need foreign keys?

#111

Post by setr »

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’re still going to pay the cost of maintaining referential integrity — you’re just doing it on the app side. You can do it faster by being not-correct — eg you don’t need a lock if you ignore race conditions — but it’s not like the database is arbitrarily slow at doing one of its basic fundamental jobs.

Of course, you can just skip the validation altogether and cross your fingers and hope you’re correct, but it’s the same reasoning as removing array bounds checking from your app code; you’ve eked out some more performance and it’s great until it’s catastrophically not so great.

Your reasoning should really be inverted. Be correct first, and maintain excessive validation as you can, and rip it out where performance matters. With OLTP workloads, your data’s correctness is generally much more valuable than the additional hardware you might have to throw at it.

I’m also not sure why dropping/creating foreign keys is a big deal for migrations, other than time spent

Re: Do you really need foreign keys?

#112

Post by SkyPuncher »

mavelikara wrote: post #64
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.

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

Re: Do you really need foreign keys?

#113

Post by Akronymus »

DasIch wrote: post #101
eddd-ddde wrote: post #71

Earlier quoted context omitted.

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…

If you operate at the scale where you consider the performance implications of foreign keys, you probably are not ok with anyone accessing the database or running just any query against it. It is not realistic that you can trust everyone who needs to access the data with access to the database as they might easily cause problems with poorly written queries. Additionally you may want invariants maintained, that the da…

> If you operate at the scale where you consider the performance implications of foreign keys, you probably are not ok with anyone accessing the database or running just any query against it.

I used to work on an application where ALL database accesses were via stored procedures. Genuinly the best dev experience, to me, so far.

Re: Do you really need foreign keys?

#114

Post by winnebago »

goostavos wrote: post #32
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.

I would argue that if you have completely different contexts / business lines / concerns (customer service, accounting, biz dev) all directly accessing the same database you have far, far larger architectural concerns that FK will ever hope to address.

Different business units often want aggregate, filter, join and transform data in different ways - sometimes in ways that are hard to anticipate.

In general, the closer to the persistence layer you can perform those transformations, the better they will scale. If you pull the transform into the app layer, you need to move and serialize more data. If you pull the transformation into a constellation of apps, you need to move and serialize a constellation of data.

(edit: formatting)

Re: Do you really need foreign keys?

#115

Post by cdcarter »

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 modelers choose to make a relationship e.g. leave dangling records or have other update/delete patterns that are appropriate for the data and data volume. Obviously there are cleanups and tradeoffs required here. It's certainly not a model I'd suggest people start with, but there really are times where database native FKs aren't the answer.

Re: Do you really need foreign keys?

#116

Post by butlerm »

dasil003 wrote: post #100
butlerm wrote: post #94

Earlier quoted context omitted.

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?

Typically you look for orphan rows - the sort of thing ON DELETE CASCADE was invented to prevent. Another thing to check for are records that need to exist but should have references cleared when something else is deleted, e.g. ON DELETE SET NULL. And the third thing is ON DELETE RESTRICT.

You can check for the first two of those things after the fact, and they are relatively benign. In many cases it will make no difference to application queries, especially with the judicious use of outer joins, which should be used for all optional relationships anyway.

If you need ON DELETE RESTRICT application code should probably check anyway, because otherwise you have unexpected delete failures with no application level visibility as to what went wrong. That can be tested for, and pretty much has to be before code that deletes rows subject to delete restrictions is released into production.

As far as race conditions go, they should be eliminated through the use of database transactions. Another alternative is never to delete rows that are referred to elsewhere and just set a deleted flag or something. That is mildly annoying to check for however. Clearing an active flag is simpler because you usually want rows like that to stay around anyway, just not be used in new transactions.

Re: Do you really need foreign keys?

#117

Post by jamestimmins »

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…

Do you think this has served y'all well in the long term?

I assume "internal data modelers" is an actual role of person whose job is to ensure long run data integrity?

Re: Do you really need foreign keys?

#118

Post by butlerm »

jrumbut wrote: post #107
dasil003 wrote: post #100

Earlier quoted context omitted.

"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?

You either end up reinventing foreign keys, your support volume will scale faster than your data, or user experience will suffer. There may be situations where foreign keys become too much overhead, but it's worth fighting to keep them as long as possible. Data integrity only becomes more important at scale. Every orphaned record is a support ticket, lost sale, etc.

Orphaned detail records are usually inconsequential, like uncollected garbage. References to anything with an optional relationship should use outer joins as a matter of course. If you delete something that really needs to be there you have a problem, which is one of the reasons not to delete rows like that, ever, but rather to mark them as inactive or deleted instead.

Re: Do you really need foreign keys?

#119

Post by jbmsf »

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…

100%.

Re: Do you really need foreign keys?

#120

Post by dasil003 »

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.

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 that owns the source-of-truth, but in many cases it's trivial and minimizes coordination overhead.

Write access on the other hand is a different story. IMO restricting write access of a database to a single application is table stakes for scaling complex applications. Sure there are other approaches such as writing all your logic in the database via constraints and stored procedures and making the DBA a god-like figure, but these approaches have fallen out of favor as they've proven less scalable compared to wrapping a DB with a service that has exclusive write access. The latter arrangement allows many constraints to be enforced in a horizontally scalable and more legible layer, while still leveraging the DB to prevent races with a better menu of tradeoffs.

Of course this requires thoughtful service and interface design by competent technical domain experts, which is easier said than done, but the alternative allows the overall system cohesion to degrade to where no one understands the system well enough to make any changes without risking major incidents. At that point, the agency of system builders and maintainers is replaced by care and feeding of the unknowable system to not upset the status quo, accompanied with increasingly byzantine hacks and workarounds to enable any business changes.

Post reply on HN